Using Data Objects with PHP and DB_OO2

      2 Comments on Using Data Objects with PHP and DB_OO2

A Matter Of Routine

If you’re building a Web-based administration tool to interact with a database, there are some routine tasks that you quickly get used to coding: listing records, adding records, and updating or deleting records. Frameworks like the Zend Framework and CakePHP come with various built-in methods to help with this, and even if you’re not using one of these frameworks, there are a number of tools that can make coding these functions a breeze.

Data objects, which provide an API for accessing and manipulating database tables, are one such tool. There are a number of implementations for data objects in PHP, most notably the popular PEAR DB_DataObject package. This tutorial focuses on one such implementation, the DB_OO2 package, showing you how it can significantly reduce your coding time when working with database tables.

Guild-ing The Lily

DB_OO2 works by allowing you to represent database tables as PHP objects, and providing pre-defined object methods to search, add, edit or delete data from the underlying tables. Not only does this approach save time, because the data object insulates you from the task of constructing and manipulating raw SQL queries, but it’s also simpler because it’s no longer necessary to be intimately aware of the structure and schema of the underlying database table to use it effectively.

Before we get started, it’s important to state the assumptions this tutorial makes:

  • First, it assumes that you understand HTML, know the basics of PHP scripting, and are familiar with using SQL result sets in PHP.
  • Second, it assumes that you have an Apache/PHP development environment and a PostgreSQL RDBMS already set up. This is because the DB_OO2 package only includes support for PostgreSQL (at the moment).
  • Third, it assumes that you’ve managed to successfully install both the PEAR DB package and the DB_OO2 package. For your reference, the PEAR DB package is freely available from http://pear.php.net/package/DB, while the DB_OO2 package was developed by Blog.com and is currently maintained by Sergio Carvalho and can be downloaded from http://pear.sergiocarvalho.com/packages/package/DB_OO2/. (Note that DB_OO2 only works with PEAR DB; however, the next version of the package will include support for PDO and also for foreign key references).

This tutorial also makes use of an example database table. You can create and populate this table using the following SQL statements:

Finally, it’s worth mentioning at this point that this tutorial is intended mainly for users new to data objects and the DB_OO2 package. As such, it includes a general introduction to the package and how it works, but shies away from very complex examples. Once you’ve worked your way through this tutorial, you’re sure to find many more sophisticated applications for DB_OO2 in your daily development.

Object Lesson

Let’s begin with a simple example that showcases some of the power of DB_OO2: defining a data object that maps to the Guilds database table and using it to fetch data from the database:

The first step in using DB_OO2 is to define objects that map to tables from the database. The listing above does this by defining a data object to represent the Guilds table, by invoking the factory() method with two parameters: a PEAR DB object that represents a connection to the PostgreSQL database, and the name of the table to use. The result of this is a Guild data object, which exposes various pre-defined methods to make interacting with the table easier:

  • The fetchAll() method retrieves all the records in the table;
  • The fetchById() methods retrieves a single record by its primary key;
  • The fetchByWhere() methods retrieves all records matching a specified condition;
  • The create() method adds a new record to the table;
  • The update() method updates an existing record in the table;
  • The delete() method removes a record from the table;

This particular listing demonstrates the fetchAll() method, which retrieves all the records in the table. A while() loop is then used to iterate over the record collection, printing each guild’s name and primary attribute. Notice the getGuildName() and getGuildAttribute() methods: these are so-called “magic methods”, which provide an easy way to retrieve the value of a specified field, simply by appending the field name to the get…() method.

Here’s what the output looks like:

It’s worth noting also that, unlike PEAR’s DB_DataObject package, DB_OO2 doesn’t require a special database schema file with table and field information to be defined in advance of using the package; instead, DB_OO2 dynamically reads schema information from the database itself as needed.

Fetch And Carry

To retrieve a specific record, use the fetchById() method, which receives as arguments the primary key values and returns the record matching that key tuple. Here’s an example:

Here’s the likely output:

It’s also possible to return a subset of records matching a specified condition, with the fetchByWhere() method. This method accepts an SQL WHERE clause, as in the following example:

Adding It All Up

So that takes care of retrieving records from the database. Now, how about adding new records? It’s actually pretty simple – all you need to do is call the data object’s loadFromArray() method with an associative array of field-value pairs representing the record to be added, and then use the create() method to actually insert the record into the database table. There’s no SQL code to write, and the entire process is fairly painless. Here’s an example:

And now, when you look in the database after running this script, you should see the newly-added record:

An alternative way to accomplish the same thing is to manually set each field value with the…wait for it…setFieldValue() method before calling create(). Here’s how:

Note that you don’t need to set all of the table’s fields. Fields not set will be filled with the default values present in the catalog. Additionally, auto-incrementing primary keys will be set automatically.

Making Changes

Every data object also comes with update() and delete() methods, which allow you to update or delete a record respectively. Updating is a two-step process: first, fetch the record using the fetchById() method and then, pass the update() method an associative array with the fields to be updated. Consider the following example, which illustrates by updating the record created in the previous example:

And now, when you look in the database, you should see the changed record:

Deleting a record follows a similar process, except that you will need to call the delete() method instead of the update() method. Here’s an example:

Putting It All Into Practice

Now that you know the theory, let’s see how DB_OO2 can aid development in a more practical context, by building a simple database administration tool with it. Begin by adding a new table to the example database, to hold records of Members belonging to various Guilds, as follows:

Note that the Members and Guilds tables are linked to each other via the ‘GuildID’ foreign key.

Now, how about writing some code to display these records?

Nothing particularly complicated here: the script generates a data object for the Members table, uses the fetchAll() method to retrieve all the Member records, and then displays them in a neat HTML table, with links to edit and delete each record and also add new records. A separate data object is also generated for the Guilds table, which uses the GuildID foreign key to retrieve the name of each Member’s Guild.

Here’s a sample of what the output might look like:

Add…

Clicking the ‘add a new record’ link directs the user to a new script, which generates an HTML form to add a Member. Here’s the code:

This script has two parts to it. The first part checks if the form has been submitted and, if not, generates an empty form to add a new Member. Here’s an example of what the form looks like:

Notice the use of the fetchAll() method to generate a human-readable selection list of Guild names.

Once this form has been submitted, the second half of the script takes over. It sanitizes and validates the user input submitted through the form and then formats this input into an associative array. This array is then passed to the loadFromArray() method, and a new Member record is formulated and saved to the database via the create() method. You should then see this newly-added record in the Member list, as below:

… And Subtract

Clicking the ‘delete’ link directs the user to another script, and also passes along the unique Member identifier to that script through the URL GET method. The script then uses this identifier to locate and delete the corresponding record, using the fetchById() and delete() methods. Here’s the code:

And clicking the ‘edit’ link takes the user to yet another script, again passing along the Member identifier in the URL. This script generates a Web form pre-filled with the Member’s current record, and invites the user to make changes to the record. On submission, the submitted input is validated, formatted into an array, and used to update the record via the update() method. Take a look at the code:

And here is a screenshot of it in action:


As this example illustrates, using DB_OO2 can significantly reduce the drudge work of formulating and executing SQL queries for common data manipulation tasks. As such, it’s a valuable addition to any programmer’s toolkit, and one that you should definitely take some time out to experiment with. Play with it sometime, and see what you think!

Copyright Melonfire, 2008. All rights reserved.