Mysqli prepared statement with multiple values for IN clause

  1. PHP 8.2 way. execute_query()
  2. PHP 8.1 way. Array into execute()
  3. Older versions, prepare/bind/execute way
  4. Comments (5)

Before running any query with mysqli, make sure you've got a properly configured mysqli connection variable that is required in order to run SQL queries and to inform you of the possible errors.

Imagine you've got an array of values that you want to use in your SQL query.

PHP 8.2 way. execute_query()

Since PHP 8.2 you can use a handy function execute_query()

// INSERT example
$sql = "INSERT INTO users (email, password) VALUES (?,?)"; // sql
$mysqli->execute_query($sql,[$email, $password]); // in one go

In case your array has variable length, you need to create a list of placeholders dynamically

// WHERE IN example
$array = ['Nashville','Knoxville']; // our array
$parameters = str_repeat('?,', count($array) - 1) . '?'; // placeholders
$sql = "SELECT name FROM table WHERE city IN ($parameters)"; // sql
$result = $mysqli->execute_query($sql, $array); // in one go
$data = $result->fetch_all(MYSQLI_ASSOC); // fetch the data   

PHP 8.1 way. Array into execute()

Since PHP 8.1 you can pass an array directly to execute:

// INSERT example
$sql = "INSERT INTO users (email, password) VALUES (?,?)"; // sql
$stmt = $mysqli->prepare($sql); // prepare
$stmt->execute([$email, $password]); // execute with data! 

// WHERE IN example
$array = ['Nashville','Knoxville']; // our array
$parameters = str_repeat('?,', count($array) - 1) . '?'; // placeholders
$sql = "SELECT name FROM table WHERE city IN ($parameters)"; // sql
$stmt = $mysqli->prepare($sql); // prepare
$stmt->execute($array);
$result = $stmt->get_result(); // get the mysqli result
$data = $result->fetch_all(MYSQLI_ASSOC); // fetch the data   

Older versions, prepare/bind/execute way

For the earlier versions the task is a bit more elaborate.

// INSERT example
$sql = "INSERT INTO users (email, password) VALUES (?,?)"; // sql
$data = [$email, $password]; // put your data into array
$stmt = $mysqli->prepare($sql); // prepare
$stmt->bind_param(str_repeat('s', count($data)), ...$data); // bind 
$stmt->execute();

While There are several tricks that would help us with this challenge.

Here is the code:

$array = [1,2,3]; // our array
$in  = str_repeat('?,', count($array) - 1) . '?';
$sql = "SELECT * FROM table WHERE column IN ($in)";
$stmt  = $mysqli->prepare($sql);
$types = str_repeat('s', count($array));
$stmt->bind_param($types, ...$array);
$stmt->execute();
$result = $stmt->get_result(); // get the mysqli result
$data = $result->fetch_all(MYSQLI_ASSOC); // fetch data   

Related articles: