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:

CREATE TABLE news (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
    date_created DATETIME NOT NULL ,
    title VARCHAR( 100 ) NOT NULL ,
    body MEDIUMTEXT NOT NULL
) TYPE = innodb;


INSERT INTO news (date_created, title, body)
VALUES 
 ('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…)

Zend_Db_Table

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:

(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:

<?php
Zend::loadClass('Zend_Db_Table');

class News extends Zend_Db_Table
{
    function __construct($config=null)
    {
        // use the Zend_Db_Adapter object in the registry
        if(!isset($config['db']))
        {
            $config['db'] = 'db';
        }
        parent::__construct($config);
    }

    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 */ ?>
<ul>
<?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.