2

If I run this simple query from the console by directly typing

 SELECT COUNT(*) AS total FROM articles WHERE MATCH(title) AGAINST ('+php +mysql' IN BOOLEAN MODE)

I get plenty of results. Now I'm trying to prepare this statement in php.

$keywords = ['php', 'mysql'];
$against = '';
for($i = 0; $i < count($keywords); $i++){
    $against .= '+? ';  
}
$query = 'SELECT COUNT(*) AS total FROM articles WHERE MATCH(title) AGAINST ('.$against.' IN BOOLEAN MODE)';

//my query is now 'SELECT COUNT(*) AS total FROM articles WHERE MATCH(title) AGAINST (+? +?  IN BOOLEAN MODE)'

$stmt = $pdo->prepare($query);
$stmt->execute($keywords);

This script only returns results if only 1 keyword is used. With 2 keywords, it returns no value and no error (but works in the console). So I suspect it's the way I prepare my statement with pdo. Any idea?

Eric
  • 9,870
  • 14
  • 66
  • 102
  • Does the spacing matter? The query that is run in console has spaces between the two keywords whereas the query generated for PDO will have all the keywords created as a single string only separated by '+'. – Dhruv Saxena Jan 22 '17 at 21:23
  • Edited for proper spacing. – Eric Jan 22 '17 at 21:23
  • 1
    The query in your console has all keywords encapsulated in quotes (`'+php +mysql'`) whereas your prepared query looks like `AGAINST (+php +mysql IN BOOLEAN MODE)` - maybe that's it? Try to use a single placeholder and build the string as 1 parameter maybe? – ccKep Jan 22 '17 at 21:28

1 Answers1

4

You should simply create a statement like this:

$query = 'SELECT COUNT(*) AS total FROM articles WHERE MATCH(title) AGAINST (:query IN BOOLEAN MODE)';
$stmt = $pdo->prepare($query);

And then bind the params:

$keywords = ['php', 'mysql'];
$against = '';
for($i = 0; $i < count($keywords); $i++){
    $against .= ' +' . $keywords[$i];   
}
$stmt->execute(array(
    ':query' => $against
));
Alon Eitan
  • 11,997
  • 8
  • 49
  • 58
  • I would need to have AGAINST with sometjing like this: AGAISNT ( ' "john doe " ' IN BOOLEAN MODE). I don't know how to write $against. I tried $against="\"joh doe\""; but it doesn't work. Do you know how can I manage this? – Ludo Feb 12 '20 at 17:29
  • I found the solution on this post:https://stackoverflow.com/questions/27053879/force-exact-string-match-for-pdo-prepared-statements – Ludo Feb 12 '20 at 18:15
  • 1
    where is the binding happening in this answer? or are they saying after you prepare() to THAN bind? – twinaholic Mar 10 '20 at 16:31
  • 1
    @twinaholic I just noticed that there's a bug in my answer (3 years ago) - This is wrong: `$stmt = $pdo->prepare(array(':query' => $against));`, it should be `$stmt->execute(array(':query' => $against));` instead – Alon Eitan Mar 10 '20 at 16:38
  • @AlonEitan will you fix your asnwer? Also, consider removing ```$stmt = $pdo->prepare(array( ':query' => $against ));``` from your answer, you bring it up twice, I worry that new users will get confused. – twinaholic Mar 10 '20 at 16:45
  • @twinaholic Sure :) Thanks for letting me know! – Alon Eitan Mar 10 '20 at 16:47