MySQLi Classes
MySQLi Connection
<?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 $_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 database 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-zA-Z0-9_$]+|`[^`]+`)/', $query, $vars);
// If the re-formatted query cannot be prepared then throw an exception
if (!$stmt = $this->_connection->prepare(preg_replace('/[idsb]:([a-zA-Z0-9_$]+|`[^`]+`)/', '?', $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('Attempt to clone ' . __CLASS__);
}
/**
* Disconnect from the server
*
* @access public
*/
public function __destruct()
{
$this->disconnect();
}
}
MySQLi Statement
<?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;
}
// 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|array $table
* @param string|array $value,...
*/
final protected function _addField()
{
// Put the parameters into an array
$select = func_get_args();
// Select all fields if required
if (in_array('*', $select))
{
// If the query was unsuccessful then throw an exception
if (!$stmt = MySQLiConnection::setConnection()->getConnection()->query("SHOW COLUMNS FROM " . current((array) $select[0])))
{
throw new \Exception("Unable to show columns");
}
// Iterate over each column and add to the array
while ($result = $stmt->fetch_object())
{
array_push($select, $result->Field);
}
// Throw an exception if there was an error
if ($result === false)
{
throw new \Exception("Unable to fetch the column(s)");
}
}
// If the table value is an array then reset to the key
if (is_array($select[0]))
{
$select[0] = key($select[0]);
}
// Iterate through the remaining parameters
for ($i = 1, $n = count($select); $i < $n; ++$i)
{
// Remove the field and continue the loop if the current field is a star (to select all fields)
if (current((array) $select[$i]) == '*')
{
unset($select[$i]); continue;
}
// 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 (count($select) > 0)
{
$this->_field .= !isset($this->_field[0]) ? implode(', ', $select) : ', ' . implode(', ', $select);
}
}
/**
* Add (a) field(s)
*
* @access public
* @param string $field,...
* @return object
*/
final public function addField()
{
// Iterate over each field
foreach (func_get_args() as $field)
{
// Add the alias if one has been supplied
if (is_string($alias = key((array) $field)))
{
$field = current($field) . ' AS ' . $alias;
}
// Add the field
$this->_field .= !isset($this->_field[0]) ? $field : ', ' . $field;
}
// Return the object
return $this;
}
/**
* 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
<?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
<?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
<?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
<?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
<?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;
/**
* Store the MySQLi_STMT object and bind the parameters
*
* @access public
* @param MySQLi_STMT $stmt
* @param array $vars
*/
final public function __construct(\MySQLi_STMT $stmt, array $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\MySQLiResult
*/
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
*/
final public function getErrno()
{
return $this->_stmt->errno;
}
/**
* Return the error
*
* @access public
* @return int
*/
final 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
<?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
<?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
<?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
<?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;
/**
* An array of the bound results
*
* @access protected
* @var array $_refs
*/
protected $_refs;
/**
* 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->table ?: 'DUAL'][$name->name];
}
// Free the metadata result
$meta->free_result();
// If the result cannot be bound then throw an exception
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 \Models\MySQLi\MySQLiResult|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 = true)
{
// 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 over each table of the bound results
foreach ($this->_refs as $table => $fields)
{
// Iterate over each field of the table and add to the associative array
foreach ($fields as $field => $value)
{
$row[$table][$field] = $value;
}
// Convert each table's field data into an object
$row[$table] = (object) $row[$table];
}
// 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(s)');
}
// Set $result to an array with an empty object if no rows were returned
if (!isset($result))
{
$result = array((object) 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()
{
reset($this->_data);
}
/**
* Validate the existence of the next element
*
* @access public
* @return bool
*/
public function valid()
{
return isset($this->_data[$this->key()]);
}
/**
* Return the current key
*
* @access public
* @return int
*/
public function key()
{
return key($this->_data);
}
/**
* Return the current value
*
* @access public
* @return mixed
*/
public function current()
{
return current($this->_data);
}
/**
* Increment the iteration index
*
* @access public
*/
public function next()
{
next($this->_data);
}
/**
* Get the field data
*
* @access public
* @param string $field
* @return mixed
*/
public function __get($field)
{
// Throw an exception if the data is not an object
if (!is_object($this->_data))
{
throw new Exception('Unable to use magic getter method on a MySQL array result set.');
}
// Return the field data
return isset($this->_data->$field) ? $this->_data->$field : null;
}
/**
* 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
*/
public function freeResult()
{
// Free the result
$this->_stmt->free_result();
// Return the \Models\MySQLi\MySQLiResult object
return $this;
}
/**
* Close the statement
*
* @access public
*/
public function __destruct()
{
$this->_stmt->close();
}
}
To open up a connection to a MySQL database, call the \Models\MySQLi\MySQLiConnection::setConnection() static method. Pass as the optional parameters the hostname, the username, the password, the database, the port, and the socket. This will create an instance of the object, or if an instance has already been established, return that instance. If the instance is to be returned no parameters need be passed:
try
{
$connection = \Models\MySQLi\MySQLiConnection::setConnection('hostname', 'username', 'password');
}
catch (Exception $e)
{
echo $e->getMessage();
}
To return the connection object, call the getConnection() method. This method does not accept any parameters:
$connection->getConnection();
To select the default database, call the setDatabase() method. This method accepts only one parameter; the database name:
try
{
$connection->setDatabase('database');
}
catch (Exception $e)
{
echo $e->getMessage();
}
To query the database call the prepareQuery() method passing as the first parameter the SQL query and as the optional second parameter one of ‘Insert’, ‘Delete’, ‘Update’, or ‘Select’ depending on the type of query being run. You must supply one of these if you wish to use any methods which involve returning the last inserted ID (‘Insert’), the number of affected rows (‘Delete’ and ‘Update’), the number of rows selected (‘Select’), or the rows themselves (‘Select’). If you wish to bind parameters, replace the value with the (unquoted) form “type:name”, where “type” is one of i (for integer), d (for double), s (for string), or b (for blob). This will return a \Models\MySQLi\MySQLiResult object:
try
{
$connection->prepareQuery("SELECT * FROM users WHERE id = i:id", 'Select');
}
catch (Exception $e)
{
echo $e->getMessage();
}
To disconnect from the database, and remove the instance, call the disconnect() method. This method does not accept any parameters:
try
{
$connection->disconnect();
}
catch (Exception $e)
{
echo $e->getMessage();
}
To prepare an INSERT statement, either call the prepareInsertStatement() method or create a new \Models\MySQLi\MySQLiInsertStatement object (the latter of which will automatically call \Models\MySQLi\MySQLiConnection::setConnection()). Neither of these accept any parameters:
try
{
$sql = $connection->prepareInsertStatement();
}
catch (Exception $e)
{
echo $e->getMessage();
}
// Or:
try
{
$sql = new \Models\MySQLi\MySQLiInsertStatement();
}
catch (Exception $e)
{
echo $e->getMessage();
}
To set the table in which data is to be inserted, call the addTable() method. This method accepts only only parameter; the table name:
try
{
$sql = $connection->prepareInsertStatement()
->addTable('users');
}
catch (Exception $e)
{
echo $e->getMessage();
}
// INSERT INTO users
To add a SET clause to the statement, call the addValue() method. Pass as the first parameter the column name, and as the second parameter the value (which can be prepared as shown above):
try
{
$sql = $connection->PrepareInsertStatement()
->addTable('users')
->addValue('username', 's:username')
->addValue('password', 'MD5(s:password)')
->addValue('location', 1);
}
catch (Exception $e)
{
echo $e->getMessage();
}
// INSERT INTO users SET username = s:username, password = MD5(s:password), location = 1
To add an ON DUPLICATE KEY UPDATE clause to the statement, call the addDuplicate() method. This method is identical in format to the addValue() method:
try
{
$sql = $connection->PrepareInsertStatement()
->addTable('users')
->addValue('username', 's:username')
->addValue('password', 'MD5(s:password)')
->addDuplicate('password', 'MD5(s:passcode)');
}
catch (Exception $e)
{
echo $e->getMessage();
}
// INSERT INTO users SET username = s:username, password = MD5(s:password) ON DUPLICATE KEY UPDATE password = MD5(s:passcode)
To return the statement, call the object as a string.
try
{
$sql = $connection->prepareInsertStatement()
->addTable('users')
->addValue('username', 's:username')
->addValue('password', 'MD5(s:password)')
->addDuplicate('password', 'MD5(s:passcode)');
echo $sql;
}
catch (Exception $e)
{
echo $e->getMessage();
}
// Outputs: INSERT INTO users SET username = s:username, password = MD5(s:password) ON DUPLICATE KEY UPDATE password = MD5(s:passcode)
To prepare a DELETE statement, either call the prepareDeleteStatement() method or create a new \Models\MySQLi\MySQLiDeleteStatement object (the latter of which will automatically call \Models\MySQLi\MySQLiConnection::setConnection()). Neither of these accept any parameters:
try
{
$sql = $connection->prepareDeleteStatement();
}
catch (Exception $e)
{
echo $e->getMessage();
}
// Or:
try
{
$sql = new \Models\MySQLi\MySQLiDeleteStatement();
}
catch (Exception $e)
{
echo $e->getMessage();
}
To add a WHERE clause to the statement, call the addWhere() method. Pass as the first parameter the condition, using the bound parameters if required:
try
{
$sql = $connection->prepareDeleteStatement()
->addTable('users')
->addWhere('username = s:username OR email_address = s:email');
}
catch (Exception $e)
{
echo $e->getMessage();
}
// DELETE FROM users WHERE (username = s:username OR email_address = s:email)
Each addWhere() method encloses the condition in parentheses. Every subsequent call will link the condition with the previous using the AND operator. If an OR operator is required, pass as the second parameter true:
try
{
$sql = $connection->prepareDeleteStatement()
->addTable('users')
->addWhere('username = s:username')
->addWhere('password = MD5(s:password)')
->addWhere('email_address = s:email', true);
}
catch (Exception $e)
{
echo $e->getMessage();
}
// DELETE FROM users WHERE (username = s:username) AND (password = MD5(s:password)) OR (email_address = s:email)
To add an ORDER BY clause, call the addOrder() method. This method accepts any number of parameters; each parameter being the ORDER BY condition:
try
{
$sql = $connection->prepareDeleteStatement()
->addTable('users')
->addOrder('location ASC', 'username ASC');
}
catch (Exception $e)
{
echo $e->getMessage();
}
// DELETE FROM users ORDER BY location ASC, username ASC
To add a LIMIT clause, call the setLimit() method. Pass as the first parameter the limit, and as the optional second parameter the offset:
try
{
$sql = $connection->prepareDeleteStatement()
->addTable('users')
->addWhere('location = 1')
->setLimit('10', '10');
}
catch (Exception $e)
{
echo $e->getMessage();
}
// DELETE FROM users WHERE (location = 1) LIMIT 10, 10
To prepare an UPDATE statement, either call the prepareUpdateStatement() method or create a new \Models\MySQLi\MySQLiUpdateStatement object (the latter of which will automatically call \Models\MySQLi\MySQLiConnection::setConnection()). Neither of these accept any parameters:
try
{
$sql = $connection->prepareUpdateStatement();
}
catch (Exception $e)
{
echo $e->getMessage();
}
// Or:
try
{
$sql = new \Models\MySQLi\MySQLiUpdateStatement();
}
catch (Exception $e)
{
echo $e->getMessage();
}
To select which data is to be updated, and to what, call the addTable() method. Pass as the first parameter the table name, and as subsequent parameters arrays of the same structure as that of the addValue() method:
try
{
$sql = $connection->prepareUpdateStatement()
->addTable('users', array('password', 'MD5(s:password)'));
}
catch (Exception $e)
{
echo $e->getMessage();
}
// UPDATE users SET users.password = MD5(s:password), users.location = 1
The table name is automatically prepended to the column name. If an alias is to be used, pass the table name as an array with the key being the alias:
try
{
$sql = $connection->prepareUpdateStatement()
->addTable(array('table' => 'users'), array('password', 'MD5(s:password)'), array('location', '1'));
}
catch (Exception $e)
{
echo $e->getMessage();
}
// UPDATE users AS table SET table.password = MD5(s:password), table.location = 1
To join a table using either LEFT or RIGHT JOIN, follow the same format as above but using the addLeftJoin() and addRightJoin() methods respectively:
try
{
$sql = $connection->prepareUpdateStatement()
->addTable('users', array('password', 'MD5(s:password)'))
->addLeftJoin('table', array('username', 's:username'));
}
catch (Exception $e)
{
echo $e->getMessage();
}
// UPDATE users LEFT JOIN table SET users.password = MD5(s:password), table.username = s:username
To add an ON clause, use the addOn() method following the same format as when using addWhere. This is one of only two times in which the method might have be called at a specific time — it prepends the ON clause to the end of the last table:
try
{
$sql = $connection->prepareUpdateStatement()
->addTable('users', array('password', 'MD5(s:password)'))
->addLeftJoin('table', array('username', 's:username'))
->addOn('users.table_id = table.id')
->addOn('users.id = table.users_id');
}
catch (Exception $e)
{
echo $e->getMessage();
}
// UPDATE users LEFT JOIN table ON (users.table_id = table.id) AND (users.id = table.users_id) SET users.password = MD5(s:password), table.username = s:username
To add a USING clause, use the addUsing() method passing as each parameter the common field to use. This is one of only two times in which the method might have be called at a specific time — it prepends the USING clause to the end of the last table:
try
{
$sql = $connection->prepareUpdateStatement()
->addTable('users', array('password', 'MD5(s:password)'))
->addLeftJoin('table', array('username', 's:username'))
->addUsing('account_id', 'account_name');
}
catch (Exception $e)
{
echo $e->getMessage();
}
// UPDATE users LEFT JOIN table USING (account_id, account_name) SET users.password = MD5(s:password), table.username = s:username
To prepare a SELECT statement, either call the prepareSelectStatement() method or create a new \Models\MySQLi\MySQLiSelectStatement object (the latter of which will automatically call \Models\MySQLi\MySQLiConnection::setConnection()). Neither of these accept any parameters:
try
{
$sql = $connection->prepareSelectStatement();
}
catch (Exception $e)
{
echo $e->getMessage();
}
// Or
try
{
$sql = new \Models\MySQLi\MySQLSelectStatement();
}
catch (Exception $e)
{
echo $e->getMessage();
}
To select which data is to be selected, call the addTable() method (or if a table is to be joined, the addLeftJoin() and addRightJoin() methods. Pass as the first parameter the table name, and as subsequent parameters the column names. If no columns are specified, they will all be selected:
try
{
$sql = $connection->prepareSelectStatement()
->addTable(array('table' => 'users'));
}
catch (Exception $e)
{
echo $e->getMessage();
}
// SELECT table.id, table.name, table.email, table.password, table.salt FROM users AS table
To perform a function on the column names, pass them as an array with the second element being the function and a question mark being a placeholder for the column name:
try
{
$sql = $connection->prepareSelectStatement()
->addTable('users', array('password', 'MD5(?)'));
}
catch (Exception $e)
{
echo $e->getMessage();
}
// SELECT MD5(password) FROM users
To add an alias to the field, pass as the first element’s key the alias to be used:
try
{
$sql = $connection->prepareSelectStatement()
->addTable('users', array('total' => 'id', 'COUNT(?)'));
}
catch (Exception $e)
{
echo $e->getMessage();
}
// SELECT COUNT(id) as total FROM users
To add a GROUP BY clause, call the addGroup() method. This method accepts any number of parameters; each parameter being the GROUP BY condition:
try
{
$sql = $connection->prepareSelectStatement()
->addTable('users')
->addGroup('users.location');
}
catch (Exception $e)
{
echo $e->getMessage();
}
// Prepares: SELECT users.* FROM users GROUP BY users.location
To add a HAVING clause to the statement, call the addHaving() method. This method is identical in format to the addWhere() method:
try
{
$sql = $connection->prepareSelectStatement()
->addTable('users')
->addGroup('users.location')
->addHaving("users.status = 'employed'");
}
catch (Exception $e)
{
echo $e->getMessage();
}
// SELECT users.* FROM users GROUP BY users.location HAVING (users.status = 'employed')
To prepare the statement, call the prepareQuery() method. This will return a \Models\MySQLi\MySQLiResult object:
try
{
$sql = $connection->prepareInsertStatement()
->addTable('users')
->addValue('username', 's:username')
->prepareQuery();
}
catch (Exception $e)
{
echo $e->getMessage();
}
// Prepares: INSERT INTO users SET username = ?
To execute the query, call the execute(). This method does not accept any parameters:
try
{
$sql = $connection->prepareInsertStatement()
->addTable('users')
->addValue('username', "'O'Connor'")
->prepareQuery()
->execute();
}
catch (Exception $e)
{
echo $e->getMessage();
}
To bind the parameters, call the setParameters() method passing as the parameters strings of the (unquoted) form “name:value”. This will automatically call the execute() method:
try
{
$sql = $connection->prepareInsertStatement()
->addTable('users')
->addValue('username', 's:username')
->prepareQuery()
->setParameters("username:O'Connor");
}
catch (Exception $e)
{
echo $e->getMessage();
}
To set the result of a single row, call the getOne() method, passing as the optional parameter true if you want to store the result. The result values can be retrieved using the (unquoted) form “$row->columnName“.
try
{
$stmt = $connection->prepareSelectStatement()
->addTable('users')
->addWhere('id = i:id')
->prepareQuery()
->setParameters('id:1');;
->getOne();
echo $stmt->username;
}
catch (Exception $e)
{
echo $e->getMessage();
}
// Outputs: O'Connor
To set all the rows of the result set as an array of objects, call the getAll() method. The \Models\MySQLi\MySQLiResult object can then be iterated to return each row in turn.
try
{
$stmt = $connection->prepareSelectStatement()
->addTable('users')
->addWhere('id > i:id')
->prepareQuery()
->setParameters('id:0')
->getAll();
foreach ($stmt as $value)
{
echo $value->username;
}
}
catch (Exception $e)
{
echo $e->getMessage();
}
// Outputs: O'Connor
To return the data as-is, call the getData() method. This method does not accept any parameters.
try
{
$stmt = $connection->prepareSelectStatement()
->addTable('users')
->addWhere('id > i:id')
->prepareQuery()
->setParameters('id:0')
->getAll();
var_dump($stmt->getData());
}
catch (Exception $e)
{
echo $e->getMessage();
}
// Outputs: A numerical array of StdClass objects where the property names are the field names and the property values the field values
// OR
try
{
$stmt = $connection->prepareSelectStatement()
->addTable('users')
->addWhere('id > i:id')
->prepareQuery()
->setParameters('id:0')
->getOne();
var_dump($stmt->getData());
}
catch (Exception $e)
{
echo $e->getMessage();
}
// Outputs: A StdClass object where the property names are the field names and the property values the field values
To return the number of rows returned, call the getNumRows() method. This method does not accept any parameters.
try
{
$stmt = $connection->prepareSelectStatement()
->addTable('users')
->addWhere('id > i:id')
->prepareQuery()
->setParameters('id:0')
->getAll();
echo $stmt->getNumRows();
}
catch (Exception $e)
{
echo $e->getMessage();
}
// Outputs: 2
To return the number of rows affected, call the getAffectedRows() method. This method does not accept any parameters.
try
{
$stmt = $connection->prepareDeleteStatement()
->addTable('users')
->addWhere('id > i:id')
->prepareQuery()
->setParameters('id:1')
echo $stmt->getAffectedRows();
}
catch (Exception $e)
{
echo $e->getMessage();
}
// Outputs: 1
To return the ID of the inserted row, call the getInsertID() method. This method does not accept any parameters.
try
{
$stmt = $connection->prepareInsertStatement()
->addTable('users')
->addValue('username', 's:username')
->addValue('password', 'MD5(s:password)')
->prepareQuery()
->setParameters('username:' . $_POST['username'], 'password:' . $_POST['password'])
echo $stmt->getInsertID();
}
catch (Exception $e)
{
echo $e->getMessage();
}
// Outputs: 2
To free the result call the freeResult() method. This method does not accept any parameters.
$stmt->freeResult();
To return the statement’s error code call the getErrno() method. This method does not accept any parameters.
echo $stmt->getErrno();
To return the statement’s error call the getError() method. This method does not accept any parameters.
echo $stmt->getError();
Statements can be chained by calling any of the prepare*Statement() methods from the MySQLi*Result object.
try
{
$stmt = $connection->prepareInsertStatement()
->addTable('users')
->addValue('username', 's:username')
->addValue('password', 'MD5(s:password)')
->prepareQuery()
->setParameters('username:' . $_POST['username'], 'password:' . $_POST['password'])
->prepareInsertStatement()
->addTable('users_revisions')
->addValue('username', 's:username')
->addValue('password', 'MD5(s:password)')
->prepareQuery()
->setParameters('username:' . $_POST['username'], 'password:' . $_POST['password']);
}
catch (Exception $e)
{
echo $e->getMessage();
}
// Inserts the data into the `users` and `users_revisions` tables
To execute the same prepared query using different parameters simply re-call the setParameters() method.
try
{
$stmt = $connection->prepareInsertStatement()
->addTable('users')
->addValue('username', 's:username')
->prepareQuery()
->setParameters('username:One')
->setParameters('username:Two');
}
catch (Exception $e)
{
echo $e->getMessage();
}
// Inserts two rows into the `users` table using one prepared query.
I am a beginner web programmers in Indonesia. I am very interested in your tutorial with the title “MySQLi Classes”.
How to call that class in index.php?
If the file is is saved, relative to index.php, in
/models/MySQLi/MySQLiConnection.php, then simply use the following:try { include '/models/MySQLi/MySQLiConnection.php'; $connection = \Models\MySQLi\MySQLiConnection::setConnection('hostname', 'username', 'password', 'database'); } catch (Exception $e) { echo $e->getMessage(); }Hi Michael,
Does your script support the use of transactions and also the use of seperating reads and writes to different servers?