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

Question

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.

karim79 · Accepted Answer · 2009-09-05T21:06:35.657

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');

I was hoping to make my own but i'm not sure. Does `$db->fetchAll($sql, 2);` behave like mysql_fetch_array? Also why does it seem like there is so much redundant code that has to exist in every script? Can I make another class to handle everything down to the end of that try, catch statement or is that considered bad practice? — , Sep 05 '09 at 21:45
@Jon - fetchAll will return an associative array by default (can be changed, there's also fetchRow, fetchCol, fetchOne and so on). The long try..catch will ideally only appear once in your application within the bootstrap (or an include or such), so that's not really an issue. — karim79, Sep 05 '09 at 22:21

score -1 · Answer 2 · answered Sep 05 '09 at 20:37

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);
}

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

2 Answers2

Linked