How to perform a LIKE query using multiple keywords from search field using mysqli prepared statement

Question

This question is similar to this previously asked question except that I am using mysqli instead of PDO.

I perform the query as such:

$word=preg_split('/[\s]+/',$terms);
$totalwords=count($word);

$terms is obtained further up from a GET

$sql="SELECT title,content FROM articles WHERE (title LIKE CONCAT('%',?,'%') OR (content LIKE CONCAT('%',?,'%'))";

for(i=1;$i<$totalwords;$i++){
  $sql.=" OR (title LIKE CONCAT('%',?,'%') OR (content LIKE CONCAT('%',?,'%'))";
}

$stmt=$conn->prepare($sql);
foreach($word as $key => $keyword){
  $term=$keyword;
  $stmt->bind_param('ss',$term,$term);
}
$stmt->execute;
$stmt->store_result;

If I enter one word in the search form, I get the right results, however, if I enter more than one word, I get no results at all.

So I included a print_r($sql); to see what was being sent to the database and this is what I get for one term (which works fine):

SELECT title,content FROM articles WHERE (title LIKE CONCAT('%',?,'%') OR content LIKE CONCAT('%',?,'%'));

and this is what I get for multiple words (which returns no results even though it should):

SELECT title,content FROM articles WHERE (title LIKE CONCAT('%',?,'%') OR content LIKE CONCAT('%',?,'%')) OR (title LIKE CONCAT('%',?,'%') OR content LIKE CONCAT('%',?,'%'));

which seems like everything is working fine, so I turned on my mysql logs and discovered that the single word queries show up but the multi-word queries are non-existent.

So I added print_r($stmt); and I get Commands out of sync; you can't run this command now for multi-word queries.

What am I doing wrong?

You could do `CONCAT(title, ' ', content) LIKE CONCAT('%',?,'%')` then you only need one binding, not two. I'd expect an error message about the number of placeholders not matching the number of parameters. The previous question is using PDO which supports named parameters. Mysqli, IMO, creates a hassle with their way of binding. — user3783243, Jun 28 '18 at 17:00
Unrelated but I wouldn't make the `$term` variable, just use `$keyword`... or just do `=> $term`. — user3783243, Jun 28 '18 at 17:07
@user3783243 Thanks for pointing out the mismatch of the bound parameters to the placeholders. I was not noticing that at all. I provided a solution below since one hasn't been provided yet. — thequerist, Jul 02 '18 at 13:53
I can move that comment to an answer if it resolved your issue. I wasn't sure if it would/wouldn't, and I didn't test it. — user3783243, Jul 02 '18 at 14:06
Also, in addition to that issue, I was binding the parameters inside the loop for some reason. — thequerist, Jul 02 '18 at 14:19

thequerist · Accepted Answer · 2018-07-02T14:31:53.283

As user3783243 states in the comments above, my placeholders and parameters where not matching. So in order to solve that, I did the following (this will be sloppy as I'm new to PHP but if someone can clean it up for me I'll award the answer to that).

First you have to create a string for the type parameter (mine are all strings so this was easy, you could run a conditional statement if you have different types). Since I use two placeholders for each entry in my SQL, each iteration will include two s's.

$typeparam='';
foreach($word as $key => $value){
  $typeparam.='ss';
}

Then create a new array to put the types and the values all together (again, since there are two placeholders for each parameter, I just add the $word twice to the array):

$bindpars=array();

$bindpars[]=&$typeparam;
foreach($word as $key => $value){
  $bindpars[]=&$word[$key];
  $bindpars[]=&$word[$key];
}

Finally, bind the parameters using call_user_func_array:

call_user_func_array(array($stmt,'bind_param'),$bindpars);

So the code in my question now looks like this:

$word=preg_split('/[\s]+/',$terms);
$totalwords=count($word);

$sql="SELECT title,content FROM articles WHERE (title LIKE CONCAT('%',?,'%') OR (content LIKE CONCAT('%',?,'%'))";

for(i=1;$i<$totalwords;$i++){
  $sql.=" AND (title LIKE CONCAT('%',?,'%') OR (content LIKE CONCAT('%',?,'%'))";
}

$stmt=$conn->prepare($sql);

$typeparam='';
foreach($word as $key => $value){
  $typeparam.='ss';
}

$bindpars=array();

$bindpars[]=&$typeparam;
foreach($word as $key => $value){
  $bindpars[]=&$word[$key];
  $bindpars[]=&$word[$key];
}

call_user_func_array(array($stmt,'bind_param'),$bindpars);

$stmt->execute;
$stmt->store_result;

How to perform a LIKE query using multiple keywords from search field using mysqli prepared statement

1 Answers1

Linked