How can I abstract mysqli prepared statements in PHP?

Question

I'm using my own class for database queries, extending mysqli:

class iDatabase extends mysqli
{
 public  $errorMsg;
 private $totalQueries;
 private $stmt;

 public function __construct()
 {
  parent::__construct( 'localhost', 'asd', 'asd', 'asd' );

  if ( mysqli_connect_errno() )
  {
   $this->errorMsg = 'Could not connect to server.<br /><i>' . mysqli_connect_error() . '</i>.';
   return;
  }

  parent::query( 'SET NAMES utf8' );
 }

}

However I'm running into trouble when executing queries and getting back results. I'm using prepared statements, but the way values and results are bound is confusing me. After a bit of research I came up with this function that accepts the query and parameters:

public function psQuery( $query, $params )
{
 $this->stmt = parent::prepare( $query );
 call_user_func_array( array($this->stmt,'bind_param'), $params );
 $this->stmt->execute();
}

My question is, what is the best way to get results back from this? I need to use bind_result, then fetch each row, then close the statement. I'd prefer to just get an associative array for each row - is that possible?

Answer 1

I would take a look at the implementation of Zend_Db, in particular the mysqli adapter, to see how they're doing it.

Answer 2

It seems that you have to do what you said "I need to use bind_result, then fetch each row, then close the statement"

I think there is no simpler way.

Answer 3

I worked on the Zend_Db_Adapter_Mysqli and Zend_Db_Statement_Mysqli classes quite a bit to get this to work, since we wanted to make it conform to the PDO and PDOStatement interface. It was pretty laborious, because of the confusing way MySQLi insists on making you bind variables to get results, and the variety of fetch modes supported by PDOStatement.

If you want to see the code in Zend_Db, pay special attention to the functions Zend_Db_Statement_Mysqli::_execute() and fetch(). Basically, the _execute() method binds an array of variable references using call_user_func_array(). The tricky part is that you have to initialize the array so the bind_result() function gets the references. Uh, that wasn't totally clear, so go take a look at the code.

Or else just use PDO's MySQL driver. That's what I would do in your shoes.