Home arrow MySQL arrow Working with User Models in PHP and MySQL

Working with User Models in PHP and MySQL

Being one of the big pillars of Object-Oriented Design, The Interface Segregation Principle (ISP) permits you to define fine-grained contracts that provide client classes with the functionality to perform a set of well-defined tasks. As with other SOLID principles (Single responsibility, Open-closed, Liskov substitution, Interface segregation and Dependency inversion), ISP is language-agnostic, meaning that it can be applied in several programming languages, including PHP.

TABLE OF CONTENTS:
  1. Working with User Models in PHP and MySQL
  2. Performing CRUD Operations in MySQL
By: Alejandro Gervasio
Rating: starstarstarstarstar / 1
June 28, 2011

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

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:

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
escort Bursa Bursa escort Antalya eskort
   

MYSQL ARTICLES

- Oracle Unveils MySQL 5.6
- MySQL Vulnerabilities Threaten Databases
- MySQL Cloud Options Expand with Google Cloud...
- MySQL 5.6 Prepped to Handle Demanding Web Use
- ScaleBase Service Virtualizes MySQL Databases
- Oracle Unveils MySQL Conversion Tools
- Akiban Opens Database Software for MySQL Use...
- Oracle Fixes MySQL Bug
- MySQL Databases Vulnerable to Password Hack
- MySQL: Overview of the ALTER TABLE Statement
- MySQL: How to Use the GRANT Statement
- MySQL: Creating, Listing, and Removing Datab...
- MySQL: Create, Show, and Describe Database T...
- MySQL Data and Table Types
- McAfee Releases Audit Plugin for MySQL Users

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: