How to make a proper mysqli extension class with prepared statements?

Question

I have scoured the web for a good example but I can't find anything.

I am trying to extend the mysqli class to make a helper class that will abstract away some of the complexities. One of the main things I want to accomplish is to make use of prepared statements.

I don't really know where to start, or how to handle input and output properly in one class. Another problem is that I am unable to output data as an array while using prepared statements.

I could really use a simple example to point me in the right direction.

Thanks!

Answer 1

Check out Zend Framework and it's absolutely modular Zend_Db class, especially the mysqli adapter.

Answer 2

Check out the implementation of Zend_Db, and in particular, Zend_Db_Select. In fact, you might just opt to use that instead of developing your own. Examples:

 //connect to a database using the mysqli adapter
 //for list of other supported adapters see
 //http://framework.zend.com/manual/en/zend.db.html#zend.db.adapter.adapter-notes
$parameters = array(
                    'host'     => 'xx.xxx.xxx.xxx',
                    'username' => 'test',
                    'password' => 'test',
                    'dbname'   => 'test'
                   );
try {
    $db = Zend_Db::factory('mysqli', $parameters);
    $db->getConnection();
} catch (Zend_Db_Adapter_Exception $e) {
    echo $e->getMessage();
    die('Could not connect to database.');
} catch (Zend_Exception $e) {
    echo $e->getMessage();
    die('Could not connect to database.');
}

//a prepared statement
$sql = 'SELECT * FROM blah WHERE id = ?';
$result = $db->fetchAll($sql, 2);

//example using Zend_Db_Select
$select = $db->select()
             ->from('blah')
             ->where('id = ?',5);
print_r($select->__toString());
$result = $db->fetchAll($select);

//inserting a record
$row = array('name' => 'foo',
             'created' => time()
            );
$db->insert('blah',$row);
$lastInsertId = $db->lastInsertId();

//updating a row
$data = array(
    'name'      => 'bar',
    'updated'   => time()
);

$rowsAffected = $db->update('blah', $data, 'id = 2');

Answer 3

Hi, check also Kohana Database Lib .. It pretty much does what you want : http://docs.kohanaphp.com/libraries/database

Answer 4

Assuming you're actually wanting to write your own version (as opposed to utilizing one of the existing libraries other answers have suggested - and those are good options, too)...

Here are a couple of functions which you may find it useful to examine. The first allows you to bind the results of a query to an associative array, and the second allows you to pass in two arrays, one an ordered array of keys and the other an associative array of data for those keys and have that data bound into a prepared statement:

function stmt_bind_assoc (&$stmt, &$out) {
    $data = mysqli_stmt_result_metadata($stmt);
    $fields = array();
    $out = array();

    $fields[0] = $stmt;
    $count = 1;

    while($field = mysqli_fetch_field($data)) {
        $fields[$count] = &$out[$field->name];
        $count++;
    }
    call_user_func_array(mysqli_stmt_bind_result, $fields);
}

function stmt_bind_params($stmt, $fields, $data) {
    // Dynamically build up the arguments for bind_param
    $paramstr = '';
    $params = array();
    foreach($fields as $key)
    {
        if(is_float($data[$key]))
            $paramstr .= 'd';
        elseif(is_int($data[$key]))
            $paramstr .= 'i';
        else
            $paramstr .= 's';
        $params[] = $data[$key];
    }
    array_unshift($params, $stmt, $paramstr);
    // and then call bind_param with the proper arguments
    call_user_func_array('mysqli_stmt_bind_param', $params);
}

Answer 5

I've written a wrapper class to do this - it provides an identical interface for parameterised queries with either MySQLi or PDO. The interface allows you to execute parameterised SQL in a single line of code, which dramatically simplifies your own code.

http://www.robpoyntz.com/blog/?p=191