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

Here's my attempt at it:

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

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

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


Solution 1:[1]

For those using named parameters, here's how to use LIKE with % partial matching for MySQL databases: 

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

where the named parameter is :dangerousstring.

In other words, use explicitly unescaped % signs in your own query that are separated and definitely not the user input.

Edit: Concatenation syntax for Oracle databases uses the concatenation operator: ||, so it'll simply become:

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

However there are caveats as @bobince mentions here that:

The difficulty comes when you want to allow a literal % or _ character in the search string, without having it act as a wildcard.

So that's something else to watch out for when combining like and parameterization.

Solution 2:[2]

$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';
}

Solution 3:[3]

You can also try this one. I face similar problem but got result after research.

$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);

Solution 4:[4]

This works:

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

Solution 5:[5]

I got this from php delusions

$search = "%$search%";
$stmt  = $pdo->prepare("SELECT * FROM table WHERE name LIKE ?");
$stmt->execute([$search]);
$data = $stmt->fetchAll();

And it works for me, very simple. Like he says , you have to "prepare our complete literal first" before sending it to the query

Solution 6:[6]

PDO escapes "%" (May lead to sql injection): The use of the previous code will give the desire results when looking to match partial strings BUT if a visitor types the character "%" you will still get results even if you don't have anything stored in the data base (it may lead sql injections)

I've tried a lot of variation all with the same result PDO is escaping "%" leading unwanted/unexcited search results.

I though it was worth sharing if anyone has found a word around it please share it

Solution 7:[7]

I had a similar need but was using a variable grabbed from a form. I did it like this to get results from my PostgreSQL DB, using PHP:

<?php
     $player = $_POST['search'];  //variable from my search form
     $find = $sqlPDO->prepare("SELECT player FROM salaries WHERE player ILIKE ?;");
     $find->execute(['%'.$player.'%']);

     while ($row = $find->fetch()) {
         echo $row['player']."</br>";
     }
?>

The "ILIKE" makes the search non-case sensitive, so a search for cart or Cart or cARt will all return the same results.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Christian
Solution 2 George G
Solution 3 josliber
Solution 4 kjdion84
Solution 5 gavin stanley
Solution 6 Ozkar R
Solution 7 zdarma

Related Questions