Join the friendly bunch on IRC...
Database Abstraction with Zend_Db - Part 1Introduction
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','alan@example.com'),
(2,'Wildhoney','wildhoney@example.com'),
(3,'Rendair','rendair@example.com');
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';
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);
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);
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'));
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();
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);
$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
)
)
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 />';
}
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'));
order() method to specify how we wish the results to be ordered:PHP Code:
$query->order('title ASC');
DESC keyword rather than ASCIf 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 />';
}
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'));
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');
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');
__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 />';
}
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');
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');
$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;
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 />';
}
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');
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');
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 />';
}
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'));
Now, lets add a limit specifying that we only want the first 4 rows this query finds:
PHP Code:
$query->limit(4);
PHP Code:
$result = $db->fetchAll($query);
echo '<h1>Articles 1-4</h1>';
foreach ($result as $row)
{
echo 'Article #' . $row['articleid'] . ': ' . $row['title'] . '<br />';
}
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);
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 />';
}
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);
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 />';
foreach() loop when using fetchRow() as only a single row was fetched - Screenshot: Fetching a Single Row ResultsConclusion
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
