Home arrow PHP arrow Page 2 - Creating a Basic Data Access Layer in PHP and MySQL

Talking to the persistence layer: defining a basic database interface - PHP

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.

TABLE OF CONTENTS:
  1. Creating a Basic Data Access Layer in PHP and MySQL
  2. Talking to the persistence layer: defining a basic database interface
By: Alejandro Gervasio
Rating: starstarstarstarstar / 1
December 29, 2010

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

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!



 
 
>>> More PHP Articles          >>> More By Alejandro Gervasio
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

PHP ARTICLES

- Hackers Compromise PHP Sites to Launch Attac...
- Red Hat, Zend Form OpenShift PaaS Alliance
- PHP IDE News
- BCD, Zend Extend PHP Partnership
- PHP FAQ Highlight
- PHP Creator Didn't Set Out to Create a Langu...
- PHP Trends Revealed in Zend Study
- PHP: Best Methods for Running Scheduled Jobs
- PHP Array Functions: array_change_key_case
- PHP array_combine Function
- PHP array_chunk Function
- PHP Closures as View Helpers: Lazy-Loading F...
- Using PHP Closures as View Helpers
- PHP File and Operating System Program Execut...
- PHP: Effects of Wrapping Code in Class Const...

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: