Factoring a database interface class: the "DBIGenerator" class into action - PHP

I firmly believe that dynamically building DB interfaces will be best understood by example. Bearing that concept in mind, let's build an example that implements the "DBIGenerator" class and quickly creates a "User" DB interface class, which behaves as a centralized mechanism to access rows in a sample "users" table. Here's the corresponding code:

// include MySQLConnector wrapping class
require_once 'mysqlclass.php';
// include the "DBIGenerator" class
require_once 'dbigeneratorclass.php';
// instantiate a new MySQLConnector object and connect to MySQL
$db=&new MySQLConnector(array('host'=>'host','user'=>'user'
      ,'password'=>'password','database'=>'databasename'));
// set class properties-accessors and modifiers
$options=array('id','firstname','lastname','email');
// instantiate a DBIGenerator object
$gn=&new DBIGenerator('User','DBICLASSES/',$options);
// create User class
$gn->create();
// get $user object
if(!$user=$gn->getObject()){
  die('Failed to create object');
}
echo 'Database interface successfully created';

The above example demonstrates how simple it is to create a DB interface class. First, I include the necessary class to connect to MySQL, along with the "DBIGenerator" class. Then, I define an array that contains the table field names that will be associated with the DB interface. Remember that I'm doing this from scratch, but in a later version, the process will be completely handled by the class.

Next, I pass to the "DBIGenerator" object the following parameters: the name of the DB Interface class (i.e. "User"), the path where I want the class file to be created, and finally the array containing the table field names.

Finally, the "create()" method is invoked, creating the class file at the specified location. Once the class is available to be used, I'm able to obtain a new $user object, by calling the "getObject()" method.

After executing the above snippet, a DB Interface class has been nicely generated inside a DBICLASSES/ directory, and looks like this:

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 submit(&$db){
    $sql="INSERT INTO users SET firstname='$this->firstname',lastname='$this->lastname',email='$this->email'";
    $db->performQuery($sql);
    $this->id=&$db->getInsertID();
  }
  function load(&$db){
    $sql="SELECT * FROM users WHERE id='$this->id'";
    $result=$db->performQuery($sql);
    $row=$result->fetchRow();
    return $row;
  }
  function update(&$db){
    $sql="UPDATE users SET firstname='$this->firstname',lastname='$this->lastname',email='$this->email' WHERE id='$this->id'";
    $db->performQuery($sql);
  }
  function delete(&$db){
    $sql="DELETE FROM users WHERE id='$this->id'";
    $db->performQuery($sql);
  }
}

Wasn't that great? The "User" class is a logical representation of the sample "users" table, where each property represents a table field. Even better, it also presents the corresponding setters and getters methods for each property, as well as the usual DML statements (insert, update, delete) packaged into several class methods. Through such a useful structure, we can establish a single point to access the "users" table and select, insert, update, or delete rows according to the application's logic.

This is a brief example. If we ever need to add a new user to the table, we'd do it in the following way:

$user->setfirstname('Homer');
$user->setlastname('Simpson');
$user->setemail('homer@springfield.com');
// insert row into database table
$user->submit($db);
echo 'Row inserted successfully';

It's really that simple. By using the methods provided in the DB interface, we've added a new user to the table. As you might guess, the other row operations can be executed in a similar manner.

Summary

In this first part, I've demonstrated how DB interfaces can help to maintain SQL code within a class structure, providing a single interaction point between the database logic and the application logic. Of course, the sample "DBIGenerator" class is still immature, since it's not capable of programmatically handling table fields. Also, all of the SQL statements use a hard-coded table name within the code.

In the next part of the series, these issues will be properly addressed, by improving the class and showing several examples to use DB Interfaces. Indeed, the topic is very promising. You won't want to miss it!