Zend Framework Database Step by Step Tutorial

What Zend_Db Gives You

Zend_Db is an abstraction layer over PHP’s PDO. It handles connection management, query building, result fetching, and table gateway patterns. You write less boilerplate and your code becomes portable across database engines.

The component comes in layers. Zend_Db_Adapter manages the connection. Zend_Db_Select builds SQL queries programmatically. Zend_Db_Table maps a class to a database table. You can use just the adapter for simple queries or the full table gateway for structured CRUD operations.

Pack Overview

This pack starts with the framework basics and configuration (you need a database connection string before anything else), then moves into building an input form backed by a database table. The config article is essential reading because your database credentials live in the config file.

Walkthrough

Step 1 - Configure the Database Adapter

In your configuration file, add the database section:

1
2
3
4
5
6
7

<database>
  <adapter>Pdo_Mysql</adapter>
  <host>localhost</host>
  <username>root</username>
  <password></password>
  <dbname>myapp</dbname>
</database>

In your bootstrap, create the adapter:

1
2

$db = Zend_Db::factory($config->database->adapter, $config->database->toArray());
Zend_Db_Table::setDefaultAdapter($db);

Setting the default adapter means every Zend_Db_Table subclass automatically uses this connection. No need to pass the adapter around.

Step 2 - Create a Table Class

For a users table, create application/models/DbTable/Users.php:

1
2
3
4

class Application_Model_DbTable_Users extends Zend_Db_Table_Abstract
{
    protected $_name = 'users';
}

That is the minimum. The $_name property tells the class which table to query. You can now call methods like fetchAll(), find(), insert(), and delete() on this class.

Step 3 - Build an Input Form

The main article in this pack walks through creating an HTML form that inserts records into the database. The flow is:

1. Controller displays the form on GET requests
2. User fills in fields and submits
3. Controller validates input on POST
4. Valid data gets passed to the model for insertion
5. Controller redirects on success or redisplays the form with errors

Zend Framework has Zend_Form for building forms with built-in validation and filtering. You define form elements, attach validators, and call isValid() on the submitted data.

Step 4 - Query with Zend_Db_Select

For queries beyond simple CRUD, use Zend_Db_Select:

1
2
3
4
5
6
7

$select = $db->select()
    ->from('users', array('id', 'name', 'email'))
    ->where('active = ?', 1)
    ->order('name ASC')
    ->limit(20);

$rows = $db->fetchAll($select);

The ? placeholder is automatically quoted and escaped. Never concatenate user input into SQL strings, even with Zend_Db. Always use placeholders.

Fetching Strategies

Zend_Db_Table gives you several ways to retrieve data:

• fetchAll() returns a Zend_Db_Table_Rowset - an iterable collection of row objects
• fetchRow() returns a single Zend_Db_Table_Row
• find($id) fetches by primary key
• fetchAll($where) accepts a WHERE clause string or Zend_Db_Select object

Row objects are live. You can modify properties and call save() to update the database record in place.

Common Pitfalls

Forgetting to set the default adapter. If you skip setDefaultAdapter(), every table class throws an exception when instantiated. Set it once in the bootstrap.

SQL injection through string concatenation. Even though you are using Zend_Db, it is still possible to build vulnerable queries by concatenating strings into where() calls. Always use the placeholder syntax.

Not closing connections. PHP closes database connections at the end of each request, so this is rarely a problem. But in long-running scripts or workers, call $db->closeConnection() when done.

Mixing table gateway with raw queries. Pick one approach and stay consistent within a model. Mixing Zend_Db_Table methods with raw $db->query() calls makes code harder to follow.

FAQ

Does Zend_Db work with PostgreSQL?
Yes. Change the adapter to Pdo_Pgsql and update the connection settings. The query builder and table classes work the same way.

How do I handle transactions?
Call $db->beginTransaction(), run your queries, then $db->commit() or $db->rollBack(). Wrap the block in a try-catch.

Can I use joins with Zend_Db_Select?
Yes. Use ->join('othertable', 'users.id = othertable.user_id'). Left joins, right joins, and cross joins are all supported.

What about database migrations?
Zend Framework 1 does not include a migration tool. You can use standalone tools or write SQL migration scripts that run during deployment.

How do I debug slow queries?
Enable the Zend_Db profiler with $db->getProfiler()->setEnabled(true). It logs every query along with execution time. Review the output to find bottlenecks.