Building Object-Oriented Database Interfaces in PHP: Working with Multiple Data Access Objects

In previous articles in this series, we examined the concept of database interfaces, and saw source code for a “DBIGenerator” class. Working with multiple DB interfaces permits the accomplishment of several operations on many tables at the same time. Alejandro Gervasio demonstrates the power of this concept, with examples.

Introduction

Here we are again. Welcome to the third part of the series “Building Object-Oriented Database Interfaces in PHP.” I’m pretty sure that you’ve been working for a while with the concept of database interfaces explained in my previous articles, playing around with the source code defined for the already familiar “DBIGenerator” class.

In order to offer a brief summary of what we’ve learned until now about database interfaces, let’s quickly review some key concepts. Undoubtedly, the most critical issue is to understand the why of the existence of such a programming structure.

Through the previous articles, we’ve seen that at times it is much better to implement a set of classes that sit between the database logic and the PHP application logic. This establishes a single access point to the data, and creates an efficient structure (the classes themselves) to wrap up core DML statements into “packages” that perform one-row operations on database tables. Definitely, the advantages of working with these classes become more evident when used with multiple DB interfaces, because it’s possible to accomplish several operations on many tables at the same time, by using each class tied to a database table.

Having this concept in mind, I’ve developed a class named “DBIGenerator,” which takes care of generating the proper DB interface for a given database table, building a class that represents the structure of the table in a logical way. Indeed, the “DBIGenerator” class behaves as a real “database access object factory,” returning a new object that allows you to work directly on tables, executing single-row DML statements.

At this point, I firmly believe that you properly understand the basic theory behind database interfaces. Thus, it’s time to make another leap forward in the learning process.

Either you’ve been developing your own set of DB Interface classes or simply expect to see several examples about its possible uses in Web applications. In this third part I will hopefully offer a practical approximation, by showing some concrete cases where the real power of DB Interfaces is clearly demonstrated. Are you ready to work with database interfaces in PHP? Fine. Let’s get started.

{mospagebreak title=Getting in touch with a DB Interface: A brief look at the “DBI Generator” class}

 

As usual, developers want to take a look at the source code of the class, so they gain a more detailed knowledge of its functionality. Therefore, in order to make available the full class code for implementation in projects, here is its complete listing:

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;
}
}

Since the logic of the above “DBIGenerator” class was covered in detail in the second part of this series, I won’t stop long for an explanation. Instead, I’ll merely show a quick implementation, before jumping directly into the practical examples. So first, let’s include the necessary class files, like this:

require_once ‘mysqlclass.php';
require_once ‘dbigeneratorclass.php';

Notice that, besides including the required file for the “DBIGenerator” class, we’re including the “MySQLConnector” class, which is handy for supplying database connectivity within the application. The next step involves the connection to MySQL, and the instantiation of a new object from the “DBIGenerator” class. We do it in the following way:

// 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/’);

The only thing to note here is that we’re passing to the “DBIGenerator” class the name of an existing database table (in this case “users”), the name of the DBI class to be generated (“User”), and finally, the path where this class will be allocated.

Next, it’s time to create the class file, by invoking the “generate()” method:

// generate class file
$g->generate();

Finally, we’re able to get the database access object returned by the “getObject()” method, as we see below:

// get an $user object
if(!$user=$g->getObject()){
die(‘Failed to create object’);
}

In this particular situation, we’re working with a specific $user object. However, in accordance with the set of database tables involved in an application, we might be dealing with objects such as $clients, $products, $messages and so forth, or whatever data that can be stored in a database. As you can see, being able to  communicate with multiple tables, through the databases classes associated, implies a very powerful technique.

Evidently, the next thing to do is look at the class that was dynamically generated after calling the “generate()” method. Want to see more? Just keep reading.

{mospagebreak title=Code generator in a nutshell: one class that generates another one}

Once we’ve called the “generate()” method, one data access object $user has been programmatically created. But let’s go one step further: if the object is available to be used, there must be a class from which the object was properly instantiated. That’s the main task of this generator class, as you might guess. One DB Interface class has been generated “behind the scenes,” providing a bridge between the database table and the application logic. As you’ve seen before, this is the class that I obtain as a result, after executing the above code:

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'”);
}
}

Definitely, the “DBIGenerator” class does its thing building the corresponding database interface class for a specified database table. In this case, I’m showing a more readable version of the code generated, since in its obfuscated version, it doesn’t contain any new lines. However, this is not a relevant issue for the PHP interpreter.

Well, happily we’ve seen how a simple code generator works, because we have a class that is capable of generating one or more classes, according to the particular application requirements. Do you realize the actual power of this approach? I guess you do. However, let’s implement some examples to see how a database interface can be put to work for us.

{mospagebreak title=Some useful examples: a practical usage of the “DBIGenerator” class}

Despite the fact that I’ve written some examples in the second part of this series, let’s refresh our memory by examining several single-row database operations. Say that we have our existing “users” table, composed of the “id”, “firstname”, “lastname” and “email” fields. Having an “$user” object available, let’s add a new row to the table:

$user->setfirstname(‘Barth’);
$user->setlastname(‘Simpson’);
$user->setemail(‘barth@springfield.com’);
$user->submit();

That was really easy, right? Now, let’s change our minds and update a row:

$user->setnombre(‘Lisa’);
$user->setapellido(‘Simpson’);
$user->setemail(‘lisa@springfield.com’);
$user->update();

Finally, let’s delete a row from the table, like this:

$user->delete();

As you can see, these database operations are very simple. However, the real power of database interfaces is fully appreciated when working with several interfaces that are tied to multiple tables. Thus, let’s build an example that manipulates data in two tables simultaneously.

This is the sample scenario: we have a database that contains two tables, “users” and “messages.” The first table stores user data, and presents the following fields: “id”, “firstname”, “lastname” and “email.” The second table contains messages submitted by the users, and contains the fields “id”, “user_id”, (this is the foreign key) and finally “message”, which simply stores user messages.

Having defined this database schema, let’s see how we can insert incoming data from a single POST form. Here’s the code for the example:

require_once(‘mysqlclass.php’);
require_once(‘dbigeneratorclass.php’);
// instantiate a new MySQLConnector object
$db=&new MySQLConnector(array(‘host’=>’host’,’user’=>’user’,’password’=>’password’,
‘database’=>’databasename’));
$db->performQuery(‘SHOW TABLES from databasename’);
// loop over database tables
while($table=$db->fetchRow()){
$g=&new DBIGenerator($table[0],$table[0],’DBICLASSES/’);
// create DBI class files
$g->generate();
// get DBI objects
if(!${$table[0]}=$g->getObject()){
die(‘Failed to create object’);
}
}
echo ‘DB Interfaces successfully created<br />';
// user data validation is required here!!!
// insert new user into “users” table
$users->setfirstname($_POST['firstname']);
$users->setlastname($_POST['lastname']);
$users->setemail($_POST['email']);
$users->submit();
echo ‘User ‘.$users->getid().’ added successfully<br />';
// insert new message into “messages” table
$messages->setmessage($_POST['message']);
$messages->setuser_id($users->getid());
$messages->submit();
echo ‘Message ‘.$messages->getid().’ added successfully';

Okay, let’s dissect the code in order to understand how it works. First, we include the class files needed to work with the “MySQLConnector” wrapping class and the “DBIGenerator”, connecting to the server, and passing the proper connection parameters:

require_once(‘mysqlclass.php’);
require_once(‘dbigeneratorclass.php’);
// instantiate a new MySQLConnector object
$db=&new MySQLConnector(array
(‘host’=>’host’,’user’=>’user’,’password’=>’password’,
‘database’=>’databasename’));

So far, the above code presents no difficulties at all. Now things get a lot more exciting. Next, we retrieve the tables contained into the sample database, as follows:

$db->performQuery(‘SHOW TABLES from databasename’);

Since our sample database contains the “users” and “messages” tables, we need to create a database interface class for each table, so we can perform SQL operations on both of them. We generate these classes through the following lines:

$db->performQuery(‘SHOW TABLES from databasename’);
// loop over database tables
while($table=$db->fetchRow()){
$g=&new DBIGenerator($table[0],$table[0],’DBICLASSES/’);
// create DBI class files
$g->generate();
// get DBI objects
if(!${$table[0]}=$g->getObject()){
die(‘Failed to create object’);
}
}
echo ‘DB Interfaces successfully created<br />';

Let’s see in detail what’s happening here. We simply loop over the tables, instantiating a new “DBIGenerator” object for each table, and generating the corresponding interface class. Notice that each class is assigned the name of the table that it communicates with, and it’s created in the “DBICLASSES” directory. If this sounds rather confusing, just take a look at the line below:

$g=&new DBIGenerator($table[0],$table[0],’DBICLASSES/’);

In each iteration, $table[0] contains the name of each table, so in this case, the values assigned in turn will be “users” and “messages”. The last thing to do is create the class files and obtain the objects that “interface” with each table:

$g->generate();
// get DBI objects
if(!${$table[0]}=$g->getObject()){
die(‘Failed to create object’);
}

After executing the above loop, (assuming that we have the right file permissions), two class files have been created, ‘users.php” and “messages.php”, located under the “DBICLASSES” directory. By now, I think that you can easily guess how each class looks. If you still don’t have a clue, here’s the list for the dynamically created “users” class:

<?php
class users{
var $id=”;
var $firstname=”;
var $lastname=”;
var $email=”;
function users(){}
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'”);
}
}
?>

And below is the list for the dynamic “messages” class:

<?php
class messages{
var $id=”;
var $message=”;
var $user_id=”;
function messages(){}
function setid($id){
$this->id=$id;
}
function getid(){
return $this->id;
}function setmessage($message){
$this->message=$message;
}
function getmessage(){
return $this->message;
}
function setuser_id($user_id){
$this->user_id=$user_id;
}
function getuser_id(){
return $this->user_id;
}
function load(){
$r=mysql_query(“SELECT * FROM messages WHERE id=’$this->id'”);
return mysql_fetch_array($r,MYSQL_ASSOC);
}
function submit(){
mysql_query(“INSERT INTO messages SET message=’$this-
>message’,user_id=’$this->user_id'”);
$this->id=mysql_insert_id();
function update(){
mysql_query(“UPDATE messages SET message=’$this->message’,user_id=’$this-
>user_id’ WHERE id=’$this->id'”);
}
function delete(){
mysql_query(“DELETE FROM messages WHERE id=’$this->id'”);
}
}
?>

This is not only convenient, but it’s fun! See how the two classes were dynamically created, exposing the core DML methods “load()”,”submit()’, “update()” and “delete()”, along with the modifiers and accessors? Indeed, the code is highly flexible. It works with two or more database tables, and the classes would be created in a transparent process, as we’ve seen before.

For the purpose of sticking strictly to the code generated for each class, here’s the obfuscated version of the above classes. First, the “users” class:

<?php class users{var $id=”;var $firstname=”;var $lastname=”;var
$email=”;function users(){}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'”);}}?>

And second, the “messages” class:

<?php class messages{var $id=”;var $message=”;var $user_id=”;function
messages(){}function setid($id){$this->id=$id;}function getid(){return
$this->id;}function setmessage($message){$this->message=$message;}function
getmessage(){return $this->message;}function setuser_id($user_id){$this-
>user_id=$user_id;}function getuser_id(){return $this->user_id;}function
load(){$r=mysql_query(“SELECT * FROM messages WHERE id=’$this-
>id'”);return mysql_fetch_array($r,MYSQL_ASSOC);}function submit()
{mysql_query(“INSERT INTO messages SET message=’$this-
>message’,user_id=’$this->user_id'”);$this->id=mysql_insert_id();}function
update(){mysql_query(“UPDATE messages SET message=’$this-
>message’,user_id=’$this->user_id’ WHERE id=’$this->id'”);}function delete
(){mysql_query(“DELETE FROM messages WHERE id=’$this->id'”);}}?>

As you can see, I’ve provided the source code for both classes as it’s actually written to the files. Of course, we’re sacrificing readability, but increasing performance, since the sizes of the files are reduced.

Once the database interface classes have been created on the fly, we’re able to easily perform data manipulation on the tables. Just take a deep breath and join me in the next explanation. We’re almost done!

{mospagebreak title=Adding rows to both tables: working with multiple data access objects}

Having created a couple of DB Interface classes that fit into the application as single communication points to each table, it’s fairly easy to insert new data into the tables. Considering this particular case, we’re utilizing a regular POST form to accomplish this task. The multi-row insertion process is performed in the following sequence: first, we insert POST data into the “users” table using the $users object returned by the “DBIGenerator” class, as shown in the lines below:

//insert new user into “users” table
$users->setfirstname($_POST['firstname']);
$users->setlastname($_POST['lastname']);
$users->setemail($_POST['email']);
$users->submit();
echo ‘User ‘.$users->getid().’ added successfully<br />';

Of course, before directly accepting user data coming from a POST/GET/COOKIE request, a validation operation should always be performed, to quickly reject invalid data. However, for keeping the sample code uncluttered, that will be left as a possible improvement. Anyway, due to the fact that each database interface is a centralized data access mechanism, data verification should be easy to implement.

The above code inserts a new row into the “users” table, adding “firstname”, “lastname”, and “email” values, simply using the $users object’s API (Application Programming Interface). Finally the data is inserted via the “submit()” method.

But, we need to maintain the relationship between the “users” and “messages” tables. So, each time a new user is added to the table, the “messages” table needs to be updated. Therefore, the user-submitted message is added to this table, using the methods provided by the “$messages” object:

// insert new message into “messages” table
$messages->setmessage($_POST['message']);
$messages->setuser_id($users->getid());
$messages->submit();
echo ‘Message ‘.$messages->getid().’ added successfully';

By doing so, we’re keeping track of the messages submitted by a user, since the “user_id” field is populated with the “ID” property of the “$users” object. The following line illustrates this process:

$messages->setuser_id($users->getid());

That’s about it. The multi-row insertion operation has been accomplished through the data access objects tied to each particular database table. Definitely, the above approximation is very powerful when applied to large web projects, where a set of data access objects carry out centralized DML operations.

Summary

In this series of articles, we’ve taken a detailed look at the concept and application of database interfaces, exploring the numerous scenarios where they can be implemented. Also, during the development process, we’ve used the “Factory” Design Pattern, to dynamically create data access objects. Hopefully, the whole experience has been instructive and rich.

However, there are more things to be reviewed. Since PHP is a very fast-growing language, the last part of this series implements database interfaces in PHP 5, taking advantage of the powerful Object Model that this newly developed version offers to programmers. See you in the last part!

[gp-comments width="770" linklove="off" ]

antalya escort bayan antalya escort bayan Antalya escort diyarbakir escort