Structuring SQL querie based on multiple checkboxes state.

Question

Hi, I'm making a restaurant search in which users can filter the search based on many criteria...

I have three tables:

Table1 - **Restaurant**
------+----------+----------
  id  +   name   +   place
------+----------+----------
   1      Rest1       Ny
   2      Rest2       La
   3      Rest3       Ph


Table2 - **r_type**
------+----------+----------
  id  +   name   +   code
------+----------+----------
   1      type1       0
   2      type2       1
   3      type3       2
   4      type4       3


Table3 - **type_stack**
------+----------+----------
  id  + rest_id  +   type
------+----------+----------
   1      2          2
   2      4          1
   3      1          2

Now people may search for restaurant with type1 and type2 or only one type etc... How do i generate queries based on the check box state that user has choose. How is it done in php? There will be around 7-12 checkboxes.

Another question is what would be the best method to implement this? Is this kind of tables ok? Because users will have the option to change their options and refresh their results using ajax. So server load will be more.

Thank You

Answer 1

i'm not sure abou my sql syntax but you can take all the check box as params and then place your query and in where you can do something like:

from ...
where
(somecolumn = @check1 or @check1 is null)
and
(somecolumn2=@check2 or @check2 is null)

i think this will be a good solution for you.

Hope it helps

Answer 2

Name your checkboxes something like this:

<input type="checkbox" name="chk[]" value="1" />
<input type="checkbox" name="chk[]" value="2" />

and so on, where the values are the ids from Table1. When the user hits the submit button, the POST will contain the values of the checked checkboxes.

In your PHP, you then have

$chkArr = isset($_POST['chk']) ? $_POST['chk'] : array();
$chkArrCSV = implode(',',$chkArr);

Then you can build up your query as

$sql = 'SELECT blah FROM blah WHERE id IN '.mysql_real_escape_string($chkArrCSV);

and whatever else you need to sanitise the user input.