Pragmatism in the real world

Zend Framework: Database Access

Now that I have the semblence of a working system with views coupled up to our action controllers it’s time to look at what the database classes do.

(I’ve uploaded some example code: ZF Test v2 to show that it all really works! Let me know if you find any bugs…)

As before, I’m looking to make my life easy when it comes to writing my applications. To understand what’s possible, I’m going to play with the latestNews action that I created last time.

The database table and example data I’m going to use is:

    date_created DATETIME NOT NULL ,
    title VARCHAR( 100 ) NOT NULL ,
) TYPE = innodb;

INSERT INTO news (date_created, title, body)
 ('2006-03-03 14:23:02', 'Zend Framework 0.1.1 Has Arrived', '<p>On the 3rd March 2006, Zend released a preview of their new framework.</p>')
,('2006-03-04 10:14:32', 'Some More News Just In', '<p>Some more interesting news has been announced and we are the first to cover it!</p>')
,('2006-03-05 12:45:18', 'New Rumours!', '<p>Is this news or just rumours? Who can tell?!</p>');

Looking at the manual, we use the Zend_Db_Adapter to connect to the database. As I haven’t got a plan for configuration yet, I’m going to connect to the database in index.php and store the $db into the registry. We can then get it out again when we need it.

    // database connection
    $params = array (
        'host'     => 'localhost',
        'username' => 'akrabat',
        'password' => '123456',
        'dbname'   => 'zf_test'

    $db = Zend_Db::factory('pdo_Mysql', $params);
    Zend::register('db', $db);
    unset($db); // don't need it here any more

That’s easy enough. (Obviously, don’t use a password like 123456 in the real world…)


From reading the manual, I think that Zend_Db_Table is the class that I want to use to encapsulate a table.

Clearly we want to extend it to create a class that represents our news database table. Zend_Db_Table expects that the name of the class is the same as the name of the table, where underscores in the table name are converted to camelCase in the class name. So, I’ll call this class “News” :) As it is specific to my test site, I’ll create a new directory called “tables” in my site directory.

Thus, my directory structure now looks like this:
ZF Test Directory Stucture
(As a reminder: Akrabat_Action maps the view to the controller behind the seams and Akrabat_Router allows me to have run the front controller within a subdirectory of my virtual host)

so.. tables/News.php:


class News extends Zend_Db_Table
    function __construct($config=null)
        // use the Zend_Db_Adapter object in the registry
            $config['db'] = 'db';

    function findLatestNews($numberOfItems = 3)
        return $this->fetchAll(null, 'date_created DESC', (int)$numberOfItems);

    function findById($id)
        return $this->find((int)$id);


A few points to note:
The constructor for Zend_Db_Table takes a $config array. One of the keys is ‘db’ which if this is a string, then is treated as a key to the Zend::registry where it expects to find a Zend_Db_Adapter. This is quite fortuitous as I’d already decided to use the registry earlier.

I’ve also created a couple of convenience functions findLatestNews() and findById() so that my controllers do not have to “speak” SQL. Of course, if I come across a situation that my News class doesn’t have a convenience function for, I can always use the Zend_Db_Table functions directly. If even those are too encapsulated, I can use Zend_Db_Table::getAdapter() to collect the db object itself and run SQL queries directly. Of course, I hope that I don’t ever need that in the controllers!

Having written News, I can now change the IndexController::latestNews() function:

function latestNews()
    $this->pageTitle = 'Latest News';
    $this->body = '<p>This is the body text set in IndexController::text()</p>';

    $news = new News();
    $this->news = $news->findLatestNews();

$this->news is now a Zend_Db_Table_Rowset and is easily used in the view template, latestnews.tpl.php:

<?php echo $this->latestNews->body; /*note: no escaping as we trust this data from the action */ ?>
<?php foreach($this->latestNews->news as $newsItem) : ?>
<li><?php echo date('d M Y', strtotime($newsItem->dateCreated)); ?>
    - <?php echo $this->escape($newsItem->title); ?></li>
<?php endforeach; ?>

As you can see, the RowSet implements the Iterator interface, so we can use it in a foreach loop. Each field in the database has been converted to camelCase from underscores (e.g. the database field date_created becomes the rowset field dateCreated) and usage is obvious.

I’m going to worry about linking through to the body page separately I think as this post has gone on a bit now.

All in all, the basic database usage for retreiving data is easy enough.

3 thoughts on “Zend Framework: Database Access

  1. Seems like a lot of boilerplate code is required for this framework. That, and everything is too verbose..

    Why does the view component need everything to be $this->var? Can't they make it so we can just do $var?

    Also, could you explain what the __construct() and the __call functions do? Do we HAVE to have those, or can we leave those out and have it still work?

    Thanks for the tutorials – they're awesome.. I hope you continue on with them.

  2. I'm not sure that there is that much boilerplate code. Obviously you need to tie the bit togeher, but it's easy enough to put it all into a subclass so that it's out of the way.

    __construct() is the class's constructor. It is called automatically by PHP whenever you create a new instance of a class.

    __call() is a "magic" function that is called whenever you try to call a class method that does not exist. i.e. it gives you a chance to implement "dynamic" functions. It's quite powerful, but you have to be careful not to over use it as you might end up with an undocumented mess :)

    Thanks for your kind words on the tutorials. I don't find writing easy at all – the coding is much simpler to do! I'm going to carry on documenting my experiences though as it's a way to force me to understand each component in turn.

Comments are closed.