PDO IN

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

A quick introduction to the IN operator

The IN operator returns true if a value is in a set of values. The IN operator can be used in the WHERE clause of the SELECT, UPDATE and DELETE statement.

For example, to get a list of books from the books table with id is either 1, 2, or 3, you can use the IN operator like this:

SELECT book_id, title
FROM books
WHERE book_id IN (1,2,3);Code language: SQL (Structured Query Language) (sql)

It’s equivalent to the = and OR operator:

SELECT book_id, title
FROM books
WHERE book_id = 1 OR 
      book_id = 2 OR 
      book_id = 3;Code language: SQL (Structured Query Language) (sql)

Execute an SQL statement with the IN operator using PDO

To execute this SQL statement in PDO, you need to construct a statement with the placeholders (?) like this:

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

And use a prepared statement to bind the values from the array:

$statement = $pdo->prepare($sql);
$statement->execute([1,2,3]);Code language: PHP (php)

In practice, the size of the id list is dynamic. Typically, you don’t know how many values will be passed to both the $sql and execute() method.

If you use one placeholder like the following, it won’t work:

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

The reason is that when you bind the values, the statement will look like this:

SELECT book_id, title
FROM books 
WHERE book_id IN ('1,2,3');Code language: SQL (Structured Query Language) (sql)

However, the correct query is:

SELECT book_id, title
FROM books 
WHERE book_id IN ('1','2','3');Code language: SQL (Structured Query Language) (sql)

To fix this, you need to construct the SQL statement based on the number of elements in the array. The following example illustrates the solution:

<?php

/**
* Return an array of books with the book id in the $list
*/
function get_book_list(\PDO $pdo, array $list): array
{
    $placeholder = str_repeat('?,', count($list) - 1) . '?';

    $sql = "SELECT book_id, title 
            FROM books 
            WHERE book_id in ($placeholder)";

    $statement = $pdo->prepare($sql);
    $statement->execute($list);

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

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

// get a list of book
$books = get_book_list($pdo, [1, 2, 3]);

print_r($books);Code language: PHP (php)

How it works.

The get_book_list() function accepts a PDO object and an array of book id. It returns an array of books.

First, generate a list of the placeholders (?) based on the number of elements in the $list array:

$placeholder = str_repeat('?,', count($list) - 1) . '?';Code language: PHP (php)

For example, if the $list has three elements, the placeholder wil be ‘?,?,?’;

Next, use the placeholder to construct the SQL statement:

$sql = "SELECT book_id, title 
        FROM books 
        WHERE book_id in ($placeholder)";Code language: PHP (php)

Then, prepare the statement for execution:

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

After that, execute the statement by passing the $list:

$statement->execute($list);Code language: PHP (php)

Finally, fetch all rows from the result set and return an associative array:

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

The following code connects to the database and uses the get_book_list() function to get the book with id in the set 1, 2, and 3:

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

// get a list of book
$books = get_book_list($pdo, [1, 2, 3]);

print_r($books);Code language: PHP (php)

Summary

• Generate placeholders (?) and construct the SQL statement with the IN operator.
• Use a prepared statement to execute the SQL statement by passing an array of values.