I have been trying to figure this out for a while, but am not getting anywhere with it. I have read a lot of the other questions posted here and across the internet but am not being able to find a solution. Specially in the case of using multiple checkboxes.
Please bear with me as I try to explain the problem I am facing.
On a page I have a list of People with their corresponding location which i pull from a database using php/sql. What I am trying to achieve is to be able to filter the list according to location using checkboxes.
This is the code I have currently for the checkboxes.
<ul>
<li><input type="checkbox" name="check_list[]" value="sydney">Sydney</li>
<li><input type="checkbox" name="check_list[]" value="durban">Durban</li>
<li><input type="checkbox" name="check_list[]" value="delhi">Delhi</li>
<li><input type="checkbox" name="check_list[]" value="cairo">Cairo</li>
<li><input type="checkbox" name="check_list[]" value="madrid">Madrid</li>
<li><input type="submit"></li>
</ul>
This is the code I am using to check if a checkbox is checked and accordingly modify the SQL query
if (empty ($_POST['check_list'])) {
$SQLquery = 'SELECT * FROM `people`';
$query = $conn->query($SQLquery);
} elseif (!empty($_POST['check_list'])) {
foreach($_POST['check_list'] as $check)
$location = $check;
$query = $conn->prepare('SELECT * FROM `people` WHERE `p_location` = :location');
$query->execute(array('location' => $location));
}
Now this code works fine when only one checkbox is checked. However, when multiple checkboxes are checked this fails. It just shows nothing.
I know i can modify the SQL query using the OR
operator to add other locations like so:
$query = $conn->prepare('SELECT * FROM `people` WHERE `p_location` = 'Delhi' OR `p_location` = 'Madrid')
However, I am unable to figure out how I can first check which all checkboxes are checked and then create the SQL statement in a way which will filter the results when there is more than one city selected.
I am also wondering if this is the best way to achieve something like this or not. I am open to alternative ways of achieving this within the scope of php/sql.