PHP Classes

File: Mysql.php

Recommend this page to a friend!
  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: Documentation cleanup.
Moved internal Mysql wrapper into base abstract class and reimplemented for mysql and mysqli extension.
Added automatic switchover to mysqli verion whenever possible.
Added support for connection compression.
Track last known mysql error and return it from GetLastError().
Added TotalQueries() to return total number of queries run through the wrapper.
Added Connected() to check if db connection is open or not.
Added SetDatabaseHandle() to pass in pre-existing open database handle without doing close/open.
Mark MysqlIterator as deprecated.
Date: 8 years ago
Size: 45,958 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 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); } }