MySQLi: prepared statement to return nested arrays

Question

I use this class method to return a nested/ multi-dimensional array,

public function fetch_all_stmt($sql,$types = null,$params = null)
    {
        # create a prepared statement
        $stmt = parent::prepare($sql);

        if($stmt)
        {
            if($types&&$params)
            {
                $bind_names[] = $types;
                for ($i=0; $i<count($params);$i++) 
                {
                    $bind_name = 'bind' . $i;
                    $$bind_name = $params[$i];
                    $bind_names[] = &$$bind_name;
                }
                $return = call_user_func_array(array($stmt,'bind_param'),$bind_names);
            }

            # execute query 
            $stmt->execute();

            # these lines of code below return multi-dimentional/ nested array, similar to mysqli::fetch_all()
            $stmt->store_result();

            $variables = array();
            $data = array();
            $meta = $stmt->result_metadata();

            while($field = $meta->fetch_field())
                $variables[] = &$data[$field->name]; // pass by reference

            call_user_func_array(array($stmt, 'bind_result'), $variables);

            $i=0;
            while($stmt->fetch())
            {
                $array[$i] = array();
                foreach($data as $k=>$v)
                    $array[$i][$k] = $v;
                $i++;
            }
            return $array;

            # close statement
            $stmt->close();
        }
        else
        {
            return self::get_error();
        }
    }

This is how I call this method,

$sql = "
SELECT *
FROM root_contacts_cfm
WHERE root_contacts_cfm.cnt_suspended = ?
ORDER BY cnt_id DESC
";

print_r($mysqli->fetch_all_stmt($sql,'s',array('0')));

it return correctly,

Array
(
    [0] => Array
        (
            [cnt_id] => 1
            [cnt_email1] => [e-mail]
            [cnt_email2] => 
            [cnt_fullname] => Lau T
            [cnt_firstname] => TK
            [cnt_lastname] => Lau
            [cnt_suspended] => 0
            [cnt_created] => 2011-02-04 00:00:00
            [cnt_updated] => 2011-02-04 13:53:49
        )

   [1] => Array
        (
            [cnt_id] => 2
            [cnt_email1] => [e-mail]
            [cnt_email2] => 
            [cnt_fullname] => Lau Txx
            [cnt_firstname] => T
            [cnt_lastname] => Lau
            [cnt_suspended] => 0
            [cnt_created] => 2011-02-04 00:00:00
            [cnt_updated] => 2011-02-04 13:53:49
        )
)

But it returns an error message when I loop it,

$sql = "
SELECT *
FROM root_contacts_cfm
WHERE root_contacts_cfm.cnt_id = ?
";

for ( $i = 1; $i <= 3; ++$i ) 
{ 
   print_r($mysqli->fetch_all_stmt($sql,'s',array($i)));
}

Notice: Undefined variable: array in C:\wamp\www\000_TEST\php\php_export_excel\class_database.php on line 377

line 377 refers to return $array; after,

$i=0;
            while($stmt->fetch())
            {
                $array[$i] = array();
                foreach($data as $k=>$v)
                    $array[$i][$k] = $v;
                $i++;
            }
 return $array;

No error occurs if I move return $array; inside while{} but the first instance then will not return a list of nested array (which is two nested arrays), only one nested array.

Why? How can I fix it?

Thanks.

Answer 1

$array = null; is needed...

$i=0;
            $array = null;
            while($stmt->fetch())
            {
                $array[$i] = array();
                foreach($data as $k=>$v)
                    $array[$i][$k] = $v;
                $i++;
            }
            return $array;

don't know why but it works!