Martyn Bissett

Tutorials | Database Abstraction Layer

It is sometimes recommended that when querying a database from you scripts you apply a layer of abstraction where all database specific commands (ie. mysql_query) are hidden from the script. See below:

Script without abstraction layer:

$sql = "INSERT INTO cars (make, model) VALUES ('Toyota','Yaris')";
mysql_query($sql, $dbConn);

Script with abstraction layer:

// $database is an instance of a database abstraction class
$arFieldValues = array(
  'make' => 'Toyota',
  'model' => 'Yaris'
);
$database->Insert('cars', $arFieldValues);

The benefits may not be entirely obvious from the above code but for me it demonstrates two advantages:

  1. the script doesn't need to know (or really care for that matter) what database we are connecting to because this is all handled within the database abstraction class. Should at any time the technology for accessing data change, we only need to replace the class and not the scripts that use it .. so long as they all use the same method interface and the class returns the same data format then the application can function as usual
  2. implementation time is made so much easier because we don't have to worry about typing out the same SQL statements or database function, only the values or conditions in which we want to insert, update, delete, etc need passing. All the rest once written correctly initially within the class is in place.

.. and with all pre-defined classes, it is completely re-usable throughout the application.

The complete Database class

class Database {

private $dbConn;

public function __construct ()
{
  $this->dbConn = @mysql_connect('localhost', 'root', 'pass');
  if(! $this->dbConn) {
    echo "Connection to database server could not be made!";
    exit();
  }

  if (! mysql_select_db('test', $this->dbConn)) {
    echo "Unable to connect to ".DB_NAME." database!";
    exit();
  }
}

public function Select($sql)
{
  $result = mysql_query($sql, $this->dbConn);
  $arReturn = array ();
  if(mysql_num_rows ($result) > 0) {
    while($row = mysql_fetch_assoc ($result)) {
      array_push($arReturn, $row);
    }
  }
  return $arReturn;
}

public function Insert($table, $arFieldValues)
{		
  $arKeys = array();
  $arValues = array();
  foreach($arFieldValues as $key => $value) {
    array_push ($arKeys, $key);
    if (is_string ($value)) {
      array_push ($arValues, '\''.mysql_escape_string ($value).'\'');
    } else if (is_null($value)) {
      array_push ($arValues, 'null');
    } else {
      array_push ($arValues, $value);
    }
  }
  $keys = implode (', ', $arKeys);
  $values = implode (', ', $arValues);
  
  $sql = "INSERT INTO $table ($keys) VALUES ($values)";
  
  if(! mysql_query ($sql, $this->dbConn))
    return false;
  
  return true;
}

public function Update ($table, $arFieldValues, $arConditions)
{
  $arSet = array();
  foreach ($arFieldValues as $key => $value) {
    if (is_string ($value)) {
      $set = $key . " = " . '\''.mysql_escape_string ($value).'\'';
    } else if (is_null($value)) {
      $set = $key . ' = null';
    } else {
      $set = $key . " = " . $value;
    }
    
    array_push ($arSet, $set);
  }
  
  $arCond = array();
  foreach ($arConditions as $key => $value) {
    if (is_string ($value)) {
      $cond = $key . " = " . '\''.mysql_escape_string ($value).'\'';
    } else if (is_null($value)) {
      $cond = $key . ' IS NULL';
    } else {
      $cond = $key . " = " . $value;
    }
    
    array_push ($arCond, $cond);
  }
  
  $sSet = implode (', ', $arSet);
  $sCond = implode (' and ', $arCond);
  
  $sql = "UPDATE $table SET $sSet WHERE $sCond";
  
  if (mysql_query ($sql, $this->dbConn))
    return false;
  
  return true;
}

public function Delete ($table, $arConditions, $limit = null)
{
  $arCond = array();
  foreach ($arConditions as $key => $value) {
    if (is_string ($value)) {
      $cond = $key . " = " . '\''.mysql_escape_string ($value).'\'';
    } else {
      $cond = $key . " = " . $value;
    }
  
    array_push ($arCond, $cond);
  }
  
  $sCond = implode (' and ', $arCond);
  
  $sql = "DELETE FROM $table WHERE $sCond";
  
  if ($limit)
    $sql.= " LIMIT $limit";
  
  if (mysql_query ($sql, $this->dbConn))
    return false;
  
  return true;
}

public function GetInsertID ()
{
  return mysql_insert_id ();
}

public function __destruct ()
{
  if (is_resource ($this->dbConn)) {
    mysql_close($this->dbConn);
  }
}
}

So to create an instance of our class which will automatically connect to the database for use, we would use the following in our script:

require_once('class/database.php');

$database = new Database;

Simple! Now lets see how we might use and manipulate the data within that database

Update

In this example we will update any rows which have a model value of 'Yaris' to 'Yaris Vitz':

$arFieldValues = array(
  'model' => 'Yaris Vitz';
);
$arConditions = array(
  'model' => 'Yaris';
);
$database->Update('cars', $arFieldValues, $arConditions);

Insert

Here we will insert a new row into the cars table.

$arFieldValues = array(
  'make' => 'Fiat',
  'model' => 'Punto'
);
$database->Insert('cars', $arFieldValues);

Delete

This method allows us to delete entries that match our conditions passed. This example will delete all rows that have a value of 'Toyota' for make.

$arConditions = array(
  'make' => 'Toyota'
);
$database->Delete('cars', $arConditions);

This is a very basic means of deleting rows but in most cases I've found it is all I need, however, for more complicated SQL queries that perhaps require a wider variety of operators (ie. LIKE), I add a Run() method to the class that basically just does a mysql_query and returns true or false. See below:

$sql = "DELETE FROM cars WHERE make LIKE 'Toyo%'";
$database->Run($sql);

Select

In many cases I find, select statements are a little more complex than simply adding and updating data so I resort to passing my SQL queries from the script. Because SQL is widely supported by all databases it doesn't really matter if you were to change because its likely that your same SQL would work still.

The Select() method works by passing an SQL statement and getting back a PHP associative array.

$sql = "SELECT * FROM cars";
$result = $database->Select($sql);

The returned array in $result can be used with a foreach loop in the same way you might use mysql_fetch_array function in a while loop. For example:

foreach($result as $row) {...

GetInsertID

Finally the GetInsertID() method is a handy function Ive left in this tutorial example (as you can expand the class to cater for however you might query a database) which you would use after performing an Insert() to get the id attribute of the new row

$arFieldValues = array(
  'make' => 'Fiat',
  'model' => 'Punto'
);
$database->Insert('cars', $arFieldValues);

$carsId = $database->GetInsertID();

So that summarises the basics of a database abstraction class and for most small to medium applications, that is all you'll ever need. Now before I close the tutorial there is one other technique that is worthwhile using here and that is the singleton pattern.

Singleton Pattern

The singleton pattern encourages that for multiple occurrence of an object, only one instance is required. So if we have an instance of our database abstraction class within other objects, unless we are referring to only a single instance of our class our overhead of the application will be increased which could affect performance.

Database classes make the perfect excuse to use the singleton pattern as we are only ever using its methods as tools rather than storing individual entries of data where each object must be a unique instance of that class.

By adding the getInstance() method to instatiate the object, a reference to that initial instance will be made and not a new object. If no instance has been made, a new object instance will be made only then. Also, by making the __construct private you will force yourself or anyone using the class to use the getInstance method.

class Database {
private function __construct() {
  .
  .
  .
}

public function getInstance()
{
  static $objDB;
  
  if(! isset ($objDB)) {
    $objDB = new Database;
  }
  
  return $objDB;
}
.
.
.
}

$Database = Database::getInstance();