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.
Did you find this tutorial useful?