epwzf20

)

);

}

?>

Deleting a View

Should you no longer require a view, consider removing it from the database for organizational reasons. To do so, use the DROP VIEW statement:

mysql>DROP VIEW latest_sales_ranks;

Reviewing View Creation Syntax

You'll often want to make modifications to a view over its lifetime. For instance, when I first created the view_latest_sales_ranks view, I neglected to limit the results to the top 100 games, resulting in a list of the top 369 games being generated. But recalling the view's lengthy SQL statement isn't easy, so how can you easily retrieve the current syntax for modification? The SHOW CREATE VIEW statement solves this dilemma nicely:

mysql>SHOW CREATE VIEW latest_sales_ranks\G View: latest_sales_ranks

Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `latest_sales_ranks` AS

select max(`ranks`.`id`) AS `id`,`games`.`title` AS `title`, `games`.`asin` AS `asin`,`games`.`platform_id` AS `platform_id`, `ranks`.`rank` AS `rank` from (`games` join `ranks` on((`games`.`id` = `ranks`.`game_id`)))

group by `ranks`.`game_id` order by `ranks`.`rank` character_set_client: latin1 collation_connection: latin1_swedish_ci

We're particularly interested in the three lines beginning with `latest_sales_ranks`, as this signifies the start of the query. It looks different from the original SQL statement because MySQL

The URL representing the fourth page of results would typically look like this:

http://gamenomad.com/games/platform/id/xbox360/page/4

Although I'll formally introduce this matter of retrieving URL parameters in the next chapter, there's nothing wrong with letting the cat out of the bag now, so to speak. The Zend Framework looks at URLs using the following pattern:

http://www.example.com/:controller/:action/:key/:value/:key/:value/.../:key/value

This means following the controller and action you can attach parameter keys and corresponding values, subsequently retrieving these values according to their key names within the action. So for instance, in the previous URL the keys are id and page, and their corresponding values are xbox360 and 4, respectively. You can retrieve these values within your controller action using the following commands:

$platform = $this->_request->getParam('id'); $page = $this->_request->getParam('page');

What's more, using a feature known as custom routes, you can tell the framework to recognize URL parameters merely according to their location, thereby negating the need to even preface each value with a key name. For instance, if you head over to GameNomad you'll see the platform-specific game listings actually use URLs like this:

http://gamenomad.com/games/platform/xbox360/4

Although not required knowledge to make the most of the Zend Framework, creating custom routes is extremely easy to do, and once you figure them out you'll wonder how you ever got along without them. Head over to http://framework.zend.com/manual/en/zend.controller.router.html to learn more about them.

With the platform and page number identified, all that's left to do is call the Game model's getGamesByPlatform() method to paginate the results:

$game = new Default_Game_Model();

$this->view->platformGames = $game->getGamesByPlatform($platform, $page);

Within the view, you can iterate over the $this->platformGames just as you would anywhere else:

<?php if (count($this->platformGames) > 0) { ?> <?php foreach ($this->platformGames AS $game) { ?>

echo "{$game->name} <br />"; <?php } ?>

<?php } ?>

Adding the Pagination Links

The user will need an easy and intuitive way to navigate from one page of results to the next. This list of linked page numbers is typically placed at the bottom of each page of output. The Zend_Paginator component can take care of the list generation for you, all you need to do is pass in the returned result set (in this case, $this->platformGames), the type of pagination control you'd like to use (in this case, Sliding), and the view helper used to stylize the page numbers:

<?= $this->paginationControl($this->platformGames, 'Sliding', 'my_pagination.phtml'); ?>

The Sliding control will keep the current page number in the middle of page range. Several other control types exist, including All, Elastic, and Jumping. Try experimenting with each to determine which one you prefer. The view helper works like any other, although several special properties are made available to it, including the total number of pages contained within the results ($this- >pageCount), the next page number ($this->next), the previous page ($this->previous), and several others. Personally I prefer to use one which is almost identical to that found in the Zend Framework documentation, which I'll reproduce here:

<?php if ($this->pageCount): ?> <div class="paginationControl">

<!-- Previous page link -->

<?php if (isset($this->previous)): ?>

<a href="<?= $this->url(array('page' => $this->previous)); ?>">< Prev</a> | <?php else: ?>

<span class="disabled">< Previous</span> | <?php endif; ?>

<!-- Numbered page links -->

<?php foreach ($this->pagesInRange as $page): ?> <?php if ($page != $this->current): ?>

<a href="<?= $this->url(array('page' => $page)); ?>"><?= $page; ?></a> | <?php else: ?>

<?= $page; ?> | <?php endif; ?>

<?php endforeach; ?>

<!-- Next page link -->

<?php if (isset($this->next)): ?>

<a href="<?= $this->url(array('page' => $this->next)); ?>">Next ></a> <?php else: ?>

<span class="disabled">Next ></span> <?php endif; ?>

</div>

<?php endif; ?>

Of course, to take full advantage of the stylization opportunities presented by a pagination control such as this, you'll need to define CSS elements for the paginationControl and disabled classes.

Test Your Knowledge

Test your understanding of the concepts introduced in this chapter by answering the following questions. You can find the answers in the back of the book.

•Define object-relational mapping (ORM) and talk about why its an advantageous approach to programmatically interacting with a database.

•Given a model named Application_Model_DbTable_Game, what will Zend_Db assume to be the name of the associated database table? How can you override this default assumption?

•What are the names and purposes of the native Zend_Db methods used to navigate model associations?

so if you haven't already upgraded I recommend doing so even if you wind up not using Doctrine 2.

Introducing Doctrine

The Doctrine website defines the project as an "object-relational mapper for PHP which sits on top of a powerful database abstraction layer". This strikes me as a rather modest description, as Doctrine's programmatic interface is nothing short of incredible, supporting the ability to almost transparently marry your domain models with Doctrine's data mappers, as demonstrated in this example which adds a new record to the database:

$em = $this->_helper->EntityManager();

$account = new \Entities\Account;

$account->setUsername('wjgilmore'); $account->setEmail('wj@wjgilmore.com'); $account->setPassword('jason'); $account->setZip('43201'); $em->persist($account);

$em->flush();

Doctrine can also traverse and manipulate even the most complex schema relations using remarkably little code. Consider this example, which adds the game "Super Mario Brothers" to a user's video game library:

$em = $this->_helper->EntityManager();

$account = $em->getRepository('Entities\Account') ->findOneByUsername('wjgilmore');

$game = $em->getRepository('Entities\Game') ->findOneByName('Super Mario Brothers');

$account->getGames()->add($game);

$em->persist($account); $em->flush();

Later in this chapter I'll provide several examples demonstrating its relationship mapping capabilities.

Incidentally, the findOneByUsername() method used in the above example is another great Doctrine feature, known as a magic finder. Doctrine dynamically makes similar methods available for all of

your table columns. For instance, if a table includes a publication_date column, a finder method named findByPublicationDate() will automatically be made available to you!

Iterating over an account's game library is incredibly easy. Just iterate over the results returned by $account->getGames() method like any other object array:

foreach ($user->getGames() as $game)

{

echo "{$game->title}<br />";

}

Doctrine's capabilities extend far beyond its programmatic interface. You can use it's CLI (command-line interface) to generate and update schemas, and can use YAML, XML or (my favorite) DocBlock annotations to define column names, data types, and even table associations. I'll talk about these powerful features in the section "Building Persistent Classes".

Note

Doctrine 2 is a hefty bit of software, so although this chapter provides you with enough information to get you started, it doesn't even scratch the surface in terms of Doctrine's capabilities. My primary goal is to provide you with enough information to get really excited about the prospects of using Doctrine within your applications. Of course, I also recommend reviewing the GameNomad code, as Doctrine 2 is used throughout.

Introducing the z2d2 Project

Figuring out how to integrate Doctrine 2 into the Zend Framework was a pretty annoying and time-consuming process, one which involved perusing countless blog posts, browsing GitHub code, and combing over the Doctrine and Zend Framework documentation. The end result is however a successful implementation, one which I've subsequently successfully integrated into the example GameNomad website. However, because the GameNomad website is fairly complicated I've opted to stray from the GameNomad theme and instead focus on a project which is much smaller in scope yet nonetheless manages to incorporate several crucial Doctrine 2 features. I've dubbed this project z2d2, and it's available as part of your code download, located in the z2d2 directory.

The project incorporates fundamental Doctrine features, including DocBlock annotations, use of the Doctrine CLI, magic finders, basic CRUD features, and relationship management. I'll use the code found in this project as the basis for instruction throughout the remainder of this chapter, so if you haven't already downloaded the companion code, go ahead and do so now.

Key Configuration Files and Parameters

As I mentioned at the beginning of this chapter, the Doctrine integration process is a fairly lengthy process and one which will certainly change with the eventual Zend Framework 2 release. However so as not to entirely leave you in the dark I'd like to at least provide an overview of the sample project's files and configuration settings which you'll need to understand in order to integrate Doctrine 2 into your own Zend Framework projects:

•The application/configs/application.ini file contains nine configuration parameters which Doctrine uses to connect to the database and determine where the class entities and proxies are located.

•The library/Doctrine directory contains three directories: Common, DBAL, and ORM. These three directories contain the object relational mapper, database abstraction layer, and other code responsible for Doctrine's operation.

•The library/WJG/Resource/Entitymanager.php file contains the resource plugin which defines the entity manager used by Doctrine to interact with the database.

•The application/Bootstrap.php file contains a method named _initDoctrine() which is responsible for making the class entities and repositories available to the Zend Framework application.

•The library/WJG/Controller/Action/Helper/EntityManager.php file is an action helper which is referenced within the controllers instead of the lengthy call which would otherwise have to be made in order to retrieve a reference to entity manager resource plugin.

•The application/scripts/doctrine.php file initializes the Doctrine CLI, and bootstraps the Zend Framework application resources, including the entity manager resource plugin. The CLI is run by executing the doctrine script, also found in application/scripts.

•The application/models/Entities directory contains the class entities. I'll talk more about the purpose of entities in a later section.

•The application/models/Repositories directory contains the class repositories. I'll talk more about the role of repositories in a later section.

•The application/models/Proxies directory contains the proxy objects. Doctrine generates proxy classes by default, however the documentation strongly encourages you to disable autogeneration, which you can do in the application/config.ini file.