Building MySQL Abstraction Classes - MySQL

Implementing the model’s data access layer: building a MySQL abstraction class

The user model will be able to interact with the storage layer through a basic MySQL accessing class, which implements the following segregated interface. Check it out: 

(DatabaseAdapterInterface.php)

<?php

interface DatabaseAdapterInterface
{
    public function connect();
   
    public function disconnect(); 
   
    public function query($query);
   
    public function fetch(); 
   
    public function select($table, $where, $fields, $order, $limit, $offset);
   
    public function insert($table, array $data);
   
    public function update($table, array $data, $where);
   
    public function delete($table, $where);
   
    public function getInsertId();
   
    public function countRows();
   
    public function getAffectedRows();
}


The following class implements the pertaining interface and also is a simple wrapper for the “mysqli” PHP extension:

(MySQLAdapter.php)

<?php

class MySQLAdapter implements DatabaseAdapterInterface
{
    protected $_config = array();
    protected $_link;
    protected $_result;
   
    /**
     * Constructor
     */
    public function __construct(array $config)
    {
        if (count($config) !== 4) {
            throw new MySQLAdapterException('Invalid number of connection parameters.');  
        }
        $this->_config = $config;
    }
       
    /**
     * Connect to MySQL
     */
    public function connect()
    {
        // connect only once
        if ($this->_link === null) {
            list($host, $user, $password, $database) = $this->_config;
            if ((!$this->_link = @mysqli_connect($host, $user, $password, $database))) {
                throw new MySQLAdapterException('Error connecting to MySQL : ' . mysqli_connect_error());
            }
            unset($host, $user, $password, $database);      
        }
    }

    /**
     * Execute the specified query
     */
    public function query($query)
    {
        if (!is_string($query) || empty($query)) {
            throw new MySQLAdapterException('The specified query is not valid.');  
        }
        // lazy connect to MySQL
        $this->connect();
        if (!$this->_result = mysqli_query($this->_link, $query)) {
            throw new MySQLAdapterException('Error executing the specified query ' . $query . mysqli_error($this->_link));
        }
    }
   
    /**
     * Perform a SELECT statement
     */
    public function select($table, $where = '', $fields = '*', $order = '', $limit = null, $offset = null)
    {
        $query = 'SELECT ' . $fields . ' FROM ' . $table
               . (($where) ? ' WHERE ' . $where : '')
               . (($limit) ? ' LIMIT ' . $limit : '')
               . (($offset && $limit) ? ' OFFSET ' . $offset : '')
               . (($order) ? ' ORDER BY ' . $order : '');
        $this->query($query);
        return $this->countRows();
    }
   
    /**
     * Perform an INSERT statement
     */ 
    public function insert($table, array $data)
    {
        $fields = implode(',', array_keys($data));
        $values = implode(',', array_map(array($this, 'quoteValue'), array_values($data)));
        $query = 'INSERT INTO ' . $table . '(' . $fields . ')' . ' VALUES (' . $values . ')';
        $this->query($query);
        return $this->getInsertId();
    }
   
    /**
     * Perform an UPDATE statement
     */
    public function update($table, array $data, $where = '')
    {
        $set = array();
        foreach ($data as $field => $value) {
            $set[] = $field . '=' . $this->quoteValue($value);
        }
        $set = implode(',', $set);
        $query = 'UPDATE ' . $table . ' SET ' . $set
               . (($where) ? ' WHERE ' . $where : '');
        $this->query($query);
        return $this->getAffectedRows(); 
    }
   
    /**
     * Perform a DELETE statement
     */
    public function delete($table, $where = '')
    {
        $query = 'DELETE FROM ' . $table
               . (($where) ? ' WHERE ' . $where : '');
        $this->query($query);
        return $this->getAffectedRows();
    }
   
    /**
     * Single quote the specified value
     */
    public function quoteValue($value)
    {
        $this->connect();
        if ($value === null) {
            $value = 'NULL';
        }
        else if (!is_numeric($value)) {
            $value = "'" . mysqli_real_escape_string($this->_link, $value) . "'";
        }
        return $value;
    }
   
    /**
     * Fetch a single row from the current result set (as an associative array)
     */
    public function fetch()
    {
        if ($this->_result !== null) {
            if ((!$row = mysqli_fetch_object($this->_result))) {
                $this->freeResult();
                return false;
            }
            return $row;
        }
    }

    /**
     * Get the insertion ID
     */
    public function getInsertId()
    {
        return $this->_link !== null ?
               mysqli_insert_id($this->_link) :
               null; 
    }
   
    /**
     * Get the number of rows returned by the current result set
     */ 
    public function countRows()
    {
        return $this->_result !== null ?
               mysqli_num_rows($this->_result) :
               0;
    }
   
    /**
     * Get the number of affected rows
     */
    public function getAffectedRows()
    {
        return $this->_link !== null ?
               mysqli_affected_rows($this->_link) :
               0;
    }
   
    /**
     * Free up the current result set
     */
    public function freeResult()
    {
        if ($this->_result !== null) {
            mysqli_free_result($this->_result);  
        }
    }
   
    /**
     * Close explicitly the database connection
     */
    public function disconnect()
    {
        if ($this->_link !== null) {
            mysqli_close($this->_link);
            $this->_link = null;
        }
    }
   
    /**
     * Close automatically the database connection when the instance of the class is destroyed
     */
    public function __destruct()
    {
        $this->disconnect();
    }
}


(MySQLAdapterException.php)

<?php

class MySQLAdapterException extends Exception{}


This adapter implements a set of discrete methods (most of them declared in the earlier “DatabaseAdapterInterface” interface), which permit you to perform some common operation in a specified MySQL database, such as executing SELECT, INSERT, UPDATE and DELETE commands, fetching and counting rows, etc.

So far, so good. Having outlined how this abstraction class works, it’s time to leave the boring things behind and face the fun ones. With the earlier caching and data access layers already up and running, the next step that must be taken is… yes, to define the sample user model!

>>> More MySQL Articles          >>> More By Alejandro Gervasio

blog comments powered by Disqus