[FIXED] Prepared Statements not returning the required multiple results

Issue

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?

Solution

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);
}

Answered By - Dharman