How can I abstract mysqli prepared statements in PHP?
I am 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 am having problems executing queries and returning results. I am using prepared statements, but the way the values and results are bound confuses me. After a bit of research, I came up with this function that accepts a request 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's the best way to get results from this? I need to use bind_result, then extract each row and then close the statement. I'd rather just get an associative array for each row - is that possible?
source to share
I did a bit of tinkering on the Zend_Db_Adapter_Mysqli
and classes Zend_Db_Statement_Mysqli
to get this to work as we wanted it to match the interface PDO
and PDOStatement
. This was quite tricky, due to the convoluted way MySQLi insists that you bind variables to get results and the variety of fetch modes supported PDOStatement
.
If you want to see the code in Zend_Db
, pay special attention to the Zend_Db_Statement_Mysqli::_execute()
and functions
fetch()
. Basically, a method _execute()
binds an array of variable references with call_user_func_array()
. The tricky part is that you have to initialize the array for the function to bind_result()
receive references. It wasn't entirely clear, so take a look at the code.
Or just use the MySQL PDO driver. This is what I will do on your shoes.
source to share