Prepared Statements not returning the required multiple results

Question

I have made this function after getting some help from How to perform a LIKE query using multiple keywords from search field using mysqli prepared statement , but made some changes as I have only one search parameter.

function searchResult($con, $search) {
   $keywords = explode(" ", $search);
   $total_keywords = count($keywords);
   $myquery = "SELECT * FROM products WHERE name LIKE CONCAT('%',?,'%')";
      for ($i=1 ; $i < $total_keywords; $i++) {
          $myquery .= " OR name LIKE CONCAT('%',?,'%')";
      }
   $stmt = mysqli_stmt_init($con);
   //check if the statement is not prepared
   if (!mysqli_stmt_prepare($stmt, $myquery)) {
       header("location: search.php?errorid=stmt_failed");
       exit();
   }
   $typeparam = '';
   foreach ($keywords as $key => $value) {
       $typeparam .= 's';
   }
   $bind_param = array();
   $bind_param[] =& $typeparam;
   foreach ($keywords as $key => $value) {
       $bind_param[]=&$keywords[$key];
   }
   call_user_func_array(array($stmt,'bind_param'), $bind_param);
   mysqli_stmt_execute($stmt);
   $matches = mysqli_stmt_get_result($stmt);
   if ($searches = mysqli_fetch_assoc($matches)) {
       return $searches;
   } else {
       $result = false;
       return $result;
   }
   mysqli_stmt_close($stmt);
}

Now, I have a table named products and in that table I have:

Nikon Paint bucket

Bricks

Asian Paint 2 liter Blue finish

PVC pipe

When I search only bricks, it return Bricks, similarly all the other products are returned. However, by searching bricks paint it only returns Nikon Paint bucket, but it should return Bricks as well as both the Paint results in the table. So, there are two issues, one is that it does not take multiple keywords into account. The other issue is that, it does not return two results having same keyword.

Also, Prepared statements is the best way to prevent SQL injection, but is it wise to use it in search bar as mysqli_real_escape_string() seems inefficient. Is there any other way to make a safe search bar?

If I were you, I would completely forget about the existence of `mysqli_real_escape_string`. Don't go that route. Just use prepared statements all the time. — Dharman, Jan 05 '22 at 20:24
I have gotten your point about escaping. Also, I am not as much familiar with PDO but looks like PDO is quite reliable and much more giving. so, PDO is the way forward. — lovecoding, Jan 06 '22 at 07:45

score 1 · Accepted Answer · answered Jan 05 '22 at 20:33

Your function returns only one row from the result set. Calling mysqli_fetch_assoc() produces only a single row.

If you want to fetch all rows from the result set, you should call mysqli_fetch_all(). I have cleaned up the implementation and used mysqli_fetch_all() that will give you an array of rows.

function searchResult(mysqli $con, string $search): array
{
    $keywords = explode(" ", $search);
    $total_keywords = count($keywords);
    $myquery = "SELECT * FROM products WHERE name LIKE CONCAT('%',?,'%')";
    for ($i = 1; $i < $total_keywords; $i++) {
        $myquery .= " OR name LIKE CONCAT('%',?,'%')";
    }
    $stmt = mysqli_prepare($con, $myquery);
    mysqli_stmt_bind_param($stmt, str_repeat('s', $total_keywords), ...$keywords);
    mysqli_stmt_execute($stmt);
    $matches = mysqli_stmt_get_result($stmt);

    return mysqli_fetch_all($matches, MYSQLI_ASSOC);
}

Don't forget to enable mysqli error reporting.

However, I would strongly recommend to use PDO if you can. PDO is much easier to use. Consider the same functionality using PDO:

function searchResult(PDO $con, string $search): array
{
    $keywords = explode(" ", $search);
    $total_keywords = count($keywords);
    $myquery = "SELECT * FROM products WHERE name LIKE CONCAT('%',?,'%')";
    for ($i = 1; $i < $total_keywords; $i++) {
        $myquery .= " OR name LIKE CONCAT('%',?,'%')";
    }
    $stmt = $con->prepare($myquery);
    $stmt->execute($keywords);
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

This answer works, looks like mysql_fetch_assoc() was the problem after all. However, if I may ask, why have you used the three dots before the $keywords in bind_param? Also, how can I skip underscore in the search as whenever I search _ it returns the complete table. The PDO solution works fine as well and much more simple. Thank you for your help again. — lovecoding, Jan 06 '22 at 07:49

Prepared Statements not returning the required multiple results

1 Answers1