call_user_func_array and bind_result

[Mari Masuda]

Hello,

I am having trouble figuring out how to properly bind the results of a mysqli prepared statement using call_user_func_array.

I have an "AbstractModel" abstract class and within the class is a method called "load" that takes the primary key of the desired item and retrieves and loads the data from the database into the object.

abstract class AbstractModel {
// lots of stuff omitted

// this is just a rough draft so no error checking is implemented
// $db is a subclass of mysqli
// $primaryKeyAsTypedItem is an object that holds a type (int, float, string, etc.) and value
public function load($db, $primaryKeyAsTypedItem) {
$query = "SELECT * FROM " . $this->tableName . " WHERE " . $this->primaryKeyName . "=?";
$ps = $db->prepare($query);
$type_string = $primaryKeyAsTypedItem->getTypeAbbreviation();
$value = $db->escapeSql($primaryKeyAsTypedItem->getValue());
$ps->bind_param($type_string, $value);
$ps->execute();
$ps->store_result();
$metadata = $ps->result_metadata()->fetch_fields();
$params = array();
foreach ($metadata as $object) {
$params[$object->orgname] = null;
}
call_user_func_array(array($ps, 'bind_result'), $params);
$ps->fetch();

// see what is going on
print "<pre>";
var_dump($params);
print "</pre>";

// more stuff omitted
}
}

The problem I am having is that mysqli_stmt::bind_result expects a list of individual variables to which the results are bound, not just an array with the proper number of available indices. I have done a lot of Googling and tried following the advice I found at [1], [2], [3], etc. but I was not successful in getting this to work. I don't know how I can generically "expand" $params so that this will work for different tables with different fields. Any pointers greatly appreciated.

[1] http://forums.devshed.com/php-development-5/mysqli-bind-result-to-return-array-568982.html
[2] http://www.charles-reace.com/blog/2009/04/28/mysqli-avoiding-bind_result-for-every-column/
[3] http://us.php.net/manual/en/mysqli-stmt.bind-result.php#102179

[David Harkness]

Each *value* in the array must be a reference to an existing variable--they
cannot be null or direct values. I didn't try this with bind_param(), but I
create a function that takes reference arguments and got it to work with
call_user_func_array():

function foo(&$x, &$y) { $x *= 2; $y *= 3; }
$x = 2;
$y = 1;
$params = array(&$x, &$y);
call_user_func_array('foo', $params);
var_dump($p);

array(2) {
[0]=>
&int(4)
[1]=>
&int(3)
}

Try changing the loop where you package up the $params array. I'm not
exactly sure what that part in your code is supposed to do, but I believe
that you have a result set where each column name is the name of a property
in the AbstractModel subclass.

foreach ($metadata as $object) {
$field = $object->orgname;
$params[] = &$this->$field;
}

If that's not the case, you can create a $values array to receive the
actual values from the result set and have the $params array hold
references to those values.

$values = array();
foreach ($metadata as $object) {
$field = $object->orgname;
$params[$field] = null;
$params[] = &$values[$field];
}

Peace,
David

[Mari Masuda]

Each *value* in the array must be a reference to an existing variable--they cannot be null or direct values.

[snip]

Thank you very much for your explanation and example code. I was missing the fact that the *values* in the array must be references. I was thinking that call_user_func_array wanted a reference to the array itself where I wanted the values stored, but when I tried doing

call_user_func_array(array($ps, 'bind_result'), &$params);

I got the following deprecation notice so I knew I was on the wrong track but didn't know where I was going wrong:
Warning: Call-time pass-by-reference has been deprecated in /Applications/apache/htdocs/hila/includes/class.AbstractModel.php on line 176

In the course of studying your example and trying to get it to work I also discovered a problem elsewhere in my code where the value of the primary key field in my MySQL database, defined as

`user_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT

was being returned to PHP as a string instead of an int, and this was causing a type mismatch that ultimately resulted in all fields of the results array being populated with NULLs, which is why all of the examples on the pages I Googled did not appear to be working for me.

In the end I changed the relevant part of my code to the following and thanks to your help it is now working:

$params = array();
$values = array();
foreach ($metadata as $object) {
$params[] = &$values[$object->orgname];
}
call_user_func_array(array($ps, 'bind_result'), $params);
$ps->fetch();

print "<pre>";
var_dump($values);
print "</pre>";

Thanks again and happy holidays!