Home arrow MySQL arrow Page 2 - Case Study: Handling MySQL Growth With a PHP Class
MYSQL

Case Study: Handling MySQL Growth With a PHP Class


Sick of updating your MySQL database to add new fields half way through a project? In this article Bjorn discusses his PHP class that you can use to make changes to your MySQL database on the fly!

Author Info:
By: Bjorn Roysland
Rating: 4 stars4 stars4 stars4 stars4 stars / 12
November 12, 2002
TABLE OF CONTENTS:
  1. · Case Study: Handling MySQL Growth With a PHP Class
  2. · Generic MySQL Tables
  3. · The Tricky Part
  4. · Formmode
  5. · Conclusion

print this article
SEARCH DEVARTICLES

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
MYSQL ARTICLES

- MySQL and BLOBs
- Two Lessons in ASP and MySQL
- Lord Of The Strings Part 2
- Lord Of The Strings Part 1
- Importing Data into MySQL with Navicat
- Building a Sustainable Web Site
- Creating An Online Photo Album with PHP and ...
- Creating An Online Photo Album with PHP and ...
- PhpED 3.2 More Features Than You Can Poke ...
- Creating An Online Photo Album with PHP and ...
- Creating An Online Photo Album with PHP and ...
- Security and Sessions in PHP
- Setup Your Personal Reminder System Using PHP
- Create a IP-Country Database Using PERL and ...
- Developing a Dynamic Document Search in PHP ...

Watch our Tech Videos 
Dev Articles Forums 
 RSS  Articles
 RSS  Forums
 RSS  All Feeds
Write For Us 
Weekly Newsletter
 
Developer Updates  
Free Website Content 
Contact Us 
Site Map 
Privacy Policy 
Support 

Developer Shed Affiliates

 




© 2003-2017 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap
Popular Web Development Topics
All Web Development Tutorials