MySQL多个“MySQL”语句

Question

我正在尝试运行一个包含多个WHERE子句的查询。如果我执行多个搜索，它会从单个条件返回一条记录。我需要这个查询返回一个包含所有条件的结果，而不是只返回一个条件。

你可以在here上看到它。

此外，我还提供了以下代码：

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 );
}
?>

表：

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

Answer 1

要确保所有选定的行都包含$criteria中的所有项，一种方法是对此条件变量中的这些项进行计数，然后具有一个HAVING COUNT(DISTINCT DetailName) = $n，这样任何选定的行都应该包含所有这些项，如下所示：

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;

。

这将为您提供类似以下内容：

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

但是，您可以缩短此查询；例如，如果从GROUP BY子句中删除detailid和detailname，并使用GROUP_CONCAT在与,连接的一行中选择它们，则如下所示：

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;

。

这将为您提供类似以下内容：

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

请注意:如果您想获得那些至少已将其更改为>=的行，则HAVING COUNT(DISTINCT d.DetailName) = 3将为您提供所有详细信息名称=3的行。

Answer 2

您需要以下查询：

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...

您不需要GROUP BY，因为您没有聚合函数(MIN、MAX等)。使用ORDER BY对列表进行排序。

另外，使用mysqli_* (或PDO)函数并使用准备好的语句来避免SQL注入也是一个好主意

Answer 3

这里有一个解决方案：

<?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 )) {
  . . . 

其余部分将与您的代码相同。