Zend_Db_Adapter is the database API abstraction layer for the Zend Framework. Based on PDO, you can use Zend_Db_Adapter to connect to and work with any of the supported SQL database systems using the same API. These include Microsoft SQL Server, MySQL, PostgreSQL, SQLite, and others.

To create an instance of Zend_Db_Adapter for your particular database backend, you need to call Zend_Db::factory() with the name of the adapter and an array of parameters describing the connection. For example, to connect to a MySQL database called "camelot" on the local host as a user named "malory":

<?php

require_once 'Zend/Db.php';

$params = array ('host'     => '127.0.0.1',
                 'username' => 'malory',
                 'password' => '******',
                 'dbname'   => 'camelot');

$db = Zend_Db::factory('PDO_MYSQL', $params);

?>

Similarly, to connect to an SQLite database called "camelot.sq3":

<?php

require_once 'Zend/Db.php';

$params = array ('dbname' => 'camelot.sq3');

$db = Zend_Db::factory('PDO_SQLITE', $params);

?>

Similarly, to connect to an SQLite2 database called "camelot.sq2": For a memory-based sqlite database, do not specify a dsnprefix, and use a dbname called ":memory:".

<?php

require_once 'Zend/Db.php';

$params = array ('dbname' => 'camelot.sq2',
                 'dsnprefix' => 'sqlite2');

$db = Zend_Db::factory('PDO_SQLITE', $params);

?>

Either way, you will be able to use the exact same API to query the database.

You should always quote values that are to be used in an SQL statement; this is to help prevent SQL injection attacks. Zend_Db_Adapter provides two methods (via the underlying PDO object) to help you manually quote values.

The first of these is the quote() method. It will quote a scalar value appropriately for your database adapter; if you attempt to quote an array, it will return a comma-separated string of the array values, each properly quoted (this is useful for functions that take a list parameter).

<?php

// create a $db object, assuming Mysql as the adapter.

// quote a scalar
$value = $db->quote('St John"s Wort');
// $value is now '"St John\"s Wort"' (note the surrounding quotes)

// quote an array
$value = $db->quote(array('a', 'b', 'c');
// $value is now '"a", "b", "c"' (a comma-separated string)

?>

The second is the quoteInto() method. You provide a base string with a question-mark placeholder, and then one scalar or array to quote into it. This is useful for constructing queries and clauses as-you-go. Scalars and arrays work just as in the quote() method.

<?php
	
// create a $db object, assuming Mysql as the adapter.

// quote a scalar into a WHERE clause
$where = $db->quoteInto('id = ?', 1);
// $where is now 'id = "1"' (note the surrounding quotes)

// quote an array into a WHERE clause
$where = $db->quoteInto('id IN(?)', array(1, 2, 3));
// $where is now 'id IN("1", "2", "3")' (a comma-separated string)

?>

Once you have a Zend_Db_Adapter instance, you can execute queries directly in SQL. Zend_Db_Adapter passes these queries to the underlying PDO object, which prepares and executes them, and then passes back a PDOStatement object for you to manipulate the results (if any).

<?php
	
// create a $db object, and then query the database
// with a properly-quoted SQL statement.
$sql = $db->quoteInto(
    'SELECT * FROM example WHERE date > ?',
    '2006-01-01'
);
$result = $db->query($sql);

// use the PDOStatement $result to fetch all rows as an array
$rows = $result->fetchAll();

?>

You may bind data into your query automatically. This means you can set multiple named placeholders in the query, and then pass an array of data that is substituted for those placeholders. The substituted values are automatically quoted for you, providing greater security against SQL injection attacks.

<?php
	
// create a $db object, and then query the database.
// this time, use placeholder binding.
$result = $db->query(
    'SELECT * FROM example WHERE date > :placeholder',
    array('placeholder' => '2006-01-01')
);

// use the PDOStatement $result to fetch all rows as an array
$rows = $result->fetchAll();

?>

Optionally, you may wish to prepare and bind data to SQL statements manually. To do so, use the prepare() method to get a prepared PDOStatement that you can manipulate directly.

<?php
	
// create a $db object, and then query the database.
// this time, prepare a PDOStatement for manual binding.
$stmt = $db->prepare('SELECT * FROM example WHERE date > :placeholder');
$stmt->bindValue('placeholder', '2006-01-01');
$stmt->execute();

// use the PDOStatement to fetch all rows as an array
$rows = $stmt->fetchAll();

?>

By default, PDO (and thus Zend_Db_Adapter) are in "auto-commit" mode. This means that all queries are committed as they are executed. If you wish to execute within a transaction, simply call the beginTransaction() method, then either commit() or rollBack() your changes. Zend_Db_Adapter returns to auto-commit mode until you call beginTransaction() again.

<?php
	
// create a $db object, and then start a transaction.
$db->beginTransaction();

// attempt a query.
// if it succeeds, commit the changes;
// if it fails, roll back.
try {
    $db->query(...);
    $db->commit();
} catch (Exception $e) {
    $db->rollBack();
    echo $e->getMessage();
}

?>

As a convenience, you may use the insert() method to create an INSERT statement for you and bind data to be inserted into it. (The bound data is quoted for you automatically to help prevent SQL injection attacks.)

The return value is not the last inserted ID, as the table may not have an auto-incremented column; instead, the return value is the number of rows affected (usually 1). If you want the ID of the last inserted record, call the lastInsertId() method after the insert.

<?php
	
//
// INSERT INTO round_table
//     (noble_title, first_name, favorite_color)
//     VALUES ("King", "Arthur", "blue");
//

// create a $db object, and then...
// the row data to be inserted in column => value format
$row = array (
    'noble_title'    => 'King',
    'first_name'     => 'Arthur',
    'favorite_color' => 'blue',
);

// the table into which the row should be inserted
$table = 'round_table';

// insert the row and get the row ID
$rows_affected = $db->insert($table, $row);
$last_insert_id = $db->lastInsertId();

?>

As a convenience, you may use the update() method to create an UPDATE statement for you and bind data to be updated into it. (The bound data is quoted for you automatically to help prevent SQL injection attacks.)

You may provide an optional WHERE clause to tell which rows to update. (Note that the WHERE clause is not a bound parameter, so you need to quote values in it yourself.)

<?php
	
//
// UPDATE round_table
//     SET favorite_color = "yellow"
//     WHERE first_name = "Robin";
//

// create a $db object, and then...
// the new values to set in the update, in column => value format.
$set = array (
    'favorite_color' => 'yellow',
);

// the table to update
$table = 'round_table';

// the WHERE clause
$where = $db->quoteInto('first_name = ?', 'Robin');

// update the table and get the number of rows affected
$rows_affected = $db->update($table, $set, $where);

?>

As a convenience, you may use the delete() method to create a DELETE statement for you; you may provide an optional WHERE clause to tell which rows to delete. (Note that the WHERE clause is not a bound parameter, so you need to quote values in it yourself.)

<?php
	
//
// DELETE FROM round_table
//     WHERE first_name = "Patsy";
//

// create a $db object, and then...
// the table to delete from
$table = 'round_table';

// the WHERE clause
$where = $db->quoteInto('first_name = ?', 'Patsy');

// update the table and get the number of rows affected
$rows_affected = $db->delete($table, $where);

?>

Although you may query the database directly with the query() method, frequently all you need to do is select rows and get the results. The fetch*() series of methods does this for you. For each of the fetch*() methods, you pass an SQL SELECT statement; if you use named placeholders in the statement, you may also pass an array of bind values to be quoted and replaced into the statement for you. The fetch*() methods are:

<?php
	
// create a $db object, and then...

// fetch all columns of all rows as a sequential array
$result = $db->fetchAll(
    "SELECT * FROM round_table WHERE noble_title = :title",
    array('title' => 'Sir')
);

// fetch all columns of all rows as an associative array;
// the first column is used as the array key.
$result = $db->fetchAssoc(
    "SELECT * FROM round_table WHERE noble_title = :title",
    array('title' => 'Sir')
);

// fetch the first column of all rows returned
$result = $db->fetchCol(
    "SELECT first_name FROM round_table WHERE noble_title = :title",
    array('title' => 'Sir')
);

// fetch only the first value
$result = $db->fetchOne(
    "SELECT COUNT(*) FROM round_table WHERE noble_title = :title",
    array('title' => 'Sir')
);

// fetch a series of key-value pairs; the first column is
// the array key, the second column is the array value
$result = $db->fetchPairs(
    "SELECT first_name, favorite_color FROM round_table WHERE noble_title = :title",
    array('title' => 'Sir')
);

// fetch only the first row returned
$result = $db->fetchRow(
    "SELECT * FROM round_table WHERE first_name = :name",
    array('name' => 'Lancelot')
);

?>