Chapter 6. Managing the database

For most websites, a database is a vital part of their applications. Zend Framework acknowledges this with a comprehensive set of database-related components that provide varying levels of abstraction.

Two important levels of abstraction are databases and tables. The database abstraction keeps your PHP code independent from the underlying database server you use. This means that it is easier for your application to support multiple database servers. Table abstraction represents the database tables and rows as PHP objects. This allows the rest of your application to interact with PHP without knowing that there is an underlying database.

We’ll start by looking at the Zend_Db_Adapter class, which provides a product-independent interface to the database server.

6.1. Database Abstraction with Zend_Db_Adapter

The subject of database abstraction appears to be a religious one, with many developers claiming that such layers are a performance drain and that they serve no useful purpose. This belief comes from the fact that to get the best from a database engine, you have to understand how the engine works and use its specific interpretation of SQL to get the best from it. Other developers wouldn’t dream of not using a database abstraction layer; they argue that it allows them to migrate easily from one database to another and to distribute their applications more widely. Also, a database abstraction layer allows the developer to learn one API for all databases that they use, which can speed up development time.

As always in such debates, it depends on what the task is. If you are developing an application that needs to extract the maximum performance out of the database, such as a search engine like Google, coding for one database is clearly the way forward. If you are writing an application that you intend to sell to customers, supporting multiple databases makes your product more desirable. There is no right answer. In Rob’s current company, we have sold to customers who will only use SQL Server and will not countenance the use of MySQL or PostgreSQL, so we have found database abstraction layers very useful for making sales of the same software to multiple customers.

Zend Framework’s interface to the database is via an abstraction layer. The standardized interface to the database that this creates ensures that the higher-level components will work with any database. It also means that the support for a given database is encapsulated at a single point, so supporting new database engines is easier.

Let’s look at using Zend Framework’s Zend_Db_Adapter to connect to a database and then interact with the data within it.

6.1.1. Creating a Zend_Db_Adapter

The heart of the database abstraction components within Zend Framework is Zend_Db_Adapter_Abstract. Each supported database engine has a specific adapter class that inherits from this class. For example, DB2’s adapter is named Zend_Db_Adapter_Db2. The PDO extension is also used for some adapters, such as Zend_Db_Adapter_Pdo_Pgsql for PostgreSQL.

The Factory design pattern creates a database adapter using the Zend_Db::factory() method, as shown in listing 6.1.

Listing 6.1. Creating a Zend_Db_Adapter instance with Zend_Db::factory()

As every adapter extends Zend_Db_Adapter_Abstract, only methods within the abstract class can be used if you want database independence. The list of supported databases is quite extensive and includes DB2, MySQL, Oracle, SQL Server, and PostgreSQL. We expect other databases to be supported over time too, especially PDO-based ones.

The database adapters use the lazy loading technique to avoid connecting to the database on creation of the object instance. This means that even though the adapter object is created using Zend_Db::factory(), the connection to the database doesn’t happen until you do something with that object that requires a connection. Calling the getConnection() method will also create a connection if there isn’t one already.

We’ve now connected to the database, so next we’ll look at how to retrieve data from it using standard SQL queries or the Zend_Db_Select object, which can make it easer.

6.1.2. Querying the Database

Running an SQL query directly against a database is very simple:

$date = $db->quote('1980-01-01')
$sql = 'SELECT * FROM users WHERE date_of_birth > ' . $date;
$result = $db->query($sql);

The $result variable contains a standard PHP PDOStatement object, so you can use the standard calls, such as fetch() or fetchAll(), to retrieve the data. In this case, we used a standard SQL string to specify the query.

The query() method also supports parameter binding to save having to quote() all strings. This system allows us to put placeholders into the SQL statement where we want our variables to be, then the adapter (or underlying core PHP) will ensure that our query is valid SQL and doesn’t contain a string that isn’t quoted properly. The preceding query could therefore be written like this:

$sql = 'SELECT * FROM users WHERE date_of_birth > ?';
$result = $db->query($sql, array('1980-01-01'));

As a rule of thumb, using parameter-based queries is a good habit to get into, because it removes the opportunity for accidentally forgetting to quote() data from the user and thereby possibly creating an SQL injection vulnerability in your application. It also has the side benefit that for some databases it is quicker to use bound data parameters.

Not everyone is comfortable creating complex SQL queries, though. Zend Frame-work’s Zend_Db_Select classes provide a PHP-based object-oriented interface to generate SQL SELECT statements.

Zend_Db_Select

Zend_Db_Select allows you to use PHP to build database query statements in the comfortable language of PHP, rather than SQL.

Zend_Db_Select provides a number of advantages. These are the most important ones:

• Metadata is automatically quoted (table and field names)
• Object-oriented interface provides for easier maintenance
• It helps to promote database-independent queries
• Quoting of values helps reduce SQL injection vulnerabilities

A simple query using Zend_Db_Select is shown in listing 6.2.

Listing 6.2. Creating a Zend_Db_Adapter instance using Zend_Db::factory()

One very useful feature of Zend_Db_Select is that you can build the query in any order. This differs from standard SQL in that you must have each section of your SQL string in the correct place. For a complex query, this simplifies the maintenance because Zend_Db_Select ensures that the SQL syntax generated is correct.

In addition to Zend_Db_Select, Zend_Db_Adapter provides functionality for interfacing directly with the database to allow you to insert, delete, and update data. Let’s look at that now.

6.1.3. Inserting, Updating, and Deleting

To simplify the inserting, updating, and deleting of database rows, Zend_Db_Adapter provides the insert(), update(), and delete() methods.

Inserting and updating use the same basic premise, in that you provide an associative array of data and it does the rest. Listing 6.3 shows how to add a user to a table called users.

Listing 6.3. Inserting a row using Zend_Db_Adapter

Note that Zend_Db_Adapter’s insert() method will automatically ensure that your data is quoted correctly because it creates an insert statement that uses parameter binding as discussed in section 6.1.2

Updating a record using the update() method works pretty much the same way, except that you need to pass in an SQL condition to limit the update to the rows that you are interested in. Typically, you would know the ID of the row you want to update, but update() is flexible enough to allow for updating multiple rows, as shown in listing 6.4.

Listing 6.4. Updating multiple rows using Zend_Db_Adapter

Again, update() automatically quotes the data in the $data array, but it does not automatically quote the data in the $where clause, so we use quoteInto() to ensure that the data is safe for use with the database.

Deleting from the database works in exactly the same manner, but we need the table and the $where clause, so the code for deleting a particular user would look like this:

$table = 'users';
$where = 'id = 1';

$rows_affected = $db->delete($table, $where);

Obviously, we need to quote strings in the $where clause again. In all cases insert(), update(), and delete() return the number of rows affected by the operation.

We’ll now look at how to handle database-specific differences.

6.1.4. Handling Database-Specific Differences

All databases are not equal, and this is especially true of their interpretation of SQL and the additional functions they provide to allow access to the more advanced features provided by the database. To call the specific SQL functions of your database server, the Zend_Db_Select class has a helper class called Zend_Db_Expr. Zend_Db_Expr is used for calling SQL functions or creating other expressions for use in SQL.

Let’s consider an example where we want to concatenate the first and last names of our users. The code is in listing 6.5.

Listing 6.5. Using functions in SQL statements

The from() method realizes that a parenthesis has been used in the columns parameter and so converts it to a Zend_Db_Expr automatically. We can, however, use Zend_Db_Expr ourselves, by setting the columns statement explicitly:

$columns = array(id, "n"=>
new Zend_Db_Expr("CONCAT(first_name, ' ', last_name"));

Now that we have considered how to abstract away the differences between database engines by using adapters created by the factory method of Zend_Db, we can turn our attention to how we use a database within an application. Novice web programmers tend to put the database calls right where they need them, which leads to a maintenance nightmare, with SQL statements spread all through the application. We will consider ways to consolidate our SQL, and we’ll look at how Zend Framework’s Zend_Db_Table component helps to improve the architecture of our applications.

6.2. Table Abstraction with Zend_Db_Table

When dealing with a database, it is useful to be able to abstract your thinking and consider the system at the domain level, rather than focusing on the nitty-gritty of the actual SQL statements. At the domain level, you can think about the problem in the language of the domain.

The easiest way to do this is to create classes that know how to load and save themselves to the database. A class that represents one row in a database table implements the Row Data Gateway pattern. This pattern provides for accessing a single row of the database and is closely related to the Active Record pattern.

One area where the Row Data Gateway pattern doesn’t work so well is when dealing with lists, such as when you are retrieving a list of products in an e-commerce application. This is because it works at the row level, and lists are generally dealt with at the table level. Zend Framework provides the Zend_Db_Table component to support database manipulation at the table level, and we’ll look at what it provides and how table-level support differs from what we have already seen in Zend_Db_Adapter.

6.2.1. What is the Table Data Gateway Pattern?

Zend_Db_Table has three main components: Zend_Db_Table_Abstract, Zend_Db_Table_Rowset, and Zend_Db_Table_Row. As indicated by its name, Zend_Db_Table_Abstract is an abstract class that has to be extended for each table that it acts as a gateway to.

When selecting multiple records from the table, an instance of Zend_Db_Table_Rowset is returned, which can then be iterated over to access each individual row.

Each row is an instance of Zend_Db_Table_Row, which is itself an implementation of the Row Data Gateway pattern. This is shown in figure 6.1.

Figure 6.1. The Zend_Db_Table components provide a clean method of managing a database table and its associated rows.

Zend_Db_Table_Abstract is always extended, but Zend_Db_Table_Rowset is much less likely to be because it provides pretty much all you need for handling a set of database rows. In more complicated systems, extending Zend_Db_Table_Row into more of an Active Record by adding domain logic to it can help avoid duplicating code in multiple places, as we’ll see when we look at how to use Zend_Db_Table.

6.2.2. Using Zend_Db_Table

In order to actually use the Zend_Db_Table components, you must create a class to represent your table, as shown in listing 6.6. This is usually named similarly to the database table that it accesses, but that is not a requirement.

Listing 6.6. Declaring a table class for use with Zend_Db_Table

class Users extends Zend_Db_Table_Abstract
{
   protected $_name = 'users';
}

As you can see in listing 6.6, we have explicitly set the name of the underlying table to users. If the $_name property is not set, the name of the class (preserving the case) will be used instead. However, because it is common practice to name the classes starting with an uppercase letter and to name the database tables entirely in lowercase, the database table name is usually explicitly declared.

Now that we have a Users class, we can fetch data from the table in exactly the same way as if we were collecting data from an instance of Zend_Db_Adapter:

$users = new Users();
$rows = $users->fetchAll();

While it performs the same function, the Zend_Db_Table_Abstract’s fetchAll() method is used completely differently from Zend_Db_Adapater_Abstract’s version of fetchAll(), which simply takes an SQL string parameter and returns an array of arrays. As we are working at a higher abstraction level with the table, fetchAll() also works at the higher level, as you can see from its signature:

public function fetchAll($where = null, $order = null,
      $count = null, $offset = null)

The fetchAll() method will create the SQL statement for us, using Zend_Db_Table_Select internally; we need to specify the parts we are interested in. For example, to select all female users born from 1980 on, we would use the following code:

$users = new Users();
$where = array('sex = ?' => 'F',
   'date_of_birth >= ?'=>'1980-01-01'),
$rows = $users->fetchAll($where);

We can also use a Zend_Db_Table_Select object like this:

$users = new Users();
$select = $users->select();
$select ->where('sex = ?', 'F'),
$select ->where('date_of_birth >= ?', '1980-01-01'),
$rows = $users->fetchAll($select);

In this example, we build the query up in exactly the same way as we would with a Zend_Db_Select object, which allows us to create the query out of order if we need to. For instance, we could set the limit before we set the $where clauses.

Now that we know how to select data from the database using Zend_Db_Table, let’s look at adding and editing the data using the insert() and update() methods.

6.2.3. Inserting and Updating with Zend_Db_Table

Inserting and updating with Zend_Db_Table is very similar to the usage with Zend_Db_Adapter, except that this time we already know which database table to operate on. table 6.1 shows exactly how similar inserting is with the two components.

Table 6.1. Inserting with Zend_Db_Table and Zend_Db_Adapter compared

// insert a user
$table = 'users';

$data = array(
 'date_created' => date('Y-m-d'),
 'date_updated' => date('Y-m-d'),
 'first_name' => 'Ben',
 'surname' => 'Ramsey'
);

$db->insert($table, $data);
$id = $db->lastInsertId();

// insert a user
$users = new Users();

$data = array(
 'date_created' => date('Y-m-d'),
 'date_updated' => date('Y-m-d'),
 'first_name' => 'Ben',
 'surname' => 'Ramsey'
);

$id = $users->insert($data);

As you can see from table 6.1, exactly the same process is used in both cases, except that with Zend_Db_Table we already know the table’s name, and we get the ID of the newly inserted record back directly.

Updating a database row is similar, as shown in table 6.2.

Table 6.2. Updating with Zend_Db_Table and Zend_Db_Adapter compared

// update a user$table = 'users';

$data = array(
 'date_updated' => date('Y-m-d'),
 'first_name' => 'Ben',
 'surname' => 'Ramsey'
);

$where = 'id=2';
$db->update($table, $data,
$where);

// update a user$users = new Users();

$data = array(
 'date_updated' => date('Y-m-d'),
 'first_name' => 'Ben',
 'surname' => 'Ramsey'
);

$where = 'id=2';
$users->update($data, $where);

Note that you need to quote any values and identifiers in the SQL expression used for the $where clause. This is done by using the database adapter’s quote(), quoteInto(), and quoteIdentifier() methods.

The similarities between inserting and updating lead to the conclusion that it might be worth having a single save method in Zend_Db_Table. This would be incorrect at the table level because you never know which record (or records) need updating. At the Zend_Db_Table_Row level, it makes perfect sense. Looking at Zend_Db_Table_Row, we discover that this functionality is already written for us in a method called save(). Its use is shown in table 6.3.

Table 6.3. Saving data with Zend_Db_Table_Row’s save() method

$users = new Users();
$row = $users->fetchNew();

$row->first_name = 'Chris';
$row->surname = 'Shiflett';

$row->save();

$users = new Users();
$row = $users->fetchRow('id=2'),

$row->first_name = 'Chris';
$row->surname = 'Shiflett';

$row->save();

In this case, all of the differences between inserting a new record and updating a current one are hidden. This is because the Zend_Db_Table_Row object works at a higher level of abstraction.

6.2.4. Deleting Records with Zend_Db_Table

As you would expect, deleting works exactly the same way as inserting and updating, only we use the delete() method. The delete() method is available in both Zend_Db and Zend_Db_Table, and as with insert() and update(), the usage is very similar between the two classes, as shown in table 6.4.

Table 6.4. Deleting rows with Zend_Db_Table and Zend_Db_Adapter compared

// delete a user
$table = 'users';

$where = 'id = 2';
$db->delete($table,
  $where);

// delete a user
$users = new Users();

$where = 'id = 2';
$users->delete($where);

The $where clause in delete() for both Zend_Db_Table and Zend_Db_Adapter does not quote the values for us, so if we were to delete based on anything other than an integer, we would have to use quote() or quoteInto(). For example, to delete all users who live in London, we would have to do the following:

$users = new Users();
$db = $users->getAdapter();
$where = $db->quoteInto('town = ?', 'London'),
$users->delete($where);

We have now looked at all the major database operations that can be done using the Zend_Db_Adapter and Zend_Db_Table components. Database operations in a Model-View-Controller application usually are part of the model. This is because the model is where the business logic of the application is, and the data stored in a database is very much related to the business of the application.

We will now look at using Zend_Db_Table-based objects within a model and, possibly more importantly, at how to test such objects.

6.3. Using Zend_Db_Table as a Model

Zend_Db_Table is usually where we integrate the Zend_Db component into a Zend Framework MVC application. One way to implement a model is to build a set of classes that extend Zend_Db_Table_Abstract and Zend_Db_Table_Row_Abstract.

Within our Places web application we use a model to represent the registered users of the website. The database table required is quite simple and is shown in figure 6.2.

Figure 6.2. The Users table for Places contains login information along with demographic information for advertisers.

As we want to make money from Places, we expect that advertisers will want to know the demographics of the membership, so we would like our members to tell us their age, sex, and location. Of course, this would be optional, but the user-name and password are mandatory. A more advanced model might even separate out the demographic information to a separate table.

We will need to access our database tables at two different levels: table and row. The table level is used when we need to display lists, and the row level is used when dealing with individual records. In order to deal with this in the code, we need two classes, Users and User, which are extensions of Zend_Db_Table_Abstract and Zend_Db_Table_Row_Abstract respectively. The class definitions are shown in listing 6.7. Note that you would usually use two files in the application/models directory for the two classes, Users.php and User.php, because it makes the application easier to maintain.

Listing 6.7. Creating a model using Zend_Db_Table components

Linking the User class to the Users class is done through the $_rowClass property in the Users class . This means that the Zend_Db_Table_Abstract class will create objects of type User whenever it would normally have created an object of type Zend_Db_Table_Row.

The implementation of the User class in listing 6.7 is no more useful than the standard Zend_Db_Table_Row, as no additional functionality is provided. To make our User class useful, we will add a new method called name. This will be used throughout the application to display the user’s name. It will initially display the combination of first name and surname, but if they are not set, it will use the user’s username.

Our initial implementation of the User::name() method is as follows:

Class User extends Zend_Db_Table_Row_Abstract
{
   public function name()
   {
      $name = trim($this->first_name . ' ' . $this->last_name);
      if (empty($name)) {

       $name = $this->username;
     }
     return $name;
   }
}

We can use our new name() method directly, like this:

$rob = $users->find(1)->current();
echo $rob->name();

It would be nicer to be able to treat the name as a read-only property of the record so that it is used in exactly the same way as, say, date_of_birth. This will increase the predictability of the class, because the programmer will not have to think about whether a given property is a field in the database or a function within the class. The easiest and most efficient way to do this is to override __get(), as shown in listing 6.8.

Listing 6.8. Overriding __get() to provide custom properties

We have to call the parent’s __get() method last, because it will throw an exception if $key does not exist in the database as a field. Note also that if one of the methods in the class is named the same as a field in the database, the method will be called rather than the value of the field being returned. This is handy if you want to do any processing on a given field before passing it through to the rest of the application, though such a use-case is rare.

We’re now able to build our models, so we need to ensure that they work. The best way to do this is to test the code using a testing system.

6.3.1. Testing the Model

As we have mentioned before, it is good practice to test your code, and it is even better practice to test it more than once! To test the Users model class, we’ll use PHPUnit, because that’s what Zend Framework itself uses. The tests will be stored in the tests/ models directory of the Places application.

We’ll start our testing by ensuring the testing environment is consistent between tests, which, in testing circles, is known as setup and teardown.

Setup and Teardown

One key thing about running tests automatically is that tests must not interfere with each other. Also, a given test must not depend upon a previous test having run. To achieve this separation, PHPUnit provides the setUp() and tearDown() methods, which are run just before and after each test.

In order to separate each database test, we use the setUp() method to recreate the database tables and populate them to a known state. This obviously means that we do not use the master database for testing! When testing, we use a different database that is configured in the main config.ini for the application. The ensures we can control the data within it. We use the [test] section to specify our test database, as shown in listing 6.9.

Listing 6.9. The [test] section overrides [general] in application/config.ini Listing

In our case, we only need to change the database name from places to places_test; we can rely on the other settings in the general section to provide the other connection information.

We are now in a position to create the unit test class skeleton, as shown in listing 6.10.

Listing 6.10. Skeleton for unit test class: tests/models/UsersTests.php

This section of the unit test file contains only the initialization code that is run once. As you can see at the top , we have to ensure that we have set up our paths correctly so that the files can be found, and we also have to include all the files we are going to need. The definition of ROOT_DIR is checked to ensure that it hasn’t already been defined. This will allow us to expand our tests in the future by placing this unit test file in a suite of files that can be tested all at the same time; when we are testing as part of a suite, we will not want to alter the path. We also include the files from the framework that we need for this test.

As the constructor is only called once, it is the ideal place to load the config.ini file and connect to the database. Again, if this class is part of a test suite, the database connection will already have been made. In that case, we just collect it from the registry. Otherwise, once we have connected, we set the database adapter as the default adapter for Zend_Db_Table and store to the registry. We also assign it as a class member variable, because it is going to be used in the setUp() method to set our database to a known state.

Initializing our database is just a case of using the database adapter to create the table and insert some rows. This is done in a method called _setupDatabase(), which is called by setUp(), as shown in listing 6.11.

Listing 6.11. Initializing the database in setUp()

This is very simple code that just ensures that our database is set up correctly for every test. It drops and recreates the table, then inserts the database rows, taking advantage of the database adapter’s insert() method, which we discussed earlier.

Note

The DROP TABLE command in listing 6.11 is a MySQL-ism, which would need to be changed for other database engines.

We are now in a position to actually write some tests to ensure that our model works as expected!

Testing the Users Class

The Users class overrides the insert() and update() methods to ensure that the date_created and date_updated fields are filled in, without the rest of the code having to worry about it. The unit tests that ensure that this code works are shown in listing 6.12. We have separated the tests into two separate methods: one to test inserting and one to test updating.

Listing 6.12. Initializing the database in setUp()

Within testInsertShouldFillInDateCreatedAndUpdatedFields() there is a reasonable amount of setup code. Before we run the actual tests (, ) that we want to conduct, we need to have inserted a new record into the database. As you can see, Zend_Db_Table’s fetchNew() method returns an empty object of type User that represents a row in the database. We can then set the fields that we need and insert it into the database using the save() method. The row’s save() method uses the associated Zend_Db_Table_Abstract’s insert() or update(), as appropriate, which then uses our overridden methods to ensure that the date_created and date_updated fields are correctly filled in.

The testUpdateShouldUpdateDateUpdatedField() method does a similar test, but here we simply edit one of the rows in the database that was created by the setUp() method to check that updating changes the date_updated field. It is important to get the row from the database again to ensure that the date_updated field has been changed in the database itself.

Now that we have explored the Zend_Db_Table class and its associated rowset and row classes, we can look at joining tables.

6.3.2. Table Relationships with Zend_Db_Table

Table relationships are where Zend_Db_Table starts getting very interesting. This is the realm of linking tables together using SQL JOIN statements, and it’s one area where it is nice to get the framework doing the legwork for us. We will look at how Zend_Db_Table can help us with one-to-many and many-to-many relationships.

One-to-Many Relationships

We have already come across one-to-many relationships in Chapter 3, when we created the initial schema for the Places application. Each location can have many reviews. This is shown in figure 6.3.

Figure 6.3. A one-to-many relationship is created via a foreign key (place_id) in the Reviews table.

To represent this using Zend_Db_Table, we’ll create two new classes that extend from Zend_Db_Table_Abstract and then use the $_dependantTables and $_referenceMap properties to link them together. This is shown in listing 6.13.

Listing 6.13. One-to-many relationship using Zend_Db_Table

To define a one-to-many relationship, the $_referenceMap is used. Each relationship has its own subarray , and to ensure maximum flexibility, you inform the class exactly which columns are used in each table. In our case, the Reviews table has a column called places_id, which is linked to the id column in the Places table.

Note

Both columns’ entries in the reference map are defined as arrays. This allows for composite keys containing more than one table field.

To actually retrieve the reviews for a given “place” record, the findDependentRowset() method is used:

$londonZoo = $places->fetchRow('id = 1'),
$reviews = $londonZoo->findDependentRowset('Reviews'),

The findDependentRowset() method takes two parameters: the name of the table class that you wish to receive the data from, and an optional rule name. The rule name must be a key in the $_referenceMap previously set up, but if you don’t supply one, it will look for a rule with a refTableClass that is the same as the class name of the table from which the row was created (“Places” in our case).

We’ll now turn our attention to many-to-many relationships, which use a joining or link table.

Many-to-Many Relationships

By definition, each place can have many reviews written by different users. Therefore, for any given user, we can obtain the list of places that they have reviewed, and we can list the users who have reviewed any given place. This is known as a many-to-many relationship between places and users.

To map this relationship within a database, a third table, known as a link table, is required. In our case, the link table is the Reviews table, as shown in figure 6.4.

Figure 6.4. A many-to-many relationship between Places and Users is created via the Reviews table acting as a link.

The Reviews table has two foreign keys, one to Places and one to Users, so there are two one-to-many relationships. We need to define three classes, as shown in listing 6.14.

Listing 6.14. A many-to-many relationship using Zend_Db_Table

As you can see in listing 6.14, the Users class follows the same pattern as the Places class and only defines the table classes that it directly depends on. This means that we don’t explicitly state a many-to-many relationship in the code, and we let Zend_Db_Table work out that there is one.

To create a list of the places that a given user has reviewed, we can use the following code:

$users = new Users();
$robAllen = $users->fetchRow('id = 1'),
$places = $robAllen->findManyToManyRowset('Places', 'Reviews'),

As you can see, the function that does the work for us is called findManyToManyRowset(). The first parameter is the destination table class, and the second is the intersection table class containing the rules that will link the initial table class to the intersection table class. In both cases, the parameters can either be strings or instances of Zend_Db_Table, so the preceding snippet could be written as follows:

$users = new Users();
$places = new Places();
$reviews = new Reviews();

$robAllen = $users->fetchRow('id = 1'),
$places = $robAllen->findManyToManyRowset($places, $reviews);

This would produce exactly the same result. Note that if there is more than one rule in the $_referenceMap of the intersection table that would link the tables, you can specify which rule to use as an additional parameter to findManyToManyRowset().

Consider the situation where each review has to be approved by a moderator. The new table diagram would look like the one in figure 6.5.

Figure 6.5. Multiple many-to-many relationships between Places and Users are created via two keys in the Reviews table.

In this case, we now have two foreign keys in the Reviews table that link to the Users table. To implement the second link, a new rule is required in the $_referenceMap for the Reviews table, as shown in listing 6.15.

Listing 6.15. Reviews class with updated $_referenceMap

As you can see, we can have multiple rules in the reference map that refer to the same table, as long as they have different names (User and ApprovedBy, in this case). We then use the columns key in each map element to tell Zend_Db_Table the field name of the correct foreign key in the Reviews table.

Selecting the list of places that have been reviewed by a given user is done using findManyToManyRowset() again, but this time we pass in the name of the rule we want to use:

// find all places that John Smith has approved a review of.
$johnSmith = $users->fetchRow(' id = 4'),
$places = $johnSmith->findManyToManyRowset('Places', 'Reviews',
  'ApprovedBy'),

Zend_Db_Table’s handling of one-to-many and many-to-many relationships is surprisingly powerful, although it requires us to manually set up the relationships in the intersection class.

6.4. Summary

Throughout this chapter, we have looked at the support for databases provided by Zend Framework. As nearly all web applications use databases to store their data, it comes as no surprise to find that rich database support is provided. The foundation is the database abstraction layer, Zend_Db_Abstract, which takes care of the differences between specific database engines and so provides a standardized interface for the rest of the components in the framework. Zend_Db_Table builds upon this foundation to provide a table-based interface to the data in the table, and so makes working with the database from within the model of an MVC application much easier. For those times when it is important to work with a specific row of data, Zend_Db_Table_Row provides simple access.

We have now covered the elements of the underlying structure of a Zend Framework application. We’ll move on to look at authentication and authorization, which will allow us to restrict certain parts of our application to specific users and to ensure that those users can only perform actions appropriate to the trust that the application has in them.