How do I create a PDO parameterized query with a LIKE statement?

Problem

Here’s my take on it:

$query = $database->prepare('SELECT * FROM table WHERE column LIKE "?%"');

$query->execute(array('value'));

while ($results = $query->fetch()) 
{
    echo $results['column'];
}

Asked by Andrew G. Johnson

Solution #1

Right after I posted, I figured it out:

$query = $database->prepare('SELECT * FROM table WHERE column LIKE ?');
$query->execute(array('value%'));

while ($results = $query->fetch())
{
    echo $results['column'];
}

Answered by Andrew G. Johnson

Solution #2

Here’s how to utilize LIKE with percent partial matching for MySQL databases if you’re using named parameters:

WHERE column_name LIKE CONCAT('%', :dangerousstring, '%')

where dangerousstring is the designated parameter

In other words, in your own query, utilize clearly unescaped percent signs that are separated, not the user input.

Edit: Oracle databases’ concatenation syntax uses the concatenation operator: ||, so it’ll just be:

WHERE column_name LIKE '%' || :dangerousstring || '%'

However, as @bobince points out, there are several caveats:

So that’s something else to keep in mind when using like and parameterization together.

Answered by Kzqai

Solution #3

$query = $database->prepare('SELECT * FROM table WHERE column LIKE ?');
$query->bindValue(1, "%$value%", PDO::PARAM_STR);
$query->execute();

if (!$query->rowCount() == 0) 
{
    while ($results = $query->fetch()) 
    {
        echo $results['column'] . "<br />\n";
    }       
} 
else 
{
    echo 'Nothing found';
}

Answered by Blazer

Solution #4

You could also try this. I had a similar issue, but after doing some research, I was able to find a solution.

$query = $pdo_connection->prepare('SELECT * FROM table WHERE column LIKE :search');

$stmt= $pdo_connection->prepare($query);

$stmt->execute(array(':search' => '%'.$search_term.'%'));

$result = $stmt->fetchAll(PDO::FETCH_ASSOC);

print_r($result);

Answered by Vijaysinh Parmar

Solution #5

This works:

search `table` where `column` like concat('%', :column, '%')

Answered by kjdion84

Post is based on https://stackoverflow.com/questions/583336/how-do-i-create-a-pdo-parameterized-query-with-a-like-statement