PDO LIKE

Summary: in this tutorial, you’ll learn how to use PDO to execute a query with the LIKE operator.

Introduction to the SQL LIKE operator

The LIKE operator returns true if a character string matches a specified pattern. Typically, a pattern includes wildcard characters like:

• % matches any string of zero or more character
• _ matches any single character.

For example, the %er% will match any string that contains the string er, e.g., peter, understand, etc.

Typically, you use the LIKE operator in the WHERE clause of the SELECT, UPDATE, and DELETE statement.

Execute a query that contains the LIKE operator in PDO

To execute a query that contains a LIKE operator in PDO, you need to construct the pattern upfront.

For example, to select the book with titles that contain the string ‘es, you first construct a SELECT statement like this:

$sql = 'SELECT book_id, title 
        FROM books 
        WHERE title LIKE :pattern';Code language: PHP (php)

And then bind the string '%es%' to the prepared statement.

The following example illustrates how to execute a query that includes the LIKE operator:

<?php

/**
* Find books by title based on a pattern
*/
function find_book_by_title(\PDO $pdo, string $keyword): array
{
    $pattern = '%' . $keyword . '%';

    $sql = 'SELECT book_id, title 
        FROM books 
        WHERE title LIKE :pattern';

    $statement = $pdo->prepare($sql);
    $statement->execute([':pattern' => $pattern]);

    return  $statement->fetchAll(PDO::FETCH_ASSOC);
}

// connect to the database
$pdo = require 'connect.php';

// find books with the title matches 'es'
$books = find_book_by_title($pdo, 'es');

foreach ($books as $book) {
    echo $book['title'] . '<br>';
}Code language: PHP (php)

How it works.

The function find_book_by_title() returns the books with the title that matches with the $keyword.

First, make the pattern by adding the wildcard characters to the beginning and end of the $keyword:

$pattern = '%' . $keyword . '%';Code language: PHP (php)

Second, construct an SQL statement that contains a LIKE operator in the WHERE clause:

$sql = 'SELECT book_id, title 
        FROM books 
        WHERE title LIKE :pattern';Code language: PHP (php)

Third, create a prepared statement:

$statement = $pdo->prepare($sql);Code language: PHP (php)

After that, execute the statement with the value that comes from the pattern:

$statement->execute([':pattern' => $pattern]);Code language: PHP (php)

Finally, return all rows from the result set by using the fetchAll() method:

return  $statement->fetchAll(PDO::FETCH_ASSOC);Code language: PHP (php)

The following code find books with the title contains the keyword 'es':

// connect to the database
$pdo = require 'connect.php';

// find books with the title matches 'es'
$books = find_book_by_title($pdo, 'es');

foreach ($books as $book) {
    echo $book['title'] . '<br>';
}Code language: PHP (php)

Output:

Marcus Makes a Movie
Box of ButterfliesCode language: PHP (php)

Summary

• Construct a pattern before passing it to a prepared statement that includes the LIKE operator.