How to Bind an Array of Strings With MySQLi Prepared Statement

Binding an array in MySQLi prepared Insert statement PHP

You seem to be binding a single string as a second argument in your bind_param(). This method takes a number of variables by reference and binds them to the placeholders in the query and since you bound a single string the number of bound parameters does not match.

You need to store the values in an array and then unpack them using the splat operator.

if (count($fields) == count($values)) {
$fielddata = implode(", ", $fields);
$questions = rtrim(str_repeat("?, ", count($values)), ", ");

$statement = "INSERT INTO ".$table." (".$fielddata.") VALUES (".$questions.")";
$stmt = $db->prepare($statement);
$stmt->bind_param(str_repeat("s", count($values)), ...$values);
$stmt->execute();
}

Also, the type should be a list of letters denoting the type of each variable being bound. The best case is to bind them all as strings, so just repeat s for each bound variable.

Take care of SQL injection. You need to make sure that the field names are properly whitelisted. If these can be arbitrary values you could be vulnerable to SQL injection.

PHP - MySQL prepared statement to INSERT an array

No... this was definitely harder than PDO with any array because of how mysqli_stmt_bind_param() works... and this works fine by changing $array to removing/adding data for other columns.

$mysqli = new mysqli('localhost', 'root', 'password', 'test');

$array = array("name"=>"pineapple", "color"=>"purple");

$table_name = "fruit";



insert_data($mysqli, $array, $table_name);



function insert_data($mysqli, $array, $table_name)
{
$placeholders = array_fill(0, count($array), '?');

$keys = array();
$values = array();
foreach($array as $k => $v) {
$keys[] = $k;
$values[] = !empty($v) ? $v : null;
}

$query = "insert into $table_name ".
'('.implode(', ', $keys).') values '.
'('.implode(', ', $placeholders).'); ';
// insert into fruit (name, color) values (?, ?);

$stmt = $mysqli->prepare($query);

// create a by reference array...
$params = array();
foreach ($array as &$value) {
$params[] = &$value;
}
$types = array(str_repeat('s', count($params)));
$values = array_merge($types, $params);

/*
$values = Array
(
[0] => ss
[1] => pineapple
[2] => purple
)
*/

call_user_func_array(array($stmt, 'bind_param'), $values);

$success = $stmt->execute();

if ($success) { print "it worked..."; }
else { print "it did not work..."; }
}

I got some help from these SO posts:

- https://stackoverflow.com/a/15933696/623952

- https://stackoverflow.com/a/6179049/623952


So... in $stmt->bind_param() the first parameter is a string that has one char for each parameter passed in. And that char represents the parameter data type. In the example above, both of the two parameters are strings so it becomes ss. A string is always assumed in the example above, too.

I found this chart in the bind_param() documentation:

types

A string that contains one or more characters which specify the types for the corresponding bind variables:

Type specification chars  

Character Description
i corresponding variable has type integer
d corresponding variable has type double
s corresponding variable has type string
b corresponding variable is a blob and will be sent in packets

Mysqli prepared statement : several WHERE clauses and WHERE IN (Array)

The important thing here is that you are calling bind_param() just once, with an array containing all of the parameters you'll need to bind, so your solution will be to just add the additional WHERE clause parameter onto your $array of values to bind. The IN() clause isn't a special case requiring call_user_func_array() separated from other parameters. You call it on all of them.

Something is missing though - bind_param()'s first parameter is a string of data types. All your types are i, so you'll need to use str_repeat() to create that.

// Eventually, this array will contain the other params too
$array = (1,2,3,4,5,6,7,8,9,10);

// This creates a string of ?,?,?,?... for the IN () clause
$clause = implode(',', array_fill(0, count($array), '?'));

// Add the additional value onto the $array array
// so the last param is bound with the others.
$array[] = $this->getTime();

$types = str_repeat('i', count($array));

// The params passed to call_user_func_array() must have as references, each subsequent value. Add those in a loop, but start with the $types string
$params = array($types);
foreach ($array as $key => $value) {
$params[] = &$array[$key];
}

if($request = $this->getConnexion()->prepare('SELECT col1, col2 FROM table WHERE col1 IN ('.$clause.') AND col2>=?') or die(mysqli_error($this->getConnexion()))) {

// Then bind_param() is called on all parameters
// using the $params array which includes types and param references
call_user_func_array(array($request, 'bind_param'), $params);

// Execute & fetch.
$request->execute();
$request->bind_result($col1, $col2);
$request->store_result();

// Following the code
}

Use an array in a mysqli prepared statement: `WHERE .. IN(..)` query

you could do it this way:

$ids = array(1,5,18,25);

// creates a string containing ?,?,?
$clause = implode(',', array_fill(0, count($ids), '?'));


$stmt = $mysqli->prepare('SELECT * FROM somewhere WHERE `id` IN (' . $clause . ') ORDER BY `name`;');

call_user_func_array(array($stmt, 'bind_param'), $ids);
$stmt->execute();

// loop through results

Using this you're calling bind_param for each id and you have sorting done by mysql.

Return MYSQLi Prepared Statement Query and Bind Results an an Array

You could try to store it in an array, one by one, in your loop:

    $results = array();
while ($stmt->fetch()) {
$results[] = array(
"email" => $user_email,
"perms" => $user_perms
);
}
return $results;

If you need this array in another file, where you include this function, this will work, but if you need it in another request, PHP will have to run this code again to have the array filled.

If you need this array during the session, then consider putting the result in a session variable:

 $_SESSION['myarray'] = $results;

Then in the function, first check if the above session variable is set, and if so, immediately return that value. Only when it is not yet set, you perform the rest of the function.

Loop through array in bind parameters - prepared statement

You need to pass each variable individually to mysqli_stmt_bind_param, so $parameters needs to be an array, not a string. Change the following lines of code:

$parameters = "";

to:

$parameters = array();

and

$parameters .= "'".$value['value']."'" ;

to:

$parameters[] = $value['value'];

(note there is no need to escape values when you are using prepared statements)

remove this line:

$parameters .= ", ";

and finally, change

mysqli_stmt_bind_param($stmt, $notation, $parameters);

to:

mysqli_stmt_bind_param($stmt, $notation, ...$parameters);

and it should work fine.



Related Topics



Leave a reply



Submit