Working with User Models in PHP and MySQL

In keeping with this concept, in earlier chapters of this series I demonstrated how to use this principle to develop a few basic scripts, such as an extendable registry library, a caching system capable of using different behind the scenes cache back-ends, and even a simple model layer, whose focus was to perform CRUD operations in a MySQL table containing data on some hypothetical users.

This last example is still in an incomplete state, as it’s necessary to prove if the model is really as functional as it seems at first sight. In this last installment of the series I’m going to create a couple of scripts which will show how to put this sample user model to work, first by using the prior APC cache-backend and lastly via its counterpart “FileCache” class.

If you missed the prior installments in this series or need to refresh your memory, you can find them here:

• Building a Service Locator
• Dynamic Registry in PHP
• Implementing a Cache System in PHP
• Building a User Model in PHP and MySQL

Recap time: showing the source code developed so far

To begin with, here’s the segregated interface implemented by the couple of cache-back-ends using the APC extension and the file system respectively:  

(CacheableInterface.php)

<?php

interface CacheableInterface
{
    public function set($key, $data);
   
    public function get($key);
   
    public function delete($key);
   
    public function exists($key);
   
    public function clear();    
}


Without a doubt, understanding the contract declared by the “CacheableInterface” interface is fairly straightforward. Keeping that in mind, let's look at its implementers. The first one is a simple wrapper for the APC extension and its source code is as follows:

(ApcCache.php)

<?php

class ApcCache implements CacheableInterface
{
    /**
     * Save data to the cache
     */
    public function set($key, $data)
    {
        if (!apc_store(strtolower($key), $data)) {
            throw new ApcCacheException('Error saving data with the key ' . $key . ' to the APC cache.');
        }
        return $this;
    }
   
    /**
     * Get the specified data from the cache
     */
    public function get($key)
    {
        if ($this->exists($key)) {
            if (!$data = apc_fetch(strtolower($key))) {
                throw new ApcCacheException('Error fetching data with the key ' . $key . ' from the APC cache.');
            }
            return $data;
        }
        return null;
    }
   
    /**
     * Delete the specified data from the cache
     */
    public function delete($key)
    {
        if ($this->exists($key)) {
            if (!apc_delete(strtolower($key))) {
                throw new ApcCacheException('Error deleting data with the key ' . $key . ' from the APC cache.');
            }
            return true;
        }
        return false;
    }
   
    /**
     * Check if the specified cache key exists
     */
    public function exists($key)
    {
        return (boolean) apc_exists(strtolower($key));
    }
   
    /**
     * Clear the cache
     */
    public function clear($cacheType = 'user')
    {
        return apc_clear_cache($cacheType);
    }       
}



(ApcCacheException.php)

<?php

class ApcCacheException extends Exception{}



The “ApcCache” class acts like a proxy for the most relevant APC functions, whichs allow you to save, retrieve and clear opcode with minor effort.

Pay attention to the following class, whose functionality is quite similar to the one provided by the APC back-end, only that in this case it uses the file system to cache data:

(FileCache.php)

<?php

class FileCache implements CacheableInterface
{
    protected $_cacheDir = 'cache';
   
    /**
     * Constructor
     */
    public function __construct($cacheDir = '')
    {
        if ($cacheDir !== '') {
            if (!is_dir($cacheDir)) {
                throw new FileCacheException('The specified cache directory is invalid.');
            }
            $this->_cacheDir = $cacheDir;
        }
    }
   
    /**
     * Save data to the specified cache file
     */
    public function set($key, $data)
    {
        $cacheFile = $this->getCacheFile($key);
        if (!file_put_contents($cacheFile, serialize($data))) {
            throw new FileCacheException('Error saving data with the key ' . $key . ' to the cache file.');
        }
        return $this;
    }
   
    /**
     * Get data from the specified cache file
     */
    public function get($key)
    {
        if ($this->exists($key)) {
            $cacheFile = $this->getCacheFile($key);
            if (!$data = unserialize(file_get_contents($cacheFile))) {
                throw new FileCacheException('Error reading data with the key ' . $key . ' from the cache file.');
            }
            return $data;
        }
        return null;
    }
   
    /**
     * Delete the specified cache file
     */
    public function delete($key)
    {
        if ($this->exists($key)) {
            $cacheFile = $this->getCacheFile($key);
            if (!unlink($cacheFile)) {
                throw new FileCacheException('Error deleting the file cache with key ' . $key);
            }
            return true;
        }
        return false;
    }
   
    /**
     * Check if the specified cache file exists
     */
    public function exists($key)
    {
        $cacheFile = $this->getCacheFile($key);
        return file_exists($cacheFile);
    }
   
    /**
     * Remove all the cache files
     */
    public function clear()
    {
        $cacheFiles = $this->_cacheDir . DIRECTORY_SEPARATOR . '*.cache';
        array_map('unlink', glob($cacheFiles));
    }
     
    /**
     * Get the specified cache file
     */
    protected function getCacheFile($key)
    {
        return $this->_cacheDir . DIRECTORY_SEPARATOR . strtolower($key) . '.cache';
    }     
}



(FileCacheException.php)

<?php

class FileCacheException extends Exception{}



If you found this code easy to grasp, then you shouldn’t have any problem understanding how the earlier “FileCache” back-end does its thing. What you should be aware of, however, is that both classes implement the same segregated interface, something that makes the whole caching layer extremely easy to extend. 

And speaking of layers, here’s the one responsible for interacting with the storage mechanism used by the user model. Take a peek at it:

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



(MySQLAdapter.php)

<?php

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) {
            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)
    {
        $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_object($this->_result))) {
                $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{}



That was pretty lengthy, right? There’s no need to feel overwhelmed, though, as the earlier “MySQLAdapter” class is simply a proxy for the “mysqli” PHP extension, which allows you to run SQL statements as well as fetch and count rows in a specified MySQL table.

>>> More MySQL Articles          >>> More By Alejandro Gervasio

blog comments powered by Disqus