PHP Hacks

Hack 36. Create Dynamic Database Access Objects

Use the new object-oriented features of PHP 5 to create classes that wrap access to any database table.

PHP 5 represents a substantial upgrade in terms of object-oriented support in the PHP language. Along with a number of upgrades in performance, PHP 5 has a major upgrade in the ability to create dynamic classes. These are classes where the methods and attributes change from object to object. This can be very handy in building database applications.

Usually, there is one PHP class for each table in the database. For example, if you have tables named books, authors, and publishers, you would have PHP classes named Book, Author, and Publisher. Each PHP class has methods to get and set the values in a record in the corresponding table.

On the one hand, this is a very clean and easy-to-understand model. On the other hand, it's a lot of work to maintain these classes (and that's just for three tables!). Is it possible to write a single class that will wrap any table in the database? Yes. With PHP 5's support for _ _call, _ _get, and _ _set methods, it is.

To understand why _ _call, _ _get, and _ _set are important you need to understand how methods on objects get called. When you invoke a method on an object, the interpreter first looks at the class to see whether the method exists. If the method does exist, it's called; if it doesn't, the base class of the class is inspected; if that fails, the base class of the base class is examined, and so on, up the chain of classes.

In PHP 5, when the method lookup fails, the _ _call method is invoked, if it exists. This method has two arguments: the name of the method and the array of arguments for that method. If you implement the _ _call method and return a real value, PHP 5 is satisfied that it has found a method and that the method invocation worked.

The _ _get and _ _set methods correspond to the getting and setting of instance variables on the object. The _ _get method has a single parameter, the name of the instance variable. The _ _set method has two parameters, the name of the instance variable and the new value.

That means that you can effectively create new methods and instance variables on your objects on the fly. And that means that you can have a class that loads a record from a database table and has dynamic methods and instance variables that make it look like an object built just for that record.

Figure 5-3 shows how these dynamic methods and fields work. The code calls the class for either a method or a field. Then the object indicates that there is no such field. PHP calls to get the field value or method value, and thenif given a valid responsereturns that value to the calling code as though the field or method were there.

Figure 5-3. The control flow with dynamic fields

If all of this seems a little confusing, don't worry. This is a powerful new paradigm for object-oriented programming that takes a little while to understand, and even longer to implement successfully and safely. Think of this hack as just a taste of what is possible.

5.4.1. The Code

Save the code in Example 5-9 as dbrecord.php.

Example 5-9. Some simple PHP that makes for a surprisingly robust script

<?php
require_once( "DB.php" );
$dsn = 'mysql://root:password@localhost/books';
$db =& DB::Connect( $dsn, array() );
if (PEAR::isError($db)) { die($db->getMessage()); }

class DBRecord
{
  var $h;

  public function DBRecord( $table, $id )
  {
	  global $db;
	  $res = $db->query( "SELECT * from $table WHERE id=?", array( $id ) );
	  $res->fetchInto( $row, DB_FETCHMODE_ASSOC );
	$this->{'h'} = $row;
  }
  public function _ _call( $method, $args )
  {
	return $this->{'h'}[strtolower($method)];
  }
  public function _ _get( $id )
  {
	return $this->{'h'}[strtolower($id)];
  }
}
?>

To test the code, enter Example 5-10 and save it as test.php.

Example 5-10. A simple script that tests the database access script

<?php
require_once( "DBrecord.php" );

$rec = new DBRecord( "author", 2 );
print $rec->Name()."\n";
?>

books.sql, shown in Example 5-11, handles database setup for the example.

Example 5-11. An SQL script that sets up a simple test database

DROP TABLE IF EXISTS author;
CREATE TABLE author (
  id MEDIUMINT NOT NULL AUTO_INCREMENT,
  name TEXT,

  PRIMARY KEY( id )
);

INSERT INTO author VALUES ( 0, "jack" );
INSERT INTO author VALUES ( 0, "bob" );

5.4.2. Running the Hack

This hack is run with PHP on the command line:

	% mysql --user=root --password=password books < books.sql
	% php test.php
	bob

It doesn't look like much; but what's interesting is that we have an object that looks like the row in the author table. However, this same object could just as easily represent a record in the book, or the publisher table; it's not hardwired to any particular database schema or table.

The code simply creates a new DBRecord object with the name of the table and the ID of the record in the table. Then the Name() method is called; but there is no Name() method on the DBRecord object, so the _ _call method is invoked. The _ _call method then converts the method name to lowercase (part of the process PHP always follows). Then the _ _call method on the DBRecord object checks the hash of information read from the databaseand stored in the $h instance variableand returns the value of the requested field.

5.4.3. Hacking the Hack

Reading data from the database is one thing. But can we update the code so that it can read and write from a record? Sure. Save the code in Example 5-12 as dbrecord2.php.

Example 5-12. Code addition for handling database updates as well as reads

<?php
require_once( "DB.php" );
$dsn = 'mysql://root:password@localhost/books';
$db =& DB::Connect( $dsn, array() );
if (PEAR::isError($db)) { die($db->getMessage()); }

class DBRecord
{
  var $h;
  var $table;
  var $id;

  public function DBRecord( $table, $id )
{
	   global $db;
	   $res = $db->query( "SELECT * from $table WHERE id=?", array( $id ) );
	   $res->fetchInto( $row, DB_FETCHMODE_ASSOC );
	$this->{'h'} = $row;
	$this->{'table'} = $table;
	$this->{'id'} = $id;
	}
	
	public function _ _call( $method, $args )
	{
	  return $this->{'h'}[strtolower($method)];
	}

	public function _ _get( $id )
	{
		print "Getting $id\n";
		return $this->{'h'}[strtolower($id)];
	}

	public function _ _set( $id, $value )
	{
		$this->{'h'}[strtolower($id)] = $value;
	}

	public function Update()
	{
	  global $db;

	  $fields = array();
	  $values = array();

	  foreach( array_keys( $this->{'h'} ) as $key )
	  {
	    if ( $key != "id" )
		{
		  $fields []= $key." = ?";
		  $values []= $this->{'h'}[$key];
		}
	  }
	  $fields = join( ",", $fields );
	  $values []= $this->{'id'};

	  $sql = "UPDATE {$this->{'table'}} SET $fields WHERE id = ?";
	  $sth = $db->prepare( $sql );
	  $db->execute( $sth, $values );
	  }
	} ?>

To test this new code, enter Example 5-13 and save it as test2.php.

Example 5-13. A script that tests dynamic database updates

<?php
require_once( "DBrecord2.php" );

$rec = new DBRecord( "author", 2 );
print $rec->Name()."\n";
$rec->Name = "New Name";
$rec->Update();
?>

Now let's run test2.php:

	
	% php test2.php
	bob
	% php test2.php
	New Name
	%

First, the script prints the current value of the record in the database. Then it sets the value to ?New Name?and updates the database record. I've run the script again to verify that the value is updated.

The trick here is that the _ _set method is called with the value ?New Name?, so the hash of fields from the record is updated with the new value. Then the Update() method is called, which executes an UPDATE command in the SQL database.

The Rails framework (http://www.rubyonrails.org/) for Ruby (http://ruby-lang.org/) uses a technique similar to this to allow web applications to quickly adapt to any database schema. It looks like Cake (http://cakephp.org) might do something similar for PHP.

5.4.4. See Also

• "Turn Any Object into an Array" [Hack #53]

• "Generate Database Select Code" [Hack #42]

• "Generate CRUD Database Code" [Hack #37]

Origami Paper AirPlane