Run a query based on multiple checkboxes

Question

I am trying to run a query based on multiple checkboxes. This is a snippet of the checkboxes on the form.

<td><strong>
        <input name="criteria[Buffet]" type="checkbox" id="Buffet" value="1"/>
        <label for="Buffet">Buffet</label>
        </strong></td>
      <td><strong>
        <input name="criteria[Breakfast]" type="checkbox" id="Breakfast" value="1"/>
        <label for="Breakfast">Breakfast</label>
        </strong></td>
      <td><strong>
        <input name="criteria[BYOB]" type="checkbox" id="BYOB" value="1" />
        <label for="BYOB">BYOB</label>
        </strong></td>

This is the php script on the result page....

<?php
require "congig.php";
if(isset($_POST['criteria']) && !empty($_POST['criteria'])){ 
    foreach($_POST['criteria'] as $key=>$value){ 
        if($value==1) $criteria[] = "'DetailName'='".mysql_escape_string($key)."'";
        } 
        $criteria = implode(' OR ', $criteria); 
        } 
        if(!$rs=mysql_query("SELECT tblLocations.CityID, tblRestaurants.RestName, tblLocations.Street,          
        tblLocations.Phone, tblLocations.Price, tblLocations.Rating, tblDetails.DetailName
        FROM (tblRestaurants INNER JOIN tblLocations ON tblRestaurants.RestID = tblLocations.RestID)            
        INNER JOIN (tblLocDet INNER JOIN tblDetails ON tblLocDet.DetailID = tblDetails.DetailID)
        ON tblLocations.LocationID = tblLocDet.LocID
        WHERE tblLocations.CityID='16'
        AND $criteria
        ORDER BY tblRestaurants.RestName ASC"))
        {
echo "Cannot parse query";
}
elseif(mysql_num_rows($rs) == 0) {
echo "No records found";
}
else {
echo "<table id=\"myTable\" table width=\"710\" class=\"beautifuldata\" align=\"Left\" cellspacing=\"0\">\n";
echo "<thead>\n<tr>";
echo "<th>PLACE</th>";
echo "<th>ADDRESS</th>";
echo "<th>PHONE</th>";
echo "<th>PRICE</th>";
echo "<th>RATING</th>";
echo "</tr>\n</thead>\n";
while($row = mysql_fetch_array($rs)) {
echo"<tr>
<td><strong><a href='$row[RestPage]'>$row[RestName]</a></strong></td>
<td>$row[Address]</td>
<td>$row[Phone]</td>
<td>$row[Price]</td>
<td>$row[Rating]</td>
</tr>\n";
}
echo "</table><br />\n";
}
?>

Any suggestion on why I am not getting any results?

Answer 1

The core of your problem appears to be the fact you're surrounding the column DetailName in single quotes: "'DetailName'='" when all it should be is "DetailName='"

On a note of security, I would like to point out that the function mysql_escape_string() you're using to force input to be mysql-friendly is old and riddled with security holes. Instead, I'd recommend using the much safer implementation: mysql_real_escape_string(). The code examples below make use of the newer, safer function.

Separate from those issues however, I'd recommend taking a slightly different approach which will be easier to read and much easier to manage in the long run.

For starters, I'd recommend using the same name on all checkboxes, and using the DetailName as the value rather than as the key:

<td>
    <input name="criteria[]" type="checkbox" id="Buffet" value="Buffet" />
    <strong><label for="Buffet">Buffet</label></strong>
</td>
<td>
    <input name="criteria[]" type="checkbox" id="Breakfast" value="Breakfast" />
    <strong><label for="Breakfast">Breakfast</label></strong>
</td>
<td>
    <input name="criteria[]" type="checkbox" id="BYOB" value="BYOB" />
    <strong><label for="BYOB">BYOB</label></strong>
</td>

Next, using the values of your inputs rather than the keys, we can now generate our clause. Very efficiently:

// Runs mysql_real_escape_string() on every value encountered.
$clean_criteria = array_map('mysql_real_escape_string', $_REQUEST['criteria']);
// Convert the array into a string.
$criteria = implode("','", $clean_criteria);

Finally, in your query, I'd recommend using the IN operator rather than the OR operator for efficiency and readability:

SELECT
    tblLocations.CityID, tblRestaurants.RestName, tblLocations.Street, tblLocations.Phone, tblLocations.Price, tblLocations.Rating, tblDetails.DetailName
FROM
    (
        tblRestaurants
    INNER JOIN
        tblLocations ON tblRestaurants.RestID = tblLocations.RestID
    )            
INNER JOIN
    (
        tblLocDet
    INNER JOIN
        tblDetails ON tblLocDet.DetailID = tblDetails.DetailID
    ) ON tblLocations.LocationID = tblLocDet.LocID
WHERE tblLocations.CityID='16' AND tblDetails.DetailName IN ($criteria)
ORDER BY tblRestaurants.RestName ASC

Here is the whole PHP side of things combining the modifications I suggest with your logic:

<?php
require "congig.php";
if(!empty($_POST['criteria'])) { // empty() checks if the value is set before checking if it's empty.
    foreach($_POST['criteria'] as $key=>$value){ 
        // Runs mysql_real_escape_string() on every value encountered.
        $clean_criteria = array_map('mysql_real_escape_string', $_REQUEST['criteria']);
        // Convert the array into a string.
        $criteria = implode("','", $clean_criteria);
    }

    $rs = mysql_query("
        SELECT
            tblLocations.CityID, tblRestaurants.RestName, tblLocations.Street, tblLocations.Phone, tblLocations.Price, tblLocations.Rating, tblDetails.DetailName
        FROM
            (
                tblRestaurants
            INNER JOIN
                tblLocations ON tblRestaurants.RestID = tblLocations.RestID
            )            
        INNER JOIN
            (
                tblLocDet
            INNER JOIN
                tblDetails ON tblLocDet.DetailID = tblDetails.DetailID
            ) ON tblLocations.LocationID = tblLocDet.LocID
        WHERE tblLocations.CityID='16' AND tblDetails.DetailName IN ($criteria)
        ORDER BY tblRestaurants.RestName ASC
    ");
    if(!$rs) {
        echo "Cannot parse query";
    } else if(mysql_num_rows($rs) == 0) {
        echo "No records found";
    } else {
        echo "<table id=\"myTable\" table width=\"710\" class=\"beautifuldata\" align=\"Left\" cellspacing=\"0\">\n";
        echo "<thead>\n<tr>";
        echo "<th>PLACE</th>";
        echo "<th>ADDRESS</th>";
        echo "<th>PHONE</th>";
        echo "<th>PRICE</th>";
        echo "<th>RATING</th>";
        echo "</tr>\n</thead>\n";
        while($row = mysql_fetch_array($rs)) {
            echo"<tr>
            <td><strong><a href='$row[RestPage]'>$row[RestName]</a></strong></td>
            <td>$row[Address]</td>
            <td>$row[Phone]</td>
            <td>$row[Price]</td>
            <td>$row[Rating]</td>
            </tr>\n";
        }
        echo "</table><br />\n";
    }
}