Login   Register  
PHP Classes
elePHPant
Icontem

File: Mysql.php

Recommend this page to a friend!
Stumble It! Stumble It! Bookmark in del.icio.us Bookmark in del.icio.us
  Classes of Indrek Altpere  >  Ultimate MySQL wrapper  >  Mysql.php  >  Download  
File: Mysql.php
Role: Class source
Content type: text/plain
Description: Main file
Class: Ultimate MySQL wrapper
MySQL database access wrapper
Author: By
Last change: EscapeString function received second parameter $likemode for LIKE clauses that also escapes _ and % characters for mysql.
Possibiliy to pass in id field name that will be used as array key in GetArrays.
Added function GetScalar that works the same way as GetSingleRowField but is more readable and shorter to use (long name remains still available).
SetDebug received second variable that enables/disables SQL query tracing to enable fine grained debugging where exactly a function gets invoked from. The result is returned ToString() function.
BuildUpdateStatement can accept id field name passed in.
UseDatabase function that allows connecting to another database (note, second database will be accessed using the original user/host configuration as for the initial database).
Close function that allows closing the mysql connection when it is no longer needed.
Reconnect function that tries to bring up a connection again after it has been closed (or dropped off).
SetTimezone function that allows setting the timezone of the connection (MySQL will translate all timestamp values from UTC to given timezone when selected out, and from timezone to UTC when inserted/updated/queried).
SetCharset function that sets the connection charset to given value.
SetTriggerError that allows enable/disable of trigger_error calls in case of mysql error occurs.
Init function received additional parameter $charset that will be used when opening connection.
When connecting/reconnecting fails 2 times in a row, the connect call will return false and not try to connect again and again.
SetSlownessTreshold function that allows triggering errors for queries that take too long.
Date: 2 years ago
Size: 39,068 bytes
 

Contents

Class file image Download
<?php

/**
 * @author Indrek Altpere
 * @copyright Indrek Altpere
 * @uses Mysql package released by me
 * @see ErrorManager for convenient error logging
 *
 * Provides means of more convenient mysql usage by making it possible to call the functions as static functions and thus removing the need of passing the mysql connection variable around.
 * Static wrapper class for DynMysql class, can contain only connection to one database, for using multiple databases simultaneously, use DynMysql class
 *
 */
class Mysql {

	/**
	 * DynMysql instance
	 *
	 * @var DynMysql
	 */
	private static $mysql;
	private static $inited = false;

	const Version = 1.3;

	/**
	 * Disables instantiating of this class
	 *
	 */
	private function __construct() {
		
	}

	/**
	 * Returns mysql escaped string
	 *
	 * @param string $str String to escape
	 * @param bool $likemode If string is to be escaped for LIKE "xx" query, where _ and % need to be escaped too
	 * @return string Escaped string
	 */
	public static function EscapeString($str, $likemode = false) {
		return self::$mysql->EscapeString($str, $likemode);
	}

	/**
	 * Initializes the static Mysql class variables
	 *
	 * @param string $db_host Hostname
	 * @param string $db_user Username
	 * @param string $db_pass Password
	 * @param string $db_name Database name
	 * @param boolean $autoconnect Whether to initialize connection right away or when first query is made
	 * @param boolean $persistent Whether to use persisten connection
	 */
	public static function Init($db_host, $db_user, $db_pass, $db_name, $autoconnect = false, $persistent = false, $defaultcharset = null) {
		if (self::$inited) {
			return;
		}
		self::$mysql = new DynMysql($db_host, $db_user, $db_pass, $db_name, $autoconnect, $persistent, $defaultcharset);
		self::$inited = true;
	}

	/**
	 * Performs query on database
	 *
	 * Note: Use SQL_CALC_FOUND_ROWS immediately after SELECT (example: SELECT SQL_CALC_FOUND_ROWS id FROM sometable WHERE somefield="2" LIMIT 100,10)
	 * to be able to get the total count of rows that matched the WHERE statement (300, 400, 1000 or whatever count rows match) later by calling FoundRows()
	 *
	 * @param string $cmd Mysql query to make
	 * @return mysql_result Mysql result
	 */
	public static function &Query($cmd) {
		return self::$mysql->Query($cmd);
	}

	public static function SetSlownessTreshold($treshold = 0) {
		return self::$mysql->SetSlownessTreshold($treshold);
	}

	public static function SetSlownessThreshold($treshold = 0) {
		return self::$mysql->SetSlownessThreshold($treshold);
	}

	/**
	 * Returns how many rows were found in last query
	 * Note: last query must include SQL_CALC_FOUND_ROWS for thist to work as meant to
	 * It is meant to take away the 2 query step where you first select the count of rows according to WHERE statement (to know the total corresponding rows),
	 * after which you select the actual rows itself with limit statement to display data on multiple pages for example.
	 * SQL_CALC_FOUND_ROWS tells mysql to remember the count of total found rows even if you used LIMIT statement to get only partial result of all matches.
	 *
	 * @return int Count of found rows
	 */
	public static function FoundRows() {
		return self::$mysql->FoundRows();
	}

	/**
	 * Returns single mysql result row as unassociated array of selected field values (array keys are integers, not fieldnames) on success, false otherwise
	 * array('val1', 'val2')
	 *
	 * Note: Use SQL_CALC_FOUND_ROWS immediately after SELECT (example: SELECT SQL_CALC_FOUND_ROWS id FROM sometable WHERE somefield="2" LIMIT 100,10)
	 * to be able to get the total count of rows that matched the WHERE statement (300, 400, 1000 or whatever count rows match) later by calling FoundRows()
	 *
	 * @param string $cmd Mysql query to make
	 * @return array|boolean Array of field values or false if query was unsuccessful
	 */
	public static function &GetRow($cmd) {
		return self::$mysql->GetRow($cmd);
	}

	/**
	 * Returns multiple mysql result rows as array of unassociated arrays of selected field values (array keys are integers, not fieldnames), empty array if unsuccessful
	 * array(array('val1', 'val2'), array('val3', 'val4'))
	 *
	 * Note: Use SQL_CALC_FOUND_ROWS immediately after SELECT (example: SELECT SQL_CALC_FOUND_ROWS id FROM sometable WHERE somefield="2" LIMIT 100,10)
	 * to be able to get the total count of rows that matched the WHERE statement (300, 400, 1000 or whatever count rows match) later by calling FoundRows()
	 *
	 * @param string $cmd Mysql query to make
	 * @param boolean $bundle Whether to bundle first values of result rows into one single level array
	 * @return array Array of results
	 */
	public static function &GetRows($cmd, $bundle = false) {
		return self::$mysql->GetRows($cmd, $bundle);
	}

	/**
	 * Returns multiple mysql result rows as array of associated arrays of selected field values (array keys are fieldnames), empty array in unsuccessful
	 * array(array('name1' => 'val1', 'name2' => 'val2'), array('name1' => 'val3', 'name2' => 'val4'))
	 *
	 * Note: Use SQL_CALC_FOUND_ROWS immediately after SELECT (example: SELECT SQL_CALC_FOUND_ROWS id FROM sometable WHERE somefield="2" LIMIT 100,10)
	 * to be able to get the total count of rows that matched the WHERE statement (300, 400, 1000 or whatever count rows match) later by calling FoundRows()
	 *
	 * @param string $cmd Mysql query to make
	 * @param boolean $assignid Whether to return arrays so that the id field values are set to be the keys of the arrays ( idvalue => array(fieldname=>fieldvalue) )
	 * @param boolean $bundle Whether to bundle all subarrays into one single array using the first subarray value (good to select id fields and instead of looping with nested foreaches or using $row['id'], just get the values as one array)
	 * @return array Array of results
	 */
	public static function &GetArrays($cmd, $assignid = false, $bundle = false, $idfield = 'id') {
		return self::$mysql->GetArrays($cmd, $assignid, $bundle, $idfield);
	}

	/**
	 * Returns single mysql result rows as associated array of selected field values (array keys are fieldnames), false in unsuccessful
	 * array('name1' => 'val1', 'name2' => 'val2')
	 *
	 * Note: Use SQL_CALC_FOUND_ROWS immediately after SELECT (example: SELECT SQL_CALC_FOUND_ROWS id FROM sometable WHERE somefield="2" LIMIT 100,10)
	 * to be able to get the total count of rows that matched the WHERE statement (300, 400, 1000 or whatever count rows match) later by calling FoundRows()
	 *
	 * @param string $cmd Mysql query to make
	 * @return array|boolean Array of fielname => fieldvalue mappings or false if unsuccessful
	 */
	public static function &GetArray($cmd) {
		return self::$mysql->GetArray($cmd);
	}

	/**
	 * Returns the id of the last row inserted into database
	 *
	 * @return int Id of last inserted row
	 */
	public static function &InsertId() {
		return self::$mysql->InsertId();
	}

	public static function &GetSingleRowField($cmd) {
		return self::$mysql->GetScalar($cmd);
	}

	/**
	 * Returns first column value of first selected row
	 *
	 * @param string $cmd Mysql query to make
	 * @return string
	 */
	public static function &GetScalar($cmd) {
		return self::$mysql->GetScalar($cmd);
	}

	/**
	 * Sets the Mysql class debug mode (in debug mode, queryes and their related values are stored and can be viewed by calling ToString method)
	 *
	 * @param boolean $debug
	 */
	public static function SetDebug($debug = false, $debugtrace = false) {
		self::$mysql->SetDebug($debug, $debugtrace);
	}

	/**
	 * Returns data about executed mysql queries in string form, to get more detailed data about each queries(spent time, affected rows etc), use SetDebug(true) before making any queries
	 *
	 * @return string
	 */
	public static function ToString() {
		return self::$mysql->ToString();
	}

	/**
	 * Starts transaction
	 *
	 * @return boolean If transaction was started successfully
	 */
	public static function TransactionBegin() {
		return self::$mysql->TransactionBegin();
	}

	/**
	 * Ends/commits transaction
	 *
	 * @return boolean If commiting was successful
	 */
	public static function TransactionEnd() {
		return self::$mysql->TransactionEnd();
	}

	/**
	 * Rolls back current transaction
	 *
	 * @return boolean If rolling back was successful
	 */
	public static function TransactionRollback() {
		return self::$mysql->TransactionRollback();
	}

	/**
	 * Retrieves iterator class for result
	 *
	 * @param string $query
	 * @return MysqlIterator
	 */
	public static function &GetIterator($query) {
		return self::$mysql->GetIterator($query);
	}

	/**
	 * Gets full column data description for wanted table as associative array with keys:
	 * Field, Type, Collation, Null, Key, Default, Extra, Privileges, Comment
	 *
	 * @param string $tablename Name of the table for what to get the column data
	 * @return array Array of
	 */
	public static function GetColumnDataForTable($tablename) {
		return self::$mysql->GetColumnDataForTable($tablename);
	}

	/**
	 * Gets the results of table expain query as associative array with keys:
	 * Field, Type, Null, Key, Default, Extra
	 *
	 * @param unknown_type $tablename
	 */
	public static function GetExplainTable($tablename) {
		return self::$mysql->GetExplainTable($tablename);
	}

	/**
	 * Builds Order by statement from passed in array(fieldname => order)
	 *
	 * @param array $fieldorderarr
	 * @return string
	 */
	public static function &BuildOrderByStatement($fieldorderarr) {
		return self::$mysql->BuildOrderByStatement($fieldorderarr);
	}

	/**
	 * Builds Limit statement from passed in variables
	 *
	 * @param int $start Start of limit array
	 * @param int $count Count of rows to select
	 * @return string
	 */
	public static function &BuildLimitStatement($start, $count) {
		return self::$mysql->BuildLimitStatement($start, $count);
	}

	/**
	 * Builds Set statement from passed in array
	 *
	 * @param array $array
	 * @return string Set statement
	 */
	public static function BuildSetStatement(&$array) {
		return self::$mysql->BuildSetStatement($array);
	}

	/**
	 * Builds Insert statement for given table using given fields and values
	 *
	 * @param string $tablename Name of the table
	 * @param array $array Field => Value array
	 * @return string Statement for inserting data into table
	 */
	public static function BuildInsertStatement($tablename, &$array) {
		return self::$mysql->BuildInsertStatement($tablename, $array);
	}

	/**
	 * Builds Update statement for given table using given fields and values
	 *
	 * @param string $tablename Name of the table
	 * @param array $array Field => Value array
	 * @param int $id Id of the row to update
	 * @return string Statement for updating a row data in table
	 */
	public static function BuildUpdateStatement($tablename, &$array, $id, $idfield = 'id') {
		return self::$mysql->BuildUpdateStatement($tablename, $array, $id, $idfield);
	}

	/**
	 * Builds Delete statement for given table using given id
	 *
	 * @param string $tablename Name of the table
	 * @param int $id Id of the row to delete
	 * @return string Statement for deleting a row from table
	 */
	public static function BuildDeleteStatement($tablename, $id) {
		return self::$mysql->BuildDeleteStatement($tablename, $id);
	}

	/**
	 * Truncates table
	 *
	 * @param string $tablename Name of the table
	 * @return boolean If truncating was successful
	 */
	public static function TruncateTable($tablename) {
		return self::$mysql->TruncateTable($tablename);
	}

	/**
	 * Retrieves table list from database
	 *
	 * @return array Array of table names in current database
	 */
	public static function &GetTables() {
		return self::$mysql->GetTables();
	}

	/**
	 * Retrieves list of fields from given table
	 *
	 * @param string $tablename
	 * @return array
	 */
	public static function &GetTableFields($tablename) {
		return self::$mysql->GetTableFields($tablename);
	}

	/**
	 * Returns time spent on last query
	 *
	 * @return float
	 */
	public static function TimeSpent() {
		return self::$mysql->TimeSpent();
	}

	/**
	 * Returns time spent on all queries together
	 *
	 * @return float
	 */
	public static function TimeSpentTotal() {
		return self::$mysql->TimeSpentTotal();
	}

	/**
	 * Returns how many rows were selected in last queriy
	 *
	 * @return int
	 */
	public static function SelectedRows() {
		return self::$mysql->SelectedRows();
	}

	/**
	 * Returns how many total rows were selected in all queries together
	 *
	 * @return unknown
	 */
	public static function SelectedRowsTotal() {
		return self::$mysql->SelectedRowsTotal();
	}

	/**
	 * Returns how many rows were affected by last query
	 *
	 * @return int
	 */
	public static function AffectedRows() {
		return self::$mysql->AffectedRows();
	}

	/**
	 * Returns how many total rows were affected in all queries together
	 *
	 * @return int
	 */
	public static function AffectedRowsTotal() {
		return self::$mysql->AffectedRowsTotal();
	}

	/**
	 * Returns if there is a transaction active currently
	 *
	 * @return bool
	 */
	public static function InTransaction() {
		return self::$mysql->InTransaction();
	}

	/**
	 * Tries to use another database using the current opened connection (current user must have rights to the other database)
	 * @param string $db_name 
	 * @return bool
	 */
	public static function UseDatabase($db_name) {
		return self::$mysql->UseDatabase($db_name);
	}

	public static function Reconnect() {
		return self::$mysql->Reconnect();
	}

	public static function SetTimezone($timezone) {
		return self::$mysql->SetTimezone($timezone);
	}

	public static function SetCharset($set = 'utf8') {
		return self::$mysql->SetCharset($set);
	}

	public static function Close() {
		return self::$mysql->Close();
	}

	/**
	 * Sets if mysql errors are converted to trigger_error for errorhandler
	 * @param bool $new
	 * @return bool
	 */
	public static function SetTriggerError($new = false) {
		return self::$mysql->SetTriggerError($new);
	}

}

/**
 * Class for mysql stuff, each instance can be connected to any database
 *
 * Supports delayed connecting: if you instantiate class, it does not bring up the mysql connection unless autoconnect is set to true
 * If autoconnect was set to false, first query made triggers the connection creation
 * Meaning if site does not need mysql queries to be done, the mysql connection is not brought up and therefore page loading takes less time
 *
 */
final class DynMysql {

	private $res = null;
	private $queries = 0;
	private $db = null;
	private $time_spent_total = 0;
	private $affected_rows_total = 0;
	private $selected_rows_total = 0;
	private $time_spent = 0;
	private $found_rows = 0;
	private $affected_rows = 0;
	private $selected_rows = 0;
	private $conndata = array('db_host' => '', 'db_user' => '', 'db_pass' => '', 'db_name' => '');
	private $defaultcharset = null;
	private $persistent = false;
	private $inited = false;
	private $queryarr = array();
	private $debug = false;
	private $debugtrace = false;
	private $in_transaction = false;
	private $trigger_error = true;

	/**
	 * Initializes the DynMysql class variables
	 *
	 * @param string $db_host Hostname
	 * @param string $db_user Username
	 * @param string $db_pass Password
	 * @param string $db_name Database name
	 * @param boolean $autoconnect Whether to initialize connection right away or when first query is made
	 * @param boolean $persistent Whether to use persisten connection or not
	 */
	public function __construct($db_host, $db_user, $db_pass, $db_name, $autoconnect = false, $persistent = false, $defaultcharset = null) {
		$this->conndata = array('db_host' => $db_host, 'db_user' => $db_user, 'db_pass' => $db_pass, 'db_name' => $db_name);
		$this->persistent = $persistent;
		$this->defaultcharset = $defaultcharset;
		if ($autoconnect) {
			$this->Connect();
		}
	}

	/**
	 * Destructor, closes open connections
	 *
	 */
	public function __destruct() {
		$this->Close();
	}

	/**
	 * Closes existing connection
	 *
	 */
	public function Close() {
		if ($this->db) {
			mysql_close($this->db);
			$this->db = null;
		}
	}

	/**
	 * Returns mysql escaped string
	 *
	 * @param string $str String to escape
	 * @param bool $likemode If string is to be escaped for LIKE "xx" query, where _ and % need to be escaped too
	 * @return string Escaped string
	 */
	public function EscapeString($str, $likemode = false) {
		//init db conn if needed
		if (is_null($this->db)) {
			$this->Connect();
			if (is_null($this->db)) {
				trigger_error('Mysql error: No connection to database!?!?', E_USER_ERROR);
				return null;
			}
		}
		if (!$likemode)
			return mysql_real_escape_string($str, $this->db);
		return str_replace(array('_', '%'), array('\_', '\%'), mysql_real_escape_string($str, $this->db));
	}

	private $failedconnections = 0;

	private function Connect() {
		if ($this->db) {
			return true;
		}
		if ($this->failedconnections > 2)
			return false;
		$fname = 'mysql_' . ($this->persistent ? 'p' : '') . 'connect';
		$conn = &$this->conndata;
		$this->db = $fname($conn['db_host'], $conn['db_user'], $conn['db_pass']);
		if (!$this->db) {
			$this->db = null;
			$this->failedconnections++;
			return false;
		}
		if (!mysql_select_db($conn['db_name'], $this->db)) {
			$this->failedconnections++;
			mysql_close($this->db);
			$this->db = null;
			return false;
		}
		if (!is_null($this->defaultcharset)) {
			$this->SetCharset($this->defaultcharset);
		}
		return true;
	}

	public function Reconnect() {
		if ($this->failedconnections > 2)
			return false;
		$fname = 'mysql_' . ($this->persistent ? 'p' : '') . 'connect';
		$conn = &$this->conndata;
		$this->db = $fname($conn['db_host'], $conn['db_user'], $conn['db_pass'], true);
		if (!$this->db) {
			$this->db = null;
			$this->failedconnections++;
			return false;
		}
		if (!mysql_select_db($conn['db_name'], $this->db)) {
			mysql_close($this->db);
			$this->db = null;
			$this->failedconnections++;
			return false;
		}
		if (!is_null($this->defaultcharset)) {
			$this->SetCharset($this->defaultcharset);
		}
	}

	public function UseDatabase($db_name) {
		if (!$this->db) {
			trigger_error('Cannot select database when not connected to mysql server!');
			return false;
		}
		if (!mysql_select_db($db_name, $this->db)) {
			trigger_error('Could not select database: ' . mysql_error($this->db));
			return false;
		}
		return true;
	}

	/**
	 * Performs query on database
	 *
	 * Note: Use SQL_CALC_FOUND_ROWS immediately after SELECT (example: SELECT SQL_CALC_FOUND_ROWS id FROM sometable WHERE somefield="2" LIMIT 100,10)
	 * to be able to get the total count of rows that matched the WHERE statement (300, 400, 1000 or whatever count rows match) later by calling FoundRows()
	 *
	 * @param string $cmd Mysql query to make
	 * @return mysql_result Mysql result
	 */
	public function &Query($cmd, $addDebugData = true) {
		$q_start = microtime(true);
		if (is_null($this->db)) {
			if (!$this->Connect())
				return null;
		}
		$this->queries += 1;
		$this->res = mysql_query($cmd, $this->db);
		$errno = mysql_errno($this->db);
		if ($errno == 2006) {//server has gone away
			$this->db = null;
			$this->Reconnect();
			//run query again after reconnect and also fetch errno again
			$this->res = mysql_query($cmd, $this->db);
			$errno = mysql_errno($this->db);
		}
		$err = mysql_error($this->db);
		if ($errno && $this->trigger_error) {
			trigger_error('Mysql error ' . $errno . ': ' . $err . " ($cmd)", E_USER_ERROR);
		}
		$this->affected_rows = @mysql_affected_rows($this->db);
		$this->affected_rows_total += $this->affected_rows;
		$this->selected_rows = is_resource($this->res) ? @mysql_num_rows($this->res) : 0;
		$this->selected_rows_total += $this->selected_rows;
		$q_end = microtime(true);
		if ($this->slowness_treshold && $q_end - $q_start >= $this->slowness_treshold) {
			trigger_error('Mysql slowness warning ' . number_format($q_end - $q_start, 4) . 's: ' . $cmd, E_USER_WARNING);
		}
		if ($addDebugData) {
			$this->time_spent = $q_end - $q_start;
			$this->time_spent_total += $this->time_spent;
			$this->AddDebugData($cmd);
		}
		return $this->res;
	}

	private $slowness_treshold = 0;

	public function SetSlownessTreshold($treshold = 0) {
		$this->slowness_treshold = max(0.0, (float) $treshold);
	}

	public function SetSlownessThreshold($treshold = 0) {
		$this->slowness_treshold = max(0.0, (float) $treshold);
	}

	/**
	 * Returns how many rows were found in last query
	 * Note: last query must include SQL_CALC_FOUND_ROWS for thist to work as meant to
	 * It is meant to take away the 2 query step where you first select the count of rows according to WHERE statement (to know the total corresponding rows),
	 * after which you select the actual rows itself with limit statement to display data on multiple pages for example.
	 * SQL_CALC_FOUND_ROWS tells mysql to remember the count of total found rows even if you used LIMIT statement to get only partial result of all matches.
	 *
	 * @return int Count of found rows
	 */
	public function FoundRows() {
		$buf = $this->GetRow('SELECT FOUND_ROWS()');
		return $buf[0];
	}

	/**
	 * Returns single mysql result row as unassociated array of selected field values (array keys are integers, not fieldnames) on success, false otherwise
	 * array('val1', 'val2')
	 *
	 * Note: Use SQL_CALC_FOUND_ROWS immediately after SELECT (example: SELECT SQL_CALC_FOUND_ROWS id FROM sometable WHERE somefield="2" LIMIT 100,10)
	 * to be able to get the total count of rows that matched the WHERE statement (300, 400, 1000 or whatever count rows match) later by calling FoundRows()
	 *
	 * @param string $cmd Mysql query to make
	 * @return array|boolean Array of field values or false if query was unsuccessful
	 */
	public function &GetRow($cmd) {
		$q_start = microtime(true);
		if ($this->Query($cmd, false))
			$buf = mysql_fetch_row($this->res);
		else
			$buf = false;
		$q_end = microtime(true);
		$this->time_spent = $q_end - $q_start;
		$this->time_spent_total += $this->time_spent;
		$this->AddDebugData($cmd);
		$this->FreeResult();
		return $buf;
	}

	/**
	 * Returns multiple mysql result rows as array of unassociated arrays of selected field values (array keys are integers, not fieldnames), empty array if unsuccessful
	 * array(array('val1', 'val2'), array('val3', 'val4'))
	 *
	 * Note: Use SQL_CALC_FOUND_ROWS immediately after SELECT (example: SELECT SQL_CALC_FOUND_ROWS id FROM sometable WHERE somefield="2" LIMIT 100,10)
	 * to be able to get the total count of rows that matched the WHERE statement (300, 400, 1000 or whatever count rows match) later by calling FoundRows()
	 *
	 * @param string $cmd Mysql query to make
	 * @param boolean $bundle Whether to bundle first values of result rows into one single level array
	 * @return array Array of results
	 */
	public function &GetRows($cmd, $bundle = false) {
		$q_start = microtime(true);
		$m = array();
		if ($this->Query($cmd, false)) {
			while (($t = mysql_fetch_row($this->res))) {
				if ($bundle) {
					$m[] = reset($t);
				} else {
					$m[] = $t;
				}
			}
		}
		$q_end = microtime(true);
		$this->time_spent = $q_end - $q_start;
		$this->time_spent_total += $this->time_spent;
		$this->AddDebugData($cmd);
		$this->FreeResult();
		return $m;
	}

	/**
	 * Returns multiple mysql result rows as array of associated arrays of selected field values (array keys are fieldnames), empty array in unsuccessful
	 * array(array('name1' => 'val1', 'name2' => 'val2'), array('name1' => 'val3', 'name2' => 'val4'))
	 *
	 * Note: Use SQL_CALC_FOUND_ROWS immediately after SELECT (example: SELECT SQL_CALC_FOUND_ROWS id FROM sometable WHERE somefield="2" LIMIT 100,10)
	 * to be able to get the total count of rows that matched the WHERE statement (300, 400, 1000 or whatever count rows match) later by calling FoundRows()
	 *
	 * @param string $cmd Mysql query to make
	 * @param boolean $assignid Whether to return arrays so that the id field values are set to be the keys of the arrays ( idvalue => array(fieldname=>fieldvalue) )
	 * @param boolean $bundle Whether to bundle all subarrays into one single array using the first subarray value (good to select id fields and instead of looping with nested foreaches or using $row['id'], just get the values as one array)
	 * @return array Array of results
	 */
	public function &GetArrays($cmd, $assignid = false, $bundle = false, $idfield = 'id') {
		$q_start = microtime(true);
		$m = array();
		if ($this->Query($cmd, false)) {
			while (($t = mysql_fetch_array($this->res, MYSQL_ASSOC))) {
				if ($assignid && isset($t[$idfield])) {
					$id = $t[$idfield];
					if ($bundle)
						unset($t[$idfield]);
					$m[$id] = $bundle ? reset($t) : $t;
				} elseif ($bundle) {
					$m[] = reset($t);
				} else {
					$m[] = $t;
				}
			}
		}
		$q_end = microtime(true);
		$this->time_spent = $q_end - $q_start;
		$this->time_spent_total += $this->time_spent;
		$this->AddDebugData($cmd);
		$this->FreeResult();
		return $m;
	}

	/**
	 * Returns single mysql result rows as associated array of selected field values (array keys are fieldnames), false in unsuccessful
	 * array('name1' => 'val1', 'name2' => 'val2')
	 *
	 * Note: Use SQL_CALC_FOUND_ROWS immediately after SELECT (example: SELECT SQL_CALC_FOUND_ROWS id FROM sometable WHERE somefield="2" LIMIT 100,10)
	 * to be able to get the total count of rows that matched the WHERE statement (300, 400, 1000 or whatever count rows match) later by calling FoundRows()
	 *
	 * @param string $cmd Mysql query to make
	 * @return array|boolean Array of fielname => fieldvalue mappings or false if unsuccessful
	 */
	public function &GetArray($cmd) {
		$q_start = microtime(true);
		if ($this->Query($cmd, false)) {
			$buf = mysql_fetch_array($this->res, MYSQL_ASSOC);
		} else
			$buf = false;
		$q_end = microtime(true);
		$this->time_spent = $q_end - $q_start;
		$this->time_spent_total += $this->time_spent;
		$this->AddDebugData($cmd);
		$this->FreeResult();
		return $buf;
	}

	public function &GetSingleRowField($cmd) {
		return $this->GetScalar($cmd);
	}

	/**
	 * Returns first column value of first selected row
	 *
	 * @param string $cmd Mysql query to make
	 * @return string
	 */
	public function &GetScalar($cmd) {
		$q_start = microtime(true);
		if ($this->Query($cmd, false))
			$buf = mysql_fetch_row($this->res);
		else
			$buf = false;
		$q_end = microtime(true);
		$this->time_spent = $q_end - $q_start;
		$this->time_spent_total += $this->time_spent;
		$this->AddDebugData($cmd);
		$this->FreeResult();
		$ret = false;
		if (is_array($buf)) {
			$ret = reset($buf);
		}
		return $ret;
	}

	/**
	 * Returns the id of the last row inserted into database
	 *
	 * @return int Id of last inserted row
	 */
	public function &InsertId() {
		$id = mysql_insert_id($this->db);
		$errno = mysql_errno($this->db);
		if ($errno) {
			$err = mysql_error($this->db);
			trigger_error('Mysql error ' . $errno . ': ' . $err . " (getting inserted id)", E_USER_ERROR);
		}
		return $id;
	}

	/**
	 * Starts transaction
	 *
	 * @return boolean If transaction was started successfully
	 */
	public function TransactionBegin() {
		if ($this->in_transaction) {
			return false;
		}
		$res = $this->Query('START TRANSACTION');
		if (!$res) {
			return false;
		}
		$this->in_transaction = true;
		return true;
	}

	/**
	 * Ends/commits transaction
	 *
	 * @return boolean If commiting was successful
	 */
	public function TransactionEnd() {
		if (!$this->in_transaction) {
			return false;
		}
		$this->in_transaction = false;
		$res = $this->Query('COMMIT');
		if (!$res) {
			return false;
		}
		return true;
	}

	/**
	 * Rolls back current transaction
	 *
	 * @return boolean If rolling back was successful
	 */
	public function TransactionRollback() {
		if (!$this->in_transaction) {
			return false;
		}
		$this->in_transaction = false;
		$res = $this->Query('ROLLBACK');
		if (!$res) {
			return false;
		}
		return true;
	}

	/**
	 * Gets full column data description for wanted table as associative array with keys:
	 * Field, Type, Collation, Null, Key, Default, Extra, Privileges, Comment
	 *
	 * @param string $tablename Name of the table for what to get the column data
	 * @return array Array of
	 */
	public function GetColumnDataForTable($tablename) {
		return $this->GetArrays('SHOW FULL COLUMNS FROM `' . $this->EscapeString($tablename) . '`');
	}

	/**
	 * Gets the results of table expain query as associative array with keys:
	 * Field, Type, Null, Key, Default, Extra
	 *
	 * @param unknown_type $tablename
	 */
	public function GetExplainTable($tablename) {
		return $this->GetArrays('EXPLAIN `' . $this->EscapeString($tablename) . '`');
	}

	/**
	 * Builds Order by statement from passed in array(fieldname => order)
	 *
	 * @param array $fieldorderarr
	 * @return string
	 */
	public function &BuildOrderByStatement($fieldorderarr) {
		if (!is_array($fieldorderarr) || !count($fieldorderarr)) {
			return '';
		}
		$allowedorders = array('ASC', 'DESC');
		$newarr = array();
		foreach ($fieldorderarr as $field => $order) {
			$order = strtoupper($order);
			if (!in_array($order, $allowedorders, true))
				$order = 'ASC';
			$newarr[] = '`' . $this->EscapeString($field) . '` ' . $order;
		}
		$str = 'ORDER BY ' . join(',', $newarr);
		return $str;
	}

	/**
	 * Builds Limit statement from passed in variables
	 *
	 * @param int $start Start of limit array
	 * @param int $count Count of rows to select
	 * @return string
	 */
	public function &BuildLimitStatement($start, $count) {
		$start = max(0, intval($start));
		$count = abs(intval($count));
		$str = 'LIMIT ' . $start . ',' . $count;
		return $str;
	}

	/**
	 * Builds Set statement from passed in array
	 *
	 * @param array $array
	 * @return string Set statement
	 */
	public function &BuildSetStatement(&$array) {
		if (!count($array))
			return '';
		$str = 'SET ';
		$strarr = array();
		foreach ($array as $k => &$v) {
			//if field is null, set database value to NULL also, otherwise escape it and put between "" since mysql does its own conversion anyways ("2" => 2)
			$strarr[] = '`' . $this->EscapeString($k) . '`=' . (is_null($v) ? 'NULL' : '"' . $this->EscapeString($v) . '"');
		}
		$str .= join(',', $strarr);
		return $str;
	}

	/**
	 * Builds Insert statement for given table using given fields and values
	 *
	 * @param string $tablename Name of the table
	 * @param array $array Field => Value array
	 * @return string Statement for inserting data into table
	 */
	public function BuildInsertStatement($tablename, &$array) {
		return 'INSERT INTO `' . $this->EscapeString($tablename) . '` ' . $this->BuildSetStatement($array);
	}

	/**
	 * Builds Update statement for given table using given fields and values
	 *
	 * @param string $tablename Name of the table
	 * @param array $array Field => Value array
	 * @param int $id Id of the row to update
	 * @return string Statement for updating a row data in table
	 */
	public function BuildUpdateStatement($tablename, &$array, $id, $idfield = 'id') {
		return 'UPDATE ' . $this->EscapeString($tablename) . ' ' . $this->BuildSetStatement($array) . ' WHERE `' . $this->EscapeString($idfield) . '`=' . intval($id);
	}

	/**
	 * Builds Delete statement for given table using given id
	 *
	 * @param string $tablename Name of the table
	 * @param int $id Id of the row to delete
	 * @return string Statement for deleting a row from table
	 */
	public function BuildDeleteStatement($tablename, $id) {
		return 'DELETE FROM `' . $this->EscapeString($tablename) . '` WHERE `id`=' . intval($id);
	}

	/**
	 * Truncates table
	 *
	 * @param string $tablename Name of the table
	 * @return boolean If truncating was successful
	 */
	public function TruncateTable($tablename) {
		$res = $this->Query('TRUNCATE TABLE `' . $this->EscapeString($tablename) . '`');
		return $res ? true : false;
	}

	/**
	 * Retrieves table list from database
	 *
	 * @return array Array of table names in current database
	 */
	public function &GetTables() {
		return $this->GetRows('SHOW TABLES', true);
	}

	/**
	 * Retrieves list of fields from given table
	 *
	 * @param string $tablename
	 * @return array
	 */
	public function &GetTableFields($tablename) {
		return $this->GetRows('EXPLAIN `' . $this->EscapeString($tablename) . '`', true);
	}

	/**
	 * Retrieves iterator class for result
	 *
	 * @param string $query
	 * @return MysqlIterator
	 */
	public function GetIterator($query) {
		return new MysqlIterator($query);
	}

	/**
	 * Returns time spent on last query
	 *
	 * @return float
	 */
	public function TimeSpent() {
		return $this->time_spent;
	}

	/**
	 * Returns time spent on all queries together
	 *
	 * @return float
	 */
	public function TimeSpentTotal() {
		return $this->time_spent_total;
	}

	/**
	 * Returns how many rows were selected in last queriy
	 *
	 * @return int
	 */
	public function SelectedRows() {
		return $this->selected_rows;
	}

	/**
	 * Returns how many total rows were selected in all queries together
	 *
	 * @return unknown
	 */
	public function SelectedRowsTotal() {
		return $this->selected_rows_total;
	}

	/**
	 * Returns how many rows were affected by last query
	 *
	 * @return int
	 */
	public function AffectedRows() {
		return $this->affected_rows;
	}

	/**
	 * Returns how many total rows were affected in all queries together
	 *
	 * @return int
	 */
	public function AffectedRowsTotal() {
		return $this->affected_rows_total;
	}

	/**
	 * Returns if there is a transaction active currently
	 *
	 * @return boolean
	 */
	public function InTransaction() {
		return $this->in_transaction;
	}

	/**
	 * Adds query to debugging array using the time_spent, affected_rows and selected_rows private variables set by last query
	 *
	 * @param string $query Mysql query that was run
	 */
	private function AddDebugData($query) {
		if ($this->debug) {
			$debug = array('query' => $query, 'time_spent' => number_format($this->time_spent, 5), 'affected_rows' => $this->affected_rows, 'selected_rows' => $this->selected_rows);
			if ($this->debugtrace) {
				$basedirlen = strlen(dirname(dirname(dirname(__FILE__)))) + 1;
				$bt = debug_backtrace();
				//remove AddDebugData call from the end
				array_shift($bt);
				//if previous call is wrapper from static mysql to dynmysl, remove it
				if (basename($bt[0]['file']) == 'Mysql.php' && $bt[0]['class'] == 'DynMysql')
					array_shift($bt);
				//remove first call (last element in stack) if it's just calling base.php
				if (basename($bt[count($bt) - 2]['file']) == 'base.php') {
					array_pop($bt);
					array_pop($bt);
				}
				$bstr = '';
				foreach ($bt as &$trace) {
					$bstr .= "\n" . ($trace['class'] ? $trace['class'] . ($trace['object'] ? '->' : '::') : '') . $trace['function'] . (isset($trace['file']) ? ' called from ' . substr($trace['file'], $basedirlen) . (isset($trace['line']) ? ' at line ' . $trace['line'] : '') : '' );
					//$bstr .= "\n" . basename($arr['file']) . ' called ' . (isset($arr['class']) ? $arr['class'] . '::' : '') . $arr['function'] . ' at line ' . $arr['line'];
				}
				$debug['trace'] = $bstr;
			}
			$this->queryarr[] = $debug;
		}
	}

	/**
	 * Sets the Mysql class debug mode (in debug mode, queryes and their related values are stored and can be viewed by calling ToString method)
	 *
	 * @param boolean $debug
	 */
	public function SetDebug($debug = false, $debugtrace = false) {
		$this->debug = !!$debug;
		$this->debugtrace = !!$debugtrace;
	}

	/**
	 * Returns data about executed mysql queries in string form, to get more detailed data about each queries(spent time, affected rows etc), use SetDebug(true) before making any queries
	 *
	 * @return string
	 */
	public function ToString() {
		$qstr = 'not available, set debug to true to see more data';
		if ($this->debug) {
			$qstr = "\r\n" . self::ArrToString($this->queryarr) . "\r\n";
		}
		return sprintf("\t(mysql: queries:%s\t time_spent_total:%.08f\t sel_rows_total:%s\t aff_rows_total:%s\t queries: %s)", $this->queries, $this->time_spent_total, $this->selected_rows_total, $this->affected_rows_total, $qstr);
	}

	/**
	 * Converts an array to string similar to print_r but instead of outputting it directly, it is returned as a function result
	 *
	 * @param array $arr Array to convert to string representation
	 * @param int $level Reperesents the depth of recursion
	 * @return string String representation of array
	 */
	private static function ArrToString(&$arr, $level = 0) {
		$str = '';
		$pad = '';
		for ($i = 0; $i < $level; $i++)
			$pad .= '  ';
		if (is_array($arr)) {
			$str .= "Array(\r\n";
			foreach ($arr as $k => $v) {
				$str .= $pad . '  [' . $k . '] => ' . self::ArrToString($v, $level + 1);
			}
			$str .= "$pad)\r\n";
		} else {
			return $arr . "\r\n";
		}
		return $str;
	}

	/**
	 * Frees up mysql resultset
	 *
	 */
	private function FreeResult() {
		if (is_resource($this->res)) {
			mysql_free_result($this->res);
		}
	}

	public function SetTimezone($timezone) {
		return !!$this->Query('SET time_zone="' . $this->EscapeString($timezone) . '"');
	}

	public function SetCharset($set = 'utf8') {
		if (function_exists('mysql_set_charset')) {
			mysql_set_charset($set, $this->db);
		} else {
			$set = addslashes($set);
			mysql_query("SET character_set_results = '$set', character_set_client = '$set', character_set_connection = '$set', character_set_database = '$set', character_set_server = '$set'", $this->db);
		}
	}

	public function SetTriggerError($new = true) {
		$this->trigger_error = $new;
	}

}

class MysqlIterator implements SeekableIterator, Countable {

	private $mysqlResult = null;
	private $currentRow = null;
	private $index = 0;
	private $count = 0;
	private $query = null;

	public function __construct($result) {
		//if query string, exequte query and store result
		if (is_string($result)) {
			$this->query = $result;
			$result = Mysql::Query($result);
		}
		$this->mysqlResult = $result;
		$this->count = mysql_num_rows($result);
		$this->index = 0;
		$this->currentRow = null;
	}

	public function seek($index) {
		$this->index = $index;
		return mysql_data_seek($this->mysqlResult, $index);
	}

	public function next() {
		$this->currentRow = mysql_fetch_array($this->mysqlResult, MYSQL_ASSOC);
		$this->index += 1;
		return $this->currentRow;
	}

	public function current() {
		return $this->currentRow;
	}

	public function valid() {
		return $this->index < $this->count;
	}

	public function rewind() {
		mysql_data_seek($this->mysqlResult, 0);
		$this->currentRow = $this->next();
		$this->index = 0;
	}

	public function key() {
		return $this->index;
	}

	public function count() {
		return $this->count;
	}

	public function __destruct() {
		if (is_resource($this->mysqlResult)) {
			mysql_free_result($this->mysqlResult);
			$this->mysqlResult = null;
		}
	}

	public function __sleep() {
		$this->__destruct();
	}

	public function __wakeup() {
		if ($this->query) {
			$this->mysqlResult = Mysql::Query($this->query);
			$this->count = mysql_num_rows($this->mysqlResult);
		}
		$old = $this->index;
		$this->seek($old);
		$this->currentObj = $this->next();
		$this->seek($old);
	}

}