Reputation: 245
I'm having some real difficulties with my code here. I'm trying to pass an array of data to a function and have it dynamically build a INSERT statement using prepared statements.
So I have this:
public function create($data) {
global $mysqli;
foreach ($data as $field => $value) {
$fields[] = $field;
$values[] = $value;
}
$query = "INSERT INTO " . $this->class_name . " (";
for ($i = 0; $i < count($fields); $i++) {
if ($i == (count($fields) - 1)) {
$query .= "" . $fields[$i] . "";
} else {
$query .= "" . $fields[$i] . ", ";
}
}
$query .= ") VALUES (";
$params = array();
for ($i = 0; $i < count($values); $i++) {
if (is_int($values[$i])) {
$params[] = "i";
} else {
$params[]= "s";
}
if ($i == (count($values) - 1)) {
$query .= "?";
} else {
$query .= "?, ";
}
}
$query .= ")";
if ($stmt = $mysqli->prepare($query)) {
call_user_func_array(array($stmt, "bind_param"), array_merge($params, $values));
} else {
die("COULD NOT CONNECT create()");
}
//echo $query;
}
The issues is I keep getting the following error:
Warning: Parameter 2 to mysqli_stmt::bind_param() expected to be a reference, value given in E:\xampp2\htdocs\school2\application\models\CRUDAObject.php on line 44
I'm pretty new to prepared statements, but I can't work out which format/layout the array needs to be when I pass it.
Can anyone help?
Upvotes: 0
Views: 2742
Reputation: 598
As of PHP 5.6 this has become ridiculously simple, without the need for call_user_func_array. The trick is to use the splat ("unpack") operator (...) for your value list. Scroll down for "The Short Version" or feel free to read through to see how I'm handling the various data elements involved in the SQL transaction.
I do three things, using three functions I wrote.
After that, it's pretty simple.
So, first, the ground work:
// This would be passed to a functionized version of this:
// $MySQLInsertArray is an array of key=>value where key is db column name
// $MySQLDataTypes is a list of data types used to bind, eg: 'sssdsdnb'
// This creates the reference pointers for the insert statement (eg: '?,?,?,?')
$MySQLQs=str_repeat("?,",strlen($MySQLDataTypes)-1)."?";
// Make a *STRING* of the column names
$ColumnList=implode(",",array_keys($MySQLInsertArray));
// Make an *ARRAY WITH NO KEYS* of the values
$ValueList=array_values($MySQLInsertArray);
// Do all the fun SQL stuff
$stmt = $conn->prepare("INSERT INTO ".$TBName." (".$ColumnList.") VALUES (".$MySQLQs.")");
// USE THE UNPACK OPERATOR (...)
$stmt->bind_param($MySQLDataTypes,...$ValueList);
$stmt->execute();
The short version is:
So, that means, the important part is this and those magic three periods:
$stmt->bind_param($MySQLDataTypes,...$ValueList);
Upvotes: 2