CODEWITHSUNDEEP
phpsqlsearchpdo
David Renolds
David Renolds

Reputation: 231

Using named parameters with PDO for LIKE

I am trying to search the name field in my database using LIKE. If I craft the SQL 'by hand` like this:

$query = "SELECT * \n"
        . "FROM `help_article` \n"
        . "WHERE `name` LIKE '%how%'\n"
        . "";
$sql = $db->prepare($query);
$sql->setFetchMode(PDO::FETCH_ASSOC);
$sql->execute();

Then it will return relevant results for 'how'.
However, when I turn it into a prepared statement:

$query = "SELECT * \n"
        . "FROM `help_article` \n"
        . "WHERE `name` LIKE '%:term%'\n"
        . "";
$sql->execute(array(":term" => $_GET["search"]));
$sql->setFetchMode(PDO::FETCH_ASSOC);
$sql->execute();

I am always getting zero results.

What am I doing wrong? I am using prepared statements in other places in my code and they work fine.

Upvotes: 23

Views: 16693

Answers (1)

mario
mario

Reputation: 145492

The bound :placeholders are not to be enclosed in single quotes. That way they won't get interpreted, but treated as raw strings.

When you want to use one as LIKE pattern, then pass the % together with the value:

$query = "SELECT * 
          FROM `help_article` 
          WHERE `name` LIKE :term ";

$sql->execute(array(":term" => "%" . $_GET["search"] . "%"));

Oh, and actually you need to clean the input string here first (addcslashes). If the user supplies any extraneous % chars within the parameter, then they become part of the LIKE match pattern. Remember that the whole of the :term parameter is passed as string value, and all %s within that string become placeholders for the LIKE clause.

Upvotes: 40

Related Questions