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
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()
{
if (!$this->varsBound) {
$meta = $this->result_metadata();
while ($column = $meta->fetch_field()) {
$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) {
foreach ($this->results as $k => $v) {
$results[$k] = $v;
}
return $results;
} else {
return null;
}
}
}
$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();
?>