Page 1 of 1

MySQLi Connection and Statement Classes

Posted: Sun Nov 29, 2009 3:50 pm
by MichaelR

Code: Select all

  /**
   * Squiloople Framework
   *
   * LICENSE: Feel free to use and redistribute this code.
   *
   * @author Michael Rushton 
   * @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 (!mysqli_select_db($this->_connection, $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 = mysqli_prepare($this->_connection, 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 (!mysqli_close($this->_connection))
      {
        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();
    }

  }

  /**
   * 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();

  }

  /**
   * 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;
    }

  }

  /**
   * 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;
    }

  }

  /**
   * 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;
    }

  }

  /**
   * 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;
    }

  }
Please see my MySQLi Result Class topic for the class I wrote some time after posting this thread for documentation.

Re: MySQL Class

Posted: Sun Nov 29, 2009 5:09 pm
by jayshields
Seems very similar to the class that's provided with Code Igniter, and probably many others. Any reason why you wrote your own?

Re: MySQL Class

Posted: Sun Nov 29, 2009 5:54 pm
by BlaineSch
You should probably update to use mysqli since mysql is deprecated I believe. Mysqli is now accepted.

Re: MySQL Class

Posted: Sun Nov 29, 2009 6:22 pm
by MichaelR
jayshields wrote:Seems very similar to the class that's provided with Code Igniter, and probably many others. Any reason why you wrote your own?
I find it easier to understand code if I've written it myself. I also find that I can write code that is much less complex than ready-made code while still retaining the same functionality (though I don't claim that my class can do everything that other classes can do; only that those functions common to both are much more complex in other classes).

And I've never checked out Code Igniter. I got the ideas from Zend. Although didn't actually look at the code behind it; only at the manual to see what can be done with the class.
BlaineSch wrote:You should probably update to use mysqli since mysql is deprecated I believe. Mysqli is now accepted.
It seems a bit superfluous to write a database class to use a database class. And I don't think MySQL is deprecated.

Re: MySQL Class

Posted: Mon Dec 14, 2009 11:13 pm
by Griven
http://www.php.net/manual/en/mysqli.overview.php
What is PHP's MySQL Extension?

This is the original extension designed to allow you to develop PHP applications that interact with a MySQL database. The mysql extension provides a procedural interface and is intended for use only with MySQL versions older than 4.1.3. This extension can be used with versions of MySQL 4.1.3 or newer, but not all of the latest MySQL server features will be available.
If you are using a version of MySQL newer than 4.1.3, then it is highly recommended that you make the switch to MySQLi.

Re: MySQLi Connection and Statement Classes

Posted: Wed Sep 22, 2010 3:49 pm
by MichaelR
Griven wrote:If you are using a version of MySQL newer than 4.1.3, then it is highly recommended that you make the switch to MySQLi.
I've updated the class to use the MySQLi extension.

Re: MySQLi Connection and Statement Classes

Posted: Thu Sep 23, 2010 2:01 am
by Karlos94

Code: Select all

    private function __construct($connect) {

      if (!$this->connection = mysqli_connect($connect[0], $connect[1], $connect[2])) {
        throw new Exception('Unable to connect to the server.');
      }

      if (isset($connect[3])) {
        $this->SetDatabase($connect[3]);
      }

    }
By what your doing, maybe you should check if it's an array first?

Code: Select all

    public function PrepareInsertStatement() {
      return new MySQLiInsertStatement($this->connection);
    }

    public function PrepareDeleteStatement() {
      return new MySQLiDeleteStatement($this->connection);
    }

    public function PrepareUpdateStatement() {
      return new MySQLiUpdateStatement($this->connection);
    }

    public function PrepareSelectStatement() {
      return new MySQLiSelectStatement($this->connection);
    }
No, no, no, no, no, no!
Why are you just putting pre-defined functions in your class without modifying them? All your doing is a putting them in a wrapper, so I'd recommend trying that bit again.

Re: MySQLi Connection and Statement Classes

Posted: Thu Sep 23, 2010 2:37 am
by MichaelR
By what your doing, maybe you should check if it's an array first?
It will be an array because the method that calls the __construct passes func_get_args() as the parameter.
Why are you just putting pre-defined functions in your class without modifying them? All your doing is a putting them in a wrapper, so I'd recommend trying that bit again.
It's not just a wrapper. It's returning the MySQLiStatement classes fluently. So rather than the following:

Code: Select all

$con = MySQLiConnection::SetConnection();

$sql = new MySQLiSelectStatement($con->GetConnection());

$sql->AddField('users');

$result = $con->Query($sql);
You can do:

Code: Select all

$con = MySQLiConnection::SetConnection();

$result = $con->PrepareSelectStatement()
  ->AddField('users');
  ->Query();

Re: MySQLi Connection and Statement Classes

Posted: Wed Sep 29, 2010 2:59 pm
by Karlos94
Within the method in your class, your only adding a wrapper in a sense.. I know what you mean but you are only using a wrapper, it may be more fluent to write but it would be more efficient to type it..

Maybe adapt the method or something?

Re: MySQLi Connection and Statement Classes

Posted: Tue Oct 05, 2010 4:17 pm
by MichaelR
If it's more efficient to not use the wrapper methods then they don't need to be used. I've purposefully allowed the developer to have two ways -- the first is to use a fluent interface using the wrapper and the second is to directly instantiate a new statement object. I prefer to use a fluent interface.

As a note, I've now updated these classes (and the MySQLi Result class) to use prepared statements, like so:

Code: Select all

...

  try {

    $stmt = $connection->PrepareSelectStatement()
      ->AddField('users')
      ->AddWhere('id = i:id OR username = s:name')
      ->Prepare();

    try {

      $one = $stmt->BindParam('name:Michael', 'id:1')
        ->GetRows();

      foreach ($one as $row) {
        echo $row->id . ': ' . $row->username . PHP_EOL . '<br />' . PHP_EOL;
      }

    }

    catch (Exception $e) {
      echo $e->getMessage();
    }

    try {

      $two = $stmt->BindParam('id:10', "name:O'Connor")
        ->GetRows();

      foreach ($two as $row) {
        echo $row->id . ': ' . $row->username . PHP_EOL . '<br />' . PHP_EOL;
      }

    }

    catch (Exception $e) {
      echo $e->getMessage();
    }

  }

  catch (Exception $e) {
    echo $e->getMessage();
  }

...