Mysqli abstraction, fetching arrays from prepared statements

Question

Lately I've stumbled upon an error in a lib that used to work just fine, and I'll be damned if I can figure out where it is.

The code sample is below, and I apologize for the debug stuff that's inside it, but I'm trying to get it to work.

The problem is that $temp is an array with correct key (the name of the columns) but all the values are NULL.

I think the problem lies in the

call_user_func_array(array($query, 'bind_result'), $params);

bit, but can't really wrap my head around it.

public function fetchRows(){
 error_reporting(E_ALL+E_NOTICE);
 $args = func_get_args();
 $sql = array_shift($args);
 traceVar($sql, "Query");
 $colTypes = array_shift($args);
 if (!$query = $this->prepare($sql, $colTypes)) {
  die('Please check your sql statement : unable to prepare');
 }
 if (count($args)){
  traceVar($args,'Binding params with');
  call_user_func_array(array($query,'bindParam'), $args);
 }

 $query->execute();

 $meta = $query->result_metadata();
 while ($field = $meta->fetch_field()) {
  $params[] = &$row[$field->name];
 }
 traceVar($params,'Binding results with');
 call_user_func_array(array($query, 'bind_result'), $params);

 while ($query->fetch()) {
  traceVar($row,'After fetch');
  $temp = array();
  foreach($row as $key => $val) {
   $temp[$key] = $val;
  } 
  $result[] = $temp;
 }

 $meta->free();
 $query->close(); 
 //self::close_db_conn(); 
 return $result;
}

Answer 1

The code you provided works for me.

The call_user_func_array(...) function just calls the bindParam or bind_result methods on the $query object with the given array, as if you had provided each element of the array as a method argument.

You may want to check the SQL statement you are having the problem with, with the code below. I've rewritten it a bit in order to make it fully testable, since the original code depends on the statement class in your abstraction layer.

<?php

$db_host = 'localhost';
$db_user = 'username';
$db_pass = 'password';
$db_name = 'database';

$mysqli = new mysqli($db_host, $db_user, $db_pass, $db_name);

print_r(fetchRows('SELECT something from some_table WHERE some_id = ?', 'i', 1));

function traceVar($a, $b) {
    print_r(array($b => $a));
}

function fetchRows(){
        error_reporting(E_ALL+E_NOTICE);
        $args = func_get_args();
        $sql = array_shift($args);
        traceVar($sql, "Query");

        // Keep the column types for bind_param.
        // $colTypes = array_shift($args);

        // Column types were originally passed here as a second
        // argument, and stored in the statement object, I suppose.
        if (!$query = $GLOBALS['mysqli']->prepare($sql)){ //, $colTypes)) {
                die('Please check your sql statement : unable to prepare');
        }
        if (count($args)){
                traceVar($args,'Binding params with');

                // Just a quick hack to pass references in order to
                // avoid errors.
                foreach ($args as &$v) {
                    $v = &$v;
                }

                // Replace the bindParam function of the original
                // abstraction layer.
                call_user_func_array(array($query,'bind_param'), $args); //'bindParam'), $args);
        }

        $query->execute();

        $meta = $query->result_metadata();
        while ($field = $meta->fetch_field()) {
                $params[] = &$row[$field->name];
        }
        traceVar($params,'Binding results with');
        call_user_func_array(array($query, 'bind_result'), $params);

        while ($query->fetch()) {
                traceVar($row,'After fetch');
                $temp = array();
                foreach($row as $key => $val) {
                        $temp[$key] = $val;
                } 
                $result[] = $temp;
        }

        $meta->free();
        $query->close(); 
        //self::close_db_conn(); 
        return $result;
}