In the third and last part of my series of posts, I have tested one of many components from Zend Framework, one of the most mature, tested and complete PHP frameworks available. Although it is possible to use this component outside a standard ZF application, there are several extra configurations needed or the adoption of other components like Zend_Loader and Zend_Exception. As they don’t apply for this article’s scope, this kind of situation was intentionaly ommitted.

In a typical MVC application, Zend_Db is the tool responsible for encapsulating all the operations done in the model layer. In addition to database abstraction, it has other classes implementing object-relational mapping patterns, which will be presented in a new series of articles.

Connection

The connection is made through a DBMS adapter. There are adapters for PDO drivers and for the most common PHP extensions, and it can be established in three ways:

– Adapter object

$config = array(
    'host'     => 'localhost',
    'username' => 'user',
    'password' => 'secret',
    'dbname'   => 'database'
);
$db = new Zend_Db_Adapter_Pdo_Mysql($config);

– Zend_Db factory method

$db = Zend_Db::factory('Pdo_Mysql', $config);

The third option is to build the connection through a Zend_Config object. This approach will not be presented, due to the fact that it is not necessary for using.

There are several parameters which can be provided in the connection configuration. Take a look at the docs for a full list.

Auto quoting

Unlike Doctrine, Zend_Db provides auto quoting for identifiers, even though this option can be turned off when the connection object is created.

$config['options'] = array(Zend_Db::AUTO_QUOTE_IDENTIFIERS => true);
$db = Zend_Db::factory('Pdo_Mysql', $config);

It is worth saying that the connection is not started automatically. Data is stored in the connection object so it can be used as soon as you provide your first query.

Fetching results

The fetchAll() method is in charge of returning your query’s result set. It is possible to use it with a standard SQL query or with a Zend_Db_Select object.

/**
 * These two snippets have the same effect
 */
// SQL
$sql = 'SELECT * FROM customer WHERE id = 2';
$result = $db->fetchAll($sql);
// Zend_Db_Select object
$select = $db->select()->from('customer')->where('id = 2');
$result = $db->fetchAll($select);
 
/*
array(1) {
  [0]=>
  array(3) {
    ["id"]=>
    string(1) "2"
    ["first_name"]=>
    string(4) "Jane"
    ["last_name"]=>
    string(2) "D."
  }
}
*/

Fetch mode

You can retrieve a result set in several ways, such as an array of associative indexes, numeric indexes, both or in the form of a stdClass object.

$db->setFetchMode(Zend_Db::FETCH_OBJ); // Zend_Db::FETCH_NUM / Zend_Db::FETCH_ASSOC / Zend_Db::FETCH_BOTH

However, if you use fetchAssoc() method this kind of definition will be overwritten and an associative array will be returned.

$db->setFetchMode(Zend_Db::FETCH_OBJ); // will be overwritten
$result = $db->fetchAssoc('SELECT * FROM customer');
/*
array(2) {
  [1]=>
  array(3) {
    ["id"]=>
    string(1) "1"
    ["first_name"]=>
    string(4) "John"
    ["last_name"]=>
    string(3) "Doe"
  }
  [2]=>
  array(3) {
    ["id"]=>
    string(1) "2"
    ["first_name"]=>
    string(4) "Jane"
    ["last_name"]=>
    string(2) "D."
  }
}
*/

Notice that the array keys refer to the primary key of the table.

Fetch single column

If necessary, you can show the results from a single column. The fetchCol() method will always return the first column of your SQL, no matter how many columns have been given.

$result = $db->fetchCol('SELECT first_name, last_name FROM customer WHERE id = 2');
 
/*
array(1) {
 [0]=>
 string(4) "Jane"
}
*/

Fetch single row

The fetchRow() method will return only a single tuple, without the need of a LIMIT declaration in you SQL statement.

$result = $db->fetchRow('SELECT * FROM customer WHERE id = 2');
/*
array(3) {
  ["id"]=>
  string(1) "2"
  ["first_name"]=>
  string(4) "Jane"
  ["last_name"]=>
  string(2) "D."
}
*/

Inserting data

The insert() method receives as parameters the name of the table and an array with key-value pairs for table columns and values. It is not necessary to escape or quote this values.

$data = array(
    'first_name' => 'G.',
    'last_name'  => 'S.',
);
$db->insert('customer', $data);

It is possible to append an SQL expression into your data array. In this case, you have to create a Zend_Db_Expr object with your expression as the constructor. Zend_Db_Expr provides a very simples way to avoid escapes in your string.

$data = array(
    'first_name' => 'George',
    'last_name'  => new Zend_Db_Expr('UPPER("secrieru")')
);
$db->insert('customer', $data);

The return from this method is the number of rows affected by the statement (although this value would probably be 1 for the most situations :-). In our case, we are using a RDBMS which allows obtaining the last value created by an auto increment definition.

$id = $db->lastInsertId();

Updating data

The update() method receives as parameters the name of the table, an array in the same format expected for insert() and a third parameter which corresponds to the WHERE clause in the SQL. It is worth saying that this parameter does not have an automatic escaping. As in insert(), this method returns the number of rows affected by its execution.

$data = array(
    'last_name' => 'Sec.'
);
$affectedRows = $db->update('customer', $data, 'id = 4');

Instead of sending a string as the third parameter, it is possible to use an array with strings that will be appended to the query with an “AND” operator.

$data = array('first_name' => 'Ge.');
$where = array(
    'id = 4',
    'last_name = "SECRIERU"'
);
$affectedRows = $db->update('customer', $data, $where);

Delete

Receive as parameters the name of the table and the WHERE clause, in the same format of update() — string or an array of string that will be operated with an “AND”. If the last parameter is omitted, all tuples will be removed.

$where = array('id = 3', "first_name = 'G.'");
$affectedRows = $db->delete('customer', $where);

Quoting

There are three methods for quoting strings:

– quote()

Insert escapes according to the adapter in use.

$injection = $db->quote("'; DROP TABLE foo; --"); // \'; DROP TABLE foo; --

There is an optional parameter that specifies the data type which is being escaped. This is useful for integers, for example.

$value = '1234';
var_dump($db->quote($value, 'FLOAT')); // string(11) "1234.000000"

– quoteInto()

Insert the string into a SQL, replacing the ‘?’ placeholder with its value. This is the same escaping done with quote().

$sql = $db->quoteInto("SELECT * FROM customer WHERE first_name = ?", "Mr. Mojo Risin'"); // Risin\'

– quoteIdentifier()

Provides a simple way of quoting identifiers such as table names or columns that might be reserved words. It also prevents the use of characters that are not allowed in standard SQL statements

$tableName = $db->quoteIdentifier('limit'); // "limit"

Transactions

The beginTransaction() method provides the use of this feature combined with a try-catch block.

$db->beginTransaction();
try {
    // an exception will be raised if any of these queries fail
    $db->insert('customer', $first);
    $db->insert('customer', $second);
    $db->insert('customer', $third);
    $db->insert('customer', $fourth);
    $db->commit();
 
} catch (Exception $e) {
  $db->rollBack();
}

Other queries

$affectedRows = $db->exec('DROP TABLE customer');

Closing connection

As PHP will handle this operation, you don’t have to worry about this. However, you can do it during your script execution if you want or need.

$db->closeConnection();

Conclusion

Zend_Db — and the whole Zend Framework — relies on massive contributions, unit tests and an active community who is always in the mood for bug hunting and making things better. These are some good points for relying and trying it out. At this very moment, the project is passing through a refactoring process for some architectural changes that will support PHP 5.3 features and increase its scalability.

Although Zend_Db has proven to be a very consistent component for database abstraction, its use seems to be more indicated for ZF-based applications due to the fact that it depends a lot on other ZF components to work properly.