Comparison of PHP Database Abstraction Classes

Simplicity/Usability/Code Comparison

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

The following table compares 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.

This comparison is incomplete, but is still useful. In the future I plan on providing benchmarks and other comparisons.

Provided by greaterscope

Connecting to a Database

dbFacile $db = new 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->update(array('name' => 'Sample'), 'products', 'id=3');
Built-in PHP Functions $result = mysql_query("update products set name = 'Sample' where id = 3");
PDO $db->exec("update products set name values ('Sample')");
ADOdb & ADOdb Lite $db->Execute("update products set name values ('Sample')");
Pear MDB2 $db->query("update products set name values ('Sample')");

Fetch Rows w/SQL Injection Prevention

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

Insert w/SQL Injection Prevention
(POST contains a valid form submission)

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

Update w/SQL Injection Prevention
(POST contains a valid form submission)

dbFacile $affectedRows = $db->update($_POST, 'products', 'id=?', array($_POST['id']));
Built-in PHP Functions $fields = array('name','email','phone');
$sql = "update products ";
foreach($fields as $field) {
    $sql .= "set " . $field . "='" . mysql_real_escape_string($_POST[$field]) . "',";
}
$sql = substr($sql,0,-1);
$sql .= " where id='" . mysql_real_escape_string($_POST['id']) . "'";
$result = mysql_query($sql);
$affectedRows = mysql_affected_rows($result);
PDO (coming soon)
ADOdb & ADOdb Lite (coming soon)
Pear MDB2 (coming soon)

Supported Databases

dbFacile MySQL, SQLite2, PostgreSQL, SQL Server
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