Have 4 'ands' in a select statement

Question

I have a search function on my website with 4 checkboxes. These are then pasted to the next page where I want to find all products which match the criteria of the check boxes.

As I have 4 check boxes I want to use 4 'ands' but I believe 3 is the max (?)

How can I get around this so it searches to see if all products are matched?

HTML Form

---

<div id = "search">
            <form name = search action = "search.php" method = "POST">
            <p class = "big"> Refine Menu </p>
            <hr>
            <input type = "text" name = "search" placeholder = "Search for an item" size = "12">
            <input type = "submit" value = "Go"> 
            <br><br>
            <input type = "checkbox" name = "vegetarian"> Vegetarian 
            <br><input type = "checkbox" name = "vegan"> Vegan 
            <br><input type = "checkbox" name = "coeliac"> Coeliac 
            <br><input type = "checkbox" name = "nutFree"> Nut free 
        </form>
        </div>

PHP

---

<?php
session_start();
include "connection.php";
if(!isset($_SESSION["username"])){
    header("Location: login.php");
}

if(isset($_POST["search"])){
    $search = $_POST["search"];
}

if(isset($_POST["vegetarian"])){
    $vegetarian = 1;
}
else{
    $vegetarian = NULL;
}

if(isset($_POST["vegan"])){
    $vegan = 1;
}
else{
    $vegan = NULL;
}

if(isset($_POST["coeliac"])){
    $coeliac = 1;
}
else{
    $coeliac = NULL;
}

if(isset($_POST["nutFree"])){
    $nutFree = 1;
}
else{
    $nutFree = NULL;
}


$sql = "SELECT * FROM products WHERE vegan = '$vegan' and nutFree = '$nutFree' and vegetarian = '$vegetarian'  and coeliac = '$coeliac'";
$result = mysqli_query($con, $sql);


while($row = mysqli_fetch_assoc($result)){

    echo $row ["name"];

} 

I've tried a number of different thing but I don't know the correct syntax for the sql.

NOTE: In my database whether it meets the requierment on it is saved as either a 1 or 0 that is why I changed it from 'on' or 'off'

Answer 1

PHP's NULL have no significance when converted to a string (the SQL query), they will evaluate to empty and your query will look like nutFree = '' and vegetarian = '' and coeliac = ''.

If those fields are 0 in the database, you must set the variables to 0 then.

On a second case, if they are NULL in the database, you must change both your query and the way you define NULL here.

First, those string wrappers should go away. You don't need them for numbers anyway, those are supposed to wrap strings only:

$sql = "SELECT * FROM products WHERE vegan = $vegan and nutFree = $nutFree and vegetarian = $vegetarian and coeliac = $coeliac";

And then instead of setting the variables to NULL, you will set them to the string "NULL".

$nutFree = "NULL";

This will make NULL show on the SQL query as its expected to.

Answer 2

I managed to solve my problem. I was mistaken when I posted the question because the reason I thought my sql statement wasn't working was because there were too many ands and I didn't see that rather my sql didn't do what I thought it should.

Here is what I changed it to or it has set values or the check boxes ticked but always the ones which aren't to be either or.

Thanks for everyone's help!

<?php
session_start();
include "connection.php";
if(!isset($_SESSION["username"])){
    header("Location: login.php");
}

if(isset($_POST["search"])){
    $search = $_POST["search"];
}

if(isset($_POST["vegetarian"])){
    $vegetarian = 1;
}
else{
    $vegetarian = "  ";
}

if(isset($_POST["vegan"])){
    $vegan = 1;
}
else{
    $vegan = "  " ;
}

if(isset($_POST["coeliac"])){
    $coeliac = 1;
}
else{
    $coeliac = "  " ;
}

if(isset($_POST["nutFree"])){
    $nutFree = 1;
}
else{
    $nutFree = "  ";
}


$sql = "SELECT * FROM products WHERE (vegan = '$vegan' or vegan = 1 xor 0) and (nutFree = '$nutFree' or nutFree = 1 xor 0) and (vegetarian = '$vegetarian' or vegetarian = 1 xor 0) and (coeliac = '$coeliac' or coeliac = 1 xor 0)";
$result = mysqli_query($con, $sql);

while($row = mysqli_fetch_assoc($result)){
    echo $row ["name"];
}

Answer 3

Rather than a large, unmaintainable chain of if statements, you might consider something similar to the following, which will dynamically build up your query depending on which of your required fields have been checked in your form:

<?php
   $search_fields = array( 'vegetarian', 'vegan', 'nutFree', 'coeliac', ...);
   $ands = array( '1' => '1');

   foreach($search_fields as $req)
   {
      if(isset($_POST[$req]) && $_POST[$req] != '')
      {
        $ands[$req] = "$req = '1'";
      }
   }

   $and_part = implode(" AND ", $ands);
   $query = "select .... from ... WHERE $and_part  ... ";
?>