MySQLi Connection and Statement Classes
Posted: Sun Nov 29, 2009 3:50 pm
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;
}
}