Prepared Statements not returning the required multiple results

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?

Thank you for your time and effort.

source https://stackoverflow.com/questions/70598229/prepared-statements-not-returning-the-required-multiple-results