php - MySQLi 准备好的语句选择不获取任何结果且没有错误
问题描述
我正在尝试使用 PHP 构建一个简单的高级搜索,但我不明白为什么它没有获取任何结果!它也没有显示任何错误!
我正在搜索所有未设置的条件,所以它会是这样的:
SELECT id,img_url1,price,type,status_prop,title,bedrooms,bathrooms,garages,sqft,address,LEFT(description, 215) AS description_preview
FROM alreemisland
WHERE price BETWEEN 0 AND 10000000
and type like '%'
and status_prop like '%'
and bedrooms BETWEEN 0 and 9
and bathrooms BETWEEN 0 and 9
而且我正确地获取了它,所以它一定与我的代码有关!
这是我的代码:
$type_search_final=mysqli_real_escape_string($con,$_POST['type_search']);
$status_prop_final=mysqli_real_escape_string($con,$_POST['status_prop']);
$min_bedrooms_final=mysqli_real_escape_string($con,$_POST['min_bedrooms']);
$min_bathrooms_final=mysqli_real_escape_string($con,$_POST['min_bathrooms']);
$min_price_final=mysqli_real_escape_string($con,$_POST['min_price']);
$max_price_final=mysqli_real_escape_string($con,$_POST['max_price']);
//============ For Pagination ============
if($type_search_final){
$query_type_search_final .= "type LIKE '$type_search_final'";
}
else {$query_type_search_final = "type LIKE '%'";}
if($status_prop_final){
$query_status_prop_final .= "status_prop LIKE '$status_prop_final'";
}
else {$query_status_prop_final = "status_prop LIKE '%'";}
if($min_bedrooms_final){
$query_min_bedrooms_final .= "bedrooms BETWEEN $min_bedrooms_final and 9'";
}
else {$query_min_bedrooms_final = "bedrooms BETWEEN 0 and 9";}
if($min_bathrooms_final){
$query_min_bathrooms_final .= "bathrooms BETWEEN $min_bathrooms_final and 9'";
}
else {$query_min_bathrooms_final = "bathrooms BETWEEN 0 and 9";}
$sql_search= "SELECT id,img_url1,price,type,status_prop,title,bedrooms,bathrooms,garages,sqft,address,LEFT(description, 215) AS description_preview FROM $from_section WHERE price BETWEEN $min_price_final AND $max_price_final and ? and ? and ? and ? order by id desc";
$stmt_search = mysqli_stmt_init ($con);
if (!mysqli_stmt_prepare($stmt_search,$sql_search)) {
echo "SQL FAILED";
}
else {
mysqli_stmt_bind_param($stmt_search, "ssss", $query_type_search_final,$query_status_prop_final,$query_min_bedrooms_final,$query_min_bathrooms_final);
mysqli_stmt_execute($stmt_search);
mysqli_stmt_store_result($stmt_search);
mysqli_stmt_bind_result($stmt_search, $id,$img_url1,$price,$type,$status_prop,$title,$bedrooms,$bathrooms,$garages,$sqft,$address,$description_preview);
$count_search = mysqli_stmt_num_rows($stmt_search);
}
$row_search= mysqli_stmt_fetch($stmt_search);
echo $row_search['img_url1'];
这不是懒惰实际上我一直在互联网上搜索并尝试了一切,但我无法让它发挥作用。我知道我错过了一些东西,但我几个小时都不知道。因此,如果第二只眼睛能看到我看不到的东西并指出我错过的地方,我会非常感激。
编辑:我更新了我的代码,但我仍然没有得到任何结果!
$where = "price BETWEEN $min_price_final AND $max_price_final";
if($type_search_final){
$where .= " and type LIKE $type_search_final";
}
if($status_prop_final){
$where .= " and status_prop LIKE $status_prop_final'";
}
if($min_bedrooms_final){
$where .= " and bedrooms BETWEEN $min_bedrooms_final and 9'";
}
if($min_bathrooms_final){
$where .= " and bathrooms BETWEEN $min_bathrooms_final and 9'";
}
$sql_search= "SELECT id,img_url1,price,type,status_prop,title,bedrooms,bathrooms,garages,sqft,address,LEFT(description, 215) AS description_preview FROM $from_section where ? order by id desc";
$stmt_search = mysqli_stmt_init ($con);
if (!mysqli_stmt_prepare($stmt_search,$sql_search)) {
echo "SQL FAILED";
}
else {
mysqli_stmt_bind_param($stmt_search, "s", $where);
mysqli_stmt_execute($stmt_search);
mysqli_stmt_store_result($stmt_search);
mysqli_stmt_bind_result($stmt_search, $id,$img_url1,$price,$type,$status_prop,$title,$bedrooms,$bathrooms,$garages,$sqft,$address,$description_preview);
$count_search = mysqli_stmt_num_rows($stmt_search);
}
$row_search= mysqli_stmt_fetch($stmt_search);
echo $row_search['img_url1'];
更新 - 它现在工作了..这是最终代码:
$where = "price BETWEEN ? AND ?";
$params[] = $min_price_final;
$params[] = $max_price_final;
$type_string = "ss";
if($type_search_final){
$where .= " and type LIKE ?";
$params[] = $type_search_final;
$type_string .="s";
}
if($status_prop_final){
$where .= " and status_prop LIKE ?";
$params[] = $status_prop_final;
$type_string .="s";
}
if($min_bedrooms_final){
$where .= " and bedrooms BETWEEN ? and 9'";
$params[] = $min_bedrooms_final;
$type_string .="s";
}
if($min_bathrooms_final){
$where .= " and bathrooms BETWEEN ? and 9";
$params[] = $min_bathrooms_final;
$type_string .="s";
}
$sql_search= "SELECT id,img_url1,price,type,status_prop,title,bedrooms,bathrooms,garages,sqft,address,LEFT(description, 215) AS description_preview
FROM $from_section
where $where
order by id desc";
$stmt_search = mysqli_stmt_init ($con);
if (!mysqli_stmt_prepare($stmt_search,$sql_search)) {
echo "SQL FAILED";
}
else {
mysqli_stmt_bind_param($stmt_search,$type_string, ...$params);
mysqli_stmt_execute($stmt_search);
mysqli_stmt_store_result($stmt_search);
mysqli_stmt_bind_result($stmt_search, $id,$img_url1,$price,$type,$status_prop,$title,$bedrooms,$bathrooms,$garages,$sqft,$address,$description_preview);
$count_search = mysqli_stmt_num_rows($stmt_search);
}
$row_search= mysqli_stmt_fetch($stmt_search);
echo $img_url1.'<br>'.$type_string;
更新#2:我错过了最后两个条件的整数类型,并且我在 9 之间错过了键入 (')
$where = "price BETWEEN ? AND ?";
$params[] = $min_price_final;
$params[] = $max_price_final;
$type_string = "ss";
if($type_search_final){
$where .= " and type LIKE ?";
$params[] = $type_search_final;
$type_string .="s";
}
if($status_prop_final){
$where .= " and status_prop LIKE ?";
$params[] = $status_prop_final;
$type_string .="s";
}
if($min_bedrooms_final){
$where .= " and bedrooms BETWEEN ? and 9";
$params[] = $min_bedrooms_final;
$type_string .="i";
}
if($min_bathrooms_final){
$where .= " and bathrooms BETWEEN ? and 9";
$params[] = $min_bathrooms_final;
$type_string .="i";
}
$sql_search= "SELECT id,img_url1,price,type,status_prop,title,bedrooms,bathrooms,garages,sqft,address,LEFT(description, 215) AS description_preview
FROM $from_section
where $where
order by id desc";
$stmt_search = mysqli_stmt_init ($con);
if (!mysqli_stmt_prepare($stmt_search,$sql_search)) {
echo "SQL FAILED";
}
else {
mysqli_stmt_bind_param($stmt_search,$type_string, ...$params);
mysqli_stmt_execute($stmt_search);
mysqli_stmt_store_result($stmt_search);
mysqli_stmt_bind_result($stmt_search, $id,$img_url1,$price,$type,$status_prop,$title,$bedrooms,$bathrooms,$garages,$sqft,$address,$description_preview);
$count_search = mysqli_stmt_num_rows($stmt_search);
}
$row_search= mysqli_stmt_fetch($stmt_search);
echo $img_url1.'<br>'.$type_string;
解决方案
占位符?
被绑定值替换并转义。所以你的
mysqli_stmt_bind_param($stmt_search, "s", $where);
只是将在 SQL 中绑定为字符串,而不是 SQL。您需要为每个值使用一个占位符,然后绑定每个值。
您现在编写的查询大致如下:
SELECT id,img_url1,price,type,status_prop,title,bedrooms,bathrooms,garages,sqft,address,LEFT(description, 215) AS description_preview
FROM SomeTable
where 'price BETWEEN \'somevalue\' AND \'some other value\'' order by id desc
因此,您应该执行以下操作:
$where = "price BETWEEN ? AND ?";
$params[] = $min_price_final;
$params[] = $max_price_final;
if($type_search_final){
$where .= " and type LIKE ?";
$params[] = $type_search_final;
}
if($status_prop_final){
$where .= " and status_prop LIKE ?";
$params[] = $status_prop_final;
}
if($min_bedrooms_final){
$where .= " and bedrooms BETWEEN ? and 9'";
$params[] = $min_bedrooms_final;
}
if($min_bathrooms_final){
$where .= " and bathrooms BETWEEN ? and 9";
$params[] = $min_bathrooms_final;
}
$sql_search= "SELECT id,img_url1,price,type,status_prop,title,bedrooms,bathrooms,garages,sqft,address,LEFT(description, 215) AS description_preview
FROM $from_section
where $where
order by id desc";
您应该使用白名单,$from_section
所以它也是安全的。您不能绑定列/表。
然后,您只需要绑定所有$params
值。使用 PDO,这只是execute($params)
使用 mysqli 会更复杂。
也许这个线程对动态绑定很有用如何在PHP中动态绑定mysqli bind_param参数?.
推荐阅读
- xml - InDesign、IDML、XSLT:混乱的 xml 输出没有明显的模式
- ios - iOS UITabBar 制作“假”活动标签?
- arrays - 为什么结构数组比较有不同的结果
- reactjs - React JS - React Material UI 图标不起作用?
- javascript - 具有多个音频流的 HTML5 视频播放器
- angular - 表单验证Angular 2中的文本框没有变红
- azure - 如何在 Data Factory v2 的自定义活动中读取安全字符串?
- c# - 为什么输入作为 JSON 字符串 C# 无效?
- deep-learning - 如何存储词向量嵌入?
- android - Android TCP监控应用程序断开从属?