Tony Marston's Blog About software development, PHP and OOP

Using PHP Objects to access your Database Tables (Part 2)

Posted on 6th June 2003 by Tony Marston

Amended on 31st August 2003

Intended Audience
Prerequisites
Extending the MySQL SELECT statement
Adding the features of a Data Dictionary/Repository
- Background
- The $fieldspec Array
- The $unique_keys Array
- The $relationship Array
Using the Data Dictionary/Repository
- Data Validation
- Changing Candidate Keys
- Deleting Rows
Default Processing
- DML Object
Custom Processing
Virtual Database Tables
Summary
Amendment History
Comments

Intended Audience

This is a follow-up to Using PHP Objects to access your Database Tables (Part 1) in which I showed you how to create a base class to contain all the standard code to access any database table, how to create extensions to this class for each individual table, and how to access these classes from within your PHP scripts. In this tutorial I will show you how to enhance the basic class to incorporate the following:

Prerequisites

It is assumed that you have read and understood Part 1 of this article and wish to know more.

Extending the MySQL SELECT statement

In the 'getData' method of Part 1 of this article I showed the basic MySQL syntax to read data from a database table, as shown below:

$query = "SELECT count(*) FROM $this->tablename $where_str";

The problem with this statement is that it can only read from one table and it will always retrieve all the columns. This does not cover all the options that are available and will quickly cause us to want to execute some custom code instead of using the standard code, which defeats the aim of maximum reusability in OOP. If you look at the section on SELECT statement syntax in the MySQL manual you will notice that it is comprised of several parts, some of which are mandatory while others are optional. You can change the code to allow for these different parts as follows:

$query = "SELECT $select_str
            FROM $from_str 
                 $where_str 
                 $group_str 
                 $having_str 
                 $sort_str 
                 $limit_str";

Some of these parts are optional and may be empty, but when they are all put together they must construct a valid query. This should immediately raise two questions:

To answer the second question first each of these component parts has its own class variable defined like this:

class Database_Table
{
   var $sql_select;
   var $sql_from;
   var $sql_where;
   var $sql_groupby;
   var $sql_having;
   var $sql_orderby;

The calling script can set the options it needs using code similar to this:

$dbobject = new mytable;
$dbobject->sql_select  = '...';
$dbobject->sql_from    = '...';
$dbobject->sql_where   = '...';
$dbobject->sql_groupby = '...';
$dbobject->sql_having  = '...';
$dbobject->sql_orderby = '...';
$data = $dbobject->getData($where);

The code inside my generic function then constructs each of these component parts using code similar to this:

if (empty($this->sql_select)) {
   $select_str = '*';    // the default is all fields
} else {
   $select_str = $this->sql_select;
} // if

if (empty($this->sql_from)) {
   $from_str = $this->tablename;   // the default is current table
} else {
   $from_str = $this->sql_from;
} // if

Here I am actually constructing the WHERE clause from two possible sources both of which are optional, but if both are present they must be combined.

if (empty($where)) {
   $where_str = NULL;
} else {
   $where_str = "WHERE $where";
} // if

if (!empty($this->sql_where)) {
   if (!empty($where_str)) {
      $where_str .= " AND $this->sql_where";
   } else {
      $where_str = "WHERE $this->sql_where";
   } // if
} // if

Now for the remainder:

if (!empty($this->sql_groupby)) {
   $group_str = "GROUP BY $this->sql_groupby";
} else {
   $group_str = NULL;
} // if

if (!empty($this->sql_having)) {
   $having_str = "HAVING $this->sql_having";
} else {
   $having_str = NULL;
} // if

if (!empty($this->sql_orderby)) {
   $sort_str = "ORDER BY $this->sql_orderby";
} else {
   $sort_str = NULL;
} // if

if ($rows_per_page > 0) {
   $limit_str = 'LIMIT ' .($pageno - 1) * $rows_per_page .',' .$rows_per_page;
} else {
   $limit_str = NULL;
} // if

Finally I can execute the completed query, construct an associative array from the result, then return to the calling script.

$query = "SELECT $select_str
            FROM $from_str 
                 $where_str 
                 $group_str 
                 $having_str 
                 $sort_str 
                 $limit_str";
$result = mysql_query($query, $dbconnect) or trigger_error("SQL", E_USER_ERROR);
$array = array();
while ($row = mysql_fetch_assoc($result)) {
   $array[] = $row;
} // while

mysql_free_result($result);

return $array;

As you can see it is not rocket science, but it does the trick.

Adding the features of a Data Dictionary/Repository

A Data Dictionary or Data Repository is a collection of descriptions of the data objects or items in a data model which can be used by both the programmers who are writing the code and the language in which they are coding. It describes all the tables in the database, the columns in each table, it identifies which columns form primary keys, candidate keys and indexes, it identifies which tables are related to one another, on which columns they are related, and what action to take when a row with existing relations is deleted.

As the information within a data dictionary is not actual application data (that which passes from the user to the database and back again) but is instead information about the application data, it is sometimes referred to as meta-data, or data-about-data.

Background

I first encountered a language which used a Data Dictionary in the early 1980s after using COBOL, a well known 3rd generation language (3GL) for several years. It made programming a lot easier because the language "knew" things about the data it was asked to manipulate and could do certain things automatically. If a field was a date, for example, it would only accept a valid date as input, and would automatically format it for display according to the specification in the dictionary. Similarly for numbers it would reject non-numeric input and automatically display it with the specified number of decimal places, currency symbol, and thousands separator. For integers it would only accept positive whole numbers, and so on and so on.

While teaching myself PHP I read a lot of books and looked at lots of sample code, and I was surprised to see a great deal of repetition when it came to validating user input before sending it to the database. As a self-confessed lazy programmer I do not like the idea of repeating the same or similar code over and over again so I sought a method of simplifying the whole data validation process. What I wanted was the features of a data dictionary so I could define the characteristics of my data and get the language to do what was necessary without further intervention. As you are aware (or should be) PHP does not have any sort of data dictionary, so I had to design and implement one myself.

My first decision was where to store this data dictionary. One thought that crossed my mind was to use a totally separate object, but then I reasoned that the best place to store the definitions of a database table was inside the object I was already building to communicate with that table. This actually fits in with the OO concept of 'encapsulation' which puts all the knowledge, properties and methods of an object in a single place.

My next decision was how to store this data. This was actually a no-brainer as the obvious choice is to use a set of multi-dimensional arrays. In Part 1 of this document I talked about the Class Constructor and introduced the concept of using $fieldlist to hold a list a fields contained within that particular table. I have actually enhanced this to hold much more than a simple list of fields. In fact it holds complete field specifications, so I have renamed it $fieldspec.

The $fieldspec Array

This is a class variable that is built within the class constructor. It is a multi-dimensional array which identifies all the fields (columns) which exist in that database table. For each field it also identifies the type (string, number, date, time), size and any other important characteristics. It is built according to the following format:

$this->fieldspec['fieldname'] = array('keyword' => 'value',
                                      'keyword' => 'value', ...);

As you can see this array is able to hold any number of fields, and for each field it is able to hold any number of keyword/value specifications. The list of field specifications includes the following:

array('type' => 'string');
array('size' => 16);
array('required' => 'y');
array('uppercase' => 'y');
array('lowercase' => 'y');

This particular one identifies the field as being part of the primary key. Note that a primary key may consist of more than one field.

array('pkey' => 'y');

This next one tells the system to display this as an HTML password field so the user's input is not echoed back:

array('password' => 'y');

If it is a multiline text field this will specify the size of the text box in columns and rows:

array('type' => 'multiline', 'cols' => 50, 'rows' => 5);

Integers can be signed or unsigned, and leading zeros may have to be filled. Be aware that all sizes of integer need to be catered for, from 'int1' all the way up to 'int8'.

array('type' => 'integer', 'unsigned' => 'y', 'zerofill' => 'y');

For numeric fields you may want to specify a minimum and/or maximum value:

array('minvalue' => 0, 'maxvalue' => 999);

Boolean fields may be stored as 'Y/N', 'T/F' or '0/1', so here you can identify which combination is being used:

array('type' => 'boolean', 'true' => 'Y', 'false' => 'N');

If a field needs to be encrypted you may choose between a built-in or custom encryption algorithm:

array('hash' => 'md5|sha1|custom');

For MySQL databases fields of type 'enum' need special handling:

array('type' => 'enum');

Then there are data and time fields. The 'infinityisnull' setting is for end dates when a blank date to the user means 'an unspecified date in the future' and should be stored in the database as '9999-12-31' and not '0000-00-00'. This makes date comparisons very much easier, and it is a technique that I have used for several years.

array('type' => 'time');
array('type' => 'date', 'infinityisnull' => 'y');

The following options can either be built into the $fieldspec array within the class constructor, or added at a later time during execution of particular scripts if particular conditions are met. One makes the field read-only and will not accept any changes while the other will prevent the field from being output to the client's browser, even as a hidden field:

array('noedit' => 'y');
array('nodisplay' => 'y');

The $unique_keys Array

This is a class variable that is built within the class constructor. It is a multi-dimensional array built according to the following format:

$this->unique_keys[] = array('fieldname1', 'fieldname2', ...);
$this->unique_keys[] = array('fieldname5', 'fieldname6', ...);

It is only used if the table contains unique keys which are in additional to the standard primary key. These are known as candidate keys. A table may contain any number of candidate keys, and each key may be comprised of one or more fields. When a record is inserted or updated the contents of this array is used to check that these keys do not already exist.

The $relationship Array

This is a class variable that is built within the class constructor. It is a multi-dimensional array built according to the following format:

$this->relationship[] = array('many' => 'tablename',
                              'fields' => array('one_id' => 'many_id',
                                                'one_id' => 'many_id', ...),
                              'type' => 'nullify|delete|restricted');

In a one-to-many or parent-child relationship between two tables these details are defined for the 'one' or 'parent' table to idetify its 'many' or 'child' tables. There can be any number of relationships, so for each it is necessary to first identify the name of the 'many' or 'child' table.

For each relationship you must identify which field(s) on the 'parent' table are related to which field(s) on the 'child' table. Again it is worth pointing out that each relationship may involve more than one field.

The final step is to identify the delete constraint for the relationship. For this there are 3 options:-

Using the Data Dictionary/Repository

Having this information available is just the start. What you really need is some code to make it work for you.

Data Validation

Having a list of all the characteristics of each field means that after obtaining data from the user and before sending it to the database it can be validated according to a standard set of rules. There needs to be two separate ways of calling the validation routine - one for an insert and another for an update.

When performing an insert it is necessary to validate every field on the table even though it may not have been supplied in the input array. The $fieldspec array can be used to identify the list of fields.

function std_fieldValidation_insert ($fieldarray)
// Validate contents of $fieldarray against $fieldspecs.
// Errors are returned in $errors array.
// NOTE: for INSERT all required fields contained in $fieldspec must be present.
{
      
   $this->errors = array();
   
   // this is a copy of $fieldarray that will be passed to the database
   $this->data_unformatted = array();
   
   // step through each fieldspec entry and compare with input data
   foreach ($this->fieldspec as $field => $spec) {
      if (isset($fieldarray[$field])) {
         $value = $fieldarray[$field];
      } else {
         $value = NULL;
      } // if
   	
      $value = $this->std_fieldvalidation($field, $value, $spec);
      
      // transfer to array which will be passed to the database
      if (strlen($value) > 0) {
         $this->data_unformatted[$field] = $value;
      } // if
      
   } // foreach
   
   return $fieldarray;	   
   	
} // std_fieldvalidation_insert

When performing an update it is only necessary to validate those fields which are supplied in the input array.

function std_fieldValidation_update ($fieldarray)
// validate contents of $fieldarray against $fieldspec.
// errors are returned in $errors array.
// NOTE: for UPDATE only a subset of fields may be supplied.
{
   
   $this->errors = array();

   // this is a copy of $fieldarray that will be passed to the database
   $this->data_unformatted = array();

   // step through input data and compare with fieldspec 
   foreach ($fieldarray as $field => $value) {
      // get specifications for this field
      $spec = $this->fieldspec[$field];

      $value = $this->std_fieldvalidation($field, $value, $spec);

      // transfer to array which will be passed to the database
      // (allow null values as field may have been cleared)
      if (strlen($value) > 0) {
         $this->data_unformatted[$field] = $value;
      } else {
         $this->data_unformatted[$field] = NULL;
      } // if

   } // foreach

   return $fieldarray;   

} // std_fieldValidation_update

Here is the code that will validate the data one field at a time:

function std_fieldvalidation ($fieldname, $fieldvalue, $fieldspec)
// standard function for validating database fields
{
   global $dateobj;
   
   // trim any leading or trailing spaces
   $fieldvalue = trim($fieldvalue);
	   
   if ($fieldspec['type'] == 'enum') {
      // get enum array for this field
      $enum = $this->getValRep($fieldname);
      // replace index number with text value
      $fieldvalue = $enum[$fieldvalue];
   } // if
      
   if ($fieldspec['type'] == 'boolean') {
      // result from radiogroup may be varied, so convert to TRUE or FALSE
      // (where actual values are defined within $fieldspec)
      if (is_True($fieldvalue)) {
         $fieldvalue = $fieldspec['true'];
      } else {
         $fieldvalue = $fieldspec['false'];
      } // if
   } // if
		
   if (strlen($fieldvalue) == 0) {
      // field is empty - is it allowed to be?
      if (isset($fieldspec['required'])) {
         $this->errors[$fieldname] = "$fieldname cannot be blank";
      } // if
		   
      if ($fieldspec['type'] == 'date' AND isset($fieldspec['infinityisnull'])) {
         $fieldvalue = '9999-12-31';
      } // if
		  
   } else {
	   
      // field is not empty - check field size
      if (isset($fieldspec['size'])) {
         $size = (int)$fieldspec['size'];
         if (strlen($fieldvalue) > $size) {
            $this->errors[$fieldname] = "$fieldname cannot be > $size characters";
         } // if
      } // if
		   
      if (isset($fieldspec['uppercase'])) {
         // value in this field must be uppercase
         $fieldvalue = strtoupper($fieldvalue);
      } // if
      if (isset($fieldspec['lowercase'])) {
         // value in this field must be lowercase
         $fieldvalue = strtolower($fieldvalue);
      } // if
         
      if ($fieldspec['type'] == 'string' OR $fieldspec['type'] == 'multiline') {
         // escape any suspect characters in string fields
         $fieldvalue = addslashes($fieldvalue);
      } // if
	      
      if (isset($fieldspec['password'])) {
         // passwords must have a 'hash' specification
         if (isset($fieldspec['hash'])) {
            switch($fieldspec['hash']){
            case 'md5': 
               $fieldvalue = md5($fieldvalue);
               break;
            case 'sha1': 
               $fieldvalue = sha1($fieldvalue);
               break;
            case 'custom':
               break;
            default:
               $this->errors[$fieldname] = "$fieldname: specification for 'hash' is invalid";
            } // switch
         } else {
            $this->errors[$fieldname] = "$fieldname: specification for 'hash' is missing";
         } // if
      } // if
         
      if ($fieldspec['type'] == 'date') {
         // value must be a date
         if (!$internaldate = $dateobj->getInternalDate($fieldvalue)) {
            $this->errors[$fieldname] = "$fieldname: " .$dateobj->getErrors();
         } else {
            // set date to internal format
            $fieldvalue = $internaldate;
         } // if
      } // if
         
      // perform validation if field type = integer
      $fieldvalue = $this->std_validateInteger($fieldname, $fieldvalue, $fieldspec);
		  
   } // if

   return $fieldvalue;

} // std_fieldvalidation

My integer validation function can cope with with field sizes from int1 all the way up to int8, both signed and unsigned. It checks the input value against maximum and minimum values which are fixed depending on the field size, but this range can be reduced by additional keywords in the $fieldspec array.

function std_validateInteger ($field, $value, $spec)
// if $spec identifies $field as an integer then check that $value is within range.
{
   $pattern = '(int1|tinyint|int2|smallint|int3|mediumint|int4|integer|int8|bigint|int)';
   if (preg_match($pattern, $spec['type'], $match)) {
      
      // test that input contains a valid value for an integer field
      $integer = (int)$value;
      if ((string)$value <> (string)$integer) {
         $this->errors[$field] = "Value is not an integer";
         return $value;
      } // if
      // set min/max values depending of size of field
      switch ($match[0]){
         case 'int1':
         case 'tinyint': 
            $minvalue = -128;
            $maxvalue =  127;
            break;
         case 'int2':
         case 'smallint': 
            $minvalue = -32768;
            $maxvalue =  32767;
            break;
         case 'int3';
         case 'mediumint':
            $minvalue = -8388608;
            $maxvalue =  8388607;
            break;
         case 'int':
         case 'int4':
         case 'integer':
            $minvalue = -2147483648;
            $maxvalue =  2147483647;
            break;
         case 'int8':
         case 'bigint':
            $minvalue = -9223372036854775808;
            $maxvalue =  9223372036854775807;
            break;
         default: 
            $this->errors[$field] = "Unknown integer type ($match)";
            return $value;
      } // switch
      
      // adjust min/max values if integer is unsigned
      if ($spec['unsigned']) {
         $minvalue = 0;
         $maxvalue = ($maxvalue * 2) +1;
      } // if

      if (isset($spec['minvalue'])) {
         // override with value provided in $fieldspec
         $minvalue = (int)$spec['minvalue'];
      } // if
      if ($integer < $minvalue) {
         $this->errors[$field] = "Value is below minimum value ($minvalue)";
      } // if

      if (isset($spec['maxvalue'])) {
         // override with value provided in $fieldspec
         $maxvalue = (int)$spec['maxvalue'];
      } // if
      if ($integer > $maxvalue) {
         $this->errors[$field] = "Value is above maximum value ($maxvalue)";
      } // if
      
      if (isset($spec['zerofill'])) {
         while (strlen($value) < $spec['size']){
            $value = '0' .$value;
         } // while
      } // if
   } // if
   
   return $value;
         
} // std_validateInteger

You will notice that any error messages are added to another class variable called $this->errors. This is an associative array where the fieldname is the key. This array is retrieved by the calling script so that it can associate each error with the relevant field when the HTML output is produced.

Changing Candidate Keys

Although it is not normal procedure to change the value of a primary key - indeed most database management systems actually forbid it - it is quite allowed to change the value of a candidate key. The only rule is that the value you wish to change to is not already in use. You also have to bear in mind that a table can have more than one candidate key, and that each key can contain more than one field. Using the $unique_keys array described previously it is possible to completely automate this validation procedure using code similar to the following:

   // validate any optional unique/candidate keys
   if (!empty($this->unique_keys)) {
      // there may be several keys with several fields in each
      foreach ($this->unique_keys as $key) {
         $where1 = NULL;   // for original values
         $where2 = NULL;   // for changed values
         foreach ($key as $fieldname) {
            if (empty($where1)) {
               $where1  = "$fieldname='" .$this->data_original[$fieldname] ."'";
               $where2  = "$fieldname='" .$updatearray[$fieldname] ."'";
            } else {
               $where1 .= " AND $fieldname='" .$this->data_original[$fieldname] ."'";
               $where2 .= " AND $fieldname='" .$updatearray[$fieldname] ."'";
            } // if
         } // foreach
         if ($where1 <> $where2) {
            // key has changed, so check for uniqueness
            $query = "SELECT count(*) FROM $this->tablename WHERE $where2";
            $count = $this->getCount($query);
            if ($count <> 0) {
               // set error message for each field within this key
               foreach ($key as $fieldname) {
                  $this->errors[$fieldname] = "A record already exists with this key.";
               } // foreach
               return $fieldarray;
            } // if
         } // if
      } // foreach
   } // if

You will have noticed here that in order to check that a candidate key value has changed I have had to retrieve the current record from the database just before it is updated. It is then a straightforward process to compare the original values with the user-supplied values to see what has changed.

Deleting Rows

When it comes to deleting a row which has relationships there may need to be some additional action required, either before the row is deleted or afterwards, depending on the nature of the relationship. Using the $relationship Array described earlier I am now able handle these situations with standard code. Before a row is deleted I check for the existence of associated rows on related tables where the relationship type has been set to 'restricted'. If any rows exist the deletion is not allowed to continue.

   foreach ($this->relationship as $reldata) {
      switch ($reldata['type']){
         case 'restricted': 
            // delete is not allowed if relationship is 'restricted'
            $where = NULL;
            foreach ($reldata['fields'] as $one => $many) {
                if (empty($where)) {
                    $where  = "$many='$fieldarray[$one]'";
                } else {
                    $where .= " AND $many='$fieldarray[$one]'";
                } // if
            } // foreach
            // set up query to count occurrences
            $query = "SELECT count(*) FROM {$reldata['many']} WHERE $where";
            $count = $this->getCount($query);
            if ($count <> 0) {
               $this->errors[] = "Cannot delete - record still linked to "
                                 .strtoupper($reldata['many'])
                                 ." table";
            } // if
            break;
         case 'delete':
         case 'nullify':
            break;
         default:
            $this->errors[] = "Unknown relation type: " .$reldata['type'];
      } // switch
   } // foreach

The following code deals with associated rows on related tables just before the selected row is deleted. There is a choice between deleting all associated rows or setting the foreign key fields to NULL.

   foreach ($this->relationship as $reldata) {
      switch ($reldata['type']){
         case 'nullify': 
            // set foreign key(s) to null
            $where  = NULL;
            $update = NULL;
            foreach ($reldata['fields'] as $one => $many) {
                if (empty($where)) {
                    $where  = "$many='$fieldarray[$one]'";
                } else {
                    $where .= " AND $many='$fieldarray[$one]'";
                } // if
                $update .= "$many=NULL,";
            } // foreach
            $update = rtrim($update, ',');
            // set up query to update the database
            $query = "UPDATE {$reldata['many']} SET $update WHERE $where";
            $result = mysql_query($query, $dbconnect) or trigger_error("SQL", E_USER_ERROR);
            break;
         case 'delete': 
            // delete all related rows
            $where = NULL;
            foreach ($reldata['fields'] as $one => $many) {
                if (empty($where)) {
                    $where  = "$many='$fieldarray[$one]'";
                } else {
                    $where .= " AND $many='$fieldarray[$one]'";
                } // if
            } // foreach
            // set up query to update the database
            $query = "DELETE FROM {$reldata['many']} WHERE $where";
            $result = mysql_query($query, $dbconnect) or trigger_error("SQL", E_USER_ERROR);
            break;
         case 'restricted':
            break;
         default:
            $this->errors[] = "Unknown relation type: " .$reldata['type'];
      } // switch      
   } // foreach

Default Processing

As shown in Part 1 - Controller Scripts you will need a separate script in order to instantiate a class into an object, then to activate methods on that object. Each of these methods can be invoked using code similar to the following:

$fieldarray = $dbobject->insertRecord($fieldarray);
$errors = $dbobject->getErrors();

$fieldarray = $dbobject->updateRecord($fieldarray);
$errors = $dbobject->getErrors();

$fieldarray = $dbobject->deleteRecord($fieldarray);
$errors = $dbobject->getErrors();

$fieldarray = $dbobject->getData($where);

When one of these methods is invoked it will actually cause a sequence of sub-methods to be invoked in a predefined sequence in order to carry out the chosen operation. For example, here is an example of my 'getData' method which contains calls to a series of sub-methods:

function getData ($where)
{
   $this->errors = array();
   $this->data_array = array();

   // perform any custom pre-retrieve processing
   $where = $this->pre_getData($where);

   if (empty($this->errors)) {
      // retrieve the data
      $this->data_array = $this->dml_getData($where);
   } // if

   if (empty($this->errors)) {
      // perform any custom post-retrieve processing
      $this->data_array = $this->post_getData($this->data_array, $where);
   } // if

   return $this->data_array;

} // getData

These 'pre_' and 'post_' methods will be defined in the abstract table (super) class but by default will not contain any code. This is because they are reserved for custom code which can only be defined in the table subclass. Each of the other operations - insertRecord(), updateRecord() and deleteRecord() - have similar 'pre_' and 'post_' methods of their own.

DML object

The standard function 'dml_' is the one which communicates with the physical database by constructing and executing the relevant SQL query using Data Manipulation Language (DML). In my first implementation all this code was defined in the superclass so that it could be inherited by every subclass, but I later moved all this code into a class of its own so that I could easily switch from the original "mysql_" extension to the improved "mysqli_" extension when MySQL version 4.1 was released. Here is an example of my amended dml_insertRecord method:

function dml_insertRecord ($fieldarray)
// insert a record using the contents of $fieldarray.
{
    $DML =& $this->_getDBMSengine($this->dbms_engine, $this->dbname);

    // use ORIGINAL, not CURRENT specifications for this database table
    $DML->fieldspec              = $this->getFieldSpec_original();
    // include other important variables
    $DML->primary_key            = $this->getPkeyNames();
    $DML->unique_keys            = $this->unique_keys;

    $array = $DML->insertRecord($this->dbname, $this->tablename, $fieldarray);
		
    $this->errors  = array_merge($DML->getErrors(), $this->errors);
    $this->numrows = $DML->numrows;
		
    return $array;
		
} // dml_insertRecord

Please note the following:

This mechanism made it easy for me to switch between the "original" and "improved" MySQL extensions at will, and it also made it easier for me to create additional DML classes for additional DBMS engines such as PostgreSQL, Oracle and SQL Server.

Custom Processing

It is all very well having all this standard code to perform standard processing, but how do you deal with those situations where you need more than the standard processing? The golden rule here is that the standard code should be left unchanged, so what we need is a simple mechanism which will allow additional code to be executed as and when necessary. As shown in Default Processing this can be achieved by creating enpty methods in the superclass which can be filled with code in each subclass. In other words the empty method in the superclass can be overridden by a non-empty method in the subclass. If nothing is defined in the subclass then the empty method in the superclass will be executed. This is an example of using the Template Method Pattern and its hook methods.

Here is an example of one of these empty methods:

function pre_getData ($where)
// perform custom processing before database record(s) are retrieved.
{
   // custom code goes here

   return $where;

} // pre_getData

When an actual table class (subclass) is created as an extension of the base class (superclass) it will inherit these empty functions unless alternatives are defined within the subclass. By incorporating a separate 'pre_' and 'post_' function for each of the major operations in the base class I have solved two potential problems:

Virtual Database Tables

By this I mean creating a class for a database table where some of the apparent details of the class, when accessed by the calling script, may exist in a different form on the physical database. Because all the details about each database table are encapsulated in the class for that table, and because all communication between a PHP script and the physical database are routed through the class, it is possible to make subtle changes between the calling script's view of the database and what physically exists.

This may sound very esoteric, a typical case of 'very clever, but what use is it?', but there may be a time when this ability comes in very useful. Here are some examples:

Summary

I hope that this article has shown you that even though PHP4 does not have all the OO capabilities to satisfy the purists it has more than enough to allow you, the developer, to create large amounts of reusable code, which is one of the big benefits that OOP is supposed to provide. With PHP I have managed to achieve the following:

NOTE: Since publishing the original article I have extracted all the code from my abstract table class which generates the SQL queries and placed it in a separate Data Access Object (DAO) (see DML Object). My original DAO dealt with the MySQL database, but I have subsequently developed additional DAOs to deal with the PostgreSQL, Oracle and SQL Server databases. This gives me the ability to switch my application from one DBMS to another simply by changing the identity of the DAO in a configuration file.

I have created a sample application which is based on the code described in this article. You can run this application from my website or download all the source code and run it on your own PC. Please refer to A Sample PHP Application for details.


Amendment history:

31 Aug 2003 Updated Default Processing to show how to move all data access logic to a separate DML object.

counter