<?php
/**
* Mysql
*
* PHP version 5 only
*
* LICENSE: This source file is subject to version 3 of the GNU GPL
* that is available through the world-wide-web at the following URI:
* http://www.gnu.org/licenses/gpl.html. If you did not receive a copy of
* the GNU GPL License and are unable to obtain it through the web, please
* send a note to adrien.gibrat@gmail.com so I can mail you a copy.
*
* 1) Features:
*
* - Easily connect and query Mysql Database
* - Build-in system to escape values and system names
* - Data is fetched only once when accessing it
* - ArrayObjects like results returned are local cache of fetched data
* - Converting results to string will create CVS data
* - Debug display of Exception dump
*
* 2) Todo/Ideas:
*
* - Rewrite using PDO http://www.php.net/manual/en/book.pdo.php
* - Add a level that build predefined queries (select, update, insert, delete...)
* - Extends classes so we could have objects that updates the DB when setting an offset
*
* 3) Usage example
*
* <code>
require_once 'Mysql.php';
$table = $_GET['table']; // this values will be escaped so it can be safely given by user
$field = $_GET['field'];
$value = $_GET['value'];
$mysql = new Mysql( 'user:password@localhost/database utf8' );
$result = $mysql->query( 'SELECT * FROM %s WHERE %s = %s', array( $table => true, $field => true, $value ) );
echo $result[0]; // this will print the CVS line of row 0
foreach ( $result[0] as $key => $value ) // this will print the content of row 0
echo $key, ' : ', $value;
echo $result[0][$field]; // this will print the $field content of row 0
echo $result[$field]; // this will print the content of $field column, one value per line
echo $result; // this will print the result as CVS
* </code>
*
* @author Adrien Gibrat <adrien [dot] gibrat [at] gmail [dot] com>
* @copyleft 2010 - Just use it!
* @license http://www.gnu.org/licenses/gpl.html GNU General Public License version 3
* @version Release: 1.0
*
*/
/**
* function __autoload
* Autoload file if exists
* @package Mysql
*
* @param class
* @return boolean file exists or not
*/
if ( ! function_exists( '__autoload' ) ) {
function __autoload( $class ) {
if ( ! is_readable( $file = realpath( $class . '.php' ) ) )
return false;
require_once $file;
return true;
}
}
/**
* Fetcher
* Fetch items from database & store it in linked result object
* @abstract
* @package Mysql
*/
abstract class Fetcher extends ArrayIterator
{
/**
* Mysql result ressource
* @access protected
* @var resource
*/
protected $result;
/**
* Number of result rows
* @access protected
* @var integer
*/
protected $count;
/**
* Current cursor position
* @access protected
* @var integer
*/
protected $cursor = 0;
/**
* Move cursor to next row
* @access public
*
* @uses self::$cursor to increase it
*
* @return void
*/
public function next()
{
$this->cursor++;
}
/**
* Validate cursor position
* @access public
*
* @uses self::$cursor to check it
* @uses self::$count to compare it
*
* @return boolean true if cursor is in bounds, else false
*/
public function valid()
{
return $this->cursor < $this->count;
}
/**
* Get cursor position
* @access public
*
* @uses self::$cursor to return it
*
* @return integer cursor position
*/
public function key()
{
return $this->cursor;
}
/**
* Move cursor to index
* @access public
*
* @uses self::$cursor to set it
*
* @param integer $index numeric index
* @return void
*/
public function seek( $index )
{
$this->cursor = $index;
}
}
/**
* Result
* Store result fetched from database, can be casted to string
* @abstract
* @package Mysql
*/
abstract class Result extends ArrayObject
{
/**
* String quote
*/
const string_quote = '"';
/**
* Glue string to concatenate escaped lists
*/
const list_glue = ';';
/**
* Fetcher iterator handle fetching
* @access protected
* @var Fetcher
*/
protected $fetcher;
/**
* Number of results
* @access protected
* @var integer
*/
protected $count;
/**
* Return result object as string
* @access public
*
* @uses self::protect() to quote and implode values
*
* @return string object casted to string
*/
public function __toString()
{
return self::protect( parent::getArrayCopy() );
}
/* UTILS METHODS */
/**
* Return quotes protected string
* @access protected
* @static
*
* @uses self::list_glue to concatenate lists
* @uses self::string_quote as default quote
*
* @param mixed $value variable to quote
* @param mixed $quote quote to use
* @return string quoted value
*/
static protected function protect( $value, $quote = self::string_quote )
{
if ( is_numeric( $value ) ) // no need to escape numeric values
return $value;
if ( is_bool( $value ) ) // return boolean as integer
return $value ? 1 : 0;
if ( is_null ( $value ) ) // return null as 'NULL'
return 'NULL';
if ( is_array( $value ) || is_object( $value ) ) // return array and object as concatenated string
return implode( self::list_glue, array_map( array( 'self', 'protect' ), (array)$value ) );
return $quote . $value . $quote; // protect with quotes
}
}
/**
* Row
* Store result row from database
* @package Mysql
*/
class Row extends Result
{
/**
* Set row value
* @access public
*
* @uses ArrayObject::offsetSet() to set row value
* @see RowsFetcher::current()
*
* @param integer $offset value offset
* @param string|integer $value value
* @return void
*/
public function __set( $offset, $value )
{
parent::offsetSet( $offset, $value );
}
}
/**
* RowsFetcher
* Fetch rows from database & store it in linked Rows/Data object
* @package Mysql
*/
class RowsFetcher extends Fetcher
{
/**
* Result data linked to this
* @access protected
* @var Rows|Data
*/
protected $data;
/* CONSTRUCTOR */
/**
* Constructor sets up {@link $data }, {@link $result } and {@link $count }
* @access public
*
* @uses Rows::$result to set this result
* @uses Rows::count() to set this count
*
* @param Rows|Data $data Result rows/data linked to this
*/
public function __construct( $data )
{
$this->data = $data;
$this->result = $data->result;
$this->count = $data->count();
}
/**
* Get current row
* @access public
*
* @uses self::$result to seek and fetch row
* @uses self::$cursor to get current row
* @uses self::$data to store in
* @uses Row::__construct() to create row
* @uses Row::__set() to build row
* @uses Data::offsetSet() to store row in data
*
* @return Row|null row if exists, else null
*/
public function current()
{
if ( @mysqli_data_seek( $this->result, $this->cursor ) )
{
$this->data->offsetSet( $this->cursor, $row = @mysqli_fetch_object( $this->result, 'Row' ) );
return $row;
}
}
/**
* Move cursor to start
* @access public
*
* @uses self::$result to seek result
* @uses self::$cursor to set it
*
* @return void
*/
public function rewind()
{
@mysqli_data_seek( $this->result, $this->cursor = 0 );
}
/**
* Move cursor to index
* @access public
*
* @uses self::$result to seek result
* @uses self::$cursor to set it
*
* @param integer $index row numeric index
* @return void
*/
public function seek( $index )
{
if ( is_int( $index ) && @mysqli_data_seek( $this->result, $index ) )
$this->cursor = $index;
else
throw new OutOfBoundsException( 'No row ' . $index );
}
}
/**
* Rows
* Store rows fetched from database, can be casted to string
* @package Mysql
*/
class Rows extends Result
{
/**
* Mysql result ressource
* @access public
* @var resource
*/
public $result;
/**
* Result SQL query
* @access protected
* @var string
*/
protected $query;
/**
* Last error occured
* @access protected
* @var Exception
*/
protected $error;
/**
* Debug mode on/off
* @access public
* @var boolean
*/
public $debug;
/* CONSTRUCTOR */
/**
* Constructor sets up {@link Result::$result }, {@link $query }, {@link $debug } and {@link Result::$fetcher }
* @access public
*
* @uses RowsFetcher::__conscruct() to fetch data
*
* @param ressource $result result ressource
* @param string $query SQL query
* @param boolean $debug debug on/off
*/
public function __construct( $result, $query, $debug )
{
$this->result = $result;
$this->query = $query;
$this->debug = $debug;
$this->fetcher = new RowsFetcher( $this );
}
/* MAGIC METHODS */
/**
* Return CSV formated result (semi-colon separated, double quoted strings)
* @access public
*
* @uses self::getIterator() to get all rows
* @uses Row::__toString() to get rows as strings
*
* @return string CSV formated result
*/
public function __toString()
{
foreach( $this as $row )
$rows[] = (string)$row;
return implode( "\n", (array)$rows );
}
/**
* Free result ressource
* @access public
*
* @uses self::$result to free result
*
* @return void
*/
public function __destruct()
{
@mysqli_free_result( $this->result );
}
/* OVERLOAD METHODS */
/**
* Check if result row exists
* @access public
*
* @uses ArrayObject::offsetExists() to check allready fetched row
* @uses self::fetcher to check unfeched row
* @uses RowsFetcher::seek() to seek row
*
* @param integer $index index of row to check
* @return boolean true row exists, false if no row at this $index
*/
public function offsetExists( $offset )
{
if ( parent::offsetExists( $offset ) ) // return true if row is already fetched
return true;
try // or check in $this->fetcher
{
$this->fetcher->seek( $offset );
}
catch(OutOfBoundsException $e)
{
return false;
}
return true;
}
/**
* Get result row
* @access public
*
* @uses ArrayObject::offsetExists() to check if row is already fetched
* @uses ArrayObject::offsetGet() to get fetched row
* @uses self::$fetcher to access results
* @uses RowsFetcher::seek() to seek row
* @uses RowsFetcher::valid() to validate if row exists
* @uses RowsFetcher::current() to fetch row
* @uses self::$debug to handle error
* @uses self::error() to throw an error or return null
*
* @param integer $offset row index
* @return Row|null row at index, null if no such row
*/
public function offsetGet( $offset )
{
if ( parent::offsetExists( $offset ) ) // return row if already fetched
return parent::offsetGet( $offset );
try
{
$this->fetcher->seek( $offset ); // seek row in $this->fetcher
}
catch(OutOfBoundsException $e)
{
return $this->error( $e->getMessage(), $this->debug ); // not found: error!
}
if ( $this->fetcher->valid() ) // return fetched row if exist
return $this->fetcher->current();
}
/**
* Get result iterator
* @access public
*
* @uses ArrayObject::count() to count rows fetched
* @uses self::$count to compare rows count
* @uses ArrayObject::getIterator() to return ArrayIterator if all data fetched
* @uses self::$fetcher to fetch rows
* @uses self::$debug to handle error
* @uses self::error() to throw an error or return null
*
* @return ArrayIterator|RowsFetcher depends if all data have been fetched or not
*/
public function getIterator()
{
if ( ! $this->count ) // empty result: error!
$this->error( 'Empty result', $this->debug );
if ( parent::count() == $this->count )
return parent::getIterator(); // return ArrayIterator if all data fetched
else
return $this->fetcher; // return RowsFetcher else
}
/**
* Count results
* @access public
*
* @uses self::$count to return/set it
*
* @return integer Number of rows, set $this->count
*/
public function count()
{
if ( $this->count ) // return $this->count if set
return $this->count;
return $this->count = @mysqli_num_rows( $this->result ); // count $this->result and set $this->count
}
/* ERROR HANDLER */
/**
* Set up error and thow it if debug is on
* @access protected
*
* @uses self::$link to get mysql error
* @uses self::$error to set it
* @uses self::debug() to print error
*
* @param string|null $message error message, will be overwrite if there is Mysql error
* @param boolean $debug thow error/print debug or not
* @return null void return
*/
protected function error( $message = null, $debug = true )
{
if ( is_resource( $this->link ) && ( $error = mysqli_error( $this->link ) ) )
$message = '#' . @mysqli_errno( $this->link ) . ' ' . $error;
if ( $message )
{
$this->error = new Exception( $message );
if ( $debug )
throw $this->debug( $message );
}
}
/* DEBUGGING METHOD */
/**
* Print debug info
* @access public
*
* @uses self::setFlags() to allow members dump
* @uses self::$error to return it
*
* @param string|null $message debug output title
* @return Exception|null current error
*/
public function debug( $message = null )
{
$this->setFlags( 1 );
echo '<div onmouseover="this.lastChild.style.display=\'block\';"><strong style="color:red">Debug: ', $message, '</strong>',
'<pre style="display:none;padding:0 3em 3em" onmouseout="this.style.display=\'none\';">', var_dump( $this ), '</pre></div>';
$this->setFlags( 0 );
return $this->error;
}
}
/**
* ColumnFetcher
* Fetch column row values from database & store it in linked Column object
* @package Mysql
*/
class ColumnFetcher extends Fetcher
{
/**
* Result column linked to this
* @access protected
* @var Column
*/
protected $column;
/**
* Result data linked to this
* @access protected
* @var Data
*/
protected $data;
/* CONSTRUCTOR */
/**
* Constructor sets up {@link $data }, {@link $column } and {@link $count }
* @access public
*
* @uses Column::$name to set this column name (shortcut)
* @uses Column::count() to set this count
*
* @param Column $column Result column linked to this
* @param Data $data Result data linked to this
*/
public function __construct( Data $data, Column $column )
{
$this->data = $data;
$this->column = $column;
$this->name = $column->name;
$this->count = $column->count();
}
/**
* Get current column row value
* @access public
*
* @uses self::$data to access row
* @uses Data::offsetGet() to get row
* @uses self::$cursor to get current row
* @uses Row::offsetGet() to to get row values
* @uses self::$column to store in
* @uses Column::offsetSet() to store value in column
*
* @return mixed|null column row value if exists, else null
*/
public function current()
{
if ( $row = $this->data->offsetGet( $this->cursor ) )
{
$this->column->offsetSet( $this->cursor, $value = $row->offsetGet( $this->name ) );
return $value;
}
}
/**
* Move cursor to start
* @access public
*
* @uses self::$cursor to set it
*
* @return void
*/
public function rewind()
{
$this->cursor = 0;
}
}
/**
* Column
* Store result column from database
* @package Mysql
*/
class Column extends Result
{
/**
* Glue string to concatenate escaped lists
*/
const list_glue = "\n";
/**
* Result data linked to this
* @access protected
* @var Data
*/
protected $data;
/**
* Column infos
* @access public
* @var stdClass
*/
public $field;
/**
* Column name
* @access public
* @var string
*/
public $name;
/* CONSTRUCTOR */
/**
* Constructor sets up {@link $data }, {@link $fetcher }, {@link $count }, {@link $field } and {@link $name }
* @access public
*
* @uses Data::count() to set this count
* @uses ColumnFetcher::__construct() to fetch column values
*
* @param Data $data Result data linked to this
* @param stdClass $field Column field infos
*/
public function __construct( Data $data, stdClass $field )
{
$this->data = $data;
$this->count = $data->count();
$this->name = $field->name;
$this->fetcher = new ColumnFetcher( $data, $this );
$this->field = $field;
}
/**
* Return formated result (double quoted strings)
* @access public
*
* @uses self::getIterator() to get all rows
* @uses Row::__toString() to get rows as strings
*
* @return string formated result
*/
public function __toString()
{
foreach( $this as $value )
$values[] = (string)$value;
return implode( "\n", (array)$values );
}
/* OVERLOAD METHODS */
/**
* Check if column row exists
* @access public
*
* @uses ArrayObject::offsetExists() to check allready fetched column row
* @uses self::$data to access result data
* @uses Data::offsetExists() to check if data row exists
*
* @param integer $offset column row index
* @return boolean true if column row exists, false if no row at this $index
*/
public function offsetExists( $offset )
{
if ( parent::offsetExists( $offset ) ) // return true if column row allready fetched
return true;
if ( is_int( $offset ) ) // return if row exist
return $this->data->offsetExists( $offset );
return false;
}
/**
* Get column row
* @access public
*
* @uses ArrayObject::offsetExists() to check allready fetched column row
* @uses ArrayObject::offsetGet() to get fetched column row
* @uses self::$data to access result data
* @uses Data::offsetGet() to get row
* @uses ArrayObject::offsetSet() to store fetched column row
*
* @param integer $index index of column row to check
* @return mixed|null column row at $offset index, else null
*/
public function offsetGet( $offset )
{
if ( parent::offsetExists( $offset ) )
parent::offsetGet( $offset );
if ( is_int( $offset) && $row = $this->data->offsetGet( $offset ) )
{
parent::offsetSet( $offset, $value = $row[$this->name] );
return $value;
}
}
/**
* Get result iterator
* @access public
*
* @uses ArrayObject::count() to count rows fetched
* @uses self::$count to compare rows count
* @uses ArrayObject::getIterator() to return ArrayIterator if all data fetched
* @uses self::$fetcher to fetch rows
*
* @return ArrayIterator|ColumnFetcher depends if all data have been fetched or not
*/
public function getIterator()
{
if ( parent::count() == $this->count )
return parent::getIterator(); // return ArrayIterator if all column data fetched
else
return $this->fetcher; // return ColumnFetcher else
}
/**
* Return results count
* @access public
*
* @uses self::$count to return it
*
* @return integer Number of rows
*/
public function count()
{
return $this->count;
}
}
/**
* FieldsFetcher
* Fetch fields from database & store it in linked Fields object
* @package Mysql
*/
class FieldsFetcher extends Fetcher
{
/**
* Result fields linked to this
* @access protected
* @var Fields
*/
protected $fields;
/* CONSTRUCTOR */
/**
* Constructor sets up {@link $fields }, {@link $result } and {@link $count }
* @access public
*
* @uses Fields::$result to set this result
* @uses Fields::count() to set this count
*
* @param Fields $fields Result fields linked to this
*/
public function __construct( Fields $fields )
{
$this->fields = $fields;
$this->result = $fields->result;
$this->count = $fields->count();
}
/**
* Get current field
* @access public
*
* @uses self::$result to seek and fetch field
* @uses self::$cursor to get current field
* @uses self::$fields to store in
* @uses Field::offsetSet() to store field in data fields
*
* @return stdClass|null field if exists, else null
*/
public function current()
{
if ( @mysqli_field_seek( $this->result, $this->cursor ) && $field = @mysqli_fetch_field( $this->result ) )
{
$this->fields->offsetSet( $this->cursor, $field );
return $field;
}
}
/**
* Move cursor to start
* @access public
*
* @uses self::$result to seek result
* @uses self::$cursor to set it
*
* @return void
*/
public function rewind()
{
@mysqli_field_seek( $this->result, $this->cursor = 0 );
}
/**
* Move cursor to index
* @access public
*
* @uses self::getIterator() to loop and find field name
* @uses self::rewind() while loop to find field name
* @uses self::valid() while loop to find field name
* @uses self::current() while loop to find field name
* @uses self::next() while loop to find field name
* @uses self::$result to seek result fields
* @uses self::$cursor to set it
*
* @param integer $index field name or numeric index
* @return void
*/
public function seek( $index )
{
if ( is_string( $index ) )
foreach ( $this as $field )
if ( $field->name == $index )
return;
if ( is_int( $index ) && @mysqli_field_seek( $this->result, $index ) )
$this->cursor = $index;
else
throw new OutOfBoundsException( 'No field ' . $index );
}
}
/**
* Fields
* Store fields infos fetched from database, can be casted to string
* @package Mysql
*/
class Fields extends Result
{
/**
* Mysql result ressource
* @access public
* @var resource
*/
public $result;
/**
* Fetcher iterator handle field fetching
* @access protected
* @var array
*/
protected $index = array();
/* CONSTRUCTOR */
/**
* Constructor sets up {@link $result } and {@link $fetcher }
* @access public
*
* @uses Data::$result to set this result
* @uses FieldsFetcher::__construct() to fetch fields
*
* @param Data $data Mysql result data linked to this
*/
public function __construct( Data $data )
{
$this->result = $data->result;
$this->fetcher = new FieldsFetcher( $this );
}
/* MAGIC METHODS */
/**
* Return fields names as first CSV line (semi-colon separated, double quoted strings)
* @access public
*
* @uses self::getIterator() to get all fields
* @uses self::escape() to escape fields name
*
* @return string first CSV line
*/
public function __toString()
{
foreach( $this as $field )
$fields[] = $field->name;
return self::protect( $fields );
}
/* OVERLOAD METHODS */
/**
* Set field
* @access public
*
* @uses ArrayObject::offsetSet() to store field
* @uses self::index to index field name
*
* @param integer $index field index
* @return void
*/
public function offsetSet( $index, $field )
{
if ( is_int( $index ) )
parent::offsetSet( $this->index[$field->name] = $index, $field );
}
/**
* Check if field exists
* @access public
*
* @uses self::index() to check if result field already fetched
* @uses self::fetcher to check unfeched field
* @uses FieldsFetcher::seek() to seek field
*
* @param integer|string $index field index or name
* @return boolean true field exists, false if no field at this $index
*/
public function offsetExists( $offset )
{
if ( $this->index( $offset ) ) // return true if field is already fetched
return true;
try // or check in $this->fetcher
{
$this->fetcher->seek( $offset );
}
catch(OutOfBoundsException $e)
{
return false;
}
return true;
}
/**
* Get result field
* @access public
*
* @uses self::index() to check/get field index
* @uses ArrayObject::offsetGet() to get allready fetched field
* @uses self::$fetcher to access fields
* @uses FieldsFetcher::seek() to seek field
* @uses FieldsFetcher::valid() to validate if field exists
* @uses FieldsFetcher::current() to fetch field
*
* @param integer|string $offset field index or name
* @return stdClass|null field at index $offset or named $offset, else null
*/
public function offsetGet( $offset )
{
if ( $index = $this->index( $offset ) ) // return field if already fetched
return parent::offsetGet( $index );
try
{
$this->fetcher->seek( $offset ); // seek field in $this->fetcher
}
catch(OutOfBoundsException $e)
{
return null; // not found!
}
if ( $this->fetcher->valid() ) // return field if exist
return $this->fetcher->current();
}
/**
* Get fields iterator
* @access public
*
* @uses ArrayObject::count() to count fields fetched
* @uses self::$count to compare fields count
* @uses ArrayObject::getIterator() to return ArrayIterator if all fields fetched
* @uses self::$fetcher to fetch fields
*
* @return ArrayIterator|FieldsFetcher depends if all fields have been fetched or not
*/
public function getIterator()
{
if ( parent::count() == $this->count )
return parent::getIterator();
else
return $this->fetcher;
}
/**
* Count fields
* @access public
*
* @uses self::$count to set it
*
* @return integer Number of fields, set $this->count
*/
public function count()
{
if ( $this->count ) // return $this->count if set
return $this->count;
return $this->count = @mysqli_num_fields( $this->result ); // count fields in $this->result and set $this->count
}
/* INDEXING METHOD */
/**
* Get field numeric index
* @access public
*
* @uses self::$index to get field muneric index
*
* @return integer|null field index, null if not set
*/
public function index( $offset )
{
if ( is_int( $offset ) && in_array( $offset, $this->index ) )
return $offset;
if ( isset( $this->index[$offset] ) )
return $this->index[$offset];
}
}
/**
* Data
* Store results (rows, columns & fields) fetched from database, can be casted to string
* @package Mysql
*/
class Data extends Rows
{
/**
* Fields infos
* @access protected
* @var Fields
*/
protected $fields;
/**
* Columns
* @access protected
* @var array
*/
protected $columns;
/* CONSTRUCTOR */
/**
* Constructor sets up {@link Result::$result }, {@link $query }, {@link $debug }, {@link Result::$fetcher } and {@link $fields }
* @access public
*
* @uses Rows::__conscruct() to get rows
* @uses Fields::__conscruct() to store fields infos
*
* @param ressource $result result ressource
* @param string $query SQL query
* @param boolean $debug debug on/off
*/
public function __construct( $result, $query, $debug )
{
parent::__construct( $result, $query, $debug );
$this->fields = new Fields( $this );
}
/* MAGIC METHODS */
/**
* Return CSV formated result (semi-colon separated, double quoted strings)
* @access public
*
* @uses self::$fields to get columns names
* @uses Fields::__toString() to get columns names as string
* @uses Rows::__toString() to get rows as strings
*
* @return string CSV formated result
*/
public function __toString()
{
return $this->fields . "\n" . parent::__toString();
}
/**
* Get field infos
* @access public
*
* @uses self::$fields to get columns names
* @uses Fields::offsetGet() to get fields infos
*
* @return stdClass|null fields infos, null means no such field
*/
public function __get( $offset )
{
return $this->fields->offsetGet( $offset ); // get fields infos
}
/* OVERLOAD METHODS */
/**
* Check if result row/column exists
* @access public
*
* @uses self::$fields to get result fields
* @uses Fields::offsetExists() to check if field exists
* @uses Rows::offsetExists() to check if row exists
*
* @param integer $index index of row to check
* @return boolean true row/column exists, false if no row/column at this $index
*/
public function offsetExists( $offset )
{
if ( is_string( $offset ) ) // return if column exists
return $this->fields->offsetExists( $offset );
return parent::offsetExists( $offset ); // return if row exists
}
/**
* Get result row/column
* @access public
*
* @uses Rows::offsetGet() to get row
* @uses self::$fields to get result fields
* @uses Fields::offsetGet() to get field infos
* @uses Column::__construct() to create column
* @uses self::$column to store/return created column
* @uses self::$debug to handle error
* @uses self::error() to throw an error or return null
*
* @param integer|string $index row index or column name
* @return Row|Column|null row at $offset index or column named $offset, else null
*/
public function offsetGet( $offset )
{
if ( is_int( $offset ) ) // return row
return parent::offsetGet( $offset );
if ( ! $field = $this->fields->offsetGet( $offset ) ) // no column found: error!
return $this->error( 'No column ' . $offset, $this->debug );
if ( isset( $this->columns[$field->name] ) ) // return column already accessed
return $this->columns[$field->name];
return $this->columns[$field->name] = new Column( $this, $field ); // store and return column
}
}
/**
* Mysql
* Connect to server/database, allow access to last result
* @package Mysql
*/
class Mysql extends Rows
{
/**
* System quote
*/
const system_quote = '`';
/**
* Glue string to concatenate escaped lists
*/
const list_glue = ', ';
/**
* Regular Expression mask to analyse connexion infos string
*/
const connexion_mask = '#^(?<pconnect>pconnect://)?(?<user>[^:@]+)(?::(?<password>[^@]+))?@(?<host>[^/]+)(?:/(?<database>[^ ]+)(?: (?<charset>.+))?)?$#';
/**
* Default connexion charset
*/
const default_charset = 'utf8';
/**
* Server host
* @access protected
* @var string
*/
protected $host;
/**
* Connexion user
* @access protected
* @var string
*/
protected $user;
/**
* Connexion password
* @access protected
* @var string
*/
protected $password;
/**
* Connexion type (permanent or not)
* @access protected
* @var boolean
*/
protected $pconnect;
/**
* Database name
* @access protected
* @var string
*/
protected $database;
/**
* Names character set
* @access protected
* @var string
*/
protected $charset;
/**
* Result data class
* @access protected
* @var string
*/
protected $return = 'Data';
/**
* Last result data
* @access protected
* @var Rows|Data|$return
*/
protected $data;
/* CONSTRUCTOR */
/**
* Constructor sets up {@link $pconnect }, {@link $host }, {@link $user }, {@link $password } and {@link $debug }
* @access public
*
* @uses self::$debug to set it
* @uses self::connexion_mask to parse $connexion
* @uses self::$pconnect to set it
* @uses self::$host to set it
* @uses self::$user to set it
* @uses self::$password to set it
* @uses self::connect() to connect server
* @uses self::database() to select database
* @uses self::default_charset to set default character set
* @uses self::error() to throw an error
*
* @param string $connexion connexion informations
* valid connexion informations string exemples
* $connexion = 'pconnect://user:pass@host/database charset';
* $connexion = 'pconnect://user:pass@host/database';
* $connexion = 'user:pass@host/database charset';
* $connexion = 'user:pass@host/database';
* $connexion = 'user@host/database charset';
* $connexion = 'user@host/database';
* $connexion = 'user@host';
* @param boolean $debug debug mode on/off, optional
*/
public function __construct( $connexion, $debug = false )
{
$this->debug = $debug; // set debug on/off
if ( ! function_exists( 'mysqli_connect' ) ) // no mysql extention: error!
$this->error( 'PHP MySQL functions are not available' );
if ( ! preg_match( self::connexion_mask, $connexion, $infos ) ) // wrong connexion informations: error!
$this->error( 'Wrong connexion informations' );
$this->pconnect = (boolean)$infos['pconnect']; // set connexion properties
$this->host = $infos['host'];
$this->user = $infos['user'];
$this->password = $infos['password'];
$this->connect(); // connect to server
if ( isset( $infos['database'] ) ) // select database if database name
$this->database( $infos['database'], isset( $infos['charset'] ) ? $infos['charset'] : self::default_charset );
}
/* SERVER CONNEXION METHOD */
/**
* Connect to server
* @access protected
*
* @uses self::$link to set it
* @uses self::$pconnect to create permanent connection or not
* @uses self::$host to connect to server
* @uses self::$user to access server
* @uses self::$password to access server
* @uses self::error() to throw an error
*
* @return void
*/
protected function connect()
{
if ( ! $this->link = $this->pconnect ? // create connexion link
@mysqli_pconnect( $this->host, $this->user, $this->password ) : // permanent connexion
@mysqli_connect( $this->host, $this->user, $this->password ) // normal connexion
) // no connexion: error!
$this->error();
}
/* DATABASE CONNEXION METHOD */
/**
* Connect to server
* @access public
*
* @uses self::$link to access server
* @uses self::$database to select it
* @uses self::version() to check server version
* @uses self::default_charset to get default character set
* @uses self::$charset to set character set
* @uses self::query() to set names character set
* @uses self::error() to throw an error
*
* @param string $database database name
* @param string $charset character set to use, optional
* @return void
*/
public function database( $database, $charset = null )
{
if ( ! @mysqli_select_db( $this->link, $this->database = $database ) ) // can't select: error!
$this->error();
if ( $this->version( '4.1' ) ) // server version > 4.1, set names charset
$this->query( 'SET NAMES %s;SET CHARACTER SET %1$s', $this->charset = is_null( $charset ) ? $this->charset : $charset );
}
/* UTILS HELPERS */
/**
* Check server version
* @access public
*
* @uses self::$link to access server
*
* @param string $min minimum version, optional
* @return string|boolean server version or is greater than $min
*/
public function version( $min = null )
{
$version = @mysqli_get_server_info( $this->link ); // get server version
return $min ? version_compare( $version, $min, '>=' ) : $version; // compare it to $min or return
}
/**
* Return last insert id
* @access public
*
* @uses self::$link to access server
*
* @return string|int last insert id
*/
public function id()
{
return @mysqli_insert_id( $this->link ); // return last insert id
}
/* OVERLOAD METHODS */
/**
* Check if last result row/column exists
* @access public
*
* @uses self::$data to check/access last result data
* @uses Rows::offsetExists() to seek row/column
*
* @param integer $index index of row to check
* @return boolean true row/column exists, false if no row/column at this $index
*/
public function offsetExists( $offset )
{
if ( $this->data ) // return if last result data offset exists
return $this->data->offsetExists( $offset );
return false; // else false
}
/**
* Get last result row/column
* @access public
*
* @uses self::$data to check/access last result data
* @uses Rows::offsetGet() to get row/column
*
* @param integer|string $index row index or column name
* @return Row|Column|null row at $offset index or column named $offset, else null
*/
public function offsetGet( $offset )
{
if ( $this->data ) // return last result data offset
return $this->data->offsetGet( $offset );
}
/**
* Get result iterator
* @access public
*
* @uses self::$data to check/access last result data
* @uses Rows::getIterator() to access rows
*
* @return ArrayIterator|RowsFetcher|null depends if all data have been fetched or not, null if no result data
*/
public function getIterator()
{
if ( $this->data ) // return last result data iterator
return $this->data->getIterator();
}
/**
* Count rows in result or affected rows depending result type
*
* @uses self::$count to return/set it
* @uses self::$data to check/access last result data
* @uses Rows::count() to count rows
* @uses self::$link to count affected rows
*
* @return integer Number of rows in result or affected rows
*/
public function count()
{
if ( $this->count ) // return $this->count if set
return $this->count;
return $this->count = $this->data ? // set $this->count depending result type
$this->data->count() : // else count data rows
@mysqli_affected_rows( $this->link ); // count affected if no data
}
/* MAGIC METHODS */
/**
* Get connexion informations string
*
* @uses self::$pconnect,
* @uses self::$user,
* @uses self::$password,
* @uses self::$host,
* @uses self::$database,
* @uses self::$charset to build the connexion informations string
*
* @return string connexion informations
*/
public function __toString()
{
return ( $this->pconnect ? 'pconnect://' : '' ) . $this->user . ( $this->password ? ':' . $this->password : '' ) .
'@' . $this->host . ( $this->database ? '/' . $this->database : '' ) .
( $this->charset ? ' ' . $this->charset : '' ); // build connexion informations
}
/**
* Get usefull properties on serialize
*
* @return array properties to store
*/
public function __sleep()
{
return array( 'pconnect', 'host', 'user', 'password', 'database', 'charset' ); // properties to store
}
/**
* Restore connexion on unserialize
*
* @uses self::connect() to connect to server
* @uses self::$database to access database name
* @uses self::database() to select database
*
* @return void
*/
public function __wakeup()
{
$this->connect(); // connect to server
if ( $this->database ) // select database
$this->database( $this->database );
}
/**
* Close connexion
*
* @uses self::$link to close connexion
*
* @return void
*/
public function __destruct()
{
@mysqli_close( $this->link ); // close connexion
}
/* QUERY METHOD */
/**
* Execute SQL query
*
* @uses self::$data to set it
* @uses self::$count to reset it
* @uses self::$error to reset it
* @uses self::queries() to handle multiple queries
* @uses self::$query to set it
* @uses self::build() to build query
* @uses self::$link to access server
* @uses self::$result to set it
* @uses self::$return to get return class
* @uses self::count() to get affected rows
* @uses self::$debug to handle error
* @uses self::error() to throw an error or return null
*
* @params string|array SQL query or multiples queries
* @param array|string $escape values to escape, optional
* @param string $return data return class, optional
* @return mixed|integer last query result (Rows|Data|UserDefinedClass) or affected rows
*/
public function query( $query = null, $escape = null, $return = null )
{
$this->data = $this->count = $this->error = null; // reset data, count and error
if ( strstr( $query = trim( $query, ';' ), ';' ) || ! is_string( $query ) ) // handle multiple queries
return $this->queries( is_string( $query ) ? $this->build( $query, $escape ) : $query );
if ( ! $this->result = @mysqli_query( $this->link, $this->query = $this->build( $query, $escape ) ) ) // execute query
$this->error(); // return query error
if ( is_object( $this->result ) ) // if result data
{
$return = is_null( $return ) ? $this->return : $return; // get return class
if ( class_exists( strtolower( $return ) ) || ( function_exists( '__autoload' ) && __autoload( $return ) ) ) // check if return type exists
return $this->data = new $return( $this->result, $this->query, $this->debug ); // store and return data
else
$this->error( 'No result class ' . $return, $this->debug ); // no return class: error
}
else
return $this->count(); // return affected rows
}
/* QUERY HELPERS */
/**
* Execute multiple SQL queries
*
* @uses self::query() to execute SQL queries
*
* @params array|string multiple SQL queries as array or semi-colon separated
* @return Rows|Data|integer the last query result
*/
protected function queries( $queries )
{
foreach ( is_string( $queries ) ? explode( ';', $queries ) : (array)$queries as $key => $value ) // for each query
$result = $this->query( is_numeric( $key ) ? $value : $key, is_numeric( $key ) ? null : $value ); // execute and get result
return $result; // return last result
}
/**
* Build query
*
* @uses self::escapes() to escapes values
*
* @params string SQL query
* @param array|string $escape values to escape
* @return Rows|Data|integer the last query result
*/
protected function build( $query, $escape )
{
return empty( $escape ) ? $query : vsprintf( $query, $this->escapes( (array)$escape ) ); // build query
}
/* UTILS METHODS */
/**
* Return escaped string
* @access public
*
* @uses self::list_glue to concatenate lists
* @uses self::system_quote to protect system strings
* @uses self::string_quote to protect non system strings
* @uses self::escapes() to escape array and objects
* @uses self::protect() to protect with quotes
* @uses self::$link to escape strings according charset (if $this->link is a ressource)
*
* @param mixed $value variable to escape
* @param boolean|null $system type of quote protection, true for ``, null for "" and false for none, optional
* @return string escaped value
*/
public function escape( $value, $system = null )
{
if ( is_numeric( $value ) ) // no need to escape numeric values
return $value;
if ( is_bool( $value ) ) // return boolean as integer
return $value ? 1 : 0;
if ( is_null ( $value ) ) // return null as 'NULL'
return 'NULL';
if ( is_array( $value ) || is_object( $value ) ) // return array and object as concatenated string
return implode( self::list_glue, $this->escapes( (array)$value, $system ) );
$escaped = mysqli_real_escape_string( $this->link, $value );
if ( $system === false ) // don't protect if $system is false
return $escaped;
return self::protect( $escaped, $system ? self::system_quote : self::string_quote ); // protect with quotes
}
/**
* Escape values in array
* @access protected
*
* @uses self::escape() to escape each value in array
*
* @param array $values values to escape, associative array must have values as key and type of quote protection as value
* @param boolean|null $system type of quote protection, true for ``, null for "" and false for none, optional
* @return array escaped values
*/
protected function escapes( array $values, $system = null )
{
foreach ( $values as $key => &$value ) // escape value (according connection charset if not call as static)
$value = $this->escape( is_numeric( $key ) ? $value : $key, is_numeric( $key ) ? $system : $value );
return $values;
}
}
?>
|
Download
