<?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 DynMysqlBase
*/
private static $mysql;
private static $inited = false;
const Version = 1.3;
/**
* Disables instantiating of this class
*
*/
private function __construct() {
}
/**
* Returns the singleton Mysql instance.
* @return DynMysqlBase
*/
public static function instance() {
return self::$mysql;
}
/**
* 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, $compression = false) {
if (self::$inited) {
return;
}
//if possible, try to use mysqli, newer PHP will be deprecating older mysql extension
if (function_exists('mysqli_connect')) {
self::$mysql = new DynMysqli($db_host, $db_user, $db_pass, $db_name, $autoconnect, $persistent, $defaultcharset, $compression);
} else
self::$mysql = new DynMysql($db_host, $db_user, $db_pass, $db_name, $autoconnect, $persistent, $defaultcharset, $compression);
self::$inited = true;
}
public static function GetLastError() {
return self::$mysql->GetLastError();
}
/**
* 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->SetSlownessThreshold($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();
}
/**
* Deprecated: returns scalar value of query (first cell of first row)
* @param string $cmd Mysql query to make
* @return string
* @deprecated
*/
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
* @param boolean $debugtrace
*/
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 string $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 int
*/
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 how many queries were run
* @return int
*/
public static function TotalQueries() {
return self::$mysql->TotalQueries();
}
/**
* 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 Connected() {
return self::$mysql->Connected();
}
public static function SetTimezone($timezone) {
return self::$mysql->SetTimezone($timezone);
}
public static function SetCharset($set = 'utf8') {
return self::$mysql->SetCharset($set);
}
public static function SetDatabaseHandle($db) {
return self::$mysql->SetDatabaseHandle($db);
}
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 extends DynMysqlBase {
public function db_affected_rows() {
return mysql_affected_rows($this->db);
}
public function db_close() {
return mysql_close($this->db);
}
public function db_connect($host, $user, $pass, $new_link = false) {
//use special flags if needed
$flags = $this->compression ? MYSQL_CLIENT_COMPRESS : 0;
return mysql_connect($host, $user, $pass, $new_link, $flags);
}
public function db_pconnect($host, $user, $pass, $new_link = false) {
if ($new_link)
return $this->db_connect($host, $user, $pass, $new_link);
//use special flags if needed
$flags = $this->compression ? MYSQL_CLIENT_COMPRESS : 0;
return mysql_pconnect($host, $user, $pass, $flags);
}
public function db_errno() {
return mysql_errno($this->db);
}
public function db_error() {
return mysql_error($this->db);
}
public function db_escape_string($str) {
return mysql_real_escape_string($str, $this->db);
}
public function db_fetch_assoc($res) {
return mysql_fetch_assoc($res);
}
public function db_fetch_row($res) {
return mysql_fetch_row($res);
}
public function db_free_result($res) {
return mysql_free_result($res);
}
public function db_insert_id() {
return mysql_insert_id($this->db);
}
public function db_num_rows($res) {
return mysql_num_rows($res);
}
public function db_query($sql) {
return mysql_query($sql, $this->db);
}
public function db_select_db($dbname) {
return mysql_select_db($dbname, $this->db);
}
public function db_set_charset($set) {
if (!function_exists('mysql_set_charset'))
return false;
return mysql_set_charset($set, $this->db);
}
public function db_warning_count() {
//old mysql does not have warning_count helper, use SELECT when in debug mode
if ($this->debug) {
$res = $this->db_query('SELECT @@warning_count');
if ($res) {
$row = $this->db_fetch_row($res);
$this->db_free_result($res);
return (int) $row[0];
}
} else
return 0;
}
public function db_get_server_version() {
return 0;
}
}
/**
* 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 DynMysqli extends DynMysqlBase {
public function db_affected_rows() {
return mysqli_affected_rows($this->db);
}
public function db_close() {
return mysqli_close($this->db);
}
public function db_connect($host, $user, $pass, $new_link = false) {
//use regular initialization
if (!$this->compression)
return mysqli_connect($host, $user, $pass);
//to pass compression flags, we need some special logic
$mysqli = mysqli_init();
$flags = MYSQLI_CLIENT_COMPRESS;
if (mysqli_real_connect($mysqli, $host, $user, $pass, null, null, null, $flags))
return $mysqli;
mysqli_close($mysqli);
return false;
}
public function db_pconnect($host, $user, $pass, $new_link = false) {
//no pconnect for mysqli, reuse connect
return $this->db_connect($host, $user, $pass, $new_link);
}
public function db_errno() {
return mysqli_errno($this->db);
}
public function db_error() {
return mysqli_error($this->db);
}
public function db_escape_string($str) {
return mysqli_real_escape_string($this->db, $str);
}
public function db_fetch_assoc($res) {
return mysqli_fetch_assoc($res);
}
public function db_fetch_row($res) {
return mysqli_fetch_row($res);
}
public function db_free_result($res) {
return mysqli_free_result($res);
}
public function db_insert_id() {
return mysqli_insert_id($this->db);
}
public function db_num_rows($res) {
return mysqli_num_rows($res);
}
public function db_query($sql) {
return mysqli_query($this->db, $sql);
}
public function db_select_db($dbname) {
return mysqli_select_db($this->db, $dbname);
}
public function db_set_charset($set) {
return mysqli_set_charset($this->db, $set);
}
public function db_warning_count() {
return mysqli_warning_count($this->db);
}
public function db_get_server_version() {
return mysqli_get_server_version($this->db);
}
}
/**
* 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
*
*/
abstract class DynMysqlBase {
private $res = null;
private $queries = 0;
protected $db = null; //to allow access from db_* functions
private $time_spent_total = 0;
private $affected_rows_total = 0;
private $selected_rows_total = 0;
private $time_spent = 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 $queryarr = array();
protected $debug = false; //to allow access from db_* functions (for mysql warnings_count)
protected $compression = false; //to allow access from db_connect function
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, $compression = false) {
$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;
$this->compression = !!$compression;
if ($autoconnect) {
$this->Connect();
}
}
/**
* Destructor, closes open connections
*/
public function __destruct() {
$this->Close();
}
abstract function db_close();
abstract function db_escape_string($str);
abstract function db_connect($host, $user, $pass, $new_link = false);
abstract function db_pconnect($host, $user, $pass, $new_link = false);
abstract function db_select_db($dbname);
abstract function db_error();
abstract function db_errno();
abstract function db_query($sql);
abstract function db_affected_rows();
abstract function db_num_rows($res);
abstract function db_fetch_assoc($res);
abstract function db_fetch_row($res);
abstract function db_free_result($res);
abstract function db_set_charset($set);
abstract function db_insert_id();
abstract function db_warning_count();
abstract function db_get_server_version();
/**
* Closes existing connection
*/
public function Close() {
if ($this->db) {
$this->db_close();
$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 $this->db_escape_string($str);
return str_replace(array('_', '%'), array('\_', '\%'), $this->db_escape_string($str));
}
private $failedconnections = 0;
private function Connect() {
if ($this->db) {
return true;
}
if ($this->failedconnections > 2)
return false;
$fname = 'db_' . ($this->persistent ? 'p' : '') . 'connect';
$conn = &$this->conndata;
$this->db = $this->$fname($conn['db_host'], $conn['db_user'], $conn['db_pass']);
if (!$this->db) {
$this->db = null;
$this->failedconnections++;
return false;
}
if (!$this->db_select_db($conn['db_name'])) {
$this->failedconnections++;
$this->db_close();
$this->db = null;
return false;
}
if (!is_null($this->defaultcharset)) {
$this->SetCharset($this->defaultcharset);
}
return true;
}
public function SetDatabaseHandle($db) {
$this->db = $db;
}
public function Reconnect() {
if ($this->failedconnections > 2)
return false;
$fname = 'db_' . ($this->persistent ? 'p' : '') . 'connect';
$conn = &$this->conndata;
$this->db = $this->$fname($conn['db_host'], $conn['db_user'], $conn['db_pass'], true);
if (!$this->db) {
$this->db = null;
$this->failedconnections++;
return false;
}
if (!$this->db_select_db($conn['db_name'])) {
$this->db_close();
$this->db = null;
$this->failedconnections++;
return false;
}
if (!is_null($this->defaultcharset)) {
$this->SetCharset($this->defaultcharset);
}
}
public function Connected() {
return !!$this->db;
}
public function UseDatabase($db_name) {
if (!$this->db) {
trigger_error('Cannot select database when not connected to mysql server!');
return false;
}
if (!$this->db_select_db($db_name)) {
trigger_error('Could not select database: ' . $this->db_error());
return false;
}
return true;
}
private $lastError = false;
public function GetLastError() {
return $this->lastError;
}
/**
* 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 ($this->db === null) {
if (!$this->Connect())
return null;
}
$this->queries++;
$this->res = $this->db_query($cmd);
$errno = $this->db_errno();
if ($errno == 2006 || $errno == 2013) {//server has gone away || lost connection during query
$this->db = null;
$this->Reconnect();
//run query again after reconnect and also fetch errno again
$this->res = $this->db_query($cmd);
$errno = $this->db_errno();
}
$err = $this->db_error();
if ($errno) {
if ($this->trigger_error)
trigger_error('Mysql error ' . $errno . ': ' . $err . " ($cmd)", E_USER_ERROR);
$this->lastError = $errno . ': ' . $err;
} else
$this->lastError = false;
$this->affected_rows = @$this->db_affected_rows();
$this->affected_rows_total += $this->affected_rows;
$this->selected_rows = $this->res && $this->res !== true /* UPDATE queries return true, not result */ ? @$this->db_num_rows($this->res) : 0;
$this->selected_rows_total += $this->selected_rows;
$q_end = microtime(true);
if ($this->slowness_threshold && $q_end - $q_start >= $this->slowness_threshold) {
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_threshold = 0;
public function SetSlownessThreshold($threshold = 0) {
$this->slowness_threshold = max(0.0, (float) $threshold);
}
/**
* 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 intval($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 = $this->db_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 = $this->db_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 = $this->db_fetch_assoc($this->res))) {
if ($assignid) {
$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 = $this->db_fetch_assoc($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;
}
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 = $this->db_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 = $this->db_insert_id();
$errno = $this->db_errno();
if ($errno) {
$err = $this->db_error();
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 string $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 int
*/
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;
}
/**
* Returns how many queries were run
* @return int
*/
public function TotalQueries() {
return $this->queries;
}
/**
* 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" . (isset($trace['class']) ? $trace['class'] . (isset($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
* @param boolean $debugtrace
*/
public function SetDebug($debug = false, $debugtrace = false) {
$this->debug = !!$debug;
$this->debugtrace = $this->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 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 ($this->res) {
$this->db_free_result($this->res);
$this->res = null;
}
}
public function SetTimezone($timezone) {
return !!$this->Query('SET time_zone="' . $this->EscapeString($timezone) . '"');
}
public function SetCharset($set = 'utf8') {
if (!$this->db_set_charset($set)) {
$set = addslashes($set);
$this->db_query("SET character_set_results = '$set', character_set_client = '$set', character_set_connection = '$set', character_set_database = '$set', character_set_server = '$set'");
}
}
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) {
trigger_error('Deprecated?!');
//if query string, execute 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 ($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);
}
}
|
Download
