Building Object-Oriented Database Interfaces in PHP: Abstracting Database Tables

Welcome to part two of the series “Building Object-Oriented Database Interfaces in PHP.” In the previous article, I offered complete coverage of the role that database interfaces play in Web applications, highlighting the immediate benefits of having a centralized mechanism for accessing, processing and verifying data, within an object-oriented environment. The “DBIGenerator” class I showed for demonstration purposes in the first article doesn’t scale well in real applications. In this article, we will start solving its problems with a revamped version of the class.

Introduction

Without having to necessarily mess up things with some concepts inherent to theory, we might generally conceive database interfaces as a logical representation coded within a class of a database table, where each table field is represented as a class data member. By following this generic rule on a given database table, we’re able to build programmatically a class that contains all of the core DML statements required to manipulate table rows, using the methods provided by the class, in this way acting as single access points to the table data.

Stepping back to the first article, I’ve shown a “DBIGenerator” class that quickly creates DB interface classes, spawning the corresponding objects to provide access to a given database table. As you probably remember, this class doesn’t scale well in real applications, since it works with a hard-coded database table and fields, thereby limiting significantly its flexibility and portability.

In order to solve these relevant issues, this second part will be the starting point for defining a revamped version of the “DBIGenerator” class, which, as we’ll see in short order, will be powerful enough to be implemented successfully in the creation of DB interfaces.

Confronted with this scenario, it’s time to put our coding abilities to the test. Let’s go!

{mospagebreak title=Turning back time: a quick look at the older “DBIGenerator” class}

For the purpose of having a brief look at the definition of the original “DBIGenerator” class, and exemplifying specific DML operations, here’s how the class looked:

class DBIGenerator{

// define data members

var $name;

var $path;

var $options;

function DBIGenerator
($name=’DEFAULTNAME.php’,$path=’DEFAULTPATH/’,$options=array
()){

$this->name=$name;

$this->path=$path;

$this->options=$options;

}

function create(){

// create class data members definition

$str='<?php’.”n”;

$str.=’class ‘.$this->name.'{‘.”n”;

foreach($this->options as $option){

$str.=’var $’.$option.’=”;’.”n”;

}

// create constructor

$str.=’function ‘.$this->name.'(){}’.”n”;

// create modifiers and accessors

foreach($this->options as $option){

$str.=’function set’.$option.'($’.$option.’){‘.”n”;

$str.=’$this->’.$option.’=$’.$option.';’.”n”;

$str.=’}’.”n”;

$str.=’function get’.$option.'(){‘.”n”;

$str.=’return $this->’.$option.';’.”n”;

$str.=’}’.”n”;

}

// create “submit()” method

$str.=’function submit(&$db){‘.”n”;

// build insert query

$str.=’$sql=”INSERT INTO users SET ‘;

foreach($this->options as $option){

$str.=($option!=’id’)?$option.’=’$this->’.$option.”,':”;

}

$str.='”;’.”n”;

// remove trailing comma

$str=preg_replace(“/,”/”,”””,$str);

// perform query

$str.=’$db->performQuery($sql);’.”n”;

$str.=’$this->id=&$db->getInsertID();’.”n”;

$str.=’}’.”n”;

// create “load” method

$str.=’function load(&$db){‘.”n”;

// build query

$str.=’$sql=”SELECT * FROM users WHERE id=’$this->id'”;’.”n”;

// perform query

$str.=’$result=$db->performQuery($sql);’.”n”;

$str.=’$row=$result->fetchRow();’.”n”;

$str.=’return $row;’.”n”;

$str.=’}’.”n”;

// create “update” method

$str.=’function update(&$db){‘.”n”;

// build query

$str.=’$sql=”UPDATE users SET ‘;

foreach($this->options as $option){

$str.=($option!=’id’)?$option.’=’$this->’.$option.”,':”;

}

// remove trailing comma

$str=preg_replace(“/,$/”,””,$str);

$str.=’ WHERE id=’$this->id'”;’.”n”;

// perform query

$str.=’$db->performQuery($sql);’.”n”;

$str.=’}’.”n”;

// create “delete” method

$str.=’function delete(&$db){‘.”n”;

// build query

$str.=’$sql=”DELETE FROM users WHERE id=’$this->id'”;’.”n”;

// perform query

$str.=’$db->performQuery($sql);’.”n”;

$str.=’}’.”n”;

$str.=’}?>';

// wite contents to class file

$fp=fopen($this->path.$this->name.’.php’,”w”) or die(‘Error
creating class file’);

fwrite($fp,$str);

fclose($fp);

}

function getObject(){

// create object

if(file_exists($this->path.$this->name.’.php’)){

require_once($this->path.$this->name.’.php’);

return new $this->name;

}

return false;

}

}

Based on the above class, we’re able to create several DB interface classes that operate directly on databases tables, allowing an efficient single-row manipulation. Again, let’s implement an example to understand how DML statements are performed against a table:

// include class files

require_once ‘mysqlclass.php';

require_once ‘dbigeneratorclass.php';

// instantiate a new MySQLConnector object

$db=&new MySQLConnector(array
(‘host’=>’host’,’user’=>’user’,’password’=>
‘password’,’database’=>’database’));

// set class properties-accessors and modifiers

$options=array(‘id’,’firstname’,’lastname’,’email’);

// instantiate a DBI generator object

$gn=&new DBIGenerator(‘User’,’DBICLASSES/’,$options);

// create User class

$gn->create();

// create user object

if(!$user=$gn->getObject()){

die(‘Failed to create object’);

}

Here, we first included the required class files to connect to MySQL and spawned a “DBIGenerator” object, passing as parameters an array containing the names of the table fields that compose the structure of the sample “users” table (notice that this table is hard-coded within the class).

Then, once the $gn object has been created, we build a DB Interface class, named “User”, which is located in the “DBICLASSES” directory, using the lines:

$gn=&new DBIGenerator(‘User’,’DBICLASSES/’,$options);

// create User class

$gn->create();

The last thing to do is to get an $user object that is properly returned by the “getObject()” method. Now, since this object is a logical representation of the “users” table structure, we can easily insert, update or delete a row from it. How is it done? It’s as simple as this:

// set user properties

$user->setfirstname(‘Homer’);

$user->setlastname(‘Simpson’);

$user->setemail(‘homer@springfield.com’);

// insert row into database table

$user->submit($db);

echo ‘Row ‘.$user->getid(). ‘ inserted successfully';

The above snippet adds a new user to the table. Simple, huh? Fine, now let’s update a row:

$user->setfirstname(‘Lisa’);

$user->setlastname(‘Simpson’);

$user->setemail(‘lisa@springfield.com’);

// update table row

$user->update($db);

echo ‘Row updated successfully ‘;

Finally, let’s delete the row:

// delete row

$user->delete($db);

echo ‘Row deleted successfully';

Definitely, that was really easy. We’ve carried out single-row operations by using directly the interface provided by the $user object.

From the given examples, we can quickly understand that a singe DB interface is rather limited in its functionality. The actual power comes when multiple interfaces are grouped together to carry out a specific task. The big picture can be grasped pretty easily.

If we have multiple tables, where each one of them has a database interface tied to it, by combining them into an application, we’re able to perform more complex operations on several tables at the same time. This changes the scenario dramatically, boosting the application considerably.

But, in fact, I’m getting ahead of myself. The big problem is that our “DBIGenerator” class only works with the predefined “users” database table. We should get rid of this big limitation, rewriting the class, in order to work with any supplied table. In the next section I’ll show a new version of the class, which presents several improvements that solve these difficulties.

{mospagebreak title=Working with multiple database interfaces: improving the “DBIGenerator” class}

So far, we’ve seen the significant limitations of the original class in real applications. Certainly, we need to develop a more flexible class that will be capable of generating multiple DB interfaces, in order to work with several database tables. Therefore, the brand new “DBIGenerator” class might be defined like this:

class DBIGenerator{

var $table;

var $name;

var $path;

function DBIGenerator
($table,$name=’default_file.php’,$path=’DEFAULTPATH/’){

$this->table=$table;

$this->name=$name;

$this->path=$path;

}

function generate(){

// build class header

$str='<?php class ‘.$this->name.'{‘;

if(!$result=mysql_query(“SHOW COLUMNS FROM $this->table”)){

die(‘Could not run query ‘.mysql_error());

}

// build data member declaration

if(mysql_num_rows($result)>0){

while($row=mysql_fetch_array($result,MYSQL_ASSOC)){

$str.=’var $’.$row['Field'].’=”;';

$methods.=’function set’.$row['Field'].'($’.$row['Field'].’)
{$this->’.$row['Field'].’=$’.$row['Field'].';}';

$methods.=’function get’.$row['Field'].'(){return $this-
>’.$row['Field'].';}';

// store field names in array

$fields[]=$row['Field'];

}

}

// build empty constructor

$str.=’function ‘.$this->name.'(){}';

// build modifiers and accesors

$str.=$methods;

unset($methods);

// build load() method

$str.=’function load(){$r=mysql_query(“SELECT * FROM
‘.$this->table.’ WHERE id=’$this->id'”);';

$str.=’return mysql_fetch_array($r,MYSQL_ASSOC);}';

// build submit() method

$str.=’function submit(){mysql_query(“INSERT INTO ‘.$this-
>table.’ SET ‘;

foreach($fields as $field){

$str.=($field!=’id’)?$field.’=’$this->’.$field.”,':”;

}

$str.='”);$this->id=mysql_insert_id();';

$str=preg_replace(“/,”/”,”””,$str).’}';

// build update() method

$str.=’function update(){mysql_query(“UPDATE ‘.$this-
>table.’ SET ‘;

foreach($fields as $field){

$str.=($field!=’id’)?$field.’=’$this->’.$field.”,':”;

}

$str=preg_replace(“/,$/”,””,$str);

$str.=’ WHERE id=’$this->id'”);}';

// build delete() method

$str.=’function delete(){mysql_query(“DELETE FROM ‘.$this-
>table.’ WHERE id=’$this->id'”);}';

$str.=’}?>';

// write class code to file

$fp=fopen($this->path.$this->name.’.php’,’w’) or die(‘Failed
opening file’);

fwrite($fp,$str);

fclose($fp);

// delete temporary variables

unset($fp,$str,$row,$fields,$field);

}

function getObject(){

// check if class file exists

if(file_exists($this->path.$this->name.’.php’)){

require_once($this->path.$this->name.’.php’);

// create object

return new $this->name;

}

return false;

}

}

Despite the fact that the source class code is quite long, its simplicity is remarkable. Taking a closer look at it, we can see that its logic is fairly similar to the previous version. Jumping straight into its code, the constructor takes three incoming parameters, described as follows: the first one $table represents the database table from which the database interface will be created. This single parameter implies a big improvement compared to the previous version, since it allows to rapidly generate a DB interface from any table passed to the class.

The second parameter $name, means the file name of the DB interface class to be created. Finally the third argument, $path, indicates the path within the file system where the class will be physically located. The constructor simply assigns these values as class properties, as we see below:

function DBIGenerator
($table,$name=’default_file.php’,$path=’DEFAULTPATH/’){

$this->table=$table;

$this->name=$name;

$this->path=$path;

}

Again, I’ve specified default values for the name of the DB interface class, as well as for the path. As you can see, the constructor definition is closely similar to the old version.

The next thing to do is to explain the logic of the “generate()” method, which obviously takes charge of generating the DB interface class file, including the required methods to set up the access points to a given database table. Join me in the next section to learn how this is done.

{mospagebreak title=Getting closer: a detailed look at the “generate()’ method}

This method is the most resource intensive, since it dynamically generates the class file that will interface with a database table. In general terms, the class file is built by concatenating strings, so the generation process shouldn’t present any difficulties to being understood.

First, the method obtains the field names of the table passed to the class, assigning them directly as class properties, storing temporarily the output in the $methods variable, to be included at a later time. The lines below perform these operations:

// build class header

$str='<?php class ‘.$this->name.'{‘;

if(!$result=mysql_query(“SHOW COLUMNS FROM $this->table”)){

die(‘Could not run query ‘.mysql_error());

}

// build data member declaration

if(mysql_num_rows($result)>0){

while($row=mysql_fetch_array($result,MYSQL_ASSOC)){

$str.=’var $’.$row['Field'].’=”;';

$methods.=’function set’.$row['Field'].'($’.$row['Field'].’)
{$this->’.$row['Field'].’=$’.$row['Field'].';}';

$methods.=’function get’.$row['Field'].'(){return $this-
>’.$row['Field'].';}';

// store field names in array

$fields[]=$row['Field'];

}

}

Then, the class constructor is generated with this single line:

$str.=’function ‘.$this->name.'(){}';

Finally, the accessors and modifiers methods are appended to the output, like this:

// build modifiers and accessors

$str.=$methods;

The next step is to append the “load()” and “submit()” methods to the output string, accomplishing the “SELECT” and “INSERT” single-row operations. They’re defined as listed below:

// build load() method

$str.=’function load(){$r=mysql_query(“SELECT * FROM
‘.$this->table.’ WHERE id=’$this->id'”);';

$str.=’return mysql_fetch_array($r,MYSQL_ASSOC);}';

// build submit() method

$str.=’function submit(){mysql_query(“INSERT INTO ‘.$this-
>table.’ SET ‘;

foreach($fields as $field){

$str.=($field!=’id’)?$field.’=’$this->’.$field.”,':”;

}

$str.='”);$this->id=mysql_insert_id();';

$str=preg_replace(“/,”/”,”””,$str).’}';

Notice that in this case, we’re using the native MySQL functions to execute queries and retrieve a row. However, this condition might be easily changed by utilizing methods provided by another object.

So far, so good. We’ve already described the two main methods used to retrieve and insert a row into the database table. Our next task is to explain the remaining methods, “update()” and “delete()”. Don’t you worry, because they’re just a few lines away.

{mospagebreak title=Updating and deleting a row: the “update()” and “delete()” methods}

Not surprisingly, these two methods are self-explanatory. They accomplish the tasks of updating and deleting a row from a table, using regular DML statements. Their definitions look like this:

// build update() method

$str.=’function update(){mysql_query(“UPDATE ‘.$this-
>table.’ SET ‘;

foreach($fields as $field){

$str.=($field!=’id’)?$field.’=’$this->’.$field.”,':”;

}

$str=preg_replace(“/,$/”,””,$str);

$str.=’ WHERE id=’$this->id'”);}';

// build delete() method

$str.=’function delete(){mysql_query(“DELETE FROM ‘.$this-
>table.’ WHERE id=’$this->id'”);}';

The above lines append the methods to the progressive output, for providing the DB interface class with the ability to update and delete a row. As you might guess, building the class is just a matter of concatenating simple strings.

Once the complete class code has being generated, we need to write it to a file, so the class file will be automatically created for spawning data access objects, and tying these objects to each related database table. The lines below create the specified class file at the given path:

// write class code to file

$fp=fopen($this->path.$this->name.’.php’,’w’) or die(‘Failed
opening file’);

fwrite($fp,$str);

fclose($fp);

// delete temporary variables

unset($fp,$str,$row,$fields,$field);

Lastly, in order to make the class behave as an “object factory”, we define the “getObject()” method, which includes the DB interface file and returns a new object for usage within the application:

function getObject(){

// check if class file exists

if(file_exists($this->path.$this->name.’.php’)){

require_once($this->path.$this->name.’.php’);

// create object

return new $this->name;

}

return false;

}

Although the class definition is certainly quite simple, it reveals its real strength when used in conjunction with multiple DB interfaces, allowing more flexible and efficient database table manipulation.

However, by now we’ll see how the class can be implemented in a rather basic way, so we’re able to spot the main differences compared to the version presented in the previous article. Thus, in the next lines, we’ll see the a brief example for proper class usage.

{mospagebreak title=Spotting the differences: the new “DBIGenerator” class in practice}

Probably, one of the most illustrative ways to demonstrate how the class “fits” into an application is by coding an example. Therefore, here’s a possible implementation of the class:

require_once ‘mysqlclass.php';

require_once ‘dbigeneratorclass.php';

// instantiate a new MySQLConnector object

$db=&new MySQLConnector(array
(‘host’=>’host’,’user’=>’user’,’password’=>
‘password’,’database’=>’database’));

// instantiate a new DBIGenerator object

$g=&new DBIGenerator(‘users’,’User’,’DBICLASSES/’);

// generate class file

$g->generate();

// get an user object

if(!$user=$g->getObject()){

die(‘Failed to create object’);

}

In the above example, we included the class files, instantiating a $db “MySQLConnector” object to provide the necessary database connectivity. Then, we instantiated a “DBIGenerator” object, passing to it the name of the database table to be associated with, in this case “users”; the name of the DBI class to be created, “User”; and lastly, the path where the class file will be located, that is “DBICLASSES/”.

For this example, the “users” table contains the following fields: “id”,”firstname”, “lastname” and “email”, which implies that the class tied to it should represent these fields as properties, as well as having the corresponding setters and getters.

After executing the snippet, a class file named “User.php” is created in the “DBICLASSES/” directory, and a “User” class has been dynamically generated, with the following definition:

<?php class User{var $id=”;var $firstname=”;var
$lastname=”;var $email=”;function User(){}function setid
($id){$this->id=$id;}function getid(){return $this->id;}
function setfirstname($firstname){$this-
>firstname=$firstname;}function getfirstname(){return $this-
>firstname;}function setlastname($lastname){$this-
>lastname=$lastname;}function getlastname(){return $this-
>lastname;}function setemail($email){$this->email=$email;}
function getemail(){return $this->email;}function load()
{$r=mysql_query(“SELECT * FROM users WHERE id=’$this-
>id'”);return mysql_fetch_array($r,MYSQL_ASSOC);}function
submit(){mysql_query(“INSERT INTO users SET
firstname=’$this->firstname’,lastname=’$this-
>lastname’,email=’$this->email'”);$this->id=mysql_insert_id
();}function update(){mysql_query(“UPDATE users SET
firstname=’$this->firstname’,lastname=’$this-
>lastname’,email=’$this->email’ WHERE id=’$this->id'”);}
function delete(){mysql_query(“DELETE FROM users WHERE
id=’$this->id'”);}}?>

Don’t get confused about the code! The class has been simply created with no new lines on it. Since the PHP interpreter doesn’t care about this, the file size is significantly reduced. However, to have a more readable version of the class, I show the same code, this time including the new lines:

<?php

class User{

var $id=”;

var $firstname=”;

var $lastname=”;

var $email=”;

function User(){}

function setid($id){

$this->id=$id;

}

function getid(){

return $this->id;

}

function setfirstname($firstname){

$this->firstname=$firstname;

}

function getfirstname(){

return $this->firstname;

}

function setlastname($lastname){

$this->lastname=$lastname;

}

function getlastname(){

return $this->lastname;

}

function setemail($email){

$this->email=$email;

}

function getemail(){

return $this->email;

}

function load(){

$r=mysql_query(“SELECT * FROM users WHERE id=’$this->id'”);

return mysql_fetch_array($r,MYSQL_ASSOC);

}

function submit(){

mysql_query(“INSERT INTO users SET firstname=’$this-
>firstname’,lastname=’$this->lastname’,email=’$this-
>email'”);

$this->id=mysql_insert_id();

}

function update(){

mysql_query(“UPDATE users SET firstname=’$this-
>firstname’,lastname=’$this->lastname’,email=’$this->email’
WHERE id=’$this->id'”);

}

function delete(){

mysql_query(“DELETE FROM users WHERE id=’$this->id'”);

}

}

?>

Wasn’t that good? I’m sure you’ll agreed. The above class is a logical representation of the “users” table. Having such a useful class, we’re able to perform all of the single-row DML operations directly on the table. Let’s consider the power of this approach when we combine these classes to access multiple tables. It’s really worth giving a try. That’s what we will do in the next article!

Wrapping up

A few considerations are needed here. Over this part of the series, I’ve coded a new “DBIGenerator” class that presents greater flexibility to work with any database table supplied as class parameter. Also, hopefully I’ve demonstrated how powerful this class can be for accessing data by using a single communication point.

However, the real strength of this approach is clearly evidenced when working with many classes tied to multiple tables. That’s the pending task for the next article, so you don’t have any excuses to miss it. See you soon!

[gp-comments width="770" linklove="off" ]
antalya escort bayan antalya escort bayan