MySQL multiple "AND" statements

Question

I am trying to run a query with multiple WHERE clauses. If I do a multiple search, it returns a record from a single criteria. I need this query to return a result that has ALL of the criteria instead of just one.

You can see it here.

Additionally, I have provided the code:

if (isset ( $_POST ["btnSearch"] )) {
echo "<br>Selected Options are :<br>";
$checked = $_POST ["criteria"];

$criteria = "";
$separator = ", ";
for($i = 0; $i < count ( $checked ); $i ++) {
    echo "  " . $checked [$i] . "<br/>";

    if ($i == count ( $checked ) - 1) {
        $separator = "";
    }

    $criteria = $criteria . "'" . $checked [$i] . "'" . $separator;
}
echo "<br><br>";

echo $criteria . "<br><br>";
include "config.php";

mysql_select_db ( "MyHead", $con );
//$DM = implode(',',$criteria);
$mysqlQuery = "SELECT tblRestaurants.RestName, tblLocDet.LocationID, tblLocDet.DetailID, tblDetails.DetailName, tblRestaurants.RestName
FROM tblRestaurants INNER JOIN (tblLocations INNER JOIN (tblLocDet INNER JOIN tblDetails ON  tblLocDet.DetailID = tblDetails.DetailID) ON tblLocations.LocationID = tblLocDet.LocationID) ON  tblRestaurants.RestID = tblLocations.RestID
GROUP BY tblRestaurants.RestName, tblLocDet.LocationID, tblLocDet.DetailID, tblDetails.DetailName
 HAVING tblDetails.DetailName IN (" . $criteria . ");";

if (! $rs = mysql_query ( $mysqlQuery )) {
    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>";
        echo "<td>" . $row ['DetailName'] . "</td>";
        echo "<td>" . $row ['Phone'] . "</td>";
        echo "<td>" . $row ['Price'] . "</td>";
        echo "<td>" . $row ['Rating'] . "</td>";
        echo "</tr>";
    }
}
echo "</table><br />\n";

mysql_close ( $con );
}
?>

Tables:

tblRestaurants (RestID, RestName)
tblLocations (LocationID, CityID, AreaID, CuisineID)
tblLocDet (DetailID, LocationID)
tblDetails (DetailID, DetailName, DetailType)

Answer 1

To ensure that all the selected rows have all the items in the $criteria, one way to do so is to count those items in this criteria variable and then having a HAVING COUNT(DISTINCT DetailName) = $n, so that any selected rows should have all of them, something like:

SELECT 
  r.RestName, 
  ld.LocationID, 
  ld.DetailID, 
  d.DetailName
FROM tblRestaurants     AS r
INNER JOIN tblLocations AS l  ON r.RestID     = l.RestID
INNER JOIN tblLocDet    AS ld ON l.LocationID = ld.LocationID
INNER JOIN
(
  SELECT l.Locationid
  FROM tblLocDet l
  INNER JOIN tbldetails d ON l.detailid = d.detailid
  WHERE d.detailname IN ('det1', 'det2', 'det3')
  GROUP BY l.locationid
  HAVING COUNT(DISTINCT DetailName) = $n
)                       AS ld2 ON ld.locationid = ld2.locationid
INNER JOIN tblDetails   AS d   ON ld.DetailID   = d.DetailID   
GROUP BY r.RestName, 
         ld.LocationID, 
         ld.DetailID, 
         d.DetailName;

SQL Fiddle Demo.

This will give you something like:

| RESTNAME | LOCATIONID | DETAILID | DETAILNAME |
-------------------------------------------------
|     res1 |          1 |        1 |       det1 |
|     res1 |          1 |        2 |       det2 |
|     res1 |          1 |        3 |       det3 |

---

You can, however, shorten this query; for example if you remove the detailid, and detailname from the GROUP BY clause and use the GROUP_CONCAT to select them in one row concatenated with , like this:

SELECT 
  r.RestName, 
  ld.LocationID, 
  GROUP_CONCAT(DISTINCT d.DetailName separator ',') Details
FROM tblRestaurants     AS r
INNER JOIN tblLocations AS l  ON r.RestID     = l.RestID
INNER JOIN tblLocDet    AS ld ON l.LocationID = ld.LocationId
INNER JOIN tblDetails   AS d  ON ld.DetailID  = d.DetailID   
WHERE d.detailname IN ('det1', 'det2', 'det3')
GROUP BY r.RestName, 
         ld.LocationID
HAVING COUNT(DISTINCT d.DetailName) = 3;

Updated SQL Fiddle Demo.

This will give you something like:

| RESTNAME | LOCATIONID |        DETAILS |
------------------------------------------
|     res1 |          1 | det3,det2,det1 |

Note that: the HAVING COUNT(DISTINCT d.DetailName) = 3 will give you all the rows that have all the details names = 3 if you want to get those rows that had at least change it to >=.

Answer 2

Here's one solution:

<?php
if (isset ( $_POST ["btnSearch"] )) {
$checked = (array) $_POST ["criteria"];
sort($checked);
$criteria = implode(",", $checked);
echo "<br>Selected Options are :<br>" . $criteria . "<br><br>";

include "config.php";

mysql_select_db ( "MyHead", $con );

$criteria = mysql_real_escape_string($criteria);

$mysqlQuery = "
SELECT r.RestName, ld.LocationID, ld.DetailID, d.DetailName, r.RestName
FROM tblRestaurants AS r
INNER JOIN tblLocations AS l 
  ON r.RestID = l.RestID
INNER JOIN tblLocDet AS ld 
  ON l.LocationID = ld.LocationID 
INNER JOIN tblDetails AS d 
  ON ld.DetailID = d.DetailID
INNER JOIN (
  SELECT DetailID, GROUP_CONCAT(DetailName ORDER BY DetailName) AS DetailList 
  FROM tblDetails GROUP BY DetailID) AS dx
  ON ld.DetailID = dx.DetailID
WHERE dx.DetailList = '$criteria'";

if (! $rs = mysql_query ( $mysqlQuery )) {
  . . . 

The rest would be the same as your code.

Answer 3

You need this query:

SELECT tblRestaurants.RestName, ...
FROM tblRestaurants 
INNER JOIN tblLocations ON tblLocations.RestID = tblRestaurants.RESTID
INNER JOIN tblLocDet ON tblLocDet.LocationID = tblLocations.LocationID
INNER JOIN tblDetails ON tblLocDet.DetailID  = tblDetails.DetailID
WHERE tblDetails.DetailName IN (" . $criteria . ");";
ORDER BY...

You do not need GROUP BY as you have no aggregate functions (MIN, MAX etc). Use ORDER BY to sort the list.

Also a good idea to move to mysqli_* (or PDO) functions and use prepared statements to avoid SQL injection