Creating a Basic Data Access Layer in PHP and MySQL

In this third installment of the series, I develop a basic data access layer using PHP and MySQL. It is made up of a single interface and an implementer of it, which in this case is a simple MySQL abstraction class. The construction of this additional layer takes us one step closer to developing a UoW that can be used in a truly helpful fashion.

If you frequently build large PHP applications whose domain layers are composed of a huge variety of domain objects, it’s probable that you’re desperately in search of an approach that helps you to optimize the interactions that those objects have with the underlying persistence mechanism.

Leave your pain behind and enter the world of a Unit of Work (UoW). This enterprise-level design pattern will let you easily create an in-memory map of domain objects that must be inserted, updated and deleted from the data access layer. The beauty of this schema is that all of these operations will be performed through a single method call, thus reducing the overhead caused by expensive (and sometimes unnecessary) database trips.

Despite the apparent complexity involved in the construction of a UoW, most of its logic can be implemented via some straightforward methods that are only responsible for manipulating plain array elements. Since the best way to prove my statement is by showing some functional code snippets, in earlier chapters of this series I went through the development of an abstract UoW class, capable of queuing objects for further insertion, updating and removal.

With this base structure existing in the form of a generic service, it was fairly easy to complement its functionality by adding to it the collaborators that it required to function properly. The first of these dependencies was a simple data mapper, tasked with interacting  with the persistence layer. The second one was an abstract class whose main responsibility was to model generic entities.

Of course, there’s still a lot more work that needs to be done before you see the actual benefits of using a UoW. This includes the creation of a basic — yet functional — database adapter. Since I want to keep things simple and easy to follow, the adapter that I plan to construct in the lines to come will be comprised of only a MySQL abstraction class, which you’ll be able to tweak and enhance at will afterwards.

It’s time to continue this educational journey that shows you how to build a UoW in PHP. Let’s go!

Review: adding a couple of collaborators to the previous Unit of Work

In the introduction, I mentioned that the sample UoW previously developed injects two additional dependencies through its constructor, which make it work properly. The first of these collaborators is an abstract data mapper. Its definition has been included below, in case you don’t recall how it looks. Here it is: 

(DataMapperAbstract.php)

<?php

abstract class DataMapperAbstract
{
    protected $_adapter;
    protected $_collection;
        
    /**
     * Class constructor
     */
    public function __construct(DatabaseAdapterInterface $adapter, CollectionAbstract $collection)
    {
        $this->_adapter = $adapter;
        $this->_collection = $collection;
        $this->init();
    }
   
    /**
     * Initialize the data mapper here
     */
    public function init(){}
   
    /**
     * Get the instance of the database adapter
     */ 
    public function getAdapter()
    {
        return $this->_adapter;
    }
   
    /**
     * Get the collection
     */
    public function getCollection()
    {
        return $this->_collection;
    } 
}

Definitely, understanding how the above abstract mapper works is a very simple process.It acts like a mediator between the UoW and the persistence layer, which is also capable of dealing with collections of entities. Considering that the UoW performs CRUD operations on the entities in question, there must exist a class that allows you to create them and assign values to their fields, right? Well, the one shown below does exactly that: 

(EntityAbstract.php)

<?php

abstract class EntityAbstract
{
    protected $_values = array();
    protected $_allowedFields = array();
   
    /**
     * Class constructor
     */
    public function __construct(array $data = array())
    {
        foreach ($data as $name => $value) {
            $this->$name = $value;
        }
    }
   
    /**
     * Assign a value to the specified field via the corresponding mutator (if it exists);
     * otherwise, assign the value directly to the ‘$_values’ protected array
     */
    public function __set($name, $value)
    {  
        if (!in_array($name, $this->_allowedFields)) {
            throw new EntityException(‘The field ‘ . $name . ‘ is not allowed for this entity.’); 
        }
        $mutator = ‘set’ . ucfirst($name);
        if (method_exists($this, $mutator) && is_callable(array($this, $mutator))) {
            $this->$mutator($value);          
        }
        else {
            $this->_values[$name] = $value;
        }   
    }
   
    /**
     * Get the value assigned to the specified field via the corresponding getter (if it exists);
    otherwise, get the value directly from the ‘$_values’ protected array
     */
    public function __get($name)
    {
        if (!in_array($name, $this->_allowedFields)) {
            throw new EntityException(‘The field ‘ . $name . ‘ is not allowed for this entity.’);   
        }
        $accessor = ‘get’ . ucfirst($name);
        if (method_exists($this, $accessor) && is_callable(array($this, $accessor))) {
            return $this->$accessor;   
        }
        return array_key_exists($name, $this->_values) ?
               $this->_values[$name] :
               null;
    }
   
    /**
     * Get an associative array with the values assigned to the fields of the entity
     */
    public function toArray()
    {
        return $this->_values;
    }             
}

 

(EntityException.php)

<?php

class EntityException extends Exception {}

Mission accomplished, at least for the moment. With the inclusion of the above "EntityAbstract" class, I finished this quick review of the dependencies that the sample UoW requires to correctly do its business. So, what’s next? Well, as you may have noticed, the earlier data mapper also injects a collaborator into its internals, which happens to be an instance of a database interface.

Since this interface and its eventual implementer(s) are tasked with talking directly to the persistence mechanism, it’s necessary to show their respective definitions, so you can see how they sit down below the mapping layer. Don’t be worried, though, as this will be done in the following section.

Now, go ahead and read the next few lines.  

{mospagebreak title=Talking to the persistence layer: defining a basic database interface}

Before I proceed to define a concrete class responsible for interacting with the persistence layer (which for the sake of simplicity will be a MySQL database), it’d be useful to declare a simple contract, which must be fulfilled by all the database adapters created from this point onward. Needless to say, this contract is a basic interface, whose signature is as follows:

(DatabaseAdapterInterface.php)

<?php

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

As you can see in the above code fragment, the methods declared by the earlier interface are pretty self-explanatory. They define a basic contract that must be agreed to by all of its further implementers. Since the storage mechanism used by the previous UoW will be a MySQL database, the next step is to construct a concrete class capable of performing some CRUD operations in that RDBMS.

The full details regarding the construction of this brand new class will be covered in the next section. Therefore, to learn more about them, keep reading.

Implementing the interface: creating a basic MySQL abstraction class

Let’s face it: writing a database access library is a pretty annoying, even boring task for most PHP developers. Since I’m the kind of person who wants to save himself from the hassle of developing one from scratch again, the implementer of the database interface defined in the preceding section will be the same MySQL abstraction class used here (http://www.devshed.com/c/a/PHP/Roll-Your-Own-Repository-in-PHP-the-Data-Access-Layer/1/).

If you don’t remember how this class was defined, here is its source code:

(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{}

Even though the definition of this MySQL abstraction class is quite lengthy, its logic is fairly easy to follow, believe me. In short, the class is nothing but a simple wrapper for the "mysqli" PHP extension, which packages the functionality required for performing CRUD operations in a selected table, along with a few other common tasks, such as counting rows in result sets and finding insertion IDs.

While it is undeniably good to have a class that hides the complexities of working with MySQL behind a friendly API, you’re probably wondering what it buys us, right? Fortunately, the answer to that question is simple: although the main goal of a UoW is to optimize transactions performed on domain objects, sooner or later those objects must be fetched, saved and deleted from the persistence mechanism, and that’s precisely the role that the previous MySQL class plays.

This implies that we’ve taken one big step forward in the development of a fully-functional UoW. Keep in mind, however, that this isn’t something that can be achieved overnight, so for the moment feel free to analyze the sample classes shown in this tutorial and tweak them according to your specific needs.   

Final thoughts

In this third installment of the series, I developed a basic data access layer made up of a single interface and an implementer of it, which in this case was a simple MySQL abstraction class. As I just noted, the construction of this additional layer takes us one step closer to developing a UoW that can be used in a truly helpful fashion.

But I’m getting ahead of myself, as there are still a few things that need to be done before reaching that point. This includes the development of a class capable of working with collections of entities. (Remember that an instance of this class is passed to the internals of the abstract data mapper discussed previously).

With that said, in the next tutorial I’m going to create this collection-handling class in a few easy steps. Therefore, now that you know what to expect from the upcoming episode, you don’t have any excuses to miss it!

Google+ Comments

Google+ Comments