Building a basic data access layer - PHP

In most cases, the data access layer of an application is comprised of multiple classes that perform well-differentiated tasks. In this case, though, the layer that I plan to build here will be made up of only one class, which will abstract the access to MySQL. Doing this will let you grasp more quickly how a data mapper will work in a rather simple context.

That being said, please pay attention to the definition of the MySQL abstraction class, which is as follows:

(MySQLAdapter.php)

<?php

class MySQLAdapter

{

    private $_config = array();

    private static $_instance = NULL;

    private static $_connected = FALSE;

    private $_link = NULL;

    private $_result = NULL;

    // return Singleton instance of MySQLAdapter class

    public static function getInstance(array $config = array())

    {

        if (self::$_instance === NULL)

        {

            self::$_instance = new self($config);

        }

        return self::$_instance;

    }

    // private constructor

    private function __construct(array $config)

    {

        if (count($config) < 4)

        {

            throw new MySQLAdapterException('Invalid number of connection parameters');  

        }

        $this->_config = $config;

    }

    // prevent cloning class instance

    private function __clone(){}

    // connect to MySQL

    private function connect()

    {

        // connect only once

        if (self::$_connected === FALSE)

        {

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

            }

            self::$_connected = TRUE;

            unset($host, $user, $password, $database);      

        }

    }

    // perform query

    public function query($query)

    {

        if (is_string($query) and !empty($query))

        {

            // lazy connect to MySQL

            $this->connect();

            if ((!$this->_result = mysqli_query($this->_link, $query)))

            {

                throw new MySQLAdapterException('Error performing query ' . $query . ' Error : ' . mysqli_error($this->_link));

            }

        }

    }

    // fetch row from result set

    public function fetch()

    {

        if ((!$row = mysqli_fetch_object($this->_result)))

        {

            mysqli_free_result($this->_result);

            return FALSE;

        }

        return $row;

    }

    // get insertion ID

    public function getInsertID()

    {

        if ($this->_link !== NUlL)

        {

            return mysqli_insert_id($this->_link); 

        }

        return NULL;  

    }

    // count rows in result set

    public function countRows()

    {

        if ($this->_result !== NULL)

        {

           return mysqli_num_rows($this->_result);

        }

        return 0;

    }

    // close the database connection

    function __destruct()

    {

        is_resource($this->_link) AND mysqli_close($this->_link);

    }

}

(MySQLAdapterException.php)

<?php

class MySQLAdapterException extends Exception{}

As seen before, the “MySQLAdapter” class is merely a Singleton that performs some typical operations on MySQL, such as connecting to the database server, executing hard-coded SQL statements and fetching database rows. Its inner working is that simple, really.

At this time, coding a class like this seems to be a pointless task, since it doesn’t shed any light on how to build a data mapper class. But this is a misconception, believe me. When I show you the definition of the data mapper, you’ll realize that the existence of a MySQL adapter makes a lot of sense.

Assuming that you understand the logic behind the “MySQLAdapter” class, it’s time to see how it works. So, in the following section I’m going to code a short script that will put it in action.

Now, go ahead and read the next segment. It’s only one click away.