Pushing MySQLi dynamic data into an array fails while everything else succeeds

Question

I have the following code to dynamically assign results from a MySQL database (assume all code prior is correct - because I do)

// Execute the query.
$statement->execute();

// Grab the dynamic number of results.
$statement->store_result();

$meta = $statement->result_metadata();
$data = array();
$refs = array();

while ($name = $meta->fetch_field()) {
  $refs[] =& $data[$name->name];
} // while ($name = $meta->fetch_field())

$meta->free_result();

call_user_func_array(array($statement, "bind_result"), $refs);

The problem occurs as soon as I do a while ($statement->fetch()).

The following code will print unique data (referred to as 'printer')

while ($statement->fetch()) {
  print print_r($data, true);
} // while ($statement->fetch())

However the following will print only the last database result, $statement->num_rows amount of times (referred to as 'pusher')

$array = array();

while ($statement->fetch()) {
  array_push($array, $data);
} // while ($statement->fetch())

print print_r($array, true);

Mimicking the answer given on this StackOverflow question while doing the following returns the same result as the 'pusher' while loop.

$array = array();

while ($statement->fetch()) {
  $temp = $data;

  array_push($array, $temp);
} // while ($statement->fetch())

print print_r($array, true);

My question is, in the 'pusher' loop, why is all my of data overwritten, even when creating a new variable at the beginning of each loop, and how can I prevent this without doing something like array_push($array, array("param1" => $data["param1"], "param2" => $data["param2"], ... "paramN" => $data["paramN"]))

Answer 1

When you execute the following line you do not bind an array, you bind elements of that array.

call_user_func_array(array($statement, "bind_result"), $refs);

This is just a dynamic way of doing

$statement->bind_result($data['x'], $data['y'], ...);

which is equivalent to:

$statement->bind_result($x, $y, ...);

The array is not a reference, but all the elements of the array are references. var_dump($data) shows:

array(2) {
  ["category_name"]=>
  &NULL
  ["id"]=>
  &NULL
}

When you push this array into a new array, you don't push the values, you push the references. Your $array array is then made up of a lot of arrays pointing to the same values in memory.

I talk about this problem in another of my answers, but the solution is to either use get_result() or dereference the values.

You can do so with a simple loop inside of your while loop. On each iteration of the foreach loop PHP will create a new zval container for the value. You can then push that value into a temporary array, which you later push into $array.

while ($statement->fetch()) {
    $temp = [];
    foreach ($data as $key => $val) {
        $temp[$key] = $val;
    }
    $array[] = $temp;
}