Creating a SQL Query with checkboxes

Question

I am new to SQL and PHP and trying to run a query.

I am struggling to put the correct operators in, and can’t figure out why it won’t work! I’ve been using AND OR CASE

I have 4 checkboxes: Bars, Clubs, Restaurants, Pubs.

In my database I have a name column and a column for each of the 4 words on my radio buttons. If the establishment is a Bar, then there is a 1 in the corresponding Bar column. If the establishment is a Pub, then there is a 1 in the corresponding Pub column. If it is both then it will have a 1 in both etc etc

This is the sort of thing it looks like:

Name  | Bar   | Club | Restaurant | Pub
———————————————————————————————————————
McD   |  0    |  0   |      1     |  0
KFC   |  1    |  0   |      1     |  0
SPN   |  1    |  1   |      1     |  1
GRG   |  0    |  1   |      1     |  0

When there is nothing checked I would like the result to show all the names of options.  When Bars is checked then Only the Bars When Clubs is checked then Only the Clubs etc etc

When Bars and Clubs are checked then I want to see all the Bars and Clubs etc etc

How do I get the the outcome I am looking for? Thanks so much for your help in advance!

This is the code I am working with at the moment (I have left the Bar variable in) :

<?php

error_reporting(E_ALL);

$servername = "**";
$username = "**";
$password = "**";
$dbname = "**";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} 

if(isset($_POST['submit'])) {

    $getselectClub = implode(",",$_POST['selectClub']);
    $getselectResturant = implode(",",$_POST['selectResturant']);
    $getselectBar = implode(",",$_POST['selectBar']);

$sql = "SELECT id, name, locale FROM theList WHERE bar='$getselectBar' ";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    // output data of each row
    while($row = $result->fetch_assoc()) {


?>

<?php echo $row['id'];?> &nbsp;
<?php echo $row['name'];?> &nbsp;
<?php echo $row['locale'];?><br>

<?php }}} ?>

<form method="post">
    Club: <input type='checkbox' name="selectClub[]" value="1" />
    Resturant: <input type='checkbox' name="selectResturant[]" value="1" />
    Bar: <input type='checkbox' name="selectBar[]" value="1"/>
    <input type="submit" name="submit" value="submit" />
</form>

Why are all your checkboxes arrays? And your SQL is only checking one condition. — miken32
– miken32, Commented Sep 18, 2018 at 22:56
Although it was fairly obvious in this case what the problem was, specifying an actual error message rather than "it doesn't work" is always helpful! — miken32
– miken32, Commented Sep 18, 2018 at 23:09

miken32 · Accepted Answer · 2018-09-19 01:50:56Z

A checkbox is not submitted if it's unchecked. So you check for its submission before adding it to the query. Adding [] to the end of a form input name submits it as an array, which is not something you need.

<?php

error_reporting(E_ALL);

$servername = "**";
$username   = "**";
$password   = "**";
$dbname     = "**";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} 

if(isset($_POST["submit"])) {
    $where = [];
    $query = "";
    if (!empty($_POST['selectClub'])) $where[] = "club=1";
    if (!empty($_POST['selectRestaurant'])) $where[] = "restaurant=1";
    if (!empty($_POST['selectBar'])) $where[] = "bar=1";

    // check in case no boxes were checked
    if (!empty($where)) {
        $query = "WHERE " . implode(" OR ", $where);
    }

    $sql = "SELECT id, name, locale FROM theList $query";
    $result = $conn->query($sql);

    if ($result->num_rows > 0) {
        // output data of each row
        while($row = $result->fetch_assoc()) {
            echo "$row[id] &nbsp; $row[name] &nbsp; $row[locale]<br/>";
        }
    }
}
?>

<form method="post">
    Club: <input type='checkbox' name="selectClub" value="1" />
    Restaurant: <input type='checkbox' name="selectRestaurant" value="1" />
    Bar: <input type='checkbox' name="selectBar" value="1"/>
    <button type="submit" name="submit" value="1">Submit</button>
</form>

Collectives™ on Stack Overflow

Creating a SQL Query with checkboxes

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related