How can I bind an array of strings with a mysqli prepared statement?
bindparam, mysqli, php
Solution
From my article, Mysqli prepared statement with multiple values for IN clause:
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, like in your case, we have an arbitrary number of placeholders, we will have to add bit more code.
- First of all we will need to create a string with as many `?` marks as many elements are in your array. For this we would use `str_repeat()` function which comes very handy for the purpose.
- Then this string with comma separated question marks have to be added to the query. Although it's a variable, in this case it is safe as it contains only constant values
- then this query must be prepared just like any other query
- then we will need to create a string with types to be used with bind_param(). Note that there is usually no reason to use different types for the bound variables - mysql will happily accept them all as strings. There are edge cases, but extremely rare. For the everyday use you can always keep it simple and use "s" for the everything. `str_repeat()` is again to the rescue.
- then we need to bind our array values to the statement. Unfortunately, you cannot just write it as a single variable, like this `$stmt->bind_param("s", $array)`, only scalar variables are allowed in `bind_param()`. Luckily, there is an argument unpacking operator that does exactly what we need - sends an array of values into a function as though it's a set of distinct variables!
- the rest is as usual - execute the query, get the result and fetch your data!
So the correct example code would be
$array = ['Nashville','Knoxville']; // our array
$in = str_repeat('?,', count($array) - 1) . '?'; // placeholders
$sql = "SELECT name FROM table WHERE city IN ($in)"; // sql
$stmt = $mysqli->prepare($sql); // prepare
$types = str_repeat('s', count($array)); //types
$stmt->bind_param($types, ...$array); // bind array at once
$stmt->execute();
$result = $stmt->get_result(); // get the mysqli result
$data = $result->fetch_all(MYSQLI_ASSOC); // fetch the data
Although this code is rather big, it is incomparably smaller than any other plausible solution offered in this topic so far.
Problem
I need to bind an array of values to `WHERE IN(?)` clause. How can I do that? This works: ``` $mysqli = new mysqli("localhost", "root", "root", "db"); if(!$mysqli || $mysqli->connect_errno) { return; } $query_str = "SELECT name FROM table WHERE city IN ('Nashville','Knoxville')"; $query_prepared = $mysqli->stmt_init(); if($query_prepared && $query_prepared->prepare($query_str)) { $query_prepared->execute(); ``` But this I cannot get to work with a bind_param like this: ``` $query_str = "SELECT name FROM table WHERE city IN (?)"; $query_prepared = $mysqli->stmt_init(); if($query_prepared && $query_prepared->prepare($query_str)) { $cities = explode(",", $_GET['cities']); $str_get_cities = "'" . implode("', '", $get_cities) . "'"; // This equals 'Nashville','Knoxville' $query_prepared->bind_param("s", $cities); $query_prepared->execute(); ``` What am I doing wrong? I've also tried `call_user_func_array`, but I can't seem to get the correct syntax.