Home arrow PHP arrow Page 4 - Building Object-Oriented Database Interfaces in PHP: Working with Multiple Data Access Objects

Some useful examples: a practical usage of the "DBIGenerator" class - PHP

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.

TABLE OF CONTENTS:
  1. Building Object-Oriented Database Interfaces in PHP: Working with Multiple Data Access Objects
  2. Getting in touch with a DB Interface: A brief look at the "DBI Generator" class
  3. Code generator in a nutshell: one class that generates another one
  4. Some useful examples: a practical usage of the "DBIGenerator" class
  5. Adding rows to both tables: working with multiple data access objects
By: Alejandro Gervasio
Rating: starstarstarstarstar / 15
August 24, 2005

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

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!



 
 
>>> More PHP Articles          >>> More By Alejandro Gervasio
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

PHP ARTICLES

- Hackers Compromise PHP Sites to Launch Attac...
- Red Hat, Zend Form OpenShift PaaS Alliance
- PHP IDE News
- BCD, Zend Extend PHP Partnership
- PHP FAQ Highlight
- PHP Creator Didn't Set Out to Create a Langu...
- PHP Trends Revealed in Zend Study
- PHP: Best Methods for Running Scheduled Jobs
- PHP Array Functions: array_change_key_case
- PHP array_combine Function
- PHP array_chunk Function
- PHP Closures as View Helpers: Lazy-Loading F...
- Using PHP Closures as View Helpers
- PHP File and Operating System Program Execut...
- PHP: Effects of Wrapping Code in Class Const...

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: