PHP DevCenter
oreilly.comSafari Books Online.Conferences.

advertisement


Simplify Business Logic with PHP DataObjects
Pages: 1, 2, 3

Result Sets

Now we can find a row and perform an insert, delete, or update on our DO_User DataObject. Remember how I mentioned that our DataObject is a simple wrapper that represents a single row from our table? What if we need to perform a query that returns more than one row? Our DataObject can't handle this, nor should it. We need another wrapper for the result set.

The simplest approach to solving this problem is to create an array of DataObjects that contains all of the found rows. That may seem logical at first, but what if your query returns 10,000, 100,000, or more rows? That'd make a mighty big array of objects, turning your application into a RAM hog and convincing your web host to shut down your site.

We need a proper wrapper for the result set that holds a reference to the native (or Pear::DB) result set. This wrapper won't return an array of each row. Instead, it returns instances of our DO_User DataObject. Pretty cool.

We'll have to write a new class for this. I always call it ReadOnlyResultSet, as the result set itself is read-only (we can't update or delete rows in the result set directly), and it happens to be the name used in the J2EE design patterns (let's hear it for consistency). Before we write this class, we'll add one more method to DO_User to find a bunch of rows using data currently in the DataObject. When it finds the rows, this new function will return a new instance of our new ReadOnlyResultSet class. This is easier than it sounds. We'll start by adding the find() method [file source]:

public function find()
{
  $sql = "SELECT * FROM User";
  
  // This array will hold the where clause
  $where = array();
  
  // Using PHP 5's handy new reflection API
  $class = new ReflectionClass('DO_User');

  // Get all of DO_User's variable (or property) names
  $properties = $class->getProperties();
  
  // Loop through the properties
  for ($i = 0; $i < count($properties); $i++) {
    $name = $properties[$i]->getName();
    if ($this->$name != '') {
      // Add this to the where clause
      $where[] = "`" . $name . "`='" 
      . mysql_escape_string($this->$name) . "'";
    }
  }
  
  // If we have a where clause, build it
  if (count($where) > 0){
    $sql .= " WHERE " . implode(' AND ', $where);
  }
    
  $rs = mysql_query($sql);
  include_once('class-ReadOnlyResultSet.php');
  return new ReadOnlyResultSet($rs);
}

This one is a doozy. PHP 5 has a built-in Reflection API that we can use to retrieve any and all information about a class and/or object. Consider this built-in reverse engineering. We use reflection here to pull the property names (member variable names) from the DataObject. Take a look at this new API some time; it's quite powerful.

Before calling find(), we have to to put data into those properties we want to search. If we want to find people with a certain first name, we set only that property and then call find. The find() method builds the query and returns a ReadOnlyResultSet. Before we explore how to use this method, we need to code the ReadOnlyResultSet class.

Coding ReadOnlyResultSet

The ReadOnlyResultSet class is a wrapper for a result set that returns DataObject instances. I use a generic ReadOnlyResultSet for all projects. It works so well that I never have to modify it. This new wrapper has three methods: getNext(), rowCount(), and reset(). Here's the code for the class [file source]:

File contents of: class-ReadOnlyResultSet.php
<?php 
class ReadOnlyResultSet {
	// This member variable will hold the native result set
  private $rs;

	// Assign the native result set to an instance variable
  function __construct($rs)
  {
    $this->rs = $rs;
  }

	// Receives an instance of the DataObject we're working on
  function getNext($dataobject)
  {
    $row    = mysql_fetch_array($this->rs);

    // Use reflection to fetch the DO's field names
    $class    = new ReflectionObject($dataobject);
    $properties = $class->getProperties();

    // Loop through the properties to set them from the current row
    for ($i = 0; $i < count($properties); $i++) {
      $prop_name        = $properties[$i]->getName();
      $dataobject->$prop_name = $row[$prop_name];
    }
    
    return $dataobject;
  }

  // Move the pointer back to the beginning of the result set
  function reset()
  {
    mysql_data_seek($this->rs, 0);
  }

  // Return the number of rows in the result set
  function rowCount()
  {
    return mysql_num_rows($this->rs);
  }
}
?>

As a note for those used to using & to pass by variables reference, PHP 5 always passes objects by reference now (like Java). That's why I avoid the & in my examples.

Because this is a wrapper for a result set, the constructor for this class requires that we pass a result set. For this example, it's a native MySQL result set, for the reasons previously stated. The reset() method moves the pointer of the native result set back to 0, the beginning. rowCount() returns the number of rows in the native result set. The getNext() method is more curious. Let's break it down a little.

First, because this class needs to work for all DataObjects, we have to have a way to tell this method which DataObject to work with. That's why we have to pass the DataObject we want to fill with the next row's data. This will usually be an empty DataObject. The first line in this method fetches the next row from the native result set with mysql_fetch_array(). Next, we need to loop through all of the passed-in object's properties using the Reflection API. Notice the usage of the class ReflectionObject instead of ReflectionClass. This is because we're trying to reverse engineer an object, not a class.

As the loop executes, it sets the value of each property in our DataObject to the value of each field in our native row. When we're all done, we return the now-filled DataObject. This class works for all DataObjects and you should never need to modify it.

It's time to see ReadOnlyResultSet in action. Here's how to look for all users who have the first name jane:

<?php
include_once('class-DO_User.php');
$user      = new DO_User();

// We'll use a literal string here, 
// but this could come from anywhere,
// such as $_POST or $_GET
$user->firstName = 'jane';

// Call find(), which returns an instance of ReadOnlyResultSet
$rs        = $user->find();
?>

<html>
  <head>
    <title>Found Users</title>
  </head>
  <body>
    <p>Here the found users:</p>
    <table border="1">
      <tr>
        <td>User ID</td>
        <td>First Name</td>
        <td>Last Name</td>
        <td>Email</td>
      </tr>
?>
// Loop through the result set
for ($i=0; $i < $rs-rowCount(); $i++) {

  // Pass on a new instance of DO_User, receiving it back filled in
  $userRow = $rs->getNext(new DO_User());
?>

	// Display the current row in an HTML table
      <tr>
        <td><?=$userRow->userId?></td>
        <td><?=$userRow->firstName?></td>
        <td><?=$userRow->lastName?></td>
        <td><?=$userRow->email?></td>
      </tr>
<?php
}
<?php
    </table>
  </body>
</html>

Notice that when we call $rs->getNext() we needed to pass a new DO_User instance. This is how the ReadOnlyResultSet knows which kind of DataObject it's working with. The ReadOnlyResultSet populates this new DataObject with the next row's data, returning the now-populated DataObject. Remember, PHP 5 always passes objects by reference, so the new DataObject passed on to getNext() is the exact same object that returned from getNext(). Once again, compact, readable and clean code.

Further Ideas

Beyond these basic five methods, you can add more functionality as needed. I often add a method to authenticate a user upon login. This method expects the username and password as parameters and returns true or false. If it returns true, I fill the DataObject with the found record. If you need very specific or more complex finding, add a new method to accomplish that task. The fundamental methods shown in this article are just the starting point for your own DataObjects.

It's actually quite possible to generalize these five methods and drop them into a super class. The Reflection API makes this pretty easy to do.

Also note that there is one limitation to this simple DataObject implementation -- you can't easily do joins. When I need a join, I create view DataObjects that only return a ReadOnlyResultSet, since you generally can't do joined INSERTs, DELETEs, and UPDATEs. There are other ways to accomplish joins, though.

Finally, you may have noticed that there is a Pear::DB_DataObject package available. This is a solid package, but it can be a bit of a challenge to figure out if you're not already familiar with the concepts of DataObjects. This article makes a good introduction to Pear::DB_DataObject, a package I hope to write about in the future.

This brings us to the end of our first look at DataObjects. What we've seen here is a simple implementation of DataObjects using PHP 5. With some small modifications, you can adapt this implementation to work with PHP 4, as well as with any database abstraction layer you wish (such as Pear::DB or DBX). Many folks also write up a script that automatically generates the initial DataObjects by querying the database's table structure and creating the necessary PHP files. This can save a lot of time.

In future articles, I hope to cover Pear::DB_DataObject and PHP 5's new Reflection API.

Resources

Darryl Patterson is a senior instructor at Centennial College in Toronto, where he has developed and taught many programming courses including PHP, SQL, Java/J2EE, HTML, JavaScript and CGI/Perl.


Return to the PHP DevCenter


Valuable Online Certification Training

Online Certification for Your Career
Earn a Certificate for Professional Development from the University of Illinois Office of Continuing Education upon completion of each online certificate program.

PHP/SQL Programming Certificate — The PHP/SQL Programming Certificate series is comprised of four courses covering beginning to advanced PHP programming, beginning to advanced database programming using the SQL language, database theory, and integrated Web 2.0 programming using PHP and SQL on the Unix/Linux mySQL platform.

Enroll today!


Sponsored by: