问题
您好。我有以下完整的代码:
<?php
require("config.inc.php");
// initial query
$query = "SELECT * FROM `appdrgreenthumb_plant_species`";
// counter for the number of conditions filled by user
// useful for determining when to add "AND" keywords to the query
$numConditions = 0;
// check if any of the fields are filled
if (!isset($_POST['btnSubmit']))
{
?>
<!DOCTYPE html>
<html>
<head>
<title>Search Dr. Green Thumb's Plant Infobook</title>
<style>
body {
font-family: "Courier New", sans-serif;
font-size: 1em;
}
input[type="submit"] {
font-family: "Courier New", sans-serif;
font-size: 1em;
width: 100%;
height: 1.5em;
}
.wrapper {
width: 30em;
padding: 0.5em;
margin: 0 auto;
}
legend {
font-size: 150%;
}
form {
}
.include_items {
width: 50%;
float: left;
margin: 0.5em 0 0.5em;
}
.form-group {
margin: 0.3em 0 0.3em;
}
.form-group label {
float: left;
width: 30%;
text-align: right;
margin-right: 0.1em;
}
.form-group input {
width: 68%;
}
</style>
</head>
<body>
<div class="wrapper">
<form method="post" action="dictionary_search.php">
<fieldset>
<legend>Search</legend>
<div class="form-group">
<label>Plant ID:</label>
<input type="text" name="id" value="" />
</div>
<div class="form-group">
<label>Plant Name:</label>
<input type="text" name="name" value="" />
</div>
<div class="form-group">
<label>Plant Family:</label>
<input type="text" name="family" value="" />
</div>
<div class="include_items">
<fieldset>
<input type="checkbox" name="cb_id" value="id">Plant ID<br />
<input type="checkbox" name="cb_name" value="name">Plant Name<br />
<input type="checkbox" name="cb_family" value="plant">Plant Family<br />
</fieldset>
</div>
<!-- submit button -->
<input type="submit" value="Search" name="btnSubmit" />
</fieldset>
</form>
</div>
</body>
</html>
<?php
}
else
{
if (!empty($_POST['id']) ||
!empty($_POST['name']) ||
!empty($_POST['family'])
)
{
$query = $query . " WHERE ";
// check if the ID field is filled
if (!empty($_POST['id']))
{
$numConditions++;
$query = $query . InfoBookUtilities::appendAND($numConditions) . "`id`" . " = " . ":id";
$query_params[':id'] = $_POST['id']; // this is to avoid SQL injection
}
// check if the NAME field is filled
if (!empty($_POST['name']))
{
$numConditions++;
# $query = $query . InfoBookUtilities::appendAND($numConditions) . "`name`" . " LIKE " . "'%:name%'";
# $query_params[':name'] = $_POST['name']; // this is to avoid SQL injection
}
// check if the FAMILY field is filled
if (!empty($_POST['family']))
{
$numConditions++;
# $query = $query . InfoBookUtilities::appendAND($numConditions) . "`family`" . " LIKE " . "'%:family%'";
# $query_params[':family'] = $_POST['family']; // this is to avoid SQL injection
}
# echo $query;
}
?>
<?php
// Run the query:
try
{
// These two statements run the query against your database table.
$stmt = $db->prepare($query);
$result = $stmt->execute($query_params);
}
catch (PDOException $ex)
{
$response["success"] = 0;
$response["message"] = "Database Error! <br />Exception: " . $ex->getMessage();
die(json_encode($response));
}
// finally, we can retrieve all of the found rows into an array using fetchAll
$rows = $stmt->fetchAll();
if ($rows)
{
$response["success"] = 1;
$response["message"] = "Records Available!";
$response["plants"] = array();
foreach ($rows as $row) {
$plant = array();
if (isset($_POST['cb_id']))
$plant["id"] = $row["id"];
if (isset($_POST['cb_name']))
$plant["name"] = $row["name"];
if (isset($_POST['cb_family']))
$plant["family"] = $row["family"];
if (isset($_POST['cb_price_floor']) || !empty($_POST['cb_price_ceiling']))
$plant["price"] = $row["price"];
if (isset($_POST['cb_introduction']))
$plant["introduction"] = $row["introduction"];
if (isset($_POST['cb_climate_and_soil']))
$plant["climate_and_soil"] = $row["climate_and_soil"];
if (isset($_POST['cb_temperature_min']))
$plant["temperature_min"] = $row["temperature_min"];
if (isset($_POST['cb_temperature_max']))
$plant["temperature_max"] = $row["temperature_max"];
if (isset($_POST['cb_soil_type']))
$plant["soil_type"] = $row["soil_type"];
if (isset($_POST['cb_soil_ph_min']))
$plant["soil_ph_min"] = $row["soil_ph_min"];
if (isset($_POST['cb_soil_ph_max']))
$plant["soil_ph_max"] = $row["soil_ph_max"];
if (isset($_POST['cb_recommended_varieties']))
$plant["recommended_varieties"] = $row["recommended_varieties"];
if (isset($_POST['cb_land_preparation']))
$plant["land_preparation"] = $row["land_preparation"];
if (isset($_POST['cb_crop_management']))
$plant["crop_management"] = $row["crop_management"];
if (isset($_POST['cb_care']))
$plant["care"] = $row["care"];
if (isset($_POST['cb_nutrition_management']))
$plant["nutrition_management"] = $row["nutrition_management"];
if (isset($_POST['cb_water_management']))
$plant["water_management"] = $row["water_management"];
if (isset($_POST['cb_harvest_management']))
$plant["harvest_management"] = $row["harvest_management"];
if (isset($_POST['cb_pests']))
$plant["pests"] = $row["pests"];
if (isset($_POST['cb_diseases']))
$plant["diseases"] = $row["diseases"];
if (isset($_POST['cb_date_sow_begin']))
$plant["date_sow_begin"] = $row["date_sow_begin"];
if (isset($_POST['cb_date_sow_end']))
$plant["date_sow_end"] = $row["date_sow_end"];
if (isset($_POST['cb_growth_min']))
$plant["growth_min"] = $row["growth_min"];
if (isset($_POST['cb_growth_max']))
$plant["growth_max"] = $row["growth_max"];
if (isset($_POST['cb_image_location']))
$plant["image_location"] = $row["image_location"];
// update our repsonse JSON data
array_push($response["plants"], $plant);
}
// echoing JSON response
echo json_encode($response);
}
else
{
$response["success"] = 0;
$response["message"] = "No Records Available!";
die(json_encode($response));
}
?>
<?php
}
?>
<?php
class InfoBookUtilities {
public static function appendAND ($numberOfConditions) {
if ($numberOfConditions == 1)
return "";
else
return " AND ";
}
}
?>
长话短说:当我使用“id”或“name”文本框过滤结果时,即使应该有记录也没有。假设我的表中有这个记录:
![enter image description here](https://istack.dev59.com/99W8E.webp)
然后,我在名称文本框中输入单词“carrot”,并选中所有复选框。我认为应该能得到一条记录,但实际上却没有。
{"success":0,"message":"No Records Available!"}
我应该在什么时候获取这个?
{"success":1,"message":"Records Available!","plants":[{"id":"1","name":"Carrot","family":"Umbelliferae"}]}
另一个问题是,当我尝试在两个文本字段一起搜索时,我会收到以下错误:
{"success":0,"message":"Database Error!
Exception: SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens"}
我认为这只是因为我添加参数的方式有问题,因为当我像这样编写代码时(虽然这是不好的实践):
// check if the NAME field is filled
if (!empty($_POST['name']))
{
$numConditions++;
# $query = $query . InfoBookUtilities::appendAND($numConditions) . "`name`" . " LIKE " . "'%:name%'";
# $query_params[':name'] = $_POST['name']; // this is to avoid SQL injection
$query = $query . InfoBookUtilities::appendAND($numConditions) . "`name`" . " LIKE " . "'%" . $_POST['name'] . "%'";
}
// check if the FAMILY field is filled
if (!empty($_POST['family']))
{
$numConditions++;
# $query = $query . InfoBookUtilities::appendAND($numConditions) . "`family`" . " LIKE " . "'%:family%'";
# $query_params[':family'] = $_POST['family']; // this is to avoid SQL injection
$query = $query . InfoBookUtilities::appendAND($numConditions) . "`family`" . " LIKE " . "'%" . $_POST['family'] . "%'";
}
我完成了工作。
其他细节:
我可以很好地过滤出id
字段(使用我在顶部粘贴的代码——是的,那个冗长的代码)。
我通过HTML表单的文本框字段自定义(设置WHERE子句条件)我的MySQL查询。复选框允许我过滤掉每行中要保留的“属性”,然后最终堆积到我的JSON数组中。
它看起来像这样:
我已经看了几个小时了,但我真的搞不清楚问题出在哪里。 :/
appdrgreenthumb_plant_species
WHEREname
LIKE '%:name%'”。我不知道:name部分是否应该像那样显示,还是应该是'%carrot%',因为当我使用ID文本字段进行搜索时,我有查询:“SELECT * FROMappdrgreenthumb_plant_species
WHEREid
= :id”,它是一个成功的搜索。 :/ - user4835507... \
name`" . " LIKE " . ":name";和
$query_params[':name'] = "%".$_POST['name']."%";` - Sean