Building a simple data access layer - PHP

I want to make the previous mapping layer flexible enough to switch over different database adapters at runtime. The easiest way to achieve this is by coding an interface, instead of relying heavily on a concrete implementation. 

In this particular case, the interface in question is the same one used here (http://www.devshed.com/showblog/41685/Roll-Your-Own-Repository-in-PHP-the-Data-Access-Layer/) and the contract that it defines is the following:

(MyApplication/Database/DatabaseAdapterInterface.php)

<?php

namespace MyApplicationDatabase;

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

Even though the use of interfaces is still somewhat limited in the PHP field (especially when considering the weakly-typed nature of the language), the one above is really useful for implementing a pluggable structure that permits you to inject different types of database adapters into the mappers previously created.

With this interface defining the contract for generic adapters, it’s fairly simple to create one that works specifically with MySQL. To prove this, in the following segment I’ll be creating a concrete adapter, so you can see how it fits into the structure of this sample application. 

Data access layer final touches: implementing a basic MySQL adapter

Constructing an adapter capable of performing some typical operations in a MySQL database is simple. One must only create an implementer of the earlier “DatabaseAdapterInterface” interface and the job is done.

Since I want to keep things clear and graspable, the MySQL adapter that I’ll be using is quite similar to the one coded in some previous articles published here at the Developer Shed Network. Therefore, the logic of the following class should be quite familiar to you. Check it out: 

(MyApplication/Database/MysqlAdapter.php)

<?php

namespace MyApplicationDatabase;

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) {
            return $this->_link;
        }
        list($host, $user, $password, $database) = $this->_config;
        if (($this->_link = @mysqli_connect($host, $user, $password, $database))) {
            unset($host, $user, $password, $database);
            return $this->_link;
        }
        throw new MySQLAdapterException('Error connecting to the server : ' . mysqli_connect_error());
    }

    /**
     * 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)) {
            return $this->_result;
        }
        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();
    }
   
    /**
     * Escape 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_array($this->_result, MYSQLI_ASSOC)) !== false) {
                return $row;
            }
            $this->freeResult();
            return false;
        }
        return null;
    }

    /**
     * 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);
            return true;
        }
        return false;
    }
   
    /**
     * Close explicitly the database connection
     */
    public function disconnect()
    {
        if ($this->_link !== null) {
            mysqli_close($this->_link);
            $this->_link = null;
            return true;
        }
        return false;
    }
   
    /**
     * Close automatically the database connection when the instance of the class is destroyed
     */
    public function __destruct()
    {
        $this->disconnect();
    }
}

(MyApplication/Database/MysqlAdapterException.php)

<?php

namespace MyApplicationDatabase;

class MysqlAdapterException extends Exception{}

As you can see from the code above, the “MysqlAdapter” class is nothing but a basic adapter that can be used for performing common CRUD SQL statements against a specified database table, counting and fetching rows in result sets, and so forth. The adapter itself is optional and can be easily replaced with the database class included with the framework of your choice, as long as you make it an implementer of the previous interface. So, if you want use that option, go for it.

With that said, it’s time to summarize what we’ve achieved so far. The domain and data access layers of this example application are already up and running. But, can we say the same about the mapping layer? Not yet, unfortunately. The mappers need the functionality of a collection handling class to function as expected.

But fear not, as the development of this class will be discussed in detail in the upcoming tutorial.  

Final Thoughts

In this third episode of the series, I extended the functionality of this sample application by adding to it an easily customizable data access layer. As you just saw, the implementation of this structure was a fairly straightforward process; its building blocks were a simple segregated interface along with a basic MySQL adapter. 

Does this mean that we’re ready to start implementing the highly-desired user service? Well, not so fast. As I just said, the mappers created before take an additional dependency, which is tasked with handling collections of entities. Therefore, it’s necessary first to bring this collaborator to life before seeing the service in action.

This will be the topic that I’ll be covering in the next tutorial of the series, so don’t miss it!