Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

Tags:

php

pdo

People also ask

How to use LIKE in PDO?

To execute a query that contains a LIKE operator in PDO, you need to construct the pattern upfront. And then bind the string '%es%' to the prepared statement. How it works. The function find_book_by_title() returns the books with the title that matches with the $keyword .

What is PDO prepared statement?

In layman's terms, PDO prepared statements work like this: Prepare an SQL query with empty values as placeholders with either a question mark or a variable name with a colon preceding it for each value. Bind values or variables to the placeholders. Execute query simultaneously.

What does the prepare method of a PDO object return when called successfully?

Return Values ¶ If the database server successfully prepares the statement, PDO::prepare() returns a PDOStatement object. If the database server cannot successfully prepare the statement, PDO::prepare() returns false or emits PDOException (depending on error handling).

What function do you use to run a query using a PDO object?

PDO::query() prepares and executes an SQL statement in a single function call, returning the statement as a PDOStatement object.


Figured it out right after I posted:

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

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

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.


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

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

This works:

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

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


Donate For Us

If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!