PHP Developers Network

A community of PHP developers offering assistance, advice, discussion, and friendship.
 
Loading
It is currently Thu Nov 14, 2019 12:48 pm

All times are UTC - 5 hours




Post new topic Reply to topic  [ 10 posts ] 
Author Message
PostPosted: Sun Nov 29, 2009 4:50 pm 
Offline
Forum Contributor

Joined: Sat Jan 03, 2009 4:27 pm
Posts: 148
Syntax: [ Download ] [ Hide ]
  /**
   * 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 topic for the class I wrote some time after posting this thread for documentation.


Last edited by MichaelR on Sat Jun 18, 2011 6:48 am, edited 66 times in total.

Top
 Profile  
 
 Post subject: Re: MySQL Class
PostPosted: Sun Nov 29, 2009 6:09 pm 
Offline
DevNet Resident
User avatar

Joined: Mon Aug 22, 2005 12:11 pm
Posts: 1912
Location: Leeds/Manchester, England
Seems very similar to the class that's provided with Code Igniter, and probably many others. Any reason why you wrote your own?


Top
 Profile  
 
 Post subject: Re: MySQL Class
PostPosted: Sun Nov 29, 2009 6:54 pm 
Offline
Forum Commoner
User avatar

Joined: Sun Jun 07, 2009 4:28 pm
Posts: 28
Location: Trapped in my own little world.
You should probably update to use mysqli since mysql is deprecated I believe. Mysqli is now accepted.


Top
 Profile  
 
 Post subject: Re: MySQL Class
PostPosted: Sun Nov 29, 2009 7:22 pm 
Offline
Forum Contributor

Joined: Sat Jan 03, 2009 4:27 pm
Posts: 148


Top
 Profile  
 
 Post subject: Re: MySQL Class
PostPosted: Tue Dec 15, 2009 12:13 am 
Offline
Forum Contributor

Joined: Sat May 09, 2009 8:23 pm
Posts: 165


Top
 Profile  
 
PostPosted: Wed Sep 22, 2010 3:49 pm 
Offline
Forum Contributor

Joined: Sat Jan 03, 2009 4:27 pm
Posts: 148


Top
 Profile  
 
PostPosted: Thu Sep 23, 2010 2:01 am 
Offline
Forum Newbie

Joined: Wed Sep 22, 2010 12:55 am
Posts: 3


Top
 Profile  
 
PostPosted: Thu Sep 23, 2010 2:37 am 
Offline
Forum Contributor

Joined: Sat Jan 03, 2009 4:27 pm
Posts: 148


Top
 Profile  
 
PostPosted: Wed Sep 29, 2010 2:59 pm 
Offline
Forum Newbie

Joined: Wed Sep 22, 2010 12:55 am
Posts: 3
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?


Top
 Profile  
 
PostPosted: Tue Oct 05, 2010 4:17 pm 
Offline
Forum Contributor

Joined: Sat Jan 03, 2009 4:27 pm
Posts: 148
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:

Syntax: [ Download ] [ Hide ]
...

  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();
  }

...
 


Top
 Profile  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 10 posts ] 

All times are UTC - 5 hours


Who is online

Users browsing this forum: No registered users and 2 guests


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Jump to:  
cron
Powered by phpBB® Forum Software © phpBB Group