Building An AND OR SQL String From Checkbox Entries

I’ve got a list of food items HERE that is filterable by ticking checkboxes. It’s all working well but I can figure out how to group checkboxes together to build an AND OR sql statement rather than just an AND statement.

Eg. Ticking Pizza AND Burgers from the FOOD section + ticking Glossop AND Stalybridge from the AREA section currently writes WHERE cat_id=2 AND cat_id = 3 AND area = "Glossop" AND area ="Stalybridge" (Obviously returning no results)

I want it to write WHERE cat_id=2 OR cat_id = 3 AND area = "Glossop" OR area ="Stalybridge" giving me all the pizzas & burgers in Stalybridge & Glossop

Here’s the code for my submit.php file…

<?php 
  $pdo = new PDO('mysql:host=blah'); 
  $select = 'SELECT pub_name, area, course, item, description, price';
  $from = ' FROM food_items 
  
  			INNER JOIN directory 
				ON food_items.author = directory.author 
				
			INNER JOIN courses 
				ON food_items.course_id = courses.course_id
				
			INNER JOIN category_id 
				ON food_items.course_cat = category_id.cat_id';
				
  $where = ' WHERE TRUE';
  $orderby = ' ORDER BY food_items.course_id, directory.pub_name, food_items.id';
  $opts = isset($_POST['filterOpts'])? $_POST['filterOpts'] : array('');


  if (in_array("Stalybridge", $opts)){
    $whereArea .= ' AND area = "Stalybridge"';
  }
  
  if (in_array("Denton", $opts)){
    $whereArea .= ' AND area = "Denton"';
  }
  
  if (in_array("Glossop", $opts)){
    $whereArea .= ' AND area = "Glossop"';
  }

  
  
  if (in_array("Starters", $opts)){
    $whereCourse .= ' AND course = "Starters"';
  }
  
  if (in_array("Mains", $opts)){
    $whereCourse .= ' AND course = "Mains"';
  }
  
  if (in_array("Desserts", $opts)){
    $whereCourse .= ' AND course = "Desserts"';
  }
  
  if (in_array("Extras", $opts)){
    $whereCourse .= ' AND course IN ("Side Dishes", "Accompaniments")';
  }
    
  
  
  
  
  if (in_array("PubClassics", $opts)){
    $whereType .= ' AND cat_id = 1';
  }
  
  if (in_array("Pizza", $opts)){
    $whereType .= ' AND cat_id = 2';
  }
  
  if (in_array("Burgers", $opts)){
    $whereType .= ' AND cat_id = 3';
  }
  
  if (in_array("SundayLunch", $opts)){
    $whereType .= ' AND cat_id = 4';
  }
  
  if (in_array("FromTheGrill", $opts)){
    $whereType .= ' AND cat_id = 5';
  }
  
  if (in_array("Salads", $opts)){
    $whereType .= ' AND cat_id = 6';
  }
  
  if (in_array("LightBites", $opts)){
    $whereType .= ' AND cat_id = 7';
  }



  
  if (in_array("offer", $opts)){
    $whereOptions .= ' AND offer = 1';
  }

  if (in_array("kids_meal", $opts)){
    $whereOptions .= ' AND kids_meal = 1';
  }

  if (in_array("vegetarian", $opts)){
    $whereOptions .= ' AND vegetarian = 1';
  }
  
  $sql = $select . $from . $where . $whereCourse . $whereType . $whereOptions . $whereArea . $orderby;
  $statement = $pdo->prepare($sql);
  $statement->execute();
  $results = $statement->fetchAll(PDO::FETCH_ASSOC);
  $json = json_encode($results);
  
echo($json);
?>

I had limited success changing the arrays from $whereType .= ' AND cat_id = 1'; to $whereType .= '1,'; then re writing the $sql using rtrim to remove the lats comma as follows…

$sql = $select . $from . $where .
		' AND (cat_id in (' . rtrim($whereType, ",") . '))' . $orderby;

Pretty sure this isn’t the best way to go about it so was wondering if anyone had any suggestions?

Use a query builder (e.g. Doctrine DBAL). this makes creating conditional queries waaay easier.

I haven’t used a query builder, but I know things can get very complicated and real messy fast so I have little doubt that is very true.

You are concatenating the conditionals as Strings.

If you saved them in an Array you could implode them with " OR "

Did you ever have to add a WHERE clause that depended on joining a separate table? Query builders allow you to add/modify a query’s parts independently that makes these types of conditions possible.

Yes, and it wasn’t quick and easy. It required some complex conditional structure and I had to “get in the zone” to do it.

If (next time) I need to do similar again I’ll be sure to look into using a query builder.

An (anonymised) Example that I actually use:

$query = $dbal->createQueryBuilder()
    ->select('...')
    ->from('...')
    // etc.
    if ($name) {
        $query
            ->innerJoin('src', 'joined_table', 'tgt', 'join_clause')
            ->andWhere('the_name = :name')
            ->setParameter(':name', $name, 'string')
        ;
    } else {
        $query
            ->leftJoin('src', 'joined_table', 'tgt', 'join_clause')
            ->andWhere('the_name IS NULL')
        ;
    }
1 Like

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.