Database Abstraction with Zend_Db - Part 1

Introduction

Database abstraction layers provide an important role in modern web applications built with PHP. They allow you to easily switch your back end database server by changing just a few lines of code in your application.

The Zend Framework provides a powerful database abstraction layer in the form of the Zend_Db component. This tutorial will be looking at the Zend_Db component in closer detail.

In Part 1 we will show you how to use the Zend_Db_Select class to build complex SELECT queries simply and quickly.


Requirements

To use the Zend_Db component you will first need to download the Zend Framework.

Visit the Zend Framework download page and download the newest version of the Zend Framework (1.5RC1 at the time of writing).

Once downloaded, unzip the Zend Framework archive to your Desktop or Home folder. The folder we are interested in is the library folder. Copy the library folder and its contents to your web folder so that your PHP scripts can access it.


Creating a database

Throughout this tutorial we will be using two tables and some sample content which are described below. If you wish to follow along with the tutorial then I would recommend creating the tables and inserting the sample data on your own database server.

Articles Table: Structure

SQL Code:

CREATE
 TABLE
  `articles`
 (


  `articleid`
 int(
10
)
 UNSIGNED
 NOT
 NULL
 AUTO_INCREMENT
,

  `title`
 varchar(
120
)
 NOT
 NULL
,

  `content`
 text NOT
 NULL
,

  `authorid`
 int(
10
)
 UNSIGNED
 NOT
 NULL
,

  `created`
 int(
10
)
 UNSIGNED
 NOT
 NULL
,

  PRIMARY
 KEY
  (
`articleid`
)


)
 ENGINE=InnoDB DEFAULT
 CHARSET=latin1;

Articles Table: Sample Data

SQL Code:

INSERT
 INTO
 `articles`
 VALUES
 

    (
1
,'Traverse Directories the Easy Way with Glob'
,'There is a powerful yet somewhat unheard of function in PHP titled glob(). Many beginners and intermediates alike struggle with the traversing of directories. Acquiring the directories contents and returning them in a readable format. If only they...'
,2
,1203082005
)
,

    (
2
,'Understanding the Life of a Session'
,'One of the most vast misconceptions in the PHP world is how sessions really do work. I/'
m fairly confident most of us know how to start sessions, terminate sessions, regenerate session IDs and easily pass data from 1 page to another. But do you really know...'
,2
,1202218024
)
,

    (
3
,'Introducing the PHP Command Line Interface'
,'This is a short article designed to introduce you to the PHP Command Line Interface (CLI) and some of the core differences you need to be aware of when developing for the command line compared to a website...'
,1
,1203773175
)
,

    (
4
,'Checking Input with Zend_Validate'
,'This article shows you how to take advantage of the Zend_Validate class from the Zend Framework to ensure that all input to your PHP applications is what you are expecting...'
,1
,1204205134
)
,

    (
5
,'Sending Emails with the Zend Framework'
,'Many PHP applications require the ability to send emails nowadays from simple plain-text emails confirming a registration to advanced HTML newsletters. PHP allows us to send emails using the mail() function but this can quickly get complex when you begin...'
,1
,1203427590
)
,

    (
6
,'The Big GD Guide - Part 1'
,'This article will take you through as much about the GD library as possible. Telling you how things can be done, how they work and how they can be used. It will take you through learning things that will help you create top image based codes that will...'
,3
,1204032358
)
;

Authors Table: Structure

SQL Code:

CREATE
 TABLE
  `authors`
 (


  `authorid`
 int(
10
)
 UNSIGNED
 NOT
 NULL
 AUTO_INCREMENT
,

  `name`
 varchar(
70
)
 NOT
 NULL
,

  `email`
 varchar(
120
)
 NOT
 NULL
,

  PRIMARY
 KEY
  (
`authorid`
)


)
 ENGINE=InnoDB DEFAULT
 CHARSET=latin1;

Authors Table: Sample Data

SQL Code:

INSERT
 INTO
 `authors`
 VALUES
 

    (
1
,'Alan'
,'[e-mail]'
)
,

    (
2
,'Wildhoney'
,'[e-mail]'
)
,

    (
3
,'Rendair'
,'[e-mail]'
)
;

Creating a new Zend_Db object

Lets start by creating a file called fetch.php and putting it in the same folder that you placed the /library folder earlier in the tutorial. We will use this file for our examples throughout this tutorial.

The first step in using Zend_Db is to include the class:

PHP Code:

set_include_path('.'
    . PATH_SEPARATOR . './library'
    . PATH_SEPARATOR . get_include_path()
);

include 'Zend/Db.php'; 


Notice how we first set the PHP include path before including the file. This allows the Zend Framework to find the files it requires.

Now that we have included the Zend_Db core we can setup our database connection:

PHP Code:

$dbConfig = array(
    'host'        => 'localhost',
    'username'    => 'user',
    'password'    => 'pass',
    'dbname'    => 'my_database'
);

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


In this example we start by creating an array with our database config, hostname, username, password and database name. We then use the Zend_Db Factory() method to create a new database connection using the Pdo_Mysql driver.

Zend_Db comes with many different database drivers including:

• IBM DB2 and Informix Dynamic Server (IDS) (Using PDO or the ibm_db2 PHP extension)
• MySQL (Using PDO or the mysqli PHP extension)
• Microsoft SQL Server (Using PDO)
• Oracle (Using PDO or the oci8 PHP extension)
• PostgreSQL (Using PDO)
• SQLite (Using PDO)
• Firebird/Interbase (Using the php_interbase PHP extension)

Now that we have our $db object we can start creating our queries using the Zend_Db_Select class.

Using Select

We are going to start by fetching the articleid and title columns for all rows in our articles table.

In a PHP script without a database abstraction layer such as Zend_Db, you may do something like:

PHP Code:

$query = "SELECT articleid, title FROM articles";
$result = mysql_query($query); 


Whilst this works fine for MySQL servers, it would break if you moved your database storage to another database server such as Microsoft SQL or Oracle.

The Zend Framework provides the Zend_Db_Select class to provide portable SELECT query creation.

To create a new query object using Zend_Db_Select we simply need to do:

PHP Code:

$query = $db->select(); 


$query is now a Zend_Db_Select object providing methods such as from(), where() and join().

So to create our SELECT query we use the following:

PHP Code:

$query = $db->select();
$query->from('articles', array('articleid', 'title')); 


Pretty simple eh?

We start by creating our new Zend_Db_Select object and then use the from() method to specify what table we wish to query and what columns we wish to fetch.

The first argument to the fetch() method is the name of the table. In our case, this is the articles table.

The second argument to fetch() is an array of column names that we want to fetch. If you wish to select all columns (SELECT *) then you can omit this argument. If you wish to fetch no columns, then you can pass an empty array.

Zend_Db_Select objects come with a special method called __toString() which allows us to see what our final query will look like.

If you add:

PHP Code:

echo $query->__toString(); 


to your code then run the script you would end up with something like:

SQL Code:

SELECT
 `articles`
.`articleid`
, `articles`
.`title`
 FROM
 `articles`

The __toString() method is handy for checking that you have built your queries correctly or testing them in an external database tool.

Now that we have our query built we need to run it. Zend_Db provides various fetch*() methods for this purpose. We are going to use the fetchAll() method to fetch all rows that match our query:

PHP Code:

$result = $db->fetchAll($query); 


The variable $result now contains an array of rows that matched our query.

If you where to use the print_r() function to display our results you would get something like:

Code:

Array

(

    [0] => Array

        (

            [articleid] => 1

            [title] => Traverse Directories the Easy Way with Glob

        )



    [1] => Array

        (

            [articleid] => 2

            [title] => Understanding the Life of a Session

        )

    

    <...snip...>

    

    [5] => Array

        (

            [articleid] => 6

            [title] => The Big GD Guide - Part 1

        )



)

As this is just a normal array you can use the foreach() function to traverse over it as you would with a normal query result:

PHP Code:

echo '<h1>All articles with default sort order</h1>';
foreach ($result as $row)
{
    echo 'Article #' . $row['articleid'] . ' - ' . $row['title'] . '<br />';
} 


If you now run this code then it would display all rows from our database in the order they where added - Screenshot: Select Results


Using Order

Now that we have seen how simple it is to select rows from a database table using Zend_Db and Zend_Db_Select, we can progress and add some order to our results.

Lets start by creating a Zend_Db_Select object and using the from() method to specify the table and column names:

PHP Code:

$query = $db->select();
$query->from('articles', array('articleid', 'title')); 


We will now use the order() method to specify how we wish the results to be ordered:

PHP Code:

$query->order('title ASC'); 


As you can see, we have specified that the results should be sorted using the title column and put into ascending (A-Z) order. If you wish the results to be in descending (Z-A) order, you would use the DESC keyword rather than ASC

If we then fetch our select query using the fetchAll() method and display our results using the following code:

PHP Code:

$result = $db->fetchAll($query);

echo '<h1>All articles sorted by title (A-Z)</h1>';
foreach ($result as $row)
{
    echo 'Article #' . $row['articleid'] . ' - ' . $row['title'] . '<br />';
} 


We would get a list of articles sorted in ascending order - Screenshot: Ordering Results


Using Joins

We now have a nice list of all articles in our database but no information on who wrote them! To retrieve this information we will need to create a JOIN query using our articles and authors tables.

As you may have seen when you created them, both tables share a related column, authorid. Each entry in the articles table has an authorid value which corresponds with the authorid column in the authors table allowing us to fetch information on the author of a particular article.

Lets start by creating our Zend_Db_Select object and using the from() method to specify our table and column names:

PHP Code:

$query = $db->select();
$query->from(array('a' => 'articles'),
             array('articleid', 'title', 'created')); 


You may have noticed that we have adjusted the arguments to the from() method in this example. As we are going to be using JOIN in our SQL query we need to give our tables aliases.

In this example, the first argument to from() is an array containing one element, 'alias' => 'tablename'. In a regular SQL query this would look like:

SQL Code:

...FROM
 articles AS
 a...

The next step is to add our table join. Zend_Db_Select provides the join() method for this:

PHP Code:

$query->join(array('m' => 'authors'), 'a.authorid = m.authorid'); 


When calling the join() method we pass it two arguments. The first is an array with a single element containing the name and alias for the table that we wish to join. In this example, we want to join the authors table and we give it an alias of 'm'.

We then use the second argument to specify what columns we wish to match. As we have a column called authorid in our articles table ('a') which references the authorid column in our authors table ('m'), we use them in our matching clause.

join() also accepts an optional third argument. This is an array of column names that you wish to select. If you do not wish to select any columns from the joined table then you can pass an empty array as the third argument. The default is to select all columns from the joined table.

And finally, we add an ORDER clause using the order() method to sort our articles by their creation date starting with the newest:

PHP Code:

$query->order('created DESC'); 


If you where to then use the __toString() method on your $query you would see something like:

SQL Code:

SELECT
 `a`
.`articleid`
, `a`
.`title`
, `a`
.`created`
, `m`
.* 

FROM
 `articles`
 AS
 `a`
 

INNER
 JOIN
 `authors`
 AS
 `m`
 

ON
 a.authorid = m.authorid 

ORDER
 BY
 `created`
 DESC

Lets now run our query and output the results:

PHP Code:

$result = $db->fetchAll($query);

echo '<h1>All articles with author and creation date</h1>';
foreach ($result as $row)
{
    echo '<a href="view.php?id=' . $row['articleid'] . '">' . $row['title'] . '</a><br />';
    echo 'Written by <a href="mailto:' . $row['email'] . '">' . $row['name'] . '</a><br />';
    echo 'Created on ' . date('jS F o', $row['created']) . '<br /><br />';            
} 


We have added a bit of formatting into our result output this time to make it slightly easier to read. If you now run this script you should see the hyper linked article titles (to a nonexistent view.php script), the authors name and the date the article was created - Screenshot: Join Results

In this example we have used the INNER JOIN type. Zend_Db_Select also supports the following JOIN types:

• INNER JOIN - join(table, condition, columns)
• LEFT JOIN - joinLeft(table, condition, columns)
• RIGHT JOIN - joinRight(table, condition, columns)
• FULL JOIN - joinFull(table, condition, columns)
• CROSS JOIN - joinCross(table, columns)
• NATURAL JOIN - joinNatural(table, columns)

Using Where

So far you have seen how to select results from a single table, how to join other tables, and how to order the results, all of which returned every row in the table.

What about those times when you only wish to retrieve a selection of rows from your tables? Zend_Db_Select provides the where() method for just such an occasion.

The where() method allows you to restrict the result set by specifying conditions.

Lets start by creating our $select object and setting our FROM, JOIN and ORDER clauses:

PHP Code:

$query = $db->select();
$query->from(array('a' => 'articles'),
             array('articleid', 'title', 'content', 'created'));
$query->join(array('m' => 'authors'), 'a.authorid = m.authorid', array());
$query->order('title ASC'); 


You'll notice that we have supplied the third argument to the join() method this time. As we have given it an empty array, no columns from the authors table will be included in our results.

Now that we have our basic query set up we need to add some WHERE conditions:

PHP Code:

$author = 'Alan';

$query->where('m.name = ?', $author);
$query->where('a.created > 1203427591'); 


The first things you will notice is that we set a new variable called $author and give it the value of 'Alan'.

We then use this $author variable in our where() method to specify that we only want to retrieve articles that where written by the author 'Alan'.

You may be wondering why we use a placeholder question mark in our query instead of just using something like:

PHP Code:

'm.name = ' . $author; 


You'll also notice that we don't use any sort of escape function on our data to sanitize it (ie, escape quotes) before using it in our query.

The reason is that when Zend_Db sends the query to the server, it sends our $author variable separately which means that it is never part of the query. This also means that SQL injection using the $author variable is impossible as it is not used in our query.

In our second WHERE clause we simply specify that we only want to retrieve articles that have a newer date than the one we specify (in Unix timestamp format).

We can now run our query using the fetchAll() method and output our results:

PHP Code:

$result = $db->fetchAll($query);

echo '<h1>All articles written by ' . $author . ' and dated after ' . date('jS F o', 1203427590) . '</h1>';
foreach ($result as $row)
{
    echo 'Article #' . $row['articleid'] . ': ' . $row['title'] . ' - ' . date('jS F o', $row['created']) . '<br />';
    echo nl2br($row['content']) . '<br /><br />';
} 


You should find that this prints out the two articles that our query matched - Screenshot: Where Results

By default, where() uses a WHERE...AND... syntax when it is given multiple WHERE statements. If you wish to use a WHERE...OR... syntax then you can use the orWhere() method instead.

For example:

PHP Code:

// WHERE...AND... syntax
$query = $db->select();
$query->from('products');
$query->where('price < ?', 500);
$query->where('price > ?', 100);
// echo $query->__toString();
// Query: SELECT * FROM products WHERE price < 500 AND price > 100

// WHERE...OR... syntax
$query = $db->select();
$query->from('colors');
$query->where('color = ?', 'red');
$query->orWhere('color = ?', 'blue');
// echo $query->__toString();
// Query: SELECT * FROM colors WHERE color = 'red' OR color = 'blue' 


Using Group

Often you will find that you need to group results together using certain criteria. Zend_Db_Select provides the group() method for this purpose.

We'll start by creating a standard SELECT query with a JOIN and ORDER clause:

PHP Code:

$query = $db->select();
$query->from(array('a' => 'articles'), array('article_count' => 'COUNT(a.articleid)'));
$query->join(array('m' => 'authors'), 'a.authorid = m.authorid', array('m.name'));
$query->order('article_count DESC'); 


The main difference here is that we specify in our from() method that we wish to use the SQL function COUNT() to count all rows in our articles table and store that number an alias called article_count. We then order our results by this article_count alias in descending order.

Now we need to add our GROUP clause:

PHP Code:

$query->group('a.authorid'); 


As you can see, we have used the group() method to specify that we want to group our results by each author.

If you where to write this query in standard SQL it would look something like:

SQL Code:

SELECT
 COUNT(
a.articleid)
 AS
 `article_count`
, `m`
.`name`
 

FROM
 `articles`
 AS
 `a`
 

INNER
 JOIN
 `authors`
 AS
 `m`
 ON
 a.authorid = m.authorid 

GROUP
 BY
 `a`
.`authorid`
 

ORDER
 BY
 `article_count`
 DESC

The result of this query is an array with each authors name and the number of articles that they have published.

Lets run the query and output the results to see it in action:

PHP Code:

$result = $db->fetchAll($query);

echo '<h1>Authors and Article Counts</h1>';
foreach ($result as $row)
{
    echo $row['name'] . " has published " . $row['article_count'] . ' article(s)<br />';
} 


If you run this you will see that it prints the number of articles that each author has written in descending (highest to lowest) order - Screenshot: Group Results


Using Limit

Zend_Db_Select provides the limit() method that allows you to limit the number of results that are returned.

Lets dive straight into the code by creating a new query:

PHP Code:

$query = $db->select();
$query->from('articles', array('articleid', 'title')); 


This should be familiar by now, we are selecting the articleid and title columns from the articles table.

Now, lets add a limit specifying that we only want the first 4 rows this query finds:

PHP Code:

$query->limit(4); 


If we now run this query and display the results:

PHP Code:

$result = $db->fetchAll($query);

echo '<h1>Articles 1-4</h1>';
foreach ($result as $row)
{
    echo 'Article #' . $row['articleid'] . ': ' . $row['title'] . '<br />';
} 


You'll see that it only displays the first 4 rows from the database - Screenshot: Limit Results


Using Limit with Offset

You can also specify an offset when using the limit() method:

PHP Code:

$query = $db->select();
$query->from('articles', array('articleid', 'title'));
$query->limit(3, 2); 


This specifies that we want to fetch three rows from the database, skipping the first two rows.

If we then run the query and display the result:

PHP Code:

$result = $db->fetchAll($query);

echo '<h1>Articles 3-5</h2>';
foreach ($result as $row)
{
    echo 'Article #' . $row['articleid'] . ': ' . $row['title'] . '<br />';
} 


You will see that it only returns rows 3, 4 and 5 from the database - Screenshot: Limit with Offset Results


Fetching a Single Row

Throughout this tutorial we have used the fetchAll() method to get our results. You may find that you have queries that only return a single row.

For these queries, Zend_Db_Select provides the fetchRow() method.

First lets build our query:

PHP Code:

$query = $db->select();
$query->from('articles');
$query->where('authorid = 1');
$query->order('created DESC');
$query->limit(1); 


We are using the limit() method to specify that we only want a single row to be returned.

Now we can use the fetchRow() method to fetch our single row and display it:

PHP Code:

$result = $db->fetchRow($query);

echo '<h1>Newest Article Written by Alan</h1>';
echo $result['title'] . ' (' . date('jS F o', $result['created']) . ')<br />'; 


You'll notice that we don't have to use a foreach() loop when using fetchRow() as only a single row was fetched - Screenshot: Fetching a Single Row Results


Conclusion

Hopefully this article has shown you that using an advanced database abstraction layer such as Zend_Db is quicker and easier than writing the manual queries!

It also has the major benefit of giving portability between different database systems that simple database wrapper classes don't provide. For example, some database servers do not support the LIMIT keyword. Zend_Db and Zend_Db_Select take that into account and the limit() method adjusts the final query as needed.


References and Further Reading

All code from this tutorial in a single file
SQL Database Dump for the sample database used in this tutorial
Copy of the results from the Zend_Db tests above

Zend Framework
Zend_Db Component Manual
Zend_Db_Select Component Manual