MySQLi Connection, Statement, and Result classes

Coding Critique is the place to post source code for peer review by other members of DevNetwork. Any kind of code can be posted. Code posted does not have to be limited to PHP. All members are invited to contribute constructive criticism with the goal of improving the code. Posted code should include some background information about it and what areas you specifically would like help with.

Popular code excerpts may be moved to "Code Snippets" by the moderators.

Moderator: General Moderators

MichaelR
Forum Contributor
Posts: 148
Joined: Sat Jan 03, 2009 3:27 pm

MySQLi Connection, Statement, and Result classes

Post by MichaelR »

I've combined this topic with my earlier topic MySQLi Connection and Statement Classes and separated out the classes to make life easier for you. The documentation is reasonably long so rather than provide it here you can see it here: MySQLi Classes.

MySQLi Connection

Code: Select all

<?php

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

  // Define the namespace
  namespace Models\MySQLi;

  /**
   * MySQLiConnection
   *
   * Control a MySQLi connection
   */
  final class MySQLiConnection
  {

    /**
     * An instance of the class
     *
     * @access private
     * @static
     * @var \Models\MySQLi\MySQLiConnection|null $_instance
     */
    private static $_instance;

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

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

      // If a connection cannot be made then throw an exception
      if (!$this->_connection = call_user_func_array('mysqli_connect', $connect))
      {
        throw new \Exception('Unable to connect to the server');
      }

    }

    /**
     * Try to create an instance of the object, and then return it
     *
     * @access public
     * @static
     * @param string $hostname
     * @param string $username
     * @param string $password
     * @param string $database
     * @return \Models\MySQLi\MySQLiConnection
     */
    public static function setConnection()
    {

      // If an instance has not been created then create one
      if (!isset(self::$_instance))
      {
        self::$_instance = new self(func_get_args());
      }

      // Return the \Models\MySQLi\Connection instance
      return self::$_instance;

    }

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

    /**
     * Try to select the database
     *
     * @access public
     * @param string $database
     * @return \Models\MySQLi\MySQLiConnection
     */
    public function setDatabase($database)
    {

      // If the database cannot be selected then throw an exception
      if (!$this->_connection->select_db($database))
      {
        throw new \Exception('Unable to select the database');
      }

      // Return the \Models\MySQLi\MySQLiConnection instance
      return $this;

    }

    /**
     * Try to prepare the query
     *
     * @access public
     * @param string $query
     * @param string $result
     * @return \Models\MySQLi\MySQLiResult
     */
    public function prepareQuery($query, $result = '')
    {

      // Store all the matches to be used for bound parameters
      preg_match_all('/[idsb]:[a-z_]+/i', $query, $vars);

      // If the re-formatted query cannot be prepared then throw an exception
      if (!$stmt = $this->_connection->prepare(preg_replace('/[idsb]:[a-z_]+/i', '?', $query)))
      {
        throw new \Exception('Unable to prepare the query');
      }

      $result = '\Models\MySQLi\MySQLi' . ucfirst(strtolower($result)) . 'Result';

      // Instantiate and return the \Models\MySQLi\MySQLiResult object
      return new $result($stmt, $vars[0]);

    }

    /**
     * Prepare an INSERT statement
     *
     * @access public
     * @return \Models\MySQLi\MySQLiInsertStatement
     */
    public function prepareInsertStatement()
    {
      return new MySQLiInsertStatement;
    }

    /**
     * Prepare a DELETE statement
     *
     * @access public
     * @return \Models\MySQLi\MySQLiDeleteStatement
     */
    public function prepareDeleteStatement()
    {
      return new MySQLiDeleteStatement;
    }

    /**
     * Prepare an UPDATE statement
     *
     * @access public
     * @return \Models\MySQLi\MySQLiUpdateStatement
     */
    public function prepareUpdateStatement()
    {
      return new MySQLiUpdateStatement;
    }

    /**
     * Prepare a SELECT statement
     *
     * @access public
     * @return \Models\MySQLi\MySQLiSelectStatement
     */
    public function prepareSelectStatement()
    {
      return new MySQLiSelectStatement;
    }

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

      // If the server connection cannot be closed then throw an exception
      if (!$this->_connection->close())
      {
        throw new \Exception('Unable to close the connection');
      }

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

    }

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

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

  }
MySQLi Statement

Code: Select all

<?php

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

  // Define the namespace
  namespace Models\MySQLi;

  /**
   * MySQLiStatement
   *
   * Control a MySQLi statement
   */
  abstract class MySQLiStatement
  {

    /**
     * The table(s)
     *
     * @access protected
     * @var string $_table
     */
    protected $_table = '';

    /**
     * The field(s)
     *
     * @access protected
     * @var string $_field
     */
    protected $_field = '';

    /**
     * The SET clause(s)
     *
     * @access protected
     * @var string $_value
     */
    protected $_value = '';

    /**
     * The ON DUPLICATE KEY clause(s)
     *
     * @access protected
     * @var string $_duplicate
     */
    protected $_duplicate = '';

    /**
     * The WHERE clause(s)
     *
     * @access protected
     * @var string $_where
     */
    protected $_where = '';

    /**
     * The ORDER BY clause(s)
     *
     * @access protected
     * @var string $_group
     */
    protected $_group = '';

    /**
     * The HAVING clause(s)
     *
     * @access protected
     * @var string $_having
     */
    protected $_having = '';

    /**
     * The ORDER BY clause(s)
     *
     * @access protected
     * @var string $_order
     */
    protected $_order = '';

    /**
     * The LIMIT clause
     *
     * @access protected
     * @var string $_limit
     */
    protected $_limit = '';

    /**
     * The JOIN clause
     *
     * @access protected
     * @var string $_join
     */
    protected $_join = ' INNER JOIN ';

    /**
     * Add a table
     *
     * @access public
     * @param string|array $table
     * @param string|array $value,...
     */
    final public function addTable()
    {

      // Put the parameters into an array
      $data = func_get_args();

      // If the table parameter is an array then set the key as the alias
      if (is_array($table = $data[0]))
      {
        $table = $table[$alias = key($table)] . ' AS ' . $alias;
      }

      // Else the table is the alias
      else
      {
        $alias = $table;
      }

      // Redefine the table as the alias
      $data[0] = $alias;

      // If the statement is a SELECT then add the fields
      if (($className = get_called_class()) == 'Models\MySQLi\MySQLiSelectStatement')
      {
        call_user_func_array(array($this, '_addField'), $data);
      }

      // Otherwise if the statement is an UPDATE then add the SET clause(s)
      elseif ($className == 'Models\MySQLi\MySQLiUpdateStatement')
      {

        // Unset the table from the array
        unset($data[0]);

        // Iterate through the array and add the SET clause(s)
        foreach ($data as $value)
        {
          $this->addValue($alias . '.' . $value[0], $value[1]);
        }

      }

      // Add the table
      $this->_table .= !isset($this->_table[0]) ? $table : $this->_join . $table;

      // Set the JOIN type to INNER
      $this->_join = ' INNER JOIN ';

      // Return the statement object
      return $this;

    }

    /**
     * Add a table and the fields to be selected
     *
     * @access public
     * @param string $table
     * @param string|array $value,...
     */
    final protected function _addField()
    {

      // Put the parameters into an array
      $select = func_get_args();

      // If no fields have been selected then select all
      if (count($select) == 1)
      {
        $select[1] = '*';
      }

       // Iterate through the remaining parameters
      for ($i = 1, $n = count($select); $i < $n; ++$i)
      {

        // Convert the parameter to an array and set the key as the field alias
        $as = key($select[$i] = (array) $select[$i]);

        // If the array contains only one element then use it as the field name
        if (count($select[$i]) == 1)
        {
          $select[$i] = $select[0] . '.' . current($select[$i]);
        }

        // Otherwise replace the placeholder in the second element with the first element and use it as the field name
        else
        {
          $select[$i] = str_replace('?', $select[0] . '.' . current($select[$i]), next($select[$i]));
        }

        // If the field alias is a string then set it
        if (is_string($as))
        {
          $select[$i] .= ' AS ' . $as;
        }

      }

      // Remove the table from the array
      unset($select[0]);

      // Add the fields to be selected, if required
      if (!empty($select[1]))
      {
        $this->_field .= !isset($this->_field[0]) ? implode(', ', $select) : ', ' . implode(', ', $select);
      }

    }

    /**
     * Add a LEFT JOIN clause
     *
     * @access public
     * @param string|array $table
     * @param string|array $value,...
     */
    final public function addLeftJoin()
    {

      // Set the JOIN type to LEFT
      $this->_join = ' LEFT JOIN ';

      // Call the relevant method passing the parameters
      call_user_func_array(array($this, 'addTable'), func_get_args());

      // Return the statement object
      return $this;

    }

    /**
     * Add a RIGHT JOIN clause
     *
     * @access public
     * @param string|array $table
     * @param string|array $value,...
     */
    final public function addRightJoin()
    {

      // Set the JOIN type to RIGHT
      $this->_join = ' RIGHT JOIN ';

      // Call the relevant method passing the parameters
      call_user_func_array(array($this, 'addTable'), func_get_args());

      // Return the statement object
      return $this;

    }

    /**
     * Add a USING clause
     *
     * @access public
     * @param string $using,...
     */
    final public function addUsing()
    {

      // Join the array of parameters into a string
      $using = implode(', ', func_get_args());

      // Add the USING clause(s)
      $this->_table .= ' USING (' . $using . ')';

      // Return the statement object
      return $this;

    }

    /**
     * Add an ON clause
     *
     * @access public
     * @param string $condition
     * @param bool $or
     */
    final public function addOn($condition, $or = false)
    {

      // Put parentheses around the ON clause
      $on = '(' . $condition . ')';

      // Add the ON clause
      $this->_table .= substr($this->_table, -1, 1) != ')' ? ' ON ' . $on : ($or ? ' OR ' : ' AND ') . $on;

      // Return the statement object
      return $this;

    }

    /**
     * Add a SET clause
     *
     * @access public
     * @param string $field
     * @param string $value
     */
    final public function addValue($field, $value)
    {

      // Create the SET clause
      $value = $field . ' = ' . $value;

      // Add the SET clause
      $this->_value .= !isset($this->_value[0]) ? ' SET ' . $value : ', ' . $value;

      // Return the statement object
      return $this;

    }

    /**
     * Add an ON DUPLICATE KEY UPDATE clause
     *
     * @access public
     * @param string $field
     * @param string $value
     */
    final public function addDuplicate($field, $value)
    {

      // Create the ON DUPLICATE KEY UPDATE clause
      $duplicate = $field . ' = ' . $value;

      // Add the ON DUPLICATE KEY UPDATE clause
      $this->_duplicate .= !isset($this->_duplicate[0]) ? ' ON DUPLICATE KEY UPDATE ' . $duplicate : ', ' . $duplicate;

      // Return the statement object
      return $this;

    }

    /**
     * Add a WHERE clause
     *
     * @access public
     * @param string $condition
     * @param bool $or
     */
    final public function addWhere($condition, $or = false)
    {

      // Put parentheses around the WHERE clause
      $where = '(' . $condition . ')';

      // Add the WHERE clause
      $this->_where .= !isset($this->_where[0]) ? ' WHERE ' . $where : ($or ? ' OR ' : ' AND ') . $where;

      // Return the statement object
      return $this;

    }

    /**
     * Add (a) GROUP BY clause(s)
     *
     * @access public
     * @param string $group,...
     */
    final public function addGroup()
    {

      // Join the array of parameters into a string
      $group = implode(', ', func_get_args());

      // Add (an) GROUP BY clause(s)
      $this->_group .= !isset($this->_group[0]) ? ' GROUP BY ' . $group : ', ' . $group;

      // Return the statement object
      return $this;

    }

    /**
     * Add a HAVING clause
     *
     * @access public
     * @param string $condition
     * @param bool $or
     */
    final public function addHaving($condition, $or = false)
    {

      // Put parentheses around the HAVING clause
      $having = '(' . $condition . ')';

      // Add the HAVING clause
      $this->_having .= !isset($this->_having[0]) ? ' HAVING ' . $having : ($or ? ' OR ' : ' AND ') . $having;

      // Return the statement object
      return $this;

    }

    /**
     * Add (an) ORDER BY clause(s)
     *
     * @access public
     * @param string $order,...
     */
    final public function addOrder()
    {

      // Join the array of parameters into a string
      $order = implode(', ', func_get_args());

      // Add (an) ORDER BY clause(s)
      $this->_order .= !isset($this->_order[0]) ? ' ORDER BY ' . $order : ', ' . $order;

      // Return the statement object
      return $this;

    }

    /**
     * Add a LIMIT clause
     *
     * @access public
     * @param int limit
     * @param int offset
     */
    final public function setLimit($limit, $offset = 0)
    {

      // Set the offset and limit
      $this->_limit = ' LIMIT ' . $offset . ', ' . $limit;

      // Return the statement object
      return $this;

    }

    /**
     * Prepare the query
     *
     * @access public
     * @return \Models\MySQLi\MySQLiResult
     */
    final public function prepareQuery()
    {
      return MySQLiConnection::setConnection()->prepareQuery($this, static::STATEMENT);
    }

    /**
     * Abstract method to convert object into a string
     *
     * @access public
     */
    abstract public function __toString();

  }
MySQLi Insert Statement

Code: Select all

<?php

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

  // Define the namespace
  namespace Models\MySQLi;

  /**
   * MySQLiInsertStatement
   *
   * Control a MySQLi INSERT statement
   */
  final class MySQLiInsertStatement extends MySQLiStatement
  {

    /**
     * Define the statement as an INSERT
      */
    const STATEMENT = 'Insert';

    /**
     * Return a string of the query
     *
     * @access public
     * @return string
     */
    public function __toString()
    {
      return 'INSERT INTO ' . $this->_table . $this->_value . $this->_duplicate;
    }

  }
MySQLi Delete Statement

Code: Select all

<?php

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

  // Define the namespace
  namespace Models\MySQLi;

  /**
   * MySQLiDeleteStatement
   *
   * Control a MySQLi DELETE statement
   */
  final class MySQLiDeleteStatement extends MySQLiStatement
  {

    /**
     * Define the statement as a DELETE
      */
    const STATEMENT = 'Delete';

    /**
     * Return a string of the query
     *
     * @access public
     * @return string
     */
    public function __toString()
    {
      return 'DELETE FROM ' . $this->_table . $this->_where . $this->_order . $this->_limit;
    }

  }
MySQLi Update Statement

Code: Select all

<?php

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

  // Define the namespace
  namespace Models\MySQLi;

  /**
   * MySQLiUpdateStatement
   *
   * Control a MySQLi UPDATE statement
   */
  final class MySQLiUpdateStatement extends MySQLiStatement
  {

    /**
     * Define the statement as an UPDATE
      */
    const STATEMENT = 'Update';

    /**
     * Return a string of the query
     *
     * @access public
     * @return string
     */
    public function __toString()
    {
      return 'UPDATE ' . $this->_table . $this->_value . $this->_where . $this->_order . $this->_limit;
    }

  }
MySQLi Select Statement

Code: Select all

<?php

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

  // Define the namespace
  namespace Models\MySQLi;

  /**
   * MySQLiSelectStatement
   *
   * Control a MySQLi SELECT statement
   */
  final class MySQLiSelectStatement extends MySQLiStatement
  {

    /**
     * Define the statement as a SELECT
      */
    const STATEMENT = 'Select';

    /**
     * Return a string of the query
     *
     * @access public
     * @return string
     */
    public function __toString()
    {
      return 'SELECT ' . $this->_field . ' FROM ' . $this->_table . $this->_where . $this->_group . $this->_having . $this->_order . $this->_limit;
    }

  }
MySQLi Result

Code: Select all

<?php

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

  // Define the namespace
  namespace Models\MySQLi;

  /**
   * MySQLiResult
   *
   * Control a MySQLi result
   */
  class MySQLiResult
  {

    /**
     * A MySQLi_STMT object
     *
     * @access protected
     * @var MySQLi_STMT $_stmt
     */
    protected $_stmt;

    /**
     * An array of the bound parameters
     *
     * @access protected
     * @var array $_vars
     */
    protected $_vars;

    /**
     * An array of the bound results
     *
     * @access protected
     * @var array $_refs
     */
    protected $_refs;

    /**
     * Store the MySQLi_STMT object and bind the parameters
     *
     * @access public
     * @param MySQLi_STMT $stmt
     * @param array $vars
     */
    final public function __construct($stmt, $vars)
    {

      // Store the MySQLi_STMT object
      $this->_stmt = $stmt;

      // If parameters have been provided then bind them
      if (!empty($vars))
      {

        // Establish the first element of the array to concatenate with the datatype
        $ref = array('');

        // Iterate through the parameters
        foreach ($vars as $var)
        {

          // Concatenate the first element of $ref with the parameter's datatype
          $ref[0] .= strtok($var, ':');

          // Store the parameter name
          $ref[ ] =& $this->_vars[strtok('')];

        }

        // If the parameters cannot be bound then throw an exception
        if (!call_user_func_array(array($stmt, 'bind_param'), $ref))
        {
          throw new \Exception('Unable to bind the parameters');
        }

      }

    }

    /**
     * Set the values of the parameters
     *
     * @access public
     * @param mixed $value,...
     * @return \Models\MySQLi\MySQLiResult
     */
    final public function setParameters()
    {

      // Iterate through the parameters and set the values
      foreach (func_get_args() as $var)
      {
        $this->_vars[strtok($var, ':')] = strtok('');
      }

      // Execute the query
      $this->execute();

      // Return the \Models\MySQLi\MySQLiResult object
      return $this;

    }

    /**
     * Execute the query
     *
     * @access public
     * @return \Models\MySQLi\MySQLResult
     */
    final public function execute()
    {

      // If the query cannot be executed then throw an exception
      if (!$this->_stmt->execute())
      {
        throw new \Exception('Unable to execute the query');
      }

      // Return the \Models\MySQLi\MySQLiResult object
      return $this;

    }

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

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

    /**
     * Prepare an INSERT statement
     *
     * @access public
     * @return \Models\MySQLi\MySQLiInsertStatement
     */
    final public function prepareInsertStatement()
    {
      return new MySQLiInsertStatement;
    }

    /**
     * Prepare a DELETE statement
     *
     * @access public
     * @return \Models\MySQLi\MySQLiDeleteStatement
     */
    final public function prepareDeleteStatement()
    {
      return new MySQLiDeleteStatement;
    }

    /**
     * Prepare an UPDATE statement
     *
     * @access public
     * @return \Models\MySQLi\MySQLiUpdateStatement
     */
    final public function prepareUpdateStatement()
    {
      return new MySQLiUpdateStatement;
    }

    /**
     * Prepare a SELECT statement
     *
     * @access public
     * @return \Models\MySQLi\MySQLiSelectStatement
     */
    final public function prepareSelectStatement()
    {
      return new MySQLiSelectStatement;
    }

  }
MySQLi Insert Result

Code: Select all

<?php

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

  // Define the namespace
  namespace Models\MySQLi;

  /**
   * MySQLiInsertResult
   *
   * Control a MySQLi INSERT result
   */
  final class MySQLiInsertResult extends MySQLiResult
  {

    /**
     * Return the ID of the last inserted or updated row
     *
     * @access public
     * @return int
     */
    public function getInsertID()
    {
      return $this->_stmt->insert_id;
    }

  }
MySQLi Delete Result

Code: Select all

<?php

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

  // Define the namespace
  namespace Models\MySQLi;

  /**
   * MySQLiDeleteResult
   *
   * Control a MySQLi DELETE result
   */
  final class MySQLiDeleteResult extends MySQLiResult
  {

    /**
     * Return the number of affected rows
     *
     * @access public
     * @return int
     */
    public function getAffectedRows()
    {
      return $this->_stmt->affected_rows;
    }

  }
MySQLi Update Result

Code: Select all

<?php

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

  // Define the namespace
  namespace Models\MySQLi;

  /**
   * MySQLiUpdateResult
   *
   * Control a MySQLi UPDATE result
   */
  final class MySQLiUpdateResult extends MySQLiResult
  {

    /**
     * Return the number of affected rows
     *
     * @access public
     * @return int
     */
    public function getAffectedRows()
    {
      return $this->_stmt->affected_rows;
    }

  }
MySQLi Select Result

Code: Select all

<?php

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

  // Define the namespace
  namespace Models\MySQLi;

  /**
   * MySQLiSelectResult
   *
   * Control a MySQLi SELECT result
   */
  final class MySQLiSelectResult extends MySQLiResult implements \Iterator
  {

    /**
     * An array or object of the data
     *
     * @access private
     * @var array|StdClass $_data
     */
    private $_data;

    /**
     * The current iteration index
     *
     * @access private
     * @var int $_index
     */
    private $_index = 0;

    /**
     * Bind the result
     *
     * @access private
     */
    private function _bindResult()
    {

      // If the result metadata cannot be retrieved then throw an exception
      if (!$meta = $this->_stmt->result_metadata())
      {
        throw new \Exception('Unable to retrieve the result metadata');
      }

      // Iterate through the fields and set a reference with the bound parameters
      while ($name = $meta->fetch_field())
      {
        $refs[] =& $this->_refs[$name->name];
      }

      // Free the metadata result
      $meta->free_result();

      // If the result cannot be bound then throw an exeption
      if (!call_user_func_array(array($this->_stmt, 'bind_result'), $refs))
      {
        throw new \Exception('Unable to bind the result');
      }

    }

    /**
     * Get one row of the result
     *
     * @access public
     * @param bool $store
     * @return object|array
     */
    public function getOne($store = false)
    {

      // Get all the rows of the result
      $this->getAll($store);

      // Set the $_data property to its first element
      $this->_data = $this->_data[0];

      // Return the \Models\MySQLi\MySQLiResult object
      return $this;

    }

    /**
     * Get all rows of the result
     *
     * @access public
     * @param bool $store
     * @return array
     */
    public function getAll($store = false)
    {

      // If the result cannot be stored then throw an exception
      if ($store && !$this->_stmt->store_result())
      {
        throw new \Exception('Unable to store the result set');
      }

      // Bind the result
      $this->_bindResult();

      // Iterate through the rows
      while ($return = $this->_stmt->fetch())
      {

        // Iterate through the bound results and use the associative keys
        foreach ($this->_refs as $key => $value)
        {
          $row[$key] = $value;
        }

        // Convert the row into an object and add to the result array
        $result[] = (object) $row;

      }

      // Throw an exception if there was an error
      if ($return === false)
      {
        throw new \Exception('Unable to fetch the row');
      }

      // If no rows were returned then set $result to an array with an empty object
      if (!isset($result))
      {
        $result = array((object) array());
      }

      // Set the result
      $this->_data = $result;

      // Return the \Models\MySQLi\MySQLiResult object
      return $this;

    }

    /**
     * Return the number of selected rows
     *
     * @access public
     * @return int
     */
    public function getNumRows()
    {
      return $this->_stmt->num_rows;
    }

    /**
     * Rewind the iterator
     *
     * @access public
     */
    public function rewind()
    {
      $this->_index = 0;
    }

    /**
     * Validate the existence of the next element
     *
     * @access public
     * @return bool
     */
    public function valid()
    {
      return isset($this->_data[$this->_index]);
    }

    /**
     * Return the current key
     *
     * @access public
     * @return int
     */
    public function key()
    {
      return $this->_index;
    }

    /**
     * Return the current value
     *
     * @access public
     * @return mixed
     */
    public function current()
    {
      return $this->_data[$this->_index];
    }

    /**
     * Increment the iteration index
     *
     * @access public
     */
    public function next()
    {
      ++$this->_index;
    }

    /**
     * Get the field data
     *
     * @access public
     * @param string $field
     * @return mixed
     */
    public function __get($field)
    {

      // If the field data is not present then return null
      if (!isset($this->_data->$field))
      {
        return null;
      }

      // Return the field data
      return $this->_data->$field;

    }

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

    /**
     * Free the result
     *
     * @access public
     * @return \Models\MySQLi\MySQLiResult
     */
    final public function freeResult()
    {

      // Free the result
      $this->_stmt->free_result();

      // Return the \Models\MySQLi\MySQLiResult object
      return $this;

    }

  }
Last edited by MichaelR on Sun Jun 19, 2011 6:23 am, edited 21 times in total.
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: MySQL Result Class

Post by Christopher »

Seems strange to put the query in the result set. I think of a result set as a container to allow access/iteration over the set. And lazy loading of records as you iterate.
(#10850)
MichaelR
Forum Contributor
Posts: 148
Joined: Sat Jan 03, 2009 3:27 pm

Re: MySQL Result Class

Post by MichaelR »

I'm not sure where else would be a good place to put it. I could do it in the *Statement classes, I suppose, but then they're just to build the SQL statement. And the Connection class is to manipulate the connection. I went for what I thought would be most appropriate.
Last edited by MichaelR on Thu Sep 16, 2010 4:43 pm, edited 1 time in total.
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Re: MySQL Result Class

Post by John Cartwright »

I'm not sure why you are returning a result set for queries based on INSERT/UPDATE (I only speculate as much since you include operators for the insert id and affected rows.

I would either, seperate this class to a base abstract class, which is inherited by the different query operators.

Something like

Code: Select all

abstract class Mysql_Result_Abstract
{
   public function __construct($query, $connection);
}

class Mysql_Result_Select extends Mysql_Result_Abstract
{
   public function getCount();
   public function getOne();
   public function getAll();
   public function getRow();
}

class Mysql_Result_Update extends Mysql_Result_Abstract
{
   public function getAffected();
}
 
class Mysql_Result_Insert extends Mysql_Result_Abstract
{
   public function getInsertId();
}
MichaelR
Forum Contributor
Posts: 148
Joined: Sat Jan 03, 2009 3:27 pm

Re: MySQL Result Class

Post by MichaelR »

That's a very good idea, John Cartwright. Thanks.

I'll update the original post (and the previous article) accordingly.
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Re: MySQL Result Class

Post by josh »

The way that works in Zend, insert() returns an instance of the statement class, so you can chain inserts

$db->insert(1)->insert(2)->insert(3)

If you need the last Insert Ids, that allows you to break the chain

Code: Select all

$db->insert(1);
$db->lastInsertId();
//etc ..
MichaelR
Forum Contributor
Posts: 148
Joined: Sat Jan 03, 2009 3:27 pm

Re: MySQL Result Class

Post by MichaelR »

Thanks for the suggestion, josh. I've added in methods to allow chaining of INSERT, UPDATE, and DELETE queries. Also added a FreeResult() method.

Edit: I've decided that having separate *Insert, *Update, and *DeleteResult classes is pointless. GetID() and GetAffected() have moved to the MySQLConnection class, and so has the running of the query (which hopefully addresses Christopher's issue).
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Re: MySQL Result Class

Post by josh »

MichaelR wrote:Edit: I've decided that having separate *Insert, *Update, and *DeleteResult classes is pointless.
Not true. You just need a "facade" in front of them (its a design pattern). Having them separate makes it a lot better for the programmers working on this framework. The facade makes sure it can be used from a single component.

For example, in Zend_Db

Code: Select all

$db = $this->adapter(); // instance of Zend_Db
$db->select() // chaining on an instance of Zend_Db_Select now
   ->from('table') // still chaning on that select object
   ->where('foo=?','bar') // Zend_Db_Select uses the quote method of Zend_Db_Adapter internally...
   ->query() // passes a select string to zend_db->query() and returns Zend_Db_Statement
   ->fetchAll() // gets an array from the statement object

// etc..

As you can see, the user only directly instantiates one class, the Zend_Db component, from there they can call other components through a "facade pattern". Although making too much use of facades results in cruft and can, if abused, defeat the use of OOP. This Zend_Db example is a good use of a facade (some might call it just a factory method instead of a facade but also consider example 2).

Ex. 2

Code: Select all

$select = new Zend_Db_Select();
$select->from('table')->where('foo =?','bar');
$result = $this->adapter()->fetchAll($select);
print_r($result); // PHP Array
MichaelR
Forum Contributor
Posts: 148
Joined: Sat Jan 03, 2009 3:27 pm

Re: MySQL Result Class

Post by MichaelR »

I have a separate class for the SELECT result because you actually do stuff with the result; fetching and whatnot. With the others, you're not doing anything with a result set because there is none. *insert_id and *affected_rows query the connection resource, not a result one, so I thought it best to have them as methods in the *Connection class. I've still kept separate INSERT, UPDATE, and DELETE statement classes though (the preparing of the SQL statement).

For example:

Code: Select all

$connection = MySQLConnection::SetConnection();

$result = $connection->PrepareSelectStatement()
  ->AddField('users', 'id', 'username', 'password')
  ->AddWhere('username = ? AND password = MD5(?)', $_POST['username'], $_POST['password'])
  ->Query() // Returns instance of MySQLResult
  ->GetAll();

$connection->PrepareInsertStatement()
  ->SetTable('users')
  ->AddValue('username', $_POST['username'])
  ->AddValue('password', $_POST['password'], 'MD5(?)')
  ->Query() // Returns static instance of MySQLConnection class
    ->PrepareDeleteStatement()
      ->SetTable('users')
      ->AddWhere('id = ?', $connection->GetID(), false);
Last edited by MichaelR on Mon Sep 20, 2010 10:54 am, edited 2 times in total.
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Re: MySQL Result Class

Post by josh »

MichaelR wrote: I thought it best to have them as methods in the *Connection class. I've still kept separate INSERT, UPDATE, and DELETE statement classes though (the preparing of the SQL statement).
I misunderstood then. By the way... http://www.refactoring.com/catalog/repl ... bject.html theres a name for that (sort of)
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Re: MySQL Result Class

Post by John Cartwright »

MichaelR wrote:Edit: I've decided that having separate *Insert, *Update, and *DeleteResult classes is pointless. GetID() and GetAffected() have moved to the MySQLConnection class, and so has the running of the query (which hopefully addresses Christopher's issue).
I'm not sure why this would be a good idea. I would say it would be more beneficial to the programmer to only expose relevant methods based on the operation, i.e., fascading.
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Re: MySQL Result Class

Post by josh »

Thats something that's challenged me. Would you rather have $db->inserter()->insert(), or $db->insert()

You could have both. The first one arguably breaks the law of Demeter. I would argue to have both. For example in Zend, controllers have access to request objects, which in turn can get request paramaters, but this is a utility method directly on the controller, $controller->getParam() - so you don't have to type out $controller->getRequest()->getParam()
MichaelR
Forum Contributor
Posts: 148
Joined: Sat Jan 03, 2009 3:27 pm

Re: MySQLi Result Class

Post by MichaelR »

I've updated the class to use the MySQLi extension.
MichaelR
Forum Contributor
Posts: 148
Joined: Sat Jan 03, 2009 3:27 pm

Re: MySQLi Result Class

Post by MichaelR »

I've updated the classes to account for suggestions by josh and John Cartwright. Statements can be chained and there are separate classes for INSERT, DELETE, UPDATE, and SELECT results. I've also implemented the Iterator interface on the MySQLiSelectResult class to allow iteration over the result set after calling the getAll() method, and added the __get magic method to allow easy access of the row after calling the getOne() method.
Last edited by MichaelR on Sun Jun 19, 2011 6:23 am, edited 1 time in total.
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Re: MySQLi Result Class

Post by Weirdan »

wouldn't using mysqli's OO interface instead of procedural interface make more sense here?
Post Reply