Build Database Interfaces

This article is about using and building Database Interfaces (DB interfaces). DB interfaces make application development faster and easier. This is accomplished by relocating scattered DML (Insert, Update, Delete) statements into a single shared tool, the Class. A Class encapsulates data post processing, integrity, and security into a single tool that can be accessed throughout the application.

Over the past couple of years many tools have been developed to address the limitations of PHP when building large database applications. These tools can be divided into two categories:

  • Database Abstraction
  • HTML Generation

DB Interfaces are a new category of tools that sits between the application logic and the database logic.

The Big Picture
PHP was designed to be a “quick and simple” tool for making web pages dynamic. However, it does not scale well to building full-blown applications without a lot of planning and design. While PHP can make building a solitary web page “quick and simple”, applications often become ‘quick and dirty’.

When an application reaches a certain size, the rules change about how it should be built. The design rules for a single HTML page with some embedded PHP are much different from a 15,000 line PHP application. As the complexity of an application grows, manageability decreases. Although PHP is designed to be embedded into HTML, this ability becomes inhibiting as the application becomes more complex.

Embedded SQL suffers from the same limitations as imbedded PHP. As application size grows, maintainability becomes more difficult.

The Solution
Separating the three common components of PHP applications is the best solution. The common components are:

  • A database
  • The PHP logic
  • The layout (in HTML)
Tools such as ADODB and Smarty are commonly used to aid this separation. ADODB makes it easier for applications to support different databases systems. Smarty separates the display logic and HTML from the application logic.

A more manageable solution is using a Database interface to communicate with the database. DB Interfaces separate the database logic from the application logic. By using a DB interface, DML operations are separate from the logic flow, which makes code easier to work with.

It is best to use a database interface to handle the DML operations on a table. By using a database interface to provide a single point of data modification, a finer level of security and data integrity can be achieved.

{mospagebreak title=Replacement Example}
Here is an example in which a database interface has replaced an embedded DML operation: [code]<?php

/* Update the client's contact information with the data from $_POST */

$userID     = (int) $_POST['userID'];
$email      = trim(addslashes($_POST['email']));
$firstname  = trim(addslashes($_POST['firstname']));
$lastname   = trim(addslashes($_POST['lastname']));
$address1   = trim(addslashes($_POST['address1']));
$address2   = trim(addslashes($_POST['address2']));
$city       = trim(addslashes($_POST['city']));
$province   = trim(addslashes($_POST['province']));
$country    = trim(addslashes($_POST['country' ]));

$DML = 'UPDATE clients SET '.
       "email     = '$email',".
       "firstname = '$firstname',".
       "lastname  = '$lastname',".
       "address1  = '$address1',".
       "address2  = '$address2',".
       "city      = '$city',".
       "province  = '$province',".
       "country   = '$country',".
       "WHERE userID=$userID";
if ($db->Execute($DML))
{
    // do error handling
}

? >
<?php

$client = new Client();
$client->setUserID    ( $_POST['userID'   );
$client->setEmail     ( $_POST['email'    );
$client->setFirstname ( $_POST['firstname');
$client->setLastname  ( $_POST['lastname' );
$client->setAddress1  ( $_POST['address1' );
$client->setAddress2  ( $_POST['address2' );
$client->setCity      ( $_POST['city'     );
$client->setProvince  ( $_POST['province' );
$client->setCountry   ( $_POST['country'  );

if ($client->submit($db) !== true)
{
    // do error handling
}

? >
[/code]
The example above demonstrates a reduction in Application Logic. The DB interface handles all communications with the database, and the programmer can focus on the success or failure of the operation. Using less code to do the same task is most beneficial when there are many parts of the application performing the same DML operations. Rather than duplicating DML queries, you simply use the necessary database interface.

{mospagebreak title=Lessen the Impact}
Another advantage to using a DB interface is that changes to the structure of the database have less of an impact on the application. Changing a single DB interface is far easier than finding and modifying code and embedded SQL throughout an entire application.

Creating a Database Interface
There are a few general guidelines that you should follow when designing your database interface:

  • Use an object oriented approach and follow OOP principles.
  • Make the DB interface autonomous and with a single purpose.
  • Base the DB interface on a standardized design.

Use an Object-Oriented Approach
A class should be used to create each DB interface you require. This class should be a logical (code) representation of a single database structure. The members of the class represent each column of a row. The methods of the class should perform the DML and DQL operations on the row. The example below illustrates how DDL and DQL operations are implemented through the load(), submit() and delete() methods.
[code]
<?php

class Client
{
 var $clientID       = 0;
 var $firstName      = '';
 var $lastName       = '';
 var $emailAddress   = '';

function Client() {}
function load(&$db,$clientID)
{
 $DQL = 'SELECT clientID,firstName,lastName,emailAddress '.
  'FROM client '.
   "WHERE clientID=$clientID";

if ($row = $db->GetOne())
  {
   $clientID = $row[ 'clientID' ];
   $firstName = $row[ 'firstName' ];
   $lastName = $row[ 'lastName' ];
   $emailAddress = $row[ 'emailAddress' ];

return true;
   } else {
   return false;
   }
  }

function submit(&$db)
  {
   // clean up the data.
   $clientID = (int) $this->clientID;
   $firstName = addslashes(trim( $firstName    ));
   $lastName  = addslashes(trim( $lastName     ));
   $emailAddress = addslashes(trim( $emailAddress ));

if ($clientID == 0)
   {
   $DML = 'INSERT INTO client (clientID,firstName,lastName, '.emailAddress) VALUES (NULL,'. "'$firstName','$lastName','$emailAddress')";

if ($db->Execute($DML))
   {
   $this->clientID = $db->Insert_ID();
   return true;
   } else {
   return false;
   }
     } else {
     $DML = 'UPDATE client SET '.
      "firstName    = '$firstName    ,' ".
      "lastName     = '$lastName     ,' ".
      "emailAddress = '$emailAddress ,' ".
      "WHERE clientID= $clientID";

if ($db->Execute($db))
      {
       return true;
      } else {
       return false;
      }
    }
   }

function delete(&$db)
  {
   $DML = 'DELETE FROM client WHERE clientID='.$this->clientID;
   if ($db->Execute($db))
    {
     return true;
      } else {
     return false;
      }
    }

function getClientID()
  {
  return $this->clientID;
  }

function setClientID($clientID)
  {
  $this->clientID = $clientID;
  }
    
function getFirstName() 
  { 
  return $this->firstName; 
  }

function setFirstName($firstName) 
  { 
  $this->firstName = $firstName; 
  }

function getLastName() 
  {
  return $this->lastName; 
  }

function setLastName($lastName)
  {
  $this->lastName = $lastName;
  }

function getEmailAddress()
  {
  return $this->emailAddress;
  }

function setEmailAddress($emailAddress)
  {
  $this->emailAddress = $emailAddress;
  }
}
? >[/code]{mospagebreak title=Autonomous With a Single Purpose}


Design the DB interface for a single purpose. This purpose is usually creating, updating and deleting a row of data. DB interfaces should be as autonomous as possible because that makes it easier to use them as a group. It is easier to combine the operations of multiple interfaces when each interface has a well-defined set of operations. (See example below.)

Standardized Design
Large applications that have many database tables result in many DB interfaces. Standardizing the way database interfaces are built makes them a lot easier to work with and become familiar with. Use a standard naming scheme, layout and implementation to simplify using multiple interfaces together.

Putting the Pieces Together
A single DB Interface is fairly limited in its usefulness. DB interfaces become very useful when their functionality is grouped together to perform a real task. Here is an example in which multiple database interfaces are combined to complete a new message posting on a forum:
[code]
<?php
/* Notes:
   $db    - instance of ADOdb connection object.
   $user  - instance of the User DB Interface
   $topic - instance of the Topic DB Interface
*/
$db->StartTrans();

// update the user's total # of posts
$user->setNumPosts($user->getNumPosts() + 1);
$user->submit($db);

// update the topics # of messages
$topic->setNumMessages($topic->getNumMessage() + 1);
$topic->submit($db);

// create the new message
$message = new Message();
$message->setTopicID($topic->getTopicID());
$message->setTopic($_POST['userTopic']);
$message->setMessage($_POST['message']);
$message->setPoster($user->getUserID());
$message->submit($db);

$db->CompleteTrans();

? > [/code]Conclusion
This article provided an introduction to using the DB interface design model for creating applications. As PHP applications become increasingly complex, they inevitably become more difficult to manage. Database interfaces provide a mechanism and a methodology for building more manageable applications faster and easier.

    Google+ Comments

    Google+ Comments