PHP Classes

File: Mysql.php

Recommend this page to a friend!
  Classes of adrien gibrat   MySQL Object   Mysql.php   Download  
File: Mysql.php
Role: Class source
Content type: text/plain
Description: The class with documentaion in comments
Class: MySQL Object
Access MySQL database data using array iterators
Author: By
Last change: Corrected few typo & bugs, use mysqli_ functions and not mysql_ !!!
Date: 13 years ago
Size: 44,239 bytes
 

Contents

Class file image Download
<?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; } } ?>