How to Bind an Array of Strings to a MySQLi WHERE IN Clause?

Binding an Array of Strings in a WHERE IN Clause Using MySQLi Prepared Statements

You can encounter challenges when attempting to bind an array of values to the WHERE IN clause using MySQLi. One common error is using bind_param incorrectly. Here's how to resolve this issue:

PHP 8.2 and Above: Execute_query()

PHP 8.2 introduced the execute_query() function, which makes it straightforward to execute prepared statements with multiple values.

Example:

$mysqli = new mysqli("localhost", "root", "root", "db");
$array = ['Nashville','Knoxville'];
$parameters = str_repeat('?,', count($array) - 1) . '?';
$sql = "SELECT name FROM table WHERE city IN ($parameters)";
$result = $mysqli->execute_query($sql, $array);
$data = $result->fetch_all(MYSQLI_ASSOC);

PHP 8.1: Array into execute()

For PHP 8.1, you can pass an array directly to execute():

$mysqli = new mysqli("localhost", "root", "root", "db");
$array = ['Nashville','Knoxville'];
$sql = "SELECT name FROM table WHERE city IN (".str_repeat('?,', count($array) - 1) . '?)';
$stmt = $mysqli->prepare($sql);
$stmt->execute($array);
$result = $stmt->get_result();
$data = $result->fetch_all(MYSQLI_ASSOC);

Older PHP Versions: Prepare/Bind/Execute

For older versions, use the following approach:

$mysqli = new mysqli("localhost", "root", "root", "db");
$array = ['Nashville','Knoxville'];
$in = str_repeat('?,', count($array) - 1) . '?';
$sql = "SELECT name FROM table WHERE city IN ($in)";
$types = str_repeat('s', count($array));
$stmt = $mysqli->prepare($sql);
$stmt->bind_param($types, ...$array);
$stmt->execute();
$result = $stmt->get_result();
$data = $result->fetch_all(MYSQLI_ASSOC);

The above is the detailed content of How to Bind an Array of Strings to a MySQLi WHERE IN Clause?. For more information, please follow other related articles on the PHP Chinese website!