Database Class

  Michael        January 27, 2011

This class is designed to be used with the MySQL Classes provided in an earlier post.

<?php

  /**
   * Squiloople Framework
   *
   * LICENSE: Feel free to use and redistribute this code.
   *
   * @author Michael Rushton <michael@squiloople.com>
   * @link http://squiloople.com/
   * @package Models
   * @subpackage Databases
   * @version 1.0
   * @copyright © 2013 Michael Rushton
   */

  /**
   * Database
   *
   * Acts as a database for simple queries
   */
  class Database
  {

    /**
     * An instance of MySQLConnection
     *
     * @access protected
     * @var MySQLConnection $_connection
     */
    protected $_connection;

    /**
     * The database table
     *
     * @access private
     * @var string $_table
     */
    private $_table;

    /**
     * The indexes
     *
     * @access private
     * @var array $_indexes
     */
    private $_indexes = array();

    /**
     * The auto increment column
     *
     * @access private
     * @var null|string $_auto_increment
     */
    private $_auto_increment = NULL;

    /**
     * An array of the data
     *
     * @access protected
     * @var array $_data
     */
    protected $_data;

    /**
     * Store the connection object and set the initial data
     *
     * @access public
     * @param string $table
     * @param array $data
     */
    public function __construct($table, array $data = array())
    {

      // Store the table name
      $this->_table = $table;

      // Store the connection
      $this->_connection = MySQLConnection::connection();

      // Get the unique indexes
      $indexes = $this->_connection->prepareQuery("SHOW INDEX FROM `" . $table . "` WHERE Non_unique = 0", MySQLSelectStatement::STATEMENT)
        ->execute()->getAll();

      // Iterate over each index
      foreach ($indexes as $index)
      {

        // Create the key if it does not exist
        if (!isset($this->_indexes[$key_name = $index->STATISTICS->Key_name]))
        {
          $this->_indexes[$key_name] = array();
        }

        // Store the key's column
        $this->_indexes[$key_name][] = $index->STATISTICS->Column_name;

      }

      // Throw an exception if there is no primary key
      if (empty($this->_indexes['PRIMARY']))
      {
        throw new Exception('Table `' . $table . '` has no primary key.');
      }

      // Get the auto increment column
      $auto_increment = $this->_connection->prepareQuery("SHOW COLUMNS FROM `" . $table . "` WHERE Extra = 'auto_increment'", MySQLSelectStatement::STATEMENT)
        ->execute()->getOne();

      // If there is an auto increment column then store it
      if (0 != $auto_increment->getNumRows())
      {
        $this->_auto_increment = $auto_increment->COLUMNS->Field;
      }

      // Store the data
      $this->_data = $data;

    }

    /**
     * Insert a row with the given data
     *
     * @access public
     */
    public function insert()
    {

      // Prepare the statement
      $stmt = $this->_connection->prepareInsertStatement()
                ->addTable($this->_table);

      // Execute the query
      $stmt = $this->_executeQuery($stmt, __FUNCTION__);

      // Store the new row's auto increment value if there is one
      if (NULL != $this->_auto_increment)
      {
        $this->_data[$this->_auto_increment] = $stmt->getInsertID();
      }

      // Select the row
      $this->select();

    }

    /**
     * Delete the row with the given data
     *
     * @access public
     */
    public function delete()
    {

      // Prepare the statement
      $stmt = $this->_connection->prepareDeleteStatement()
                ->addTable($this->_table)
                ->setLimit(1);

      // Iterate over each primary key
      foreach ($this->_indexes['PRIMARY'] as $key)
      {

        // Throw an exception if the value has not been set
        if (NULL == $this->$key)
        {
          throw new Exception('The primary key has not been set.');
        }

      }

      // Execute the query
      $this->_executeQuery($stmt, __FUNCTION__);

    }

    /**
     * Update the row with the given data
     *
     * @access public
     */
    public function update()
    {

      // Prepare the statement
      $stmt = $this->_connection->prepareUpdateStatement()
                ->addTable($this->_table)
                ->setLimit(1);

      // Iterate over each primary key
      foreach ($this->_indexes['PRIMARY'] as $key)
      {

        // Throw an exception if the value has not been set
        if (NULL == $this->$key)
        {
          throw new Exception('The primary key has not been set.');
        }

      }

      // Execute the query
      $this->_executeQuery($stmt, __FUNCTION__);

    }

    /**
     * Select the row with the given data
     *
     * @access public
     */
    public function select()
    {

      // Iterate over each index
      foreach ($this->_indexes as $index)
      {

        // Set the unique index status to true
        $unique = TRUE;

        // Iterate over each column
        foreach ($index as $column)
        {

          // If the column value has not been set
          if (NULL == $this->$column)
          {

            // Set the unique index status to false
            $unique = FALSE;

            // Break out of the iteration
            break;

          }

        }

        // If a unique index has been set then break out of the iteration
        if ($unique)
        {
          break;
        }

      }

      // Throw an exception a unique index has not been set
      if (!$unique)
      {
        throw new Exception('A unique key has not been set.');
      }

      // Prepare the select statement
      $stmt = $this->_connection->prepareSelectStatement()
                ->addTable($this->_table, '*')
                ->setLimit(1);

      // Get the result
      $stmt = $this->_executeQuery($stmt, __FUNCTION__)->getOne();

      // If there are results then store the data
      if (0 != $stmt->getNumRows())
      {
        $this->_data = (array) current($stmt->getData());
      }

      // Otherwise if there is an auto increment column then unset it
      elseif (NULL != $this->_auto_increment)
      {
        unset($this->_data[$this->_auto_increment]);
      }

      // Free the result
      $stmt->freeResult();

    }

    /**
     * Prepare, bind, and execute the query
     *
     * @access private
     * @param MySQLStatement $stmt
     * @param string $method
     * @return object
     */
    final private function _executeQuery(MySQLStatement $stmt, $method)
    {

      // Throw an exception if the data is empty
      if (empty($this->_data))
      {
        throw new Exception('No data has been set.');
      }

      // Iterate over the data
      foreach ($this->_data as $field => $value)
      {

        // Get the data type
        switch (gettype($value))
        {

          // If an integer
          case ('integer'):
            $type = 'i';
            break;

          // Otherwise if a double
          case ('double'):
            $type = 'd';
            break;

          // Otherwise if a string
          case ('string'):
            $type = 's';
            break;

          // Otherwise if null
          case ('NULL'):
            $type = NULL;
            break;

          // Default
          default:
            continue 2;

        }

        // Store the parameter if not null
        if (NULL != $type)
        {
          $parameters[$field] = $value;
        }

        // If the parent method is not "insert"
        if ('insert' != $method)
        {

          // Iterate over each index
          foreach ($this->_indexes as $key => $index)
          {

            // If the parent method is "select" or the key is primary
            if ('select' == $method || 'PRIMARY' == $key)
            {

              // If the field is an index
              if (in_array($field, $index))
              {

                // If the value is not null
                if (NULL != $type)
                {
                  $stmt->addWhere('`' . $field . '` = ' . $type . ':' . $field);
                }

                // Otherwise
                else
                {
                  $stmt->addWhere('`' . $field . '` IS NULL');
                }

              }

            }

          }

        }

        // Add a SET clause if the parent method is "insert" or if it is "update" and the field is not a primary key
        if ('insert' == $method || ('update' == $method && !isset($this->_indexes['PRIMARY'][$field])))
        {

          // If not NULL
          if (NULL != $type)
          {
            $stmt->addValue($field, $type . ':' . $field);
          }

          // Otherwise
          else
          {
            $stmt->addValue($field, 'NULL');
          }

        }

      }

      // Set the parameters, execute the statement, and return the object
      return $stmt->setParameters($parameters)->execute();

    }

    /**
     * Set the field data
     *
     * @access public
     * @param string $field
     * @param mixed $value
     */
    final public function __set($field, $value)
    {
      $this->_data[$field] = $value;
    }

    /**
     * Get the field data
     *
     * @access public
     * @param string $field
     * @return mixed
     */
    final public function __get($field)
    {
      return isset($this->_data[$field]) ? $this->_data[$field] : NULL;
    }

    /**
     * Set all the field data
     *
     * @access public
     * @param array $data
     */
    final public function setData(array $data)
    {
      $this->_data = $data;
    }

    /**
     * Get all the field data
     *
     * @access public
     * @return array
     */
    final public function getData()
    {
      return $this->_data;
    }

    /**
     * Clear all the field data
     *
     * @access public
     */
    final public function clearData()
    {
      $this->_data = array();
    }

  }

Continue reading →

MySQL Classes

  Michael        September 22, 2010

MySQL Connection

<?php

  /**
   * Squiloople Framework
   *
   * LICENSE: Feel free to use and redistribute this code.
   *
   * @author Michael Rushton <michael@squiloople.com>
   * @link http://squiloople.com/
   * @package Squiloople
   * @version 1.0
   * @copyright © 2012 Michael Rushton
   */

  /**
   * MySQLConnection
   *
   * Control a MySQL connection
   */
  final class MySQLConnection
  {

    /**
     * An instance of the class
     *
     * @access private
     * @static
     * @var MySQLConnection $_instance
     */
    private static $_instance;

    /**
     * The mysqli connection resource
     *
     * @access private
     * @var mysqli $_connection
     */
    private $_connection;

    /**
     * Try to connect to the server
     *
     * @access private
     * @param array $connect
     */
    private function __construct(array $connect)
    {

      // Throw an exception if a connection cannot be made
      if (!$this->_connection = call_user_func_array('mysqli_connect', $connect))
      {
        throw new MySQLException($this->getConnectError());
      }

    }

    /**
     * Create and/or return an instance of the object
     *
     * @access public
     * @static
     * @param string $hostname
     * @param string $username
     * @param string $password
     * @param string $database
     * @return MySQLConnection
     */
    public static function connection()
    {

      // Instantiate the object if one does not exist
      if (!isset(self::$_instance))
      {
        self::$_instance = new self(func_get_args());
      }

      // Return the MySQLConnection instance
      return self::$_instance;

    }

    /**
     * Return the connection
     *
     * @access public
     * @return mysqli|boolean
     */
    public function getConnection()
    {
      return $this->_connection;
    }

    /**
     * Select the database
     *
     * @access public
     * @param string $database
     * @return MySQLConnection
     */
    public function setDatabase($database)
    {

      // Throw an exception if the database cannot be selected
      if (!$this->_connection->select_db($database))
      {
        throw new MySQLException($this->getError());
      }

      // Return the MySQLConnection instance
      return $this;

    }

    /**
     * Prepare the query
     *
     * @access public
     * @param string $query
     * @param string $result
     * @param array $vars
     * @return object
     */
    public function prepareQuery($query, $result = '', array $vars = array())
    {

      // Store all the matches to be used for bound parameters
      preg_match_all('/[idsb]:[a-zA-Z0-9_]+/', $query, $refs);

      // Throw an exception if the re-formatted query cannot be prepared
      if (!$stmt = $this->_connection->prepare(preg_replace('/[idsb]:[a-zA-Z0-9_]+/', '?', $query)))
      {
        throw new MySQLException($this->getError());
      }

      // Set the relevant MySQL result class name
      $class = 'MySQL' . ucfirst(strtolower($result)) . 'Result';

      // Instantiate and return the object
      return new $class($stmt, $refs[0], $vars);

    }

    /**
     * Prepare an INSERT statement
     *
     * @access public
     * @return MySQLInsertStatement
     */
    public function prepareInsertStatement()
    {
      return new MySQLInsertStatement;
    }

    /**
     * Prepare a DELETE statement
     *
     * @access public
     * @return MySQLDeleteStatement
     */
    public function prepareDeleteStatement()
    {
      return new MySQLDeleteStatement;
    }

    /**
     * Prepare an UPDATE statement
     *
     * @access public
     * @return MySQLUpdateStatement
     */
    public function prepareUpdateStatement()
    {
      return new MySQLUpdateStatement;
    }

    /**
     * Prepare a SELECT statement
     *
     * @access public
     * @return MySQLSelectStatement
     */
    public function prepareSelectStatement()
    {
      return new MySQLSelectStatement;
    }

    /**
     * Return the connection errno
     *
     * @access public
     * @return integer
     */
    public function getConnectErrno()
    {
      return mysqli_connect_errno();
    }

    /**
     * Return the connection error
     *
     * @access public
     * @return string
     */
    public function getConnectError()
    {
      return mysqli_connect_error();
    }

    /**
     * Return the error
     *
     * @access public
     * @return integer
     */
    public function getErrno()
    {
      return $this->_connection->errno;
    }

    /**
     * Return the error
     *
     * @access public
     * @return string
     */
    public function getError()
    {
      return $this->_connection->error;
    }

    /**
     * Disconnect from the server
     *
     * @access public
     */
    public function disconnect()
    {

      // Throw an exception if the server connection cannot be closed
      if (!$this->_connection->close())
      {
        throw new MySQLException($this->getError());
      }

      // Destroy the instance
      self::$_instance = NULL;

    }

    /**
     * Throw an exception if a clone is attempted
     *
     * @access public
     */
    public function __clone()
    {
      throw new Exception('Attempt to clone MySQLConnection instance.');
    }

    /**
     * Disconnect from the server
     *
     * @access public
     */
    public function __destruct()
    {
      $this->disconnect();
    }

  }

Continue reading →