How can I bind an array of strings with a mysqli prepared statement?

Question

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.

`$str_get_cities= "'".implode("','", $get_cities)."'";` . Do not use quotes !! This is done bye `bind_param` with the "s" option ! — moskito-x, Jul 10 '13 at 14:14
As others recommend, use **call_user_func_array** function to bind required parameters to you parametrized query. Just to emphasise that it accepts parameters passed by reference. I could find only pieces of code of how execute parametrized queries with dynamic amount of parameters to bind, so I ended up doing my own function (see [this](http://stackoverflow.com/a/41809415/1424613) post). It accepts any parametrized SELECT, UPDATE, INSERT and DELETE queries and it helps me a lot to dynamically do any MySQL DB interaction in my PHP code. — mbuster, Jan 23 '17 at 15:16
Earlier, clear, complete question asking the exact same thing: [Use an array in a mysqli prepared statement: `WHERE .. IN(..)` query](https://stackoverflow.com/q/3703180/2943403) — mickmackusa, Apr 03 '22 at 04:59

Your Common Sense · Answer 1 · 2023-03-16T06:23:17.600

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.

I don't get the first paragraph of your answer. I don't think a question supposed to provide answer, he is seeking answer which you and others answered. What I was looking for wasn't how many quesiton marks I need to add, that I already had. I couldn't find a proper explanation of bind_param as it seems php.net document didn't care about it. I wanted to know if I can pass an array as parameter to it. — AaA, Apr 17 '20 at 14:00
@AaA there is another paragraph in my answer, one mentions an argument unpacking operator. did it answer your question? — Your Common Sense, Apr 18 '20 at 03:39
Thanks, the "argument unpacked operator" was the answer for me. This answer solves many other similar questions with solutions very elaborated. — Marco Muciño, Jul 23 '20 at 22:02
this doesnt work.`$stmt->bind_param($paramstring, $params );` give me `Array to string conversion` every time — djack109, Dec 31 '20 at 17:01

score 12 · Answer 2 · edited Feb 01 '23 at 18:42

You can not bind two variables with one question mark!

For every variable you bind, you need one question mark.

"bind_param" checks each variable whether it matches the requirements. Afterwards, the string value is placed between quotes.

This will not work:

"SELECT name FROM table WHERE city IN (?)"; ( becomes too )
$q_prepared->bind_param("s", $cities);
"SELECT name FROM table WHERE city IN ('city1,city2,city3,city4')";

It must be:

"SELECT name FROM table WHERE city IN (?,?,?,?)"; ( becomes too )
$q_prepared->bind_param("ssss", $city1, $city2, $city3, $city4);
"SELECT name FROM table WHERE city IN ('city1', 'city2', 'city3', 'city4')";

$query_prepared->bind_param quotes string parameters one by one. And the number of variables and length of string types must match the parameters in the statement.

$query_str = "SELECT name FROM table WHERE city IN ('Nashville','Knoxville')";

will become

$query_str = "SELECT name FROM table WHERE city IN (?,?)";

Now bind_param must be

bind_param("ss", $arg1, $arg2)

with this

$query_str = "SELECT name FROM table WHERE city IN (?)";

and bind_param with

bind_param("s", $cities)

You get:

$query_str = "SELECT name FROM table WHERE city IN ('Nashville,Knoxville')";

That's why an array does not work. The only solution for this fact is call_user_func_array.

If you initialise a statement, the following is unnecessary:

$query_prepared = $mysqli->stmt_init();
if($query_prepared && $query_prepared->prepare($query_str)) {

This is correct:

$query_prepared = $mysqli->stmt_init();
if($query_prepared->prepare($query_str)) {

If you don't want to use call_user_func_array and you have only a small count of arguments, you can do it with the following code.

[...]
$cities = explode(",", $_GET['cities']);
if (count($cities) > 3) { echo "too many arguments"; }
else
{
    $count = count($cities);
    $SetIn = "(";
    for($i = 0; $i < $count; ++$i)
    {
        $code .= 's';
        if ($i>0) {$SetIn.=",?";} else {$SetIn.="?";}
    }
    $SetIn .= ")";
    $query_str = "SELECT name FROM table WHERE city IN " . $SetIn;
    // With two arguments, $query_str will look like
    // SELECT name FROM table WHERE city IN (?,?)
    $query_prepared = $mysqli->stmt_init();
    if($query_prepared->prepare($query_str))
    {
        if ($count==1) { $query_prepared->bind_param($code, $cities[0]);}
        if ($count==2) { $query_prepared->bind_param($code, $cities[0], $cities[1]);}
        if ($count==3) { $query_prepared->bind_param($code, $cities[0], $cities[1], $cities[2]);
        // With two arguments, $query_prepared->bind_param() will look like
        // $query_prepared->bind_param("ss", $cities[0], $cities[1])
        }
        $query_prepared->execute();
    }
    [...]
}

I would suggest you try it with call_user_func_array to reach.

Look for the solution of nick9v.

mysqli_stmt::bind_param

u suggest to use `call_user_func_array` but u never show it here how to do it. =_=' — ggDeGreat, Oct 24 '15 at 05:24
here is a use case of call_user_func_array and the whole problem explained https://www.pontikis.net/blog/dynamically-bind_param-array-mysqli — Mochi, Apr 11 '20 at 10:09
"Care must be taken when using mysqli_stmt_bind_param() in conjunction with call_user_func_array(). Note that mysqli_stmt_bind_param() requires parameters to be passed by reference, whereas call_user_func_array() can accept as a parameter a list of variables that can represent references or values." Source -> https://www.php.net/manual/en/mysqli-stmt.bind-param.php — I try so hard but I cry harder, Jul 03 '20 at 18:20

score 5 · Answer 3 · edited Feb 01 '23 at 18:50

5

Use call_user_func_array like this:

$stmt = $mysqli->prepare("INSERT INTO t_file_result VALUES(?,?,?,?)");

$id = '1111';
$type = 2;
$result = 1;
$path = '/root';

$param = array('siis', &$id, &$type, &$result, &$path);
call_user_func_array(array($stmt, 'bind_param'), $param);

$stmt->execute();

printf("%d row inserted. \n", $stmt->effected_rows);
$stmt->close;

edited Feb 01 '23 at 18:50

Peter Mortensen

30,738
21
105
131

answered May 09 '16 at 09:33

SingSong

96
1
5

1

If you add more explanation and source to documentation then this will be accepted answer for sure! lovely answer here! – gonatee May 27 '17 at 21:28
4

This won't work, all parameters must be references. So you need to have the format defined as in `$format = "siis";` and then in the array you use `&$format`. All parameters must be references. – Alexis Wilke Jul 22 '17 at 09:22
1

@Alexis Wilke: It may be an example of the many code-only bogus answers. – Peter Mortensen Feb 01 '23 at 18:53

miken32 · Answer 4 · 2021-12-16T23:35:04.767

As of PHP version 8.1, binding is no longer required. As with PDO since version 5.0, you can now pass parameters as an array directly to the execute method.

$mysqli       = new mysqli("localhost", "root", "root", "db");
$params       = ['Nashville','Knoxville'];
$placeholders = str_repeat('?,', count($params) - 1) . '?'
$query        = "SELECT name FROM table WHERE city IN ($placeholders)";
$stmt         = $mysqli->prepare($query);

$stmt->execute($params);

Another example, if you have an associative array with keys matching column names:

$mysqli       = new mysqli("localhost", "root", "root", "db");
$data         = ["bar" => 23, "baz" => "some data"];
$params       = array_values($data);
$placeholders = str_repeat('?,', count($params) - 1) . '?'
$columns      = implode("`,`", array_keys($data));
$query        = "INSERT INTO foo (`$columns`) VALUES ($placeholders)";
$stmt         = $mysqli->prepare($query);

$stmt->execute($params);

Also worth mentioning is that the library now defaults to throwing exceptions in the event of errors. Prior to version 8.1 this was not the case.

score 1 · Answer 5 · edited Feb 01 '23 at 18:49

I was having trouble with this too, and got it working with eval before finding out that most people are using call_user_func_array:

$fields = array('model', 'title', 'price'); // Fields in WHERE clause
$values = array( // Type and value for each field
    array('s', 'ABCD-1001'),
    array('s', '[CD] Test Title'),
    array('d', '16.00')
);
$sql = "SELECT * FROM products_info WHERE "; // Start of query
foreach ($fields as $current) { // Build where clause from fields
    $sql .= '`' . $current . '` = ? AND ';
}
$sql = rtrim($sql, 'AND '); // Remove last AND
$stmt = $db->prepare($sql);
$types = ''; $vals = '';
foreach ($values as $index => $current_val) { // Build type string and parameters
    $types .= $current_val[0];
    $vals .= '$values[' . $index . '][1],';
}
$vals = rtrim($vals, ','); // Remove last comma
$sql_stmt = '$stmt->bind_param("' . $types . '",' . $vals . ');'; // Put bind_param line together
eval($sql_stmt); // Execute bind_param
$stmt->execute();
$stmt->bind_result($col1, $col2, $col3, $col4, $col5, $col6); // This could probably also be done dynamically in the same way
while ($stmt->fetch()) {
    printf("%s %s %s %s %s %s\n", $col1, $col2, $col3, $col4, $col5, $col6);
}

Definitely requires some toying with, but I actually really like this answer. It may be a bit bulkier than `call_user_func_array` but it's honestly easier to read and figure out what's going on. — Ethan Moore, Jul 20 '20 at 00:35

Puneet Sharma · Answer 6 · 2023-02-13T18:33:07.617

0

php 8.2 solved kind of everything with execute_query

$this->execute_query($query, $parms);

like

$query = "SELECT * FROM users WHERE `email` IN (?) LIMIT 1";
$parms = ["xyx@gmailx.com"];
$this->execute_query($query, $parms);

Remember ? is placeholder and number of ? (placeholder) is same number as count($parms) and it can be done in various ways.

So in your case it should be would

$query = "SELECT name FROM table WHERE city IN (? , ?)";
$parms = ['Nashville','Knoxville'];
$this->execute_query($query, $parms);

https://www.php.net/manual/en/mysqli.execute-query

if you want to create dynamic $query first,then

$query = "SELECT name FROM table WHERE city IN (".implode(",",array_map(fn()=>"?",$parms)).")";

edited Feb 13 '23 at 18:33

answered Feb 13 '23 at 17:17

Puneet Sharma

307
3
9

What if $parms = ["xyx@gmailx.com","zaz@gmailx.com"];? – Your Common Sense Feb 13 '23 at 17:20
then in query there should we two ? (placeholder) in $query as it put array values in your query – Puneet Sharma Feb 13 '23 at 17:23
And what if you don't know how many elements in this array beforehand? it was asked in the question you wrote your answer to – Your Common Sense Feb 13 '23 at 17:27
new update should be have no issue regarding that now – Puneet Sharma Feb 13 '23 at 18:33

score -1 · Answer 7 · edited Feb 02 '23 at 03:15

The way I did it: prepare the query with all its separate question marks, as well as the type string.

$cities = array('Nashville', 'Knoxville');
$dibs = '';
$query = "SELECT name FROM table WHERE city IN (";
$marks = array();

foreach ($cities as $k => $city) {
    // i, s, b, d type based on the variables to bind.
    $dibs .= 's';
    array_push($marks, '?');
}

$query .= implode(',', $marks) . ')';

Connect.

$mysql = new mysqli($host, $user, $pass, $dbname);
$statement =
    $mysql->prepare($query)
OR    die(sprintf(
        'Query error (%s) %s', $mysql->errno, $mysql->error
    ))
;

Then you use "..." token / ellipsis (documentation) in order to bind the array.

if ($statement) {
    $statement->bind_param($dibs, ...$cities);
    $statement->execute();

    $statement->close();
}
$mysql->close();

I know it kinda defeats the purpose of binding in order to escape (but at least it works good with a list of integers, i.e. IDs).

How can I bind an array of strings with a mysqli prepared statement?

7 Answers7

PHP 8.2 way. execute_query()

PHP 8.1 way. Array into execute()

Older versions, prepare/bind/execute way

Linked

Related