How to combine result sets of a sql query?

Question

I am having 5 checkbox with the food items like Chicken, fruits, vegetables, Snacks,.. like that.

What i am trying to implement is when the user selects the checkbox items and clicks submit, it will search for the restaurants providing the selected items,

Table will have 2 fields: restid, menu and the data is stored like this in the table

restid->1, menu->chicken
restid->1, menu->Burger
restid->2, menu->fruits
restid->3, menu->chicken

My doubt is how to search for the restaurants in the table. My plan is, loop through the for loop for each item with the select statement like (select * from restaurant_table where menu='menu';)

when we loop through the loop how can we combine the results for each menu?

Plz help any help will be appreciated

Answer 1

The query below will return you the restids for restaurants that have both chicken and burger on the menu - assuming that restaurant_table doesn't have duplicate rows.

SELECT restid, COUNT(*) as cnt
FROM restaurant_table
WHERE menu IN ('chicken', 'burger')
GROUP BY restid
HAVING COUNT(*) = 2

Answer 2

Maybe this can help you as starting point:

$where = " 1=1 ";

if ( $_POST["chicken"] ) 
$where = $where . " AND menu = 'chicken' ";

if ( $_POST["Burger"] ) 
$where = $where . " AND menu = 'Burger' ";

if ( $_POST["fruits"] ) 
$where = $where . " AND menu = 'fruits' ";

$sql = "SELECT * FROM restaurants WHERE " + $where;

Answer 3

i hope you are having two table restaurant and menu

restaurant - restid, name

menu - menuid, restid, menu

the php code can be like this

<?php 
     //$_POST['menus'] is the array of checkboxes
     foreach($_POST['menus'] as $menu)
     {
        if($menu) $selected[]=intval($menu);
     }
     $selectedlist=implode(",",$selected);

    //The SQL Query
    $query="SELECT name from restaurant WHERE menu in(".$selectedlist.")";
    $record=mysql_fetch_array(mysql_query($query)); //Loop this to get more records
?>

Hope this is useful