MySQLi: prepared statement to return nested arrays
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->n开发者_运维知识库ame]; // 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] => lau@xx.uk
[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] => lauxx@xx.uk
[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.
$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!
精彩评论