I'm trying to make a function that receive a query (sql) and a parameter (array) but I receive this error:
PHP Warning: Parameter 2 to mysqli_stmt::bind_param() expected to be a reference, value given
My code is:
function dbRead($query, $param) {
$mysqli = new mysqli(DB::READ_HOST, DB::READ_USER, DB::READ_PASS, DB::NAME);
// Check that connection was successful.
if ($mysqli->connect_error) {
$result = "Connection error";
} else {
// Check that $conn creation succeeded
if ($conn = $mysqli->prepare($query)) {
call_user_func_array(array($conn, 'bind_param'), $param);
$conn->execute();
$result = $conn->get_result();
$result = $result->fetch_array();
$conn->close();
} else {
$result = "Prepare failed";
}
}
$mysqli->close();
return $result;
}
$test = dbRead('SELECT * FROM user WHERE id=? and email=?', array(123,'example@example.com'))
And if my function code is
function dbRead($query, $param) {
$mysqli = new mysqli(DB::READ_HOST, DB::READ_USER, DB::READ_PASS, DB::NAME);
// Check that connection was successful.
if ($mysqli->connect_error) {
$result = "Connection error";
} else {
// Check that $conn creation succeeded
if ($conn = $mysqli->prepare($query)) {
$ref = array();
foreach ($param as $key => $value) {
$ref[$key] = &$param[$key];
}
call_user_func_array(array($conn, 'bind_param'), $ref);
$conn->execute();
$result = $conn->get_result();
$result = $result->fetch_array();
$conn->close();
} else {
$result = "Prepare failed";
}
}
$mysqli->close();
return $result;
}
I receive this error
PHP Warning: mysqli_stmt::bind_param(): Number of elements in type definition string doesn't match number of bind variables
My PHP version is 5.4.36
Here is some code to bind variable numbers of parameters, stored in an array, in a 'mysqli' query.
I used it in an answer that has since been removed, not due to me i hope. ;-/
I haven't modified it it fit these requirements but hopefully it will show the way to get the 'mysqli' query to work. It was tested at the time.
The 'tricky' part is getting the correct 'references'.
<?php // Q23967579
// I used a lot of code from this example: http://www.php.net/manual/en/mysqli-stmt.bind-param.php
session_start();
// show input... delete this line later
var_dump($_POST, __FILE__.__LINE__);
// database connection...
$mysqlDb = mysqli_connect('localhost', 'test', 'test', 'testmysql');
$columns = array('category' => array('type' => 's', 'test' => ' `category` = ? '),
'color' => array('type' => 's', 'test' => ' `color` = ? '),
'pic' => array('type' => 's', 'test' => ' `pic` = ? '),
'size' => array('type' => 's', 'test' => ' `size` = ? '),
'min_price' => array('type' => 'd', 'test' => ' `price` >= ? '),
'max_price' => array('type' => 'd', 'test' => ' `price` <= ? '),
);
$params = new BindParam();
$whereClause = '';
$andLit = ' AND ';
// i choose to drive off the search columns...
foreach ($columns as $searchCol => $selection) {
if (!empty($_POST[$searchCol])) { // process it
$value = $_POST[$searchCol];
if ($value == 'all') { // not needed for the search...
continue;
}
// add it to all the various places
$whereClause .= (!empty($whereClause) ? $andLit : '') . $selection['test'];
$params->add($selection['type'], $value);
}
}
// now build the query...
$sql = 'select * from products '. (!empty($whereClause) ? ' where '. $whereClause : '');
$query = mysqli_prepare($mysqlDb, $sql);
$allOk = call_user_func_array('mysqli_stmt_bind_param', array_merge(array($query), $params->get()));
$allOk = mysqli_execute($query);
$queryResult = mysqli_stmt_get_result($query);
while ($row = mysqli_fetch_assoc($queryResult)) {
var_dump($row);
}
// end of program
exit;
/* -------------------------------------------------
* utility classes
*/
class BindParam {
private $values = array(),
$types = '';
public function add( $type, $value ) {
$this->values[] = $value;
$this->types .= $type;
}
public function get() {
$out = array();
$out[] = $this->types;
foreach($this->values as &$value) {
$out[] =& $value;
}
return $out;
}
}
I was trying to do something very similar and pieced together the solution from a few different posts on PHP References and bind_param. What's probably not immediately clear from the bind_param examples (or you forgot) is that the first argument is a string of the parameter types, one character per parameter (in your case, likely "is" for int and string), and you already got that the rest of the arguments must be references in your second function definition.
So, creating the arguments array should be something like this instead:
$ref = array("is");
foreach ($param as $value)
$ref[count($ref)] = &$value;
Though there are many ways to do it... and you should probably pass in the argument types along with the query, but MySQL seems to be relaxed when it comes to type exact types. I also prefer to pass the connection around, and support multiple result rows, e.g.:
function doRead($conn, $query, $argTypes, $args){
$success = false;
$execParams = array($argTypes);
foreach($args as $a)
$execParams[count($execParams)] = &$a;
if (!$stmt = $conn->prepare($query)){
echo "Prepare failed: (" . $conn->errno . ") " . $conn->error;
}else if (!call_user_func_array(array($stmt, "bind_param"), $execParams)){
echo "Param Bind failed, [" . implode(",", $args) . "]:" . $argTypes . " (" . $stmt->errno . ") " . $stmt->error;
} else if (!$stmt->execute()) {
echo "Execute failed: (" . $stmt->errno . ") " . $stmt->error;
} else
$success = true;
$ret = array();
if($success){
$res = $stmt->get_result();
while ($row = $res->fetch_array(MYSQLI_ASSOC))
array_push($ret, $row);
}
$stmt->close();
return $ret;
}