Comparison of PHP Database Abstraction Classes

Simplicity/Usability Comparison

The main purpose of this is to show that database interaction doesn't have to be convoluted.

The following tables compare the code required to perform incredibly common database operations and queries. Care has been taken to use the least code possible per package per example.

Provided by greaterscope.net. (Might I also interest you in my PHP ORM experimentations?)

Connecting to a Database

dbFacile $db = dbFacile::open('mysql', 'dbname', 'user', 'pass', 'hostname');
Built-in PHP Functions $resource = mysql_connect('hostname', 'user', 'pass');
mysql_select_db('dbname', $resource);
PDO $db = new PDO('mysql:host=localhost;dbname=test', 'user', 'pass');
ADOdb & ADOdb Lite $db = ADONewConnection('mysql');
$result = $db->Connect('hostname', 'user', 'pass', 'dbname');
Pear MDB2 $db =& MDB2::factory('mysql://user:pass@hostname/dbname');

Iterate Over Rows

dbFacile foreach($db->fetch('select * from products') as $row) {
    // ...
}
Built-in PHP Functions $result = mysql_query('select * from products');
while(($row = mysql_fetch_assoc($result))) {
    // ...
}
PDO foreach($db->query('select * from products') as $row) {
    // ...
}
ADOdb & ADOdb Lite $recordSet = $db->Execute("select * from products");
foreach ($recordSet as $row) {
    // ...
}
Pear MDB2 $result = $db->query('select * from products');
while (($row = $result->fetchRow())) {
    // ...
}

Fetch the First Row

dbFacile $row = $db->fetchRow('select * from products');
Built-in PHP Functions $result = mysql_query('select * from products');
$row = mysql_fetch_assoc($result);
PDO $statement = $db->prepare('select * from products');
$statement->execute();
$row = $statement->fetch();
ADOdb & ADOdb Lite $row = $db->GetRow('select * from products');
Pear MDB2 $result = $db->queryRow('select * from products');

Select Single Table Cell from First Record

dbFacile $cell = $db->fetchCell('select name from products');
Built-in PHP Functions $result = mysql_query('select name from products');
$row = mysql_fetch_assoc($result);
$cell = $row['name'];
PDO $stmt = $db->prepare('select name from products');
$stmt->execute();
$cell = $stmt->fetchColumn();
ADOdb & ADOdb Lite $cell = $db->GetOne('select name from products');
Pear MDB2 $cell = $db->queryCol('select name from products');

Select Column as 1-Dimensional Array

dbFacile $column = $db->fetchColumn('select name from products');
Built-in PHP Functions $result = mysql_query('select name from products');
$column = array();
foreach($row = mysql_fetch_assoc($result)) {
    $column[] = $row['name'];
}
PDO $column = array();
foreach($db->query('select name from products') as $row) {
    $column[] = $row['name'];
}
ADOdb & ADOdb Lite $column = array();
foreach($db->query('select name from products') as $row) {
    $column[] = $row['name'];
}
Pear MDB2 $rows = $db->queryAll('select name from products');
$column = array();
foreach($rows as $row) {
    $column[] = $row['name'];
}

Insert and Get Newly Created ID

dbFacile $id = $db->insert(array('name' => 'Sample'), 'products');
Built-in PHP Functions $result = mysql_query("insert into products (name) values ('Sample')");
$id = mysql_insert_id($result);
PDO $db->exec("insert into products (name) values ('Sample')");
$id = $db->lastInsertId();
ADOdb & ADOdb Lite $db->Execute("insert into products (name) values ('Sample')");
$id = $db->Insert_ID();
Pear MDB2 (coming soon)

Update

dbFacile $db->execute("update products set name = 'Sample' where id = 3");
Built-in PHP Functions $result = mysql_query("update products set name = 'Sample' where id = 3");
PDO $db->exec("update products set name = 'Sample' where id = 3");
ADOdb & ADOdb Lite $db->Execute("update products set name = 'Sample' where id = 3");
Pear MDB2 $db->query("update products set name = 'Sample' where id = 3");

Query w/SQL Injection Prevention

dbFacile $data = array($_GET['id'], $_GET['category']);
$rows = $db->fetch('select * from products where id = ? and category = ?', $data);
foreach($rows as $row) {
    // ...
}
Built-in PHP Functions $id = mysql_real_escape_string($_GET['id']);
$category = mysql_real_escape_string($_GET['category']);
$sql = "select * from products where id = '" . $id . "' and category = '" . $category . "'";
$result = mysql_query($sql);
while(($row = mysql_fetch_assoc($result))) {
    // ...
}
PDO $statement = $db->prepare('select * from products where id = ? and category = ?');
$statement->execute(array($_GET['id'], $_GET['category']));
while($statement->fetch() as $row) {
    // ...
}
ADOdb & ADOdb Lite (coming soon)
Pear MDB2 $query = "select * from products where id = '" . $db->quote($_GET['id'], 'integer') . "' and category = '" . $db->quote($_GET['category'], 'integer') . "'";
$rows = $db->queryAll($query);
    // ...
}

Insert w/SQL Injection Prevention

dbFacile $newProductId = $db->insert(array('name' => $_POST['name']), 'products');
Built-in PHP Functions $name = mysql_real_escape_string($_POST['name']);
$sql = "insert into products (name) values('" . $name . "')";
$result = mysql_query($sql);
$newProductId = mysql_insert_id($result);
PDO $statement = $db->prepare('insert into products (name) values(:name)');
$statement->execute(array('name' => $_POST['name']));
$newProductId = $db->lastInsertId();
ADOdb & ADOdb Lite (coming soon)
Pear MDB2 (coming soon)

Update w/SQL Injection Prevention

dbFacile $affectedRows = $db->update(array('name' => $_POST['name']), 'products', 'id=?', array($_POST['id']));
Built-in PHP Functions $id = mysql_real_escape_string($_POST['id']);
$name = mysql_real_escape_string($_POST['name']);
$sql = "update products set name='" . $name . "' where id='" . $id . "'";
$result = mysql_query($sql);
$affectedRows = mysql_affected_rows($result);
PDO $statement = $db->prepare('update products set name=:name where id=:id');
$statement->execute(array('name' => $_POST['name'], 'id' => 123));
ADOdb & ADOdb Lite (coming soon)
Pear MDB2 (coming soon)

Supported Databases

dbFacile Uhh ... MySQL, SQLite2. I've severely slacked in this area.
Built-in PHP Functions Many...
PDO MySQL, SQLite2, SQLite3, PostgreSQL, SQL Server, others
ADOdb & ADOdb Lite MySQL, SQLite, PostgreSQL, SQL Server, others
Pear MDB2 MySQL, SQLite, PostgreSQL, SQL Server, others