Guide to simple Database Abstraction

A database abstraction layer is an application programming interface which unifies the communication between a computer application and databases such as MySQL, Oracle or SQLite. Traditionally, all database vendors provide their own interface tailored to their products which leaves it to the application programmer to implement code for all database interfaces he would like to support. Database abstraction layers reduce the amount of work by providing a consistent API to the developer and hide the database specifics behind this interface as much as possible.

I will try to guide you through creating your own database abstraction php class.

1. Creating database object

Define abstraction class.

class Dapc {
}

Next thing we will do is create class method which will allow you to load specific data into php object. This method we will name init(). This method will need primary key, table name and array with table fields. We will define them later, don’t worry about that.

class Dapc {

  function init() {
    global $mysqli; // db connection holder... object.
    $mysqli->stmt_init();
    $key = $this->primary_key; // primary key
    $sql = "SELECT * FROM ".$this->table_name." where ".$this->primary_key." = ".$this->$key;
    $stmt = $mysqli->query($sql) or trigger_error('SQL QUERY ERROR: '.$sql);
    $obj = $stmt->fetch_array();
    foreach($this->fields as $k=>$v) {
      $this->$k = $obj[$k];
    }
    $this->object_loaded = 'Y';
    return true;
  }
}

Now what we have done here? We created sql query which will select all fields from given table ($this->table_name) which primary key ($this->primary_key) has value $this->$key and then loaded all those fields in single object.

Abstraction Interface

Above we have created bottom layer of our db abstraction. To use this we will need another layer which I call “interface”. This layer will keep you away from database. Lets create it.

First say we have example mysql table looking like this:

CREATE TABLE email
(
   ID INT NOT NULL AUTO_INCREMENT,
   name VARCHAR(35) NOT NULL,
   email VARCHAR(35) NOT NULL,
   PRIMARY KEY (ID)
);

Now that we have mysql table we can create little interface class. This class will extend our Dapc class. We will name this class something like {table_name}DAPC and define some variables. First primary key of our table (email) which is ID, next table name and finally table fields array. Table fields should be exactly like our fields in DB table. Form is ‘field_name’=>’field_type’ finally we will define simple flag which will tell us if object is loaded or not.

class emailDAPC extends Dapc {
  var $primary_key = 'id';
  var $table_name = 'email';

  protected $fields = array(
   'id'=>'int',
   'name'=>'string',
   'email'=>'string'
  );
  var $object_loaded = 'N';
}

Next we will create method for emailDAPC class called initData(). It is very simple and will only call init method from our DAPC class.

function initData() {
    $this->init();
 }

And constructor…

 function emailDAPC($id=-1) {
    if ($id!=-1) {
      $key = $this->primary_key;
      $this->$key = $id;
      $this->initData($id);
    }
    return true;
  }

Lets see what have we done so far… If we load class with value 12, email object will load fields and values from table email.

$email = new emailDAPC(12);
print_r($email);
/*Output
email Object
(
    ...
    [object_loaded] => 'Y'
    [id] => 12
    [email] => 'person@example.com'
    [name] => 'Some Person'
    ...
)
*/

Inserting/Updating DB data

To insert data in table we will need to create another method in bottom layer. This method will be called insert(). We will do similar thing as with init() :

  function insert() {
    global $mysqli;
    unset($stmt);
    $i = 0;
    foreach($this->fields as $k=>$v) {
      if ($obj->$k != $this->$k) {
        if ($i==0) $a = '';
        else $a = ' , ';
        if ($v=='int') $this->sql_prep .= $a.$k.'='.$this->$k;
        else $this->sql_prep .= $a.$k.'="'.$this->$k.'"';
        $i++;
      }
    }
    $sql = "INSERT INTO ".$this->table_name." SET ".$this->sql_prep;

    trigger_error('--> INSERT');
    trigger_error('Executing SQL: '.$sql);
    $mysqli->stmt_init();

    $stmt = $mysqli->prepare($sql) or trigger_error('SQL QUERY ERROR: '.$sql);
    $stmt->execute();
  }

We looped fields array (remember that we defined it in interface class) and create query. Very simple.

To be able to update rows we will create update method:

  function update() {
    global $mysqli;
    $mysqli->stmt_init();
    $key = $this->primary_key;
    $sql = "SELECT * FROM ".$this->table_name." where ".$this->primary_key." = ".$this->$key;
    $stmt = $mysqli->query($sql) or trigger_error('SQL QUERY ERROR: '.$sql);;
    $obj = $stmt->fetch_array();
    $i = 0;
    foreach($this->fields as $k=>$v) {
      if ($obj->$k != $this->$k) {
        if ($i==0) $a = '';
        else $a = ' , ';
        if ($v=='int') $this->sql_prep .= $a.$k.'='.$this->$k;
        else $this->sql_prep .= $a.$k.'="'.$this->$k.'"';
        $i++;
        trigger_error('PREPARE: '.$obj->$k.'!='.$this->$k.' ('.$key.'='.$this->$key.')');
      }
    }
    unset($stmt);
    $mysqli->stmt_init();
    $sql = 'UPDATE '.$this->table_name.' SET '.$this->sql_prep.' WHERE '.$this->primary_key.'='.$this->$key;
    trigger_error('--> UPDATE');
    trigger_error('Executing SQL: '.$sql);
    $stmt = $mysqli->prepare($sql) or trigger_error('SQL QUERY ERROR: '.$sql);
    $stmt->execute();
  }

Again same thing, build query from fields array and exec.

Now to simplify… we will create another method called saveDapc() If you remember we defined that flag $this->object_loaded we will use this flag so we can know what action is needed (insert or update). If object is loaded and saveDapc() method is called we will assume that update is needed and thus call update method and vice versa.

  function saveDapc() {
    if ($this->object_loaded=='Y') {
      $this->update();
    } else {
      $this->insert();
    }
  }

Saving from interface

With all this now it is very easy to insert/update database from interface. All we need to do is make another method called save() in our email table interface.

  function save() {
    if (!$this->validate()) {
      return false;
    }
    $this->saveDapc();
  }
 function validate() {
  return true;
 }

Here is how you will save data:

Insert

$email = new emailDAPC();
$email->name='Some person';
$email->email='name@example.com';
$email->save();
// That's all! Data inserted in DB

Now say we want to change email for some record. We will simply load it with it’s id:

$email = new emailDAPC(12);
$email->email='newmail@example.com';
$email->save();
// That's all! Email is changed!

Data delete can be done the same way as, say… update. Simply load object and call method delete().

As you see with db abstraction it is much easier/faster to work. Beside speed this will also allow to migrate to another sql server with only changing bottom level class.