note 72720 added to function.mysqli-stmt-fetch
| From: | php at johnbaldock dot co dot uk | Date: | Wed, 31 Jan 2007 00:35:12 +0000 |
| Subject: | note 72720 added to function.mysqli-stmt-fetch | ||
| Groups: | php.notes | ||
| Request: | Send a blank email to php-notes+get-122383@lists.php.net to get a copy of this message | ||
Good point about the spaces in column names. I also had trouble with the fetch assoc returning
references so when done multiple times all the data pointed to the last result set. I got around it
by adding a foreach loop:
<?php
// creates an array of real values from an array of references
foreach ($this->results as $k => $v) {
$results[$k] = $v;
}
?>
To tidy all this up into a nice package I extended the mysqli and stmt classes. Here is the code to
extend both, I also included 'Typer85 at gmail dot com's fix for column names that have
spaces in them.
<?php
class mysqli_Extended extends mysqli
{
protected $selfReference;
public function __construct($dbHost, $dbUsername, $dbPassword, $dbDatabase)
{
parent::__construct($dbHost, $dbUsername, $dbPassword, $dbDatabase);
}
public function prepare($query)
{
$stmt = new stmt_Extended($this, $query);
return $stmt;
}
}
class stmt_Extended extends mysqli_stmt
{
protected $varsBound = false;
protected $results;
public function __construct($link, $query)
{
parent::__construct($link, $query);
}
public function fetch_assoc()
{
// checks to see if the variables have been bound, this is so that when
// using a while ($row = $this->stmt->fetch_assoc()) loop the following
// code is only executed the first time
if (!$this->varsBound) {
$meta = $this->result_metadata();
while ($column = $meta->fetch_field()) {
// this is to stop a syntax error if a column name has a space in
// e.g. "This Column". 'Typer85 at gmail dot com' pointed this
out
$columnName = str_replace(' ', '_', $column->name);
$bindVarArray[] = &$this->results[$columnName];
}
call_user_func_array(array($this, 'bind_result'), $bindVarArray);
$this->varsBound = true;
}
if ($this->fetch() != null) {
// this is a hack. The problem is that the array $this->results is full
// of references not actual data, therefore when doing the following:
// while ($row = $this->stmt->fetch_assoc()) {
// $results[] = $row;
// }
// $results[0], $results[1], etc, were all references and pointed to
// the last dataset
foreach ($this->results as $k => $v) {
$results[$k] = $v;
}
return $results;
} else {
return null;
}
}
}
// this is an example use of the extended mysqli.
// the only difference is using "mysqli_Extended" instead of "mysqli"
$db = new mysqli_Extended($dbHost, $dbUsername, $dbPassword, $dbDatabase);
$query = 'SELECT foo FROM bar';
$stmt = $db->prepare($query);
$stmt->execute();
$stmt->store_result();
while ($row = $stmt->fetch_assoc()) {
$foo[] = $row['foo'];
}
$stmt->close();
$db->close();
?>
----
Server IP: 85.116.0.130
Probable Submitter: 82.3.204.27
----
Manual Page -- http://www.php.net/manual/en/function.mysqli-stmt-fetch.php
Edit -- https://master.php.net/note/edit/72720
Del: integrated -- https://master.php.net/note/delete/72720/integrated
Del: useless -- https://master.php.net/note/delete/72720/useless
Del: bad code -- https://master.php.net/note/delete/72720/bad+code
Del: spam -- https://master.php.net/note/delete/72720/spam
Del: non-english -- https://master.php.net/note/delete/72720/non-english
Del: in docs -- https://master.php.net/note/delete/72720/in+docs
Del: other reasons-- https://master.php.net/note/delete/72720
Reject -- https://master.php.net/note/reject/72720
Search -- https://master.php.net/manage/user-notes.php