MySQLi prepared statements select not fetching any result with no error

Question

I'm trying to build a simple advanced search using PHP, but I can't see why it isn't fetching any results! It also doesn't show any error !

I'm searching with all conditions not set so it would be like this:

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

And I got it fetched properly, so it must be something to do with my code!

Here's my code:

$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'];

It's not laziness actually I've been searching all over the internet and tried everything, but I can't get it to work. I know I'm missing something but I couldn't know for hours. So if a second eye could see what I couldn't see and point me where I missed I will appreciated it a lot.

EDIT: I Updated my code but still I got no results at all!

$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'];

UPDATE - It worked now .. this is the final code:

$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;

UPDATE #2 : i missed integer type on the last two conditions and i miss-typed (') near between 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;

Are you sure you are using `LIKE` right? Are you not meant to pass something to the subsequent string and not just `%`. — Script47, Jun 07 '18 at 01:20
@Script47 i'm trying ignore it if it's not selected in the html select drop down — belal, Jun 07 '18 at 01:28

user3783243 · Accepted Answer · 2018-06-07T02:51:21.923

0

A placeholder ? is replaced with the bound value and escaped. So your

mysqli_stmt_bind_param($stmt_search, "s", $where);

is just going to bind in the SQL as a string, not as SQL. You need to use a placeholder for each value, and then bind each one.

Your query as it is written right now would roughly come out as:

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

So instead you should do something like:

$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";

You should use a whitelist for the $from_section so it also is safe. You can't bind a column/table.

You then just need to bind all the $params values. With PDO this would just be execute($params) with mysqli it is more complicated.

Maybe this thread would be useful for the binding dynamically How to bind mysqli bind_param arguments dynamically in PHP?.

edited Jun 07 '18 at 02:51

answered Jun 07 '18 at 02:34

user3783243

5,368
5
22
41

mysqli_stmt_bind_param($stmt_search, "ssssss", $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); – belal Jun 07 '18 at 02:53
that's my code to execute but unfortunately it dosen't work .. i will try to get it work with call_user_func_array – belal Jun 07 '18 at 02:54
Thank you very much – belal Jun 07 '18 at 02:54
Yea, unfortunately the `mysqli_stmt_bind_param` doesn't take arrays. Looks like your own the right path. Good luck. Also don't use the `mysqli_real_escape_string` anymore. With parameterized queries that can get weird because you'll be double escaping. – user3783243 Jun 07 '18 at 02:57
1

oh thanks i will remove it right away .. about the execute .. i tried it with `... operator ("spread operator")` and it worked fine ! but unfortunately when the min_bedrooms_final has value with number the execute failed .. i'm figuring it out now .. and you forgot to add type s in every condition .. i don't know if i'm doing it right .. could you please look at it and tell me if it's ok .. i will post what the code is now and how it worked thanks to you and your pattern .. Many Thanks – belal Jun 07 '18 at 03:24
Maybe iterate over the array and make a string of `i`s and `s`s. The first param needs to be a string of all the data types. You should use the `i`s for integers. – user3783243 Jun 07 '18 at 03:29
ooooooh yeaaah i forgot the integer type .. Thanks A lot .. you really saved my day .. Thanks .. many thanks again – belal Jun 07 '18 at 03:31

MySQLi prepared statements select not fetching any result with no error

1 Answers1