ramblings on PHP, SQL, the web, politics, ultimate frisbee and what else is on in my life

MDB2 example

I just wanted to show case some of the new features in MDB2. I especially wanted to demo some PHP5 specific features. The following example just creates a table with an autoincrementing primary key column 'id', inserts some values, fetches the last inserted id and finally, after selecting those values and outputs them using an iterator, drops the table again ..

I quickly hacked up this example after releasing MDB2 2.0.0RC2 and a number of driver updates. It should work fine with mysql and pgsql. It will not work with sqlite, because automagically determining the types from a result set is not supported by the sqlite API. I have not tried another backend.


/* a nice PHP5 only show case of MDB2 */

require 'MDB2.php';

$dsn = array(
    'phptype'  => 'mysql',
    'username' => 'root',
    'password' => '',
    'hostspec' => 'localhost',
    'database' => 'driver_test',

// create MDB2 instance
$mdb2 =& MDB2::factory($dsn);

$fields = array(
    'id' => array(
        'type'     => 'integer',
        'unsigned' => true,
        'autoincrement'  => true,
    'somename' => array(
        'type'     => 'text',
        'length'   => 12,
    'somedate'  => array(
        'type'     => 'date',
$table = 'sometable';

// create a table
// since we are on php5 we can use the magic __call() method to:
// - load the manager module: $mdb2->loadModule('Manager');
// - redirect the method call to the manager module: $mdb2->manager->createTable('sometable', $fields);
$mdb2->mgCreateTable($table, $fields);

$query = "INSERT INTO $table (somename, somedate) VALUES (:name, :date)";
// parameters:
// 1) the query (notice we are using named parameters, but we could also use ? instead
// 2) types of the placeholders
// 3) true denotes a DML statement
$stmt = $mdb2->prepare($query, array('name' => 'text', 'date' => 'date'), true);

// load Date helper class

$stmt->execute(array('name' => 'hello', 'date' => MDB2_Date::mdbToday()));
// get the last inserted id
var_dump($mdb2->lastInsertId($table, 'id'));
$stmt->execute(array('name' => 'world', 'date' => '2005-11-11'));
// get the last inserted id
var_dump($mdb2->lastInsertId($table, 'id'));

// load Iterator implementations

$query = 'SELECT * FROM '.$table;
// parameters:
// 1) the query
// 2) true means MDB2 tries to determine the result set type automatically
// 3) true is the default and means that internally a MDB2_Result instance should be created
// 4) 'MDB2_BufferedIterator' means the MDB2_Result should be wrapped inside an SeekableIterator
$result = $mdb2->query($query, true, true, 'MDB2_BufferedIterator');

// iterate over the result set
foreach ($result as $row) {

// call drop table
// we could also have done $mdb2->mgDropTable($table);


Re: MDB2 example

Thanks very much. It will be very useful for us!

Re: MDB2 example

Thanks for that example. I've tried MDB2 a few times and hope to use it in an up coming project. I tried building a Rails like ActiveRecord object and gave up from lack of time. Want to post that (ActiveRevord) as an example too? That'd be a great way to show the metadata support!? :)


Re: MDB2 example

There is currently nothing like an ActiveRecord implementation based on MDB2 that I am aware of. I have written a fairly easy to use SQL Query Builder for the LiveUser_Admin package. A user is currently working on expanding the scope of this package. In that case it will come fairly close to an ActiveRecord implementation.

In the example you can already see an indication of the metadata support when in the query I just specify true as the parameter for the result set types and MDB2 then reverse engineers the types from the result set automagically. You can try changing the true for false and depending on the database you test on things will change. At the least you will notice that the integers will be returned as strings if you change the true to a false.

Re: MDB2 example

Great! That's perfect for what I was trying to do. Nice examples in there.

How would you compare Creole to DB2? Besides the fact that Creole is pretty much PHP5 only.


Re: MDB2 example

Well I looked at Creole a while back. I send Hans a few notes of bugs I have solved myself in MDB2 that I saw in Creole back then. I am sure things have improved. However I doubt that it has surpassed MDB2 in any relevant area.

Propel seems like a much more worthwhile thing to spend your time on. Then again Hans was aiming at E_STRICT compliance I presume and so there was nothing around. However since by now PDO is on the block I see little value in using Creole. If you need E_STRICT go PDO (or use a PDO based layer). And if you don't then you use MDB2, because frankly its the bestest, fastest, extensibilitest .. yada yada ;-)

Re: MDB2 example

Is anyone porting MDB_Querytool to MDB2_Querytool currently?

Re: MDB2 example

There already is an ActiveRecord implementation in PHP5 using Pear::DB and will be switching soon to MDB2, in PHP on Trax.

Re: MDB2 example

So are you going to propose it as a PEAR package?

Re: MDB2 example


Before you can post a comment please solve the following captcha.
your name