基于多个复选框运行查询 [英] Run a query based on multiple checkboxes
问题描述
我正在尝试基于多个复选框运行查询.这是表单上的复选框的一部分.
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>
这是结果页面上的php脚本....
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?
推荐答案
问题的核心似乎是因为您将列DetailName括在单引号中:"'DetailName'='"当应为"DetailName='"
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='"
关于安全性，我想指出，您用来强制输入对MySQL友好的函数mysql_escape_string()年代久远，并且充满安全漏洞.相反，我建议使用更安全的实现:mysql_real_escape_string().下面的代码示例利用了更新，更安全的功能.
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.
对于初学者，我建议在所有复选框上使用相同的名称，并使用DetailName作为值而不是键:
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);
最后，在您的查询中，为提高效率和可读性，建议使用IN运算符而不是OR运算符:
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
这是将我建议的修改与您的逻辑结合在一起的整个PHP方面:
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";
}
}
这篇关于基于多个复选框运行查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
