Correct syntax for PDO LIKE statement

Question

I have a mysql query using PDO, where i would like to use the LIKE statement to search my database for values.

I am currently using the query

$selectdp = $connection->prepare(
    "SELECT `FilePath`,`ItemPicID`,`Extension`
    FROM `ItemPics`
    WHERE `BusinessID`=:bizid
    AND `ItemID`=:itemid
    AND `FilePath` LIKE :search ");

with

$selectdp->bindValue(":search",'%DP');

but i am getting the error

Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '%DP' ....

Things i've tried in the query

LIKE=:search
LIKE CONCAT('%'.:search)

I've taken a look at

implement LIKE query in PDO and How prepare statement with bindvalue and %? but nothing seems to be working.

Please note that it's unnecessary and distracting to quote your column and table names. — Andy Lester, Jan 06 '14 at 06:01
possible duplicate of [implement LIKE query in PDO](http://stackoverflow.com/questions/11117134/implement-like-query-in-pdo) — david strachan, Jan 06 '14 at 10:13

score 1 · Answer 1 · edited May 23 '17 at 10:32

I was taught to concatenate for LIKE expressions within the SQL, and set a parameter within the concatenation. For example:

$selectdp = $connection->prepare(
    "SELECT `FilePath`,`ItemPicID`,`Extension`
    FROM `ItemPics`
    WHERE `BusinessID`=:bizid
    AND `ItemID`=:itemid
    AND `FilePath` LIKE concat('%', :search)

Take a look at Kzqai's answer here, who seems to understand it much more than I do.

From his explanation, a primary difficult with this is preventing the wildcard character from acting as a literal.

Correct syntax for PDO LIKE statement

1 Answers1