5

I am trying to write a keyword search using PDO prepared statements. Ideally, I'd like to make use of the LIKE operator to search for a substring inside the field value. Here is my code:

$statement = $this->db->prepare("select * from whatever where title like ? or author like ?");
$statement->execute(array("%$titleKeyword%","%$authorKeyword%"));
$rows = $statement->fetchAll(PDO::FETCH_ASSOC);

Unfortunately, $rows is always empty when I try this. However, if I copy the SQL into phpMyAdmin, and substitute '%keyword%' for each of the ? symbols, it works fine (I get results when the keyword used exists).

I have also tried the following code:

$statement = $this->db->prepare("select * from whatever where title like :titleKeyword or author like :authorKeyword");
$statement->bindValue(":titleKeyword",  '%'.$titleKeyword.'%',  PDO::PARAM_STR);
$statement->bindValue(":authorKeyword", '%'.$authorKeyword.'%', PDO::PARAM_STR);
$statement->execute();
$rows = $statement->fetchAll(PDO::FETCH_ASSOC);

I had read in another question that you are supposed to include the % when binding the parameters, not in the SQL itself (pre-prepared statement), but that doesn't work.

I could resort to just inserting the keyword directly into the SQL (after doing some sanitization), but I want to stick with prepared statements. Any help would be greatly appreciated.

steffen
  • 16,138
  • 4
  • 42
  • 81
Randy
  • 69
  • 1
  • 4
  • 1
    Just done a quick mock of this and working fine (PHP 5.3.5) ~You definitely want to add your `%` when binding values. Maybe problems with your $titleKeyword & $authorKeyword? – TerryProbert Oct 17 '12 at 08:31
  • Unfortunately, no. I checked those before posting on here. – Randy Oct 17 '12 at 08:38

2 Answers2

3

This actually works for me:

$stmt = $pdo->prepare("select * from t where c like ?");
$stmt->execute(array("70%"));
print_r($stmt->fetchAll());

What PHP version do you use?

steffen
  • 16,138
  • 4
  • 42
  • 81
  • I'm using 5.3.1. Even some comments on the PHP docs for PDOStatement suggest using one of those two methods. – Randy Oct 17 '12 at 08:17
  • 1
    Double check your variables $titleKeyword and $authorKeyword (use ```var_dump```!) – steffen Oct 17 '12 at 08:26
  • Already done (this is in a version where I modify the vars to have % before and after before I call bindValue): string(6) "%iPod%" string(6) "%iPod%" – Randy Oct 17 '12 at 08:36
  • Are you using the same databases in PDO and phpMyAdmin? Then enable the mysql query log (see http://dev.mysql.com/doc/refman/5.6/en/query-log.html) – steffen Oct 17 '12 at 08:47
1

Thanks Steffen and Terry for the help.

I ended up solving the problem myself by switching to bindParam() instead of bindValue(). I am not sure why I couldn't do it with bindValue(), but right now I am just too tired to care.

Randy
  • 69
  • 1
  • 4
  • 1
    Glad you figured it out Randy! further reading; http://stackoverflow.com/questions/1179874/pdo-bindparam-versus-bindvalue – TerryProbert Oct 17 '12 at 08:58