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
Difference Between Require, Include, Require_Once and Include_Once
How to Get File_Get_Contents() to Work With Https
MySQL Query to Get Column Names
How to Display HTML Tags as Plain Text
When Should I Use Prepared Statements
PHP 7.2 Function Create_Function() Is Deprecated
How to Use Pdo Connection in Other Classes
How to Savehtml of Domdocument Without HTML Wrapper
PHP Foreach Change Original Array Values
Convert Time in Hh:Mm:Ss Format to Seconds Only
PHP: Return All Dates Between Two Dates in an Array
How to Reindex an Array in PHP But With Indexes Starting from 1
Can PHP Curl Retrieve Response Headers and Body in a Single Request
How to Find Where I Will Be Redirected Using Curl in PHP
How to Extract Data from CSV File in PHP
"Fatal Error: Cannot Redeclare ≪Function≫"