131CHAPTER 5 • INTRODUCING THE ZEND FRAMEWORK
Listing 5-11. Registration E-mail in Production Mode
Dear Jason,

Welcome to the GameNomad community! To conrm your e-mail address, click on
the following URL:

http://www.gamenomad.com/gamers/verify/key/zs7lquz958qknj1hqq70gcg89egqsdf1

Questions? Comments? Contact us at [email protected]!

Thank you,
The GameNomad Team

We can create these e-mail variations by passing conguration variables into a template (Listing
5-12).
Listing 5-12. The Registration E-mail Template
Dear {$rstName},

Welcome to the GameNomad community! To conrm your e-mail address, click on
the following URL:

{$this->cong->website->url}/gamers/verify/key/{$registrationKey}

Questions? Comments? Contact us at $this->cong->email->support

Thank you,
The GameNomad Team

The Zend Framework will be able to identify which set of conguration variables should be passed
based on the value of

When referring to the parameter within a script, you'll prex it with $this->cong and converting
the periods to right-facing arrows (->), like so:
$this->cong->database->params->username

Notice how in the same cong.ini le (Listing 5-13) these parameters are listed twice. Once under
the section titled
[production] and a second time under the section titled [development]. The
Zend_Cong component will choose the appropriate parameter according to the aforementioned AP-
PLICATION_ENVIRONMENT setting.
Listing 5-13. Sample cong.ini le
; Production site conguration data
[production]

website.params.url = http://www.gamenomad.com

database.params.host = mysql.gamenomad.com
database.params.username = gamenomad_prod
database.params.password = supersecret
database.params.dbname = gamenomad_prod

email.params.support = [email protected]

; Development site conguration data
[development]

website.params.url = http://beta.gamenomad.com

database.params.host = mysql.gamenomad.com
database.params.username = gamenomad_dev
database.params.password = supersecret

}

public function conrmationAction()
{
echo $this->cong->db->username;
}

}
Step #9. Creating Action Helpers
Continuing the theme of guring out ways to eliminate redundancy at every opportunity, the init()
method is very useful if you want to share access to for instance the conguration le handle across
multiple actions, but in all likelihood you'll want access to the conguration le and certain other
variables throughout your application. The result is a duplicated
init() method within each control-
ler, resulting in the need to touch every controller each time you want to make another variable glob-
ally available, a clear violation of framework strategy.
Or suppose you require a solution for generating random strings, which might be used when resetting
Download at Boykma.Com
134 CHAPTER 5 • INTRODUCING THE ZEND FRAMEWORK
passwords or within CAPTCHAs (see Chapter 7). It's easy to insert such a method into a controller,
however over time it's likely the method will begin to multiply like rabbits as it's needed within other
parts of the application.
One of the easiest solutions for remedying both of these problems involves creating an action helper.
Much like view helpers facilitate the rendering of data within the view, action helpers facilitate the
execution of certain tasks within controllers. Once in place, you'll be able to refer to certain variables
dened within the helper from any controller, not to mention call methods such as the aforementioned
random string generator.
Let's conclude this chapter by creating an action helper which will make the Zend_Registry's
$cong
variable automatically available to all controllers. I'll also show you how to create a method which

Download at Boykma.Com
135CHAPTER 5 • INTRODUCING THE ZEND FRAMEWORK
Loading the Action Helper
To identify the location and prex of your action helper, add the following line to your bootstrap.
php
le. Of course, you'll need to change the values of each self-explanatory addPath() parameter as
necessary:
Zend_Controller_Action_HelperBroker::addPath(
'C:\apache\htdocs\gamenomad.com\application\My\Helper', 'My_Action_Helper');

You can then invoke an action helper as needed using the following command
$initStuff = Zend_Controller_Action_HelperBroker::getStaticHelper('Initializer');

Note I'm just identifying the action helper by the name following the prex. If you want the cong
variable to be available to all controllers, invoke the helper in your
bootstrap.php le. Alternatively,
if you just wanted these variables or other features to be available within select actions, you can call
the helper as needed within the actions. The latter approach is particularly useful when you want to
call a method, as is explained next.
Calling an Action Helper Method
I regularly use a method called generate_id() for generating random strings. Rather than dene this
method within every controller, it makes much more sense to dene it within an action helper and call
it as needed. This approach is not only useful in terms of removing code redundancy within a website,
but also in terms of enhancing portability when you require similar features within other websites.
You'll create such a method within the action helper as you would any other class, just be sure to
declare it as public so the method can be accessed outside of the class:
public function generate_id()
{
return "superrandomstring";
}

The goals of this chapter are accomplished in ten steps:
• Step #1. Introducing Object-relational Mapping: In this opening step I'll introduce you to
a concept known as object-relational mapping, which is the fundamental premise behind the
Zend_Db component which makes database access so easy using the Zend Framework.
• Step #2. Introducing Zend_Db: The Zend_Db component is the conduit for talking to a
database using the Zend Framework. In this step I'll introduce you to this component, which
is so powerful that it almost manages to make database access fun.
• Step #3. Creating Your First Model: When using Zend_Db, you'll rely upon a series of
classes (known as models) as the conduits for talking to your database, meaning you'll be able
to query and manage data without having to write SQL queries! In this step I'll show you how
to create a model for managing video game data which we'll subsequently use throughout the
remainder of this chapter.
• Step #4. Querying Your Models: With the
Game model created, we can set about pulling data
from the games table using the query syntax exposed through the Zend_Db component. In
this step I'll introduce you to this syntax, showing you how to retrieve data from the games
table in a variety of useful ways.
• Step #5. Creating a Row Model: Row models give you the ability to query and manipulate
tables at the row-level. In this step I'll show you how to create and use this powerful feature.
• Step #6. Inserting, Updating, and Deleting Data: Just as you can retrieve data through the
Download at Boykma.Com
138 CHAPTER 6 • TALKING TO THE DATABASE WITH ZEND_DB
Zend_Db component, so can you use the component to insert, update, and delete data. In this
step I'll show you how.
• Step #7. Creating Model Relationships: Zend_Db can account for table relationships, al-
lowing you to deftly interact with the database in amazingly convenient ways. In my experi-
ence this is one of the component's most compelling features, and in this step I'll show you
how to take advantage of it by dening a second model named Platform (for managing gam-
ing platforms, such as Xbox 360 and Nintendo Wii), and tying it to the Game model so we
can associate each game with its platform.

Step #2. Introducing Zend_Db
The Zend_Db component provides Zend Framework users with a exible, powerful, and above all,
easy, solution for integrating a database into a website. It's easy because Zend_Db almost completely
eliminates the need to write SQL statements (although you're free to do so if you'd like), instead pro-
viding you with an object-oriented interface for retrieving, inserting, updating, and deleting data from
the database.
TIP. In addition to MySQL, Zend_Db supports a number of other databases, including DB2, Micro-
soft SQL Server, Oracle, PostgreSQL, SQLite, and others.
In this Step I'm going to take the liberty of foregoing much of the introductory material found in the
Zend Framework manual, having the belief the Zend_Db component is so intuitive that you'll be able
to grasp the basic syntax almost immediately. Either way, if you're looking for a complete treatment
on the component, I recommend taking some time to peruse the excellent documentation at
http://
framework.zend.com/manual/en/.
Connecting to the Database
Before doing anything with the database, you'll need to connect to it. As discussed in Chapter 5, the
most effective way to manage conguration data is via the cong.ini le, so let's begin by dening
the database connection parameters there:
database.params.host = localhost
database.params.username = gamenomad_user
database.params.password = supersecret
database.params.dbname = gamenomad_prod

Of course, you'll likely want to dene these parameters twice, once in the [production] section of
the cong.ini le, and a second time within the [development] section. If you can't recall the rea-
son for doing so, consult Step 7 of Chapter 5.
Within the
bootstrap.php le you can create the database connection using these parameters:
01 $cong = new Zend_Cong_Ini('/home/webadmin/html/application/cong.ini',
02 APPLICATION_ENVIRONMENT);

with the assumption the developer would be most comfortable interacting with the database by way
of the very same language used to build the application, in our case, PHP. The developer employs an
object-oriented approach, building classes which represent the various tables and even rows within
the database. The Zend_Db component automatically enhances these special classes, giving you the
ability to interact with the database using a variety of well-dened methods. These well-dened meth-
ods are immediately available because when creating the class you extend the
Zend_Db_Table_Ab-
stract class (or the Zend_Db_Table_Row_Abstract class when modeling rows).
As usual, the best way to learn how all of this works is by using it. So let's begin by creating a class
which we'll use to query the games table. Save this class as
Game.php, and store it in a directory
named models which resides within your application directory. We'll start with a very simple class
(Listing 6-1), and expand the class as your understanding grows.
Listing 6-1. The Game model
01 class Game extends Zend_Db_Table_Abstract
02 {
03 protected $_name = 'games';
04 protected $_primary = 'id';
05 }

Although just ve lines of code, there are some pretty important things going on in this listing:
• Line 01 denes the name of the model (
Game), and species that the model should extend
the Zend_Db_Table_Abstact class. The latter step is important because in doing so, the Game
model will inherit all of the traits the Zend_Db grants to models. As for naming your model,
Download at Boykma.Com
141CHAPTER 6 • TALKING TO THE DATABASE WITH ZEND_DB
I prefer to use the singular form of the word used for the table name (in this case, the model
name is Game, and the table name is games).
• Because of my personal preference for using singular form when naming models, in line


Returning:
Call of Duty 4: Modern Warfare (ASIN: B0016B28Y8)

Alternatively, you can forego the need to refer to an index offset by using the current() method:
Download at Boykma.Com
142 CHAPTER 6 • TALKING TO THE DATABASE WITH ZEND_DB
$game = new Game();
$cod = $game->nd(1)->current();
echo "{$cod->title} (ASIN: {$cod->asin})";

But why do we even have to deal with index offsets in the rst place? After all, using the primary key
implies there should only be one result anyway, right? This is because the nd() method also sup-
ports the ability to simultaneously query for multiple primary keys, like so:
$cod = $game->nd(array(1,4));

Presuming both of the primary keys exist in the database, the row associated with the primary key 1
will be found in offset [0], and the row associated with the primary key 4 will be found in offset [1].
Querying by a Non-key Column
You'll inevitably want to query for rows using criteria other than the primary key. For instance, vari-
ous features of the GameNomad site search by ASIN. If you only need to search by ASIN at a single
location within your website, you can hardcode the query, like so:
$game = new Game();
$query = $game->select();
$query->where("asin = ?", "B0016B28Y8");
$cod = $game->fetchRow($query);
echo "{$cod->title} (ASIN: {$cod->asin})";

Note that unlike when searching by primary key, there's no need to specify an index offset when refer-
encing the result. This is because the fetchRow() method will always return only one row.

GameNomad.com uses the ASIN each game's ASIN to determine what game the user would like to
view. For instance, to retrieve the Gears of War 2 (Xbox 360) page, you would navigate to the follow-
ing page:
http://www.gamenomad.com/games/show/B000ZK9QD2

As you've just learned, in order to retrieve the row associated with data which isn't the primary key
yet is nonetheless unique to the desired row, you'll need to create a custom method. To retrieve games
according to ASIN, add the aforementioned ndByAsin() method to the Game class. Once added, you
can then pass an ASIN to the method, thereby retrieving the desired game:
$asin = $this->_request->getParam('asin');
$game = new Game();
$this->view->game = $game->ndByAsin($asin);

Your searches don't have to be restricted to retrieving a single row. For instance, the following
Game
model method retrieves all games in which the title includes a particular keyword:
function getGamesMatching($keywords)
{
$query = $this->select();
$query->where('title LIKE ?', "%$keywords%");
$query->order('title');
$results = $this->fetchAll($query);
return $results;
}

You can then use this method within a controller action like so:
// Retrieve the keywords
$this->view->keywords = $this->_request->getParam('keywords');
Download at Boykma.Com
144 CHAPTER 6 • TALKING TO THE DATABASE WITH ZEND_DB

$cod = $game->fetchRow($query);
echo "{$cod->title} (${$cod->price})";
Ordering the Results by a Specic Column
To order the results according to a specic column, use the ORDER clause:
$game = new Game();
$query = $game->select();
$query->order('title ASC');
$rows = $game->fetchAll($query);

To order by multiple columns, pass them to the ORDER clause in the order you'd like them to take
Download at Boykma.Com
145CHAPTER 6 • TALKING TO THE DATABASE WITH ZEND_DB
precedence:
$query->order('release_date, price ASC');

This would have the effect of ordering the games starting with the earliest release dates. Should two
games share the same release date, their precedence will be determined by the price.
Limiting the Results
To limit the number of returned results, you can use the LIMIT clause:
$game = new Game();
$query = $game->select();
$query->where('title LIKE ?', $keyword);
$query->limit(15);
$rows = $game->fetchAll($query);

You can also specify an offset by passing a second parameter to the clause:
$query->limit(15, 5);
Executing Custom Queries
Although Zend_Db's built-in query construction capabilities should sufce for most situations, you
might occasionally want to manually write and execute a query. To do so, you can just create the

{
function latestSalesRank()
{
$rank = new Rank();
$query = $rank->select('rank');
$query->where('game_id = ?', $this->id);
$query->order('created_at DESC');
$query->limit(1);
$row = $rank->fetchRow($query);
return $row->rank;
}
}

To demonstrate this feature, suppose you wanted to output the sales ranks of all video games released
to the market before January 1, 2009. First we'll use the Game model to retrieve the games. Second
we'll iterate through the array of games (which are objects of type GameRow), calling the latest-
SalesRank() method to output the latest sales rank:
$game = new Game();
$query = $game->select()->where("release_date < ?", "2009-01-01");
$results = $game->fetchAll($query);
foreach($results AS $result)
{
echo "{$result->title} (Latest Sales Rank: {$result->latestSalesRank()})<br />";
}

Executing this snippet produces output similar to the following:
Call of Duty 4: Modern Warfare (Latest Sales Rank: 14)
Call of Duty 2 (Latest Sales Rank: 2,208)
NBA 2K8 (Latest Sales Rank: 475)
NHL 08 (Latest Sales Rank: 790)


$game->update($data, $where);

Alternatively, you can simply change the attribute of a row loaded into an object of Zend_Db_Table_
Abstract, and subsequently use the save() method to save the change back to the database:
$game = new Game();

// Find Tiger Woods PGA Tour 09
$golf = $game->ndByAsin('B00164TDUC');

// Change the price to $39.99
$golf->price = 39.99;

// Save the change back to the database
$golf->save();
Deleting a Row
To delete a row, you can use the delete() method:
$game = new Game();

$where = $game->getAdapter()->quoteInto('asin = ?', 'B001B0BB3S');
Download at Boykma.Com
148 CHAPTER 6 • TALKING TO THE DATABASE WITH ZEND_DB

$game->delete($where);
Step #7. Modeling Table Relationships
Because even most rudimentary database-driven websites rely upon multiple related tables for data
management, it's fair to say you'll spend a good deal of time as a developer writing code for effec-
tively managing these relations. Recognizing this, the Zend developers integrated several powerful
features capable of dealing with related data. Most notably, these features allow you to transparently
treat a related row as another object attribute. For instance, you'll recall from Chapter 4 the games

$xbox360 = $platform->ndById(1);

// Retrieve all games associated with platform ID 1
$games = $xbox360->ndGame();
Download at Boykma.Com
149CHAPTER 6 • TALKING TO THE DATABASE WITH ZEND_DB

// Display the count
echo count($games);

The method is named ndGame() because we're nding the platform's associated rows in the Game
model. If the model happened to be named Games, we would use the method ndGames().
Finally, there's still another magic method at your disposal, in this case,
ndGameByPlatform():
$platform = new Platform();

// Retrieve the platform row associated with the Xbox 360
$xbox360 = $platform->ndById(1);

// Retrieve all games associated with platform ID 1
$games = $xbox360->ndGameByPlatform();

// Display the count
echo count($games);

To use these features, you need to congure your models so they are able to recognize the relation-
ships. I'll show you how to do this next.
VIDEO. Working with Table Relations
Arguably one of the most confusing features of Zend_Db, understanding how to create table rela-
tions is nonetheless crucial to mastering this powerful component. This video dispels that confusion,

03 'columns' => array('platform_id'),
04 'refTableClass' => 'Platform'
05 )
06 );

In this snippet we're identifying the foreign keys stored in the Game model, identifying both the col-
umn storing the foreign key (platform_id), and the model that foreign key represents (Platform).
Of course, it's entirely likely for a model to store multiple foreign keys. For instance, GameNomad's
User model refers to three other models (State, Country, and Platform):
protected $_referenceMap = array (
'States' => array (
'columns' => array('state_id'),
'refTableClass' => 'State'
),
'Countries' => array (
'columns' => array('country_id'),
'refTableClass' => 'Country'
),
'Platforms' => array (
'columns' => array('favorite_platform'),
'refTableClass' => 'Platform'
)
);

With this snippet in mind, returning to the magic methods, what methods would be at your disposal
when attempting to retrieve a list of all users belonging to the state of Ohio? Ohio belongs to the User
model's parent State model, so we're looking for dependent rows, meaning we have these methods
available:
$ohio->ndDependentRowset('User');
$ohio->ndUser();

parts of the GameNomad website. If you'd like to experiment with each join (recommended), you can
do so using phpMyAdmin's SQL interface, or using MySQL's command-line client.
Finding a User's Friends
The typical social networking website offers a means for examining a user's list of friends. GameNo-
mad is no different, using a table called friends to manage these relationships:
CREATE TABLE friends (
id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
user_id INTEGER UNSIGNED NOT NULL,
friend_id INTEGER UNSIGNED NOT NULL,
friends_since TIMESTAMP NOT NULL
);

Let's begin by examining the most basic type of join, known as the inner join. An inner join will re-
turn the desired rows whenever there is at least one match in both tables, the match being determined
by a shared value such as a user's primary key. So for example, you might use a join to retrieve a list
of a particular user's friends:
Download at Boykma.Com
152 CHAPTER 6 • TALKING TO THE DATABASE WITH ZEND_DB
mysql>SELECT u.handle FROM users u
->INNER JOIN friends f ON f.friend_id = u.id WHERE f.user_id = 44;

This join requests the handle of each user found in the friends table who is mapped to a friend of the
user identied by 44.
Determine the Number of Copies of a Game Found in Your Network
Suppose you would like to borrow a particular game, but knew your best friend John had already
loaned his copy to Carli. Chances are however somebody else in your network owns the game, but
how can you know? Using a simple join, it's possible to determine the number of copies owned by
friends, a feature integrated into GameNomad and shown in Figure 6-1.
Figure 6-1. Determining the number of copies of a game within a user's network
You might notice in Figure 6-1 this feature is actually used twice; once to determine the number of

| Active Life Outdoor Challenge | Nintendo Wii |
| Advance Wars: Days of Ruin | Nintendo DS |
| American Girl Kit Mystery Challenge | Nintendo DS |
| Amplitude | Playstation 2 |
| Animal Crossing: Wild World | Nintendo DS |
| Animal Genius | Nintendo DS |
| Ant Bully | NULL |
| Atelier Iris Eternal Mana | Playstation 2 |
+ + +

Note how the game "Ant Bully" has not been assigned a platform. Using an inner join, this row would
not have appeared in the listing.
Counting Users by State
As your site grows in terms of registered users, chances are you'll want to create a few tools for ana-
lyzing statistical matters such as the geographical distribution of users according to state. To create
a list tallying the number of registered users according to state, you can use a right join, which will
list every record found in the rightside table, even if no users are found in that state. The following
example demonstrates the join syntax used to perform this calculation:
mysql>SELECT COUNT(users.id), states.name
->FROM users RIGHT JOIN states ON users.state_id = states.id
->GROUP BY states.name;

Executing this query produces output similar to the following:
| 145 | New York |
| 18 | North Carolina |
| 0 | North Dakota |
| 43 | Ohio |
| 22 | Oklahoma |
| 15 | Oregon |
| 77 | Pennsylvania |

an array containing the columns which are to be selected, otherwise all column will by default be
selected.
• Line 06 identies the joined table, and join condition. If you'd like to select specic columns
from the joined table, pass those columns along in an array as was done in line 05; otherwise
pass in an empty array to select no columns.
• Line 07 denes a
WHERE clause, which will restrict the result set to a specic set of rows. In
this case, we only want rows in which the
friends table's user_id column is set to the value
identied by $this->id.
You'll come to nd the Zend_Db's join capabilities are particularly useful as your website grows in
complexity. When coupled with Zend_Db's relationship features (see Step #7), it's possible to create
impressively powerful data mining features with very little code.
Download at Boykma.Com
155CHAPTER 6 • TALKING TO THE DATABASE WITH ZEND_DB
Step #9. Paginating Results with Zend_Paginator
For reasons of performance and organization, chances are you're going to want to spread returned
database results across several pages if a lengthy number are returned. However, doing so manually
can be a tedious chore, requiring you to track things such as the number of results per page, the page
number, and the current offset of the overarching query result. Recognizing this importance of such
a feature, the Zend Framework developers created the Zend_Paginator component, giving develop-
ers an easy way to paginate result sets without having to deal with all of the gory details otherwise
involved in a custom implementation.
The Zend_Paginator component is quite adept, capable of paginating not only arrays, but also da-
tabase results. It will also autonomously manage the number of results returned per page and the
number of pages comprising the result set. In fact, Zend_Paginator will even create a formatted page
navigator which you can insert at an appropriate location within the results page (see Figure 6-2).
Figure 6-2. Zend_Paginator will automatically segment results across multiple pages
In this section I'll show you how to paginate a large set of video games across multiple pages.
Update Your cong.ini File