Karl Katzke

Today, let’s take a look at Zend_Db. Zend_Db implements a Table Data Gateway, in which the object is considered to be extended to have the properties of a table, as opposed to a Object Relational Model (ORM) / Data Access Object (DAO) where the object is considered to be a representation of the database object. Philosophy aside, it’s a great tool, and we’re mostly interested in how to use it. It is worth noting that you can use Zend Framework along with the excellent Doctrine ORM… and the syntax is even pretty similar.

The general procedure when setting up Zend_Db is to create a bunch of objects that extend Zend_Db_Table_Abstract. When defining an object, you can define what table it connects to, what the primary key is, and any foreign keys that relate to it. Upon instantiating the object in your code, you can use it to build queries against it’s related table, which returns a collection of row sets for direct use in your applications. Today’s tutorial assumes that you’re very comfortable with looping and other control structures in PHP.

Configuration File

You should have a MySQL database accessible to your webserver. Knowing how to use MySQL is outside of the scope of this tutorial but the basic gist of what you need to do is — create a database, create a user and password for that database, and insert the SQL to create the tables.

ZFApp (Day 3) SQL

When you’ve collected that information, let’s write a configuration file. This file will go in app/config/db.ini.

[dev]
database.params.host=localhost
database.adapter=pdo_mysql
database.params.username=foobaruser
database.params.password=foobar
database.params.dbname=zfapp

Save that file, and then let’s go tell the front controller how to access the zfapp database.

Front Controller

The first thing we need to do is go back and visit our friend the Front Controller. It needs some setup if we’re to use Zend_Db. Let’s open up index.php and add a few lines. We’ll be using Zend_Config_Ini to load up the configuration file. Note the ‘dev’ - that tells us to load up the area after the [dev] in the above .ini file. I use this to tell the difference between my dev and live configuration, since that keyword is set by a SetEnv statement in the Apache configuration file.

$dbconf = new Zend_Config_Ini(BASEDIR.'/app/config/db.ini','dev');

Now that we’ve got a handle to the configuration file, we’ll use Zend_Db’s factory method to create a DB Connection.

$db = Zend_Db::factory($dbconf->database);

That’s it. That’s all we need to do. $db is now a database handle. Two lines of code! Zing! The only other things that we’ll find helpful here is setting this DB handle as the default handle for tables. I also register the DB handle with the registry so that I have an easy way to get it back later if I want to do something deep and complicated with the db.

Zend_Db_Table_Abstract::setDefaultAdapter($db);
Zend_Registry::set('db',$db);

Using the DB

Tomorrow, we’ll really get into using the database for something, but for today let’s just finish the prep work and make a basic use of our new toy.

First, we need a model to access. Make sure you’ve got the models on your include path in your front controller:

set_include_path(ini_get('include_path').PATH_SEPARATOR.BASEDIR.'app/models');

Now, we’ll create our first model. A model is a basic file that just tells the database driver where a table is, and provides some defaults. In /app/models/users.php, we’ll want to extend a Zend_Db_Table_Abstract as follows:

class Users extends Zend_Db_Table_Abstract {
   protected $_name = 'users';
   protected $_primary = 'id
}

… and that’s it. Since you included the models directory in your include path, all you need to do is instantiate a Users module anywhere in your application, and you can access the users table via the db handle you created in your front controller and assigned as default. Pretty neat, huh?

Ok, let’s select some rows out.

Head over to the /app/controllers/IndexController.php that you made yesterday. In the indexAction() function, place this code:

public function indexAction() {
  $this->initView(); // Initialize the view object for this module
  $user = new Users(); // Users Table Object. 
  $rowset = $user->select(); // Created a default 'select all' query
  $list_of_users = array();
  foreach($rowset as $row) {
    $list_of_users[] = $row->id;
  }
  $this->view->userlist = join($list_of_users,', '); // Concatenate the list of users and make it available to the view. 
}

And we’ll head over to the view script you created yesterday … /app/views/scripts/index/index.phtml … and add in a line to echo that userlist. Don’t forget to add the carat-questionmarks here, of course.

 echo $this->userlist;

… and as Emeril says, Bang! You’re reading from your database.

Tomorrow we’ll do some ‘heavy’ lifting — we’ll set up Zend_Auth to authenticate users from the database, a Zend_Form to allow users to sign up, and a Zend_Mail to send users their passwords when they’ve forgotten it.