//, PHP/Using PHP Objects to Access Your Database Tables (Part 2)

Using PHP Objects to Access Your Database Tables (Part 2)

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:

• How to cater for the many options on a MySQL SELECT statement.

• How to add the features of a Data Dictionary or Repository.

• How to use the Repository for standard validation.

• How to use the Repository to validate changes to candidate keys.

• How to use the Repository to handle related tables when deleting rows.

• How to add code for custom processing.

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:

• How do I construct each of these component parts?

• Where does the data for these component parts come from?

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:-

• Nullify – the foreign key field in all related rows in this ‘child’ table will be set to NULL.

• Delete – any related rows in this ‘child’ table will also be deleted.

• Restricted – the existence of any related rows in this ‘child’ table will prevent the entry from the ‘parent’ table from being deleted.

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) {
$where .= "$many='$fieldarray[$one]' AND ";
} // foreach
$where = rtrim($where, ' AND');
// 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) {
$where .= "$many='$fieldarray[$one]' AND ";
$update .= "$many=NULL,";
} // foreach
$where = rtrim($where, ' AND');
$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) {
$where .= "$many='$fieldarray[$one]' AND ";
} // foreach
$where = rtrim($where, ' AND');
// 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

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 are a series of empty spaces where custom code can be inserted as and when necessary. What I mean by an ’empty space’ is a method/function which is pre-defined in the base class but which is empty. The standard code needs to contain calls to each of these custom methods/functions at an appropriate point so that they can be actioned even if they are empty. This is best demonstrated by showing you some sample code.

For example, here is a version of my ‘getData’ method which contains calls to custom functions:

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

The standard function ‘dml_getdata’ is the one which communicates with the physical database. The custom functions, in this example ‘pre_getData’ and ‘post_getData’, are created along these lines:

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 functions in the base class I have solved two potential problems:

It makes it easy for the developer to know where to insert any custom code for each of the different events.

It greatly reduces the possibility of accidentally corrupting any of the standard code as these dummy functions do not have any default code of their own.

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:

You have coded a class for a database table, you have coded all the scripts which communicate with this class when you suddenly find that your RDBMS will not let you create a table with that name because it is a reserved word. Instead of having to change lots of lines of code in lots of scripts all you need do is go to the class constructor for that table and change

   $this->tablename = 'reserved_word';

to

   $this->tablename = 'not_a_reserved_word';

So as far as all your PHP scripts are concerned they are talking to a database table called reserved_word when in actual fact the database object is transparently redirecting them to not_a_reserved_word. You may come across other legitimate reasons for having to rename tables, or move them from one database to another, but by using classes in this way it is possible to redirect your whole system simply by changing one single line of code.

It is possible to create alias names for databases tables. This is useful if you are using different rows of the same database table for different purposes, such as in a senior-to-junior relationship, and you need to identify which is which. Creating an alias name is as simple as this:

<?php

require_once 'real.class.inc';
class Alias extends Real
{
} // end class
?>

Because your PHP script instantiates an object from the Alias class it thinks it is talking to a database table called Alias when in fact it is really talking to a table called Real.

As well as giving database tables different names it is also possible to rename individual columns. As all the data communication between the PHP script and the object is via standard arrays and not database resources it is therefore eminently possible to intercept each communication and use standard PHP array processing functions to change a column name from this to that when going one way, and from that to this when going the other.

It may become necessary in the lifetime of a system, for reasons of performance for example, to split a database table into several parts. This situation could be dealt with simply by changing the class dealing with that table. All the scripts using the class would still think of it as a single database table, but only the class itself would really know that it is actually several tables.

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:

Create a base class which can handle all the basic read/write/update/delete functions for any database table.

Incorporate the usefulness of a data dictionary/repository into the class so that common tasks can be performed by standard code without the need for duplicated custom code.

• This base class contains all the necessary code to perform standard field validation when performing an insert or an update.

• This base class contains all the necessary code to validate changes in any candidate keys.

• This base class contains all the necessary code to handle the delete constraints in any relationship.

• This base class is used to create extended classes for each individual table in your database.

• Each of these extended classes will automatically inherit all the standard code from the parent (base) class which means that custom code is only required to deal with custom circumstances.

2010-05-26T11:39:39+00:00 April 7th, 2005|MySQL, PHP|0 Comments

About the Author:

I have been a software engineer, both designing and developing, since 1977. I have worked with a variety of 2nd, 3rd and 4th generation languages on a mixture of mainframes, mini- and micro-computers. I have worked with flat files, indexed files, hierarchical databases, network databases and relational databases. The user interfaces have included punched card, paper tape, teletype, block mode, CHUI, GUI and web. I have written code which has been procedural, model-driven, event-driven, component-based and object oriented. I have built software using the 1-tier, 2-tier, 3-tier and Model-View-Controller (MVC) architectures. After working with COBOL for 16 years I switched to UNIFACE in 1993, starting with version 5, then progressing through version 6 to version 7. In the middle of 2002 I decided to teach myself to develop web applications using PHP and MySQL.

Leave A Comment