Building the data access layer - PHP

In reality, before creating a concrete class tasked with talking directly to MySQL, we need to define a simple contract. This contract must be implemented by this class, and by other classes responsible for interacting with different RDBMS. In doing so, it'll be possible to create easily "pluggable" database adapters that aren't tied to a specific implementation.

For obvious reasons, the contract is nothing but an interface, whose definition looks like this: 

(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();
}

As you can see above, the "DatabaseAdapterInterface" interface defines a set of methods which must be implemented later by any adapter class that works with a specific database server. In many popular frameworks, such as the Zend Framework and Symfony, common functionality shared by different database adapters is usually encapsulated within an abstract class. Since in this case I plan to use only a single MySQL adapter, I decided to define a single interface, which is open to further implementations.

So far, so good. Now that you understand the purpose of coding the previous interface, it's time to  create the MySQL abstraction class mentioned before.

The source code of this class will be shown in the next section. Thus, to get there, just keep reading. 

Creating an implementer of the previous interface: building a basic MySQL abstraction class

For demonstration purposes, the underlying persistence mechanism that will be used for storing and retrieving all of the user objects spawned from the "User" class will be a MySQL database. Therefore, it's mandatory to build a class capable of interacting with that database server through a simple API.

This class is naturally an implementer of the previous "DatabaseAdapterInterface" interface. Its source code is shown below:    

(MySQLAdapter.php)

<?php

class MySQLAdapter implements DatabaseAdapterInterface
{
    protected $_config = array();
    protected $_link;
    protected $_result;
    protected static $_instance;
   
    /**
     * Get the Singleton instance of the class
     */
    public static function getInstance(array $config = array())
    {
        if (self::$_instance === null) {
            self::$_instance = new self($config);
        }
        return self::$_instance;
    }
   
    /**
     * Class constructor
     */
    protected function __construct(array $config)
    {
        if (count($config) !== 4) {
            throw new MySQLAdapterException('Invalid number of connection parameters.');  
        }
        $this->_config = $config;
    }
   
    /**
     * Prevent cloning the instance of the class
     */
    protected function __clone(){}
   
    /**
     * 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)
    {
        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_array($this->_result, MYSQLI_ASSOC))) {
                $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{}

Don't feel overwhelmed by the rather lengthy definition of the above "MySQLAdapter" class. The logic that drives it is fairly easy to follow, trust me. Basically, the class is a simple wrapper for the "mysqli" PHP extension, which exposes a set of discrete methods that perform some common operations on the database server. These operations include fetching table rows, executing selects, insertions/updates and deletions. Naturally, it's possible to swap this database class with the one included with the framework of your choice (if you use one), so feel free to skip over this explanation.

Anyway, with the inclusion of the "MySQLAdapter" class, the data access layer of this example is now complete. Even though it's valid to note that the pair of sample layers defined so far can't work together yet, their respective implementations have brought us closer to the construction of a user repository. At the risk of being repetitive, bear in mind that this is a step-by-step development process, so be patient for now. In the end, the wait will be really worthwhile.

Final thoughts

In this second installment of the series, I created a simple data access layer. It was comprised of a single interface and a basic MySQL abstraction class whose underlying logic was hopefully easy to catch.

Having already created two independent sample layers, the next logical step is to define one more, responsible for talking to both while keeping them isolated from each other. As you may have guessed, this extra layer will be composed of the data mappers, and in the course of the coming tutorial I'll show you how to implement them in a pretty straightforward fashion.

Don't miss the next part!