PHP:使用选择选项过滤 SQL 查询 [英] PHP: Filtering SQL query with select options
问题描述
嘿伙计们，我现在有点卡住了，基本上我正在尝试找出如何过滤我的 SQL 查询，但是在理解如何完成时遇到了一些麻烦.
基本上，我将我网站上所有品牌的品牌"列出到一个表格中.我只是从我的产品表中取出包含每个产品的列.
这里是代码示例，这是 PDO 和 MySQL 选择:
query("SELECT Make, Model, Colour, FuelType, Year, Mileage, Bodytype, Doors, Variant, EngineSize, Price, Transmission, PictureRefs, ServiceHistory, PreviousOwners, Options, FourWheelDrive FROM import ORDER BY MakeASC");} 捕获(异常 $e){echo "错误";出口;}尝试 {$filterres = $db->query("SELECT DISTINCT Make FROM import ORDER BY Make ASC");} 捕获(异常 $e){echo "错误";出口;}?>我已将 DISTINCT 添加到该代码块中，因为 SQL 列中有重复的Make's".
这是表格，正如您所看到的，我正在使用 while 循环将表格中的每个品牌显示到它自己的选项中.
我正在使用此代码将 SQL 行显示到列出的结果中:
fetch(PDO::FETCH_ASSOC)){回声'<div class="listing-container"><a href="carpage.php"><h3 class="model-listing-title clearfix">'.$row["Make"].''.$row["模型"].''.$row["Variant"].'</h3></a><h3 class="price-listing">£'.number_format($row['Price']).'</h3><div class="listing-container-spec"><img src="'.(explode(',', $row["PictureRefs"])[0]).'" class="stock-img-finder"/><div class="ul-listing-container"><ul class="overwrite-btstrp-ul"><li class="diesel-svg list-svg">'.$row["FuelType"].'</li><li class="saloon-svg list-svg">'.$row["Bodytype"].'</li><li class="gear-svg list-svg">'.$row["Transmission"].'</li><li class="color-svg list-svg">'.$row["Colour"].'</li>
<ul class="overwrite-btstrp-ul other-specs-ul h4-style"><li>里程:'.number_format($row["Mileage"]).'</li><li>引擎大小:'.$row["EngineSize"].'cc</li><button href="#" class="btn h4-style checked-btn hover-listing-btn"><span class="glyphicon glyphicon-ok"></span>历史检查<button href="#" class="btn h4-style more-details-btn hover-listing-btn tst-mre-btn"><span class="glyphicon glyphicon-list"></span>更多细节<button href="#" class="btn h4-style test-drive-btn hover-listing-btn tst-mre-btn"><span class="test-drive-glyph"></跨度>试驾<h4 class="h4-style listing-photos-count"><span class="glyphicon glyphicon-camera"></span>5 更多照片</h4>
';?>
我的问题是如何使用选择元素来过滤确切的制作"，例如，如果 SQL 行包含与用户选择的相同的制作"，那么我希望整行显示到列表和任何其他不显示的品牌.
关于如何实现这一点的任何代码示例?
谢谢
你需要有类似的东西:
$where = "";如果 (!!$selectedMake) {$stmt = $db->prepare("SELECT Make, Model, Colour, FuelType, Year, Mileage, Bodytype, Doors, Variant, EngineSize, Price, Transmission, PictureRefs, ServiceHistory, PreviousOwners, Options, FourWheelDrive FROM import ORDER BY MakeASC");$stmt->execute();} 别的 {$stmt = $db->prepare("SELECT Make, Model, Colour, FuelType, Year, Mileage, Bodytype, Doors, Variant, EngineSize, Price, Transmission, PictureRefs, ServiceHistory, PreviousOwners, Options, FourWheelDrive FROM import where Make =?");$stmt->execute(array($selectedMake));}请注意，else 块中不需要 order by，因为您有一个唯一的 Make.如果您的 Make 是文本的，那么您可能需要 '%?%' 而不是 ? 在查询中.
如果我理解的很好，你仍然有一个问题，即你缺乏确定$selectedMake 的值应该是什么的知识.首先，你应该为你的选择标签命名，所以你应该有这个:
这样当您提交表单时，您选择的标签的值将被发送到服务器.所以如果你想在表单提交时传递一个值，你应该给给定的标签一个名字.
可以获取或发布表单.如果是get，那么传入的值应该在get中.试试看:
echo var_dump($_GET);如果是post，传入的值应该是post.试试看:
echo var_dump($_POST);最后，您可以像这样初始化您的 $selectedMake:
$selectedMake = "";if (isset($_GET["selected-make"])) {$selectedMake = $_GET["selected-make"];} else if (isset($_POST["selected-make"])) {$selectedMake = $_POST["selected-make"];}Hey guys I am a little stuck at the moment, basically I am trying to find out how I can filter my SQL queries however having a bit of trouble understanding how it can be done.
Basically I am listing all of the 'Makes' of the brands I have on my site into a form. I am simply taking the column from my products table that contains every product.
Here is an example of the code, this is the PDO and MySQL selection:
<?php
include('database.php');
try {
$results = $db->query("SELECT Make, Model, Colour, FuelType, Year, Mileage, Bodytype, Doors, Variant, EngineSize, Price, Transmission, PictureRefs, ServiceHistory, PreviousOwners, Options, FourWheelDrive FROM import ORDER BY Make ASC");
} catch (Exception $e) {
echo "Error.";
exit;
}
try {
$filterres = $db->query("SELECT DISTINCT Make FROM import ORDER BY Make ASC");
} catch (Exception $e) {
echo "Error.";
exit;
}
?>
I have added DISTINCT to that block of code as there are duplicate 'Make's' in the SQL column.
Here is the form, as you can see I am using a while loop to display every make in the table into it's own option.
<form>
<select class="form-control select-box">
<option value="make-any">Make (Any)</option>
<?php while($make = $filterres->fetch(PDO::FETCH_ASSOC))
{
echo '
<option value="">'.$make["Make"].'</option>
';
} ?>
</select>
<button href="#" class="btn btn-block car-search-button btn-lg btn-success"><span class="glyphicon car-search-g glyphicon-search"></span> Search cars
</button>
</form>
I am using this code to display the SQL rows into listed results:
<?php while($row = $results->fetch(PDO::FETCH_ASSOC))
{
echo '
<div class="listing-container">
<a href="carpage.php"><h3 class="model-listing-title clearfix">'.$row["Make"].' '.$row["Model"].' '.$row["Variant"].'</h3></a>
<h3 class="price-listing">£'.number_format($row['Price']).'</h3>
</div>
<div class="listing-container-spec">
<img src="'.(explode(',', $row["PictureRefs"])[0]).'" class="stock-img-finder"/>
<div class="ul-listing-container">
<ul class="overwrite-btstrp-ul">
<li class="diesel-svg list-svg">'.$row["FuelType"].'</li>
<li class="saloon-svg list-svg">'.$row["Bodytype"].'</li>
<li class="gear-svg list-svg">'.$row["Transmission"].'</li>
<li class="color-svg list-svg">'.$row["Colour"].'</li>
</ul>
</div>
<ul class="overwrite-btstrp-ul other-specs-ul h4-style">
<li>Mileage: '.number_format($row["Mileage"]).'</li>
<li>Engine size: '.$row["EngineSize"].'cc</li>
</ul>
<button href="#" class="btn h4-style checked-btn hover-listing-btn"><span class="glyphicon glyphicon-ok"></span> History checked
</button>
<button href="#" class="btn h4-style more-details-btn hover-listing-btn tst-mre-btn"><span class="glyphicon glyphicon-list"></span> More details
</button>
<button href="#" class="btn h4-style test-drive-btn hover-listing-btn tst-mre-btn"><span class="test-drive-glyph"></span> Test drive
</button>
<h4 class="h4-style listing-photos-count"><span class="glyphicon glyphicon-camera"></span> 5 More photos</h4>
</div>
';
} ?>
My question is how can I use the selection element to filter the exact 'Make', so for example if the SQL row contains the same 'Make' as the user has selected then I would like the whole row to display into the list and any other makes to not show.
Any code examples to how I can achieve this?
Thanks
You need to have something like:
$where = "";
if (!!$selectedMake) {
$stmt = $db->prepare("SELECT Make, Model, Colour, FuelType, Year, Mileage, Bodytype, Doors, Variant, EngineSize, Price, Transmission, PictureRefs, ServiceHistory, PreviousOwners, Options, FourWheelDrive FROM import ORDER BY Make ASC");
$stmt->execute();
} else {
$stmt = $db->prepare("SELECT Make, Model, Colour, FuelType, Year, Mileage, Bodytype, Doors, Variant, EngineSize, Price, Transmission, PictureRefs, ServiceHistory, PreviousOwners, Options, FourWheelDrive FROM import where Make = ?");
$stmt->execute(array($selectedMake));
}
Note that the order by is not needed in the else block, as you have a unique Make. If your Make is textual, then you might need '%?%' instead of ? in the query.
EDIT:
If I understood well, you still have a problem, namely, you lack the knowledge to determine what the value of $selectedMake should be. First of all, you should have a name for your select tag, so you should have this:
<select class="form-control select-box" name="selected-make">
This way when you submit the form, the value of your selected tag will be sent to the server. So if you want to pass a value when the form submits, you should give the given tag a name.
A form can be get or post. If it is get, then the passed value should be in get. Try it out with:
echo var_dump($_GET);
If it is post, the passed value should be in post. Try it out with:
echo var_dump($_POST);
Finally, you can initialize your $selectedMake like this:
$selectedMake = "";
if (isset($_GET["selected-make"])) {
$selectedMake = $_GET["selected-make"];
} else if (isset($_POST["selected-make"])) {
$selectedMake = $_POST["selected-make"];
}
这篇关于PHP:使用选择选项过滤 SQL 查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
