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

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!