esafwan esafwan - 1 year ago 64
MySQL Question

Structuring SQL query based on multiple checkboxes state

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 Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download