Case Study: Handling MySQL Growth With a PHP Class

Case Study: Handling MySQL Growth With a PHP Class - Generic MySQL Tables
(Page 2 of 5 )

In this article I will show you a case study of code that I have used. You can download the PHP class as part of the support material at the bottom of the last page of this article.

When developing web applications, I find it tedious to add fields to my MySQL table as a new idea pops into my head. I thought there must be a better solution, but I couldn't didn't find any, so I came up with my own.

The general idea is just to define a PHP class that contains the SQL-structure of your MySQL database, and have another object update the tables in your database automatically. Take this code as an example:

function WSDocument($id=-1) {
$this->addField("id","int(11) NOT NULL auto_increment");
$this->addField("title","varchar(81) NOT NULL default 'Untitled'");
$this->addField("published","int(11) NOT NULL default '1'");
$this->addField("body","blob");
$this->addField("category","int(11)");

if($id!=-1) {
$this->load($this->id);
}
}

This is the constructor in my class that I call WSDocument and is a class to implement a document for my publishing engine.

The rest of my class looks like this:

class WSDocument extends WSDynamicObject
{
var $tableName = "WSDocument";
var $uniqueField = "id";
...
}

Notice the "extends WSDynamicObject" line, which is the object that handles my SQL-communication. Now, whenever I want to add a new field to my table for this class, I simply use:

$this->addField("myfieldname","int(10)");

What's the Magic?
This isn't much to brag about, you might say, but here comes the good stuff! Because I have added all the table fields of my class into my object WSDynamicObject, I can use that list of fields to generate an automatic load and save function. Now we are getting somewhere...

Imagine the tedious task of handling a submit form and saving the result into your MySQL database. The problem is easily solved with these dynamic classes. The object WSDynamicObject runs through the list of fields you have defined in your class, and checks if this field is in the HTTP_GET_VARS or HTTP_POST_VARS array, if it is, then it loads the values to an instance of your class and runs the save() function (which will be described later).

How about updating records? Well, if you add a hidden field with the name of your unique field and the unique field value as its value, then the instance of your class will be given the unique value and the correct record will be updated. If this value is -1, then a new record is inserted into the database. Simple!

Show Me How!
We've already seen how we add fields to the WSDynamicObject class, but how does it work? Here's the class declaration:

class WSDynamicObject {
var $fields; // an array of the fields in this structure
var $newobject; // is this a new object or loaded values from db?
var $table_fields; // the database equivalent of $this->fields

function addField($fieldname, $fieldproperties, $defaultvalue="") {
$this->fields[] = array($fieldname, $fieldproperties,$defaultvalue);
}
}

This shouldn't be too hard to understand, I'm simply showing the variables of the class and the addField function.

The save() algorithm that is used in form submission handling looks like this:

for( $i=0; $i<count($f_obj->fieldName); $i++) {
$fieldName = $f_obj->fieldName[$i];
if($fieldName!=$f_obj->uniqueField) {
$f_obj->$fieldName = $HTTP_POST_VARS[$fieldName]; // set data
}
}

Here's the load() algorithm that is called in the constructor of my WSDocument class:

function load($id) {
$sqlQuery = "SELECT * FROM $this->tableName WHERE _
$this->uniqueField=$this->id";

$result = mysql_query($sqlQuery);

while( $row = mysql_fetch_array($result)) {
for( $i=0; $i<count($this->fieldName); $i++) {
$fieldName = $this->fieldName[$i];
$this->$fieldName = $row["$fieldName"];
}
$this->newObject = false;
}
}

Another no-brainer. This simply runs through the record set that it fetched from the MySQL table and inputs the values into the object. The last line:

$this->newObject = false;

... simply says that the object is no longer new, and that it has loaded values – this makes sure that when the object is saved, the record set is updated and not inserted.
blog comments powered by Disqus