-1

The following line throws an exception -

$sth = $db->prepare("SELECT * FROM providers WHERE name LIKE %:name%");

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[HY000]: General error: 1 near "%": syntax error'

Taking the % signs out gets rid of the error, but obviously the search doesn't work as I want it. I've tried toying with quotes, putting in an actual value instead of the :-prefixed PDO variable, the only thing that gets rid of the error is removing the % signs. I'm at a loss.

Dan Goodspeed
  • 3,484
  • 4
  • 26
  • 35
  • 2
    http://stackoverflow.com/questions/11117134/implement-like-query-in-pdo – Royal Bg Oct 29 '13 at 07:15
  • @RoyalBg, that SO actually didn't help me at all. My parameters come straight from the $_POST, and modifying that didn't seem like a good solution. Knittl's answer of putting the % signs in quotes in the query and concatenating them with || was the exact answer I needed. – Dan Goodspeed Oct 29 '13 at 07:43
  • you didn't mention that in the beginning :) – Royal Bg Oct 29 '13 at 07:51

1 Answers1

1

The LIKE operator takes a string:

$db->prepare("SELECT * FROM providers WHERE name LIKE '%' + :name + '%'");

Note that an empty :name will match everything ('%%')

Sqlite uses || to concatenate strings, so '%' || :name || '%' should do.

Another way (taken from this question) is to add the percent signs to the value of your param. So you'd have a query LIKE :name and then before binding the parameter, add the percent signs: $name = '%'.$name.'%'; $sth->execute(array('name' => $name));

Community
  • 1
  • 1
knittl
  • 246,190
  • 53
  • 318
  • 364
  • Thanks! This worked! I'd actually say this is probably my best experience with stackoverflow to date. After several hours banging my head against the desk, to get a workable answer in the first two minutes after posting the question. If only it didn't make me wait 15 minutes before being allowed to check your answer as "accepted". – Dan Goodspeed Oct 29 '13 at 07:23