php - How to bind an array of strings with mysqli prepared statement?

Solution:

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

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

For the earlier versions the task is a bit elaborate but doable. For a simple case when you already have a query with placeholders, the code would be

$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 array at once
$stmt->execute();
Copy code

While, like in your case, we have an arbitrary number of placeholders, we will have to add bit more code. I'll take the explanation from my article Mysqli prepared statement with multiple values for IN clause:

• 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 usestr_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 inbind_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   
Copy code

Although this code is rather big, it is incomparably smaller than any other plausible solution offered in this topic so far.

Solution:

You can not bind two variables with onequestion mark !

For every variable you bind you need onequestion 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')";
Copy code

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')";
Copy code

$query_prepared->bind_param quotes string params 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')";
Copy code

will become

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

nowbind_param must be

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

with this

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

andbind_param with

bind_param("s",$cities)
Copy code

you get

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

That's why an array not works .
The only solution for this fact iscall_user_func_array

if you init a statement, following is unnecessary

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

This is correct

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

if you don't want to usecall_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 2 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 2 arguments $query_prepared->bind_param() will look like
    // $query_prepared->bind_param("ss",$cities[0],$cities[1])      
  }    

    $query_prepared->execute();
  } 
 [...]
 }
Copy code

I would suggest you try it withcall_user_func_array to reach.

look for the solution ofnick9v
mysqli-stmt.bind-param

Solution:

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;
Copy code

Solution:

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);
Copy code

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);
Copy code

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.

Solution:

I was having trouble with this too, and got it working witheval before finding out that most people are usingcall_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);
}
Copy code

Solution:

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) .')';
Copy code

Connect.

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

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();
Copy code

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). If you see a way how to improve this approach, feel free to edit/comment.