Simple Yii-Like Base Class for MySQL Tables

cache.php

<?php
/**
 * Cache class
 *
 * @author Brett O'Donnell - cornernote@gmail.com
 * @copyright 2013, All Rights Reserved
 *
 */
class cache
{

    /**
     * @var array
     */
    public $memcacheOptions = array(
        'host' => 'localhost',
        'port' => '11211',

        // prevent conflicts when sharing the same memcache server
        'namespace' => 'unique-for-your-app',
    );

    /**
     * @var array
     */
    public $filecacheOptions = array(
        'path' => '/tmp/cache',
    );

    /**
     * Stores instance of static object
     *
     * @var cache
     */
    private static $_cache = array();

    /**
     * Stores the cache_id as a prefix so cache can be cleared
     *
     * @var string
     */
    private $_cache_id = array();

    /**
     * Stores the memcache connection
     *
     * @var string
     */
    private $_memcache;

    /**
     * Connect to memcache
     */
    public function __construct()
    {
        if (class_exists('Memcache')) {
            $this->_memcache = new Memcache;
            @$this->_memcache->connect($this->memcacheOptions['host'], $this->memcacheOptions['port']) or ($this->_memcache = false);
        }
    }

    /**
     * Initialize and set options
     */
    public static function init($options=array())
    {
        if (self::$_cache) return self::$_cache;
        self::$_cache = new cache();
        foreach ($options as $k => $v) {
            self::$_cache->$k = $v;
        }
        return self::$_cache;
    }

    /**
     * Get a cache key
     *
     * @param $key
     * @return array|bool|mixed|string
     */
    public static function get($key)
    {
        // assume null
        $value = false;

        // convert key
        $key = self::init()->getKey($key);

        // get the ttl
        $ttl = 0;
        if (self::$_cache->_memcache) {
            $ttl = self::$_cache->_memcache->get($key . '.ttl');
        }
        else if (file_exists($key . '.ttl')) {
            $ttl = file_get_contents($key . '.ttl');
        }

        // it lives!
        if ($ttl >= time()) {
            // memcache
            if (self::$_cache->_memcache) {
                $value = self::$_cache->_memcache->get($key . '.data');
            }
            // filecache
            else if (file_exists($key . '.data')) {
                $value = unserialize(file_get_contents($key . '.data'));
            }
        }

        // return cached value
        return $value;
    }

    /**
     * Set a cache key
     *
     * @param $key
     * @param $value
     * @param string $ttl
     * @return mixed
     */
    public static function set($key, $value, $ttl = null)
    {
        // convert key
        $key = self::$_cache->getKey($key);

        // set the expire time
        $ttl = $ttl ? $ttl : '+1 hour';
        if (is_numeric($ttl)) {
            $ttl += time();
        }
        else {
            $ttl = strtotime($ttl, time());
        }

        // memcache
        if (self::$_cache->_memcache) {
            self::$_cache->_memcache->set($key . '.data', $value);
            self::$_cache->_memcache->set($key . '.ttl', $ttl);
        }
        // filecache
        else {
            if (!file_exists(dirname($key))) {
                mkdir(dirname($key), 0700, true);
            }
            file_put_contents($key . '.data', serialize($value));
            file_put_contents($key . '.ttl', $ttl);
        }

        // return the data
        return $value;
    }

    /**
     * Delete a cache key
     *
     * @param $key
     */
    public static function delete($key)
    {
        // convert key
        $key = self::$_cache->getKey($key);

        // memcache
        if (self::$_cache->_memcache) {
            self::$_cache->_memcache->delete($key . '.time');
            self::$_cache->_memcache->delete($key . '.data');
        }

        // filecache
        else {
            if (file_exists($key . '.time')) unlink($key . '.time');
            if (file_exists($key . '.data')) unlink($key . '.data');
        }
    }


    /**
     * Clear all cache by resetting the cache_id prefix
     */
    public static function clear()
    {
        // delete the cache_id will unlink all the cache
        self::$_cache->delete('_cache_id');
    }

    /**
     * Get the cache_id used for prefixing all other cache keys
     *
     * @param $key
     * @return string
     */
    private function getKey($key)
    {
        // do not process internal cache_id
        if ($key != '_cache_id') {
            if (!$this->_cache_id) {
                $this->_cache_id = $this->get('_cache_id');
            }
            if (!$this->_cache_id) {
                $cache_id = md5(microtime());
                $this->set('_cache_id', $cache_id);
            }
            $key = $this->_cache_id . '.' . $key;
        }

        // memcache
        if ($this->_memcache) {
            $key = $this->memcacheOptions['namespace'] . '.' . $key;
        }

        // filecache
        else {
            $md5 = md5($key);
            $key = $this->filecacheOptions['path'] . '/' . substr($md5, 0, 1) . '/' . substr($md5, 0, 2) . '/' . substr($md5, 0, 3) . '/' . $key;
        }

        return $key;
    }

}

globals.php

<?php

/**
 * Debug the target with syntax highlighting on by default.
 *
 * @param null $var
 * @param null $name
 */
function debug($var = null, $name = null)
{
    $bt = array();
    $file = '';
    if ($name !== false) {
        $bt = debug_backtrace();
        $file = str_replace(bp(), '', $bt[0]['file']);
        print '<div style="font-family: arial; background: #FFFBD6; margin: 10px 0;  padding: 5px; border:1px solid #666;">';
        if ($name) $name = '<b>' . $name . '</b><br/>';
        print '<span style="font-size:14px;">' . $name . '</span>';
        print '<div style="border:1px solid #ccc; border-width: 1px 0;">';
    }
    print '<pre style="margin:0;padding:10px;">';
    print_r($var);
    print '</pre>';
    if ($name !== false) {
        print '</div>';
        print '<span style="font-family: helvetica; font-size:10px;">' . $file . ' on line ' . $bt[0]['line'] . '</span>';
        print '</div>';
    }
}

/**
 * @return string
 */
function bp()
{
    return $_ENV['bp'];
}

/**
 * @param $page
 * @return string
 */
function url($page)
{
    return 'http://' . $_SERVER['HTTP_HOST'] . dirname($_SERVER['SCRIPT_NAME']) . '/' . $page;
}

/**
 * Render a view element
 *
 * @param $view
 * @param array $params
 * @param bool $return
 * @return string|bool
 * @throws Exception
 */
function render($view, $params = array(), $return = false)
{
    extract($params);
    $include = bp() . '/views/' . $view . '.php';
    if (!file_exists($include)) {
        throw new Exception('Element not found: ' . $include);
    }
    if ($return)
        ob_start();
    include($include);
    if ($return)
        return ob_get_clean();
    return true;
}

/**
 * @param $location
 * @param int $statusCode
 */
function redirect($location, $statusCode = 302)
{
    header('Location: ' . $location, true, $statusCode);
    exit;
}

init.php

<?php
/**
 * Global Init
 */

// set error reporting
//error_reporting(E_ALL);

// setup environment
$_ENV['bp'] = dirname(dirname(__FILE__)); // base path

// functions
require('includes/functions/globals.php');

// classes
require('includes/classes/cache.php');
require('includes/classes/mysql.php');
require('includes/classes/mysql_table.php');

// models
foreach (glob('includes/models/*.php') as $model) require($model);

// start the session
session_start();

// connect to database
cache::init();
$_ENV['mysql']['default'] = new mysql('localhost', 'root', '', 'testdb');

mysql.php

<?php
/**
 *
 * MySQL class
 *
 * @author Brett O'Donnell - cornernote@gmail.com
 * @copyright 2013, All Rights Reserved
 *
 */
final class mysql
{
    /**
     * @var resource
     */
    private $connection;

    /**
     * @param $hostname
     * @param $username
     * @param $password
     * @param $database
     * @param bool $new_link
     */
    public function __construct($hostname, $username, $password, $database, $new_link = false)
    {
        if (!$this->connection = mysql_connect($hostname, $username, $password, $new_link)) {
            exit('Error: Could not make a database connection using ' . $username . '@' . $hostname);
        }

        if (!mysql_select_db($database, $this->connection)) {
            exit('Error: Could not connect to database ' . $database);
        }

        mysql_query("SET NAMES 'utf8'", $this->connection);
        mysql_query("SET CHARACTER SET utf8", $this->connection);
        mysql_query("SET CHARACTER_SET_CONNECTION=utf8", $this->connection);
        mysql_query("SET SQL_MODE = ''", $this->connection);
    }

    /**
     * @param $sql
     * @throws Exception
     * @return bool|stdClass
     */
    public function query($sql)
    {
        $resource = mysql_query($sql, $this->connection);

        if ($resource) {
            if (is_resource($resource)) {
                $data = array();
                while ($result = mysql_fetch_object($resource)) {
                    $data[] = $result;
                }
                mysql_free_result($resource);
                return $data;
            }
            else {
                return true;
            }
        }
        else {
            throw new Exception('Error: ' . mysql_error($this->connection) . '<br />Error No: ' . mysql_errno($this->connection) . '<br />' . $sql);
        }
    }

    /**
     * @param $value
     * @return string
     */
    public function escape($value)
    {
        return mysql_real_escape_string($value, $this->connection);
    }

    /**
     * @return int
     */
    public function getAffectedRows()
    {
        return mysql_affected_rows($this->connection);
    }

    /**
     * @return int
     */
    public function getInsertId()
    {
        return mysql_insert_id($this->connection);
    }

    /**
     *
     */
    public function __destruct()
    {
        mysql_close($this->connection);
    }
}

mysql_table.php

<?php
/**
 * Base class for mysql tables
 *
 * @author Brett O'Donnell - cornernote@gmail.com
 * @copyright 2013, All Rights Reserved
 *
 * @property mysql $mysql
 * @property string $table
 * @property string $primaryKey
 * @property array $fields
 * @property array $schema
 *
 */
abstract class mysql_table
{

    /**
     * Stores instances of static objects
     *
     * @var mysql_table[]  - class name => model
     */
    private static $_models = array();

    /**
     * Stores table fields
     *
     * @var array
     */
    private $_fields = array();

    /**
     * Stores table primaryKey
     *
     * @var bool|string|array
     */
    private $_primaryKey = false;

    /**
     * Stores table schema
     *
     * @var array  - field name => metadata
     */
    private $_schema = array();

    /**
     * Returns a property value.
     * Do not call this method. This is a PHP magic method that we override to allow using the following syntax to read a property:
     * <pre>
     * $value=$mysql_table->propertyName;
     * </pre>
     * @param string $name the property name
     * @return mixed the property value, event handlers attached to the event, or the named behavior
     * @throws Exception if the property is not defined
     * @see __set
     */
    public function __get($name)
    {
        $getter = 'get' . $name;
        if (method_exists($this, $getter))
            return $this->$getter();

        if ($name == $this->primaryKey || in_array($name, $this->fields))
            return null;

        throw new Exception(strtr('Property "{class}.{property}" is not defined.', array(
            '{class}' => get_class($this),
            '{property}' => $name,
        )));
    }

    /**
     * Sets value of a property.
     * Do not call this method. This is a PHP magic method that we override to allow using the following syntax to set a property
     * <pre>
     * $this->propertyName=$value;
     * </pre>
     * @param string $name the property name
     * @param mixed $value the property value
     * @return mixed
     * @throws Exception if the property is not defined or the property is read only.
     * @see __get
     */
    public function __set($name, $value)
    {
        $setter = 'set' . $name;
        if (method_exists($this, $setter))
            return $this->$setter($value);

        if ($name == $this->primaryKey || in_array($name, $this->fields))
            return $this->$name = $value;

        if (method_exists($this, 'get' . $name))
            throw new Exception(strtr('Property "{class}.{property}" is read only.', array(
                '{class}' => get_class($this),
                '{property}' => $name,
            )));
        else
            throw new Exception(strtr('Property "{class}.{property}" is not defined.', array(
                '{class}' => get_class($this),
                '{property}' => $name,
            )));
    }

    /**
     * Returns the static model of the specified table class.
     * The model returned is a static instance of the table class.
     * It is provided for invoking class-level methods (something similar to static class methods.)
     *
     * EVERY derived table class must override this method as follows,
     * <pre>
     * public static function model($className=__CLASS__)
     * {
     *     return parent::model($className);
     * }
     * </pre>
     *
     * @param string $className table class name.
     * @return mysql_table table model instance.
     */
    public static function model($className = __CLASS__)
    {
        if (isset(self::$_models[$className]))
            return self::$_models[$className];
        return self::$_models[$className] = new $className(null);
    }

    /**
     * Get cache relating to this table class
     *
     * @param $key
     * @param $usePk
     * @return mixed
     */
    public function getCache($key, $usePk = true)
    {
        $key = $this->getCacheKeyPrefix($usePk) . '_' . get_class($this) . '_' . $key;
        if ($usePk) {
            $key .= '_' . $this->{$this->primaryKey};
        }
        return cache::get($key);
    }

    /**
     * Get cache relating to this table class
     *
     * @param $key
     * @param $data
     * @param $ttl
     * @param bool $usePk
     * @return mixed
     */
    public function setCache($key, $data, $ttl = null, $usePk = true)
    {
        $key = $this->getCacheKeyPrefix($usePk) . '_' . get_class($this) . '_' . $key;
        if ($usePk) {
            $key .= '_' . $this->{$this->primaryKey};
        }
        return cache::set($key, $data, $ttl);
    }

    /**
     * Clear cache relating to this table class
     *
     * @param bool $usePk
     * @return mixed
     */
    public function clearCache($usePk = true)
    {
        $this->getCacheKeyPrefix($usePk, true);
    }

    /**
     * @param bool $usePk
     * @param bool $removeOldKey
     * @return bool|string
     */
    public function getCacheKeyPrefix($usePk = true, $removeOldKey = false)
    {
        $key = 'getCacheKeyPrefix.' . get_class($this);
        if ($usePk) {
            $key .= '_' . $this->{$this->primaryKey};
        }
        $prefix = false;
        if (!$removeOldKey) {
            $prefix = cache::get($key);
        }
        if (!$prefix) {
            $prefix = uniqid();
            cache::set($key, $prefix);
        }
        return $prefix . '.';
    }

    /**
     * Database object
     *
     * @return mysql
     */
    protected function getMysql()
    {
        return $_ENV['mysql']['default'];
    }

    /**
     * Name of the database table
     *
     * @return string
     */
    public function getTable()
    {
        return get_class($this);
    }

    /**
     * Name of the primary key field
     *
     * @return bool|string|array
     */
    public function getPrimaryKey()
    {
        if ($this->_primaryKey)
            return $this->_primaryKey;

        $fields = array();
        foreach ($this->schema as $field => $metadata) {
            if ($metadata->Key != 'PRI') continue;
            $fields[] = $field;
        }
        if (!$fields)
            return false;
        if (count($fields) == 1)
            return $fields[0];

        return $this->_primaryKey = $fields;
    }

    /**
     * Fields that will be loaded and saved
     *
     * @return array
     */
    public function getFields()
    {
        if ($this->_fields)
            return $this->_fields;

        $fields = array();
        foreach ($this->schema as $field => $metadata) {
            if ($metadata->Key == 'PRI') continue;
            $fields[] = $field;
        }

        return $this->_fields = $fields;
    }

    /**
     * Table schema
     *
     * @return array
     */
    public function getSchema()
    {
        if ($this->_schema)
            return $this->_schema;

        if ($this->_schema = $this->getCache('schema', false))
            return $this->_schema;

        $this->_schema = array();
        $fields = $this->query("SHOW COLUMNS FROM `" . $this->table . "`");
        foreach ($fields as $field) {
            $this->_schema[$field->Field] = $field;
        }

        return $this->setCache('schema', $this->_schema, null, false);
    }

    /**
     * Find all rows matching the criteria
     *
     * @param $where
     * @param array $params
     * @return array
     */
    public function findAll($where = null, $params = array())
    {
        // build where
        $where = $where ? " WHERE $where" : '';
        foreach ($params as $k => $v) {
            $params[$k] = "'" . $this->mysql->escape($v) . "'";
        }
        $where = strtr($where, $params);

        // build fields
        $fields = array();
        foreach ($this->fields as $field) {
            $fields[] = "`$field`";
        }
        if ($this->primaryKey) {
            $fields = array_merge($fields, array('`' . $this->primaryKey . '`'));
        }

        // get results
        $results = $this->query("SELECT " . implode(', ', $fields) . " FROM `" . $this->table . "` " . $where);
        foreach ($results as $k => $result) {
            $class = get_class($this);
            $model = new $class;
            foreach ($result as $kk => $vv) {
                $model->$kk = $vv;
            }
            $results[$k] = $model;
        }
        return $results;
    }

    /**
     * Find a single row matching the criteria
     *
     * @param $where
     * @param array $params
     * @return array
     */
    public function find($where = null, $params = array())
    {
        $results = $this->findAll($where . " LIMIT 1", $params);
        return $results ? $results[0] : false;
    }

    /**
     * Find a single row with the selected pk
     *
     * @param $pk
     * @return mysql_table
     */
    public function findByPk($pk)
    {
        return $this->find("`" . $this->primaryKey . "`='" . $pk . "'");
    }

    /**
     * Save this row's attributes to the database
     *
     * @return mixed
     */
    public function save()
    {
        $pk = $this->primaryKey;
        $fields = array();
        foreach ($this->fields as $field) {
            if (isset($this->$field)) {
                $value = $this->mysql->escape($this->$field);
                $fields[] = "`$field`='$value'";
            }
        }
        $query = (isset($this->$pk) ? "UPDATE" : "INSERT INTO") . " `" . $this->table . "` SET " . implode(', ', $fields);
        if (isset($this->$pk)) {
            $query .= " WHERE `" . $this->primaryKey . "`='" . (int)$this->$pk . "'";
        }
        $result = $this->query($query);
        $this->clearCache();
        if ($result && empty($this->$pk)) {
            $this->$pk = $this->mysql->getInsertId($result);
        }
        return $result;
    }

    /**
     * Delete this row from the database
     *
     * @return mixed
     */
    public function delete()
    {
        $pk = $this->primaryKey;
        if (!isset($this->$pk)) {
            return false;
        }
        $query = "DELETE FROM `" . $this->table . "` WHERE `" . $this->primaryKey . "`='" . (int)$this->$pk . "'";
        $result = $this->query($query);
        $this->clearCache();
        return $result;
    }

    /**
     * Delete all rows matching the criteria
     *
     * @param $where
     * @return mixed
     */
    public function deleteAll($where)
    {
        $query = "DELETE FROM `" . $this->table . "` WHERE " . $where;
        $result = $this->query($query);
        $this->clearCache();
        return $result;
    }

    /**
     * @param $sql
     * @param $params
     * @return bool|stdClass[]
     */
    public function query($sql, $params = array())
    {
        foreach ($params as $k => $v) {
            $params[$k] = "'" . $this->mysql->escape($v) . "'";
        }
        $sql = strtr($sql, $params);
        return $this->mysql->query($sql);
    }

}