====== nsDatabase ====== This is MySQL database driver. Currently NickSoft Class Library supports only mysql. ===== Members ===== ==== totalTime ==== public $totalTime = 0.0; This is total time in seconds of all database queries. It is float value which contains microseconds info too. ==== timeStart ==== public $timeStart = 0.0; This is the time since MySQL connection was open. ==== lastQuery ==== public $lastQuery; This is the last query executed. ==== dbLink ==== protected $dbLink = null; This is MySQL database link resource. It can be set using [[#import]] method ===== Methods ===== ==== Constructor ==== public function __construct($dbHost,$dbName,$dbUser,$dbPass); Constructor. Sets credentials. Note that connection is not opened on object creation. FIXME - credentials need to be passed as array; ==== connect ==== * **return value:** link id resource public function connect(); ==== import ==== * $link - MySQL link id resource * $dbName - Database name * **return value:** - public function import($link, $dbName = false); Imports a link id resourse. Note that reconnecting will not work if credentials are not correct. ==== selectDb ==== * $dbName - Database name * **return value:** - public function selectDb($dbName); If not successful an exception is thrown. ==== setOptions ==== * $dbFlags - flags. * **return value:** - public function setOptions($dbFlags); Accepted flags are: * **MYSQL_CLIENT_SSL** - use ssl connection * **MYSQL_CLIENT_COMPRESS** - use compression * **MYSQL_CLIENT_IGNORE_SPACE** - :?: * **MYSQL_CLIENT_INTERACTIVE** - :?: ==== escape ==== * $str - string to escape. * **return value** - escaped string public function escape($str); Escape a string to use as value in MySQL connection. It uses **mysql_real_escape_string()** and takes connection information in account ==== quote ==== Synonym for [[#escape]] ==== foundRows ==== * **return value** - number of rows without LIMIT public function foundRows(); Returns number of rows that would have returned previous SELECT query if there was not LIMIT clause. To work this previous SELECT query must contain SQL_CALC_FOUND_ROWS directive. ==== query ==== * $query - the query to run. * **return value** - mysql result resource public function query($query); Runs query and returns result resource. In case of error an exception is thrown. ==== queryFirstField ==== * $query - the query to run. * **return value** - value of first field of first row of the result public function queryFirstRow($query); Runs query and returns value of first field of first row of the result. In case of error an exception is thrown. ==== queryFirstRow ==== * $query - the query to run. * $fetchFunc - type of array - MYSQL_ASSOC or MYSQL_NUM. * **return value** - array of values in first row public function query($query, $fetchFunc = MYSQL_ASSOC); Runs query and returns array of values of the first row in the result. If $fetchFunc is MYSQL_ASSOC array is associative using column names as keys. In case of error an exception is thrown. ==== queryRows ==== * $query - the query to run. * $fetchFunc - type of array - MYSQL_ASSOC or MYSQL_NUM. * **return value** - all rows in the result as array of arrays. public function query($query, $fetchFunc = MYSQL_ASSOC); Runs query and returns rows of result as array of arrays. If $fetchFunc is MYSQL_ASSOC array is associative using column names as keys. In case of error an exception is thrown. ==== queryAssoc ==== * $query - the query to run. * $key - Key field for associative array. * $value - Value field for associative array. * **return value** - associative array with results. public function queryAssoc($query, $key, $value); Returns associative array where key is value of $key field and value id value of $value field. Good for . example: queryAssocSimple('SELECT id, name FROM groups'); // returns associative array where id field is used for key and name field is used for value ==== listFields ==== * $table - table name * **return value** - result from SHOW COLUMNS FROM //// public function listFields($table); Get info about columns in $table. ==== prepareInsert ==== * $fields_arr - associative array of values using field names as keys. * **return value** - values part of INSERT query - SQL formated. public function prepareInsert($fields_arr); The argument is associative array of values with field names as keys. * If value is a **integer** or **float** it is passed without a change. * If value is array there are few cases: * ['type'] is set - value of **type** element is a function name. Supported functions are: * **relace** - replace a string. search for **{value}** in ['string'] and replace with ['value'] * ['type'] is not set - use value directly, without escaping it. * else the value is escaped. Formats the values to be used in INSERT query. Complete query will be: INSERT INTO
==== prepareUpdate ==== * $fields_arr - associative array of values using field names as keys. * **return value** - values part of UPDATE query - SQL formated. public function prepareUpdate($fields_arr); The argument is associative array of values with field names as keys. * If value is a **integer** or **float** it is passed without a change. * If value is array there are few cases: * ['type'] is set - value of **type** element is a function name. Supported functions are: * **relace** - replace a string. search for **{value}** in ['string'] and replace with ['value'] * ['type'] is not set - use value directly, without escaping it. * else the value is escaped. Formats the values to be used in UPDATE query. Complete query will be: UPDATE
SET WHERE ... ==== insert ==== This function was renamed from **execInsert**. execInsert is also available as alias, but it's deprecated, so don't use it and fix it in existing projects. * $table - table name * $fields_arr - associative array of values using field names as keys. * **return value** - last insert id public function insert($table, $fields_arr); Prepares values with [[#prepareInsert]] and executes the INSERT query. result is last insert id. ==== update ==== This function was renamed from **execUpdate**. execUpdate is also available as alias, but it's deprecated, so don't use it and fix it in existing projects. * $table - table name * $fields_arr - associative array of values using field names as keys. * $where - WHERE clause * **return value** - last insert id public function execUpdate($table,$fields_arr,$where); Prepares values with [[#prepareUpdate]] and executes the UPDATE query. result is last insert id. ==== insertUpdate ==== This function was renamed from **execInsertUpdate**. execInsertUpdate is also available as alias, but it's deprecated, so don't use it and fix it in existing projects. * $table - table name * $fields_arr - associative array of values using field names as keys. * $key - primary key * $insertId - true if the key is autoincrement id and false if not. * **return value** - last insert id if primary key is autoincrement or else the primary key. public function execInsertUpdate($table, $fields_arr, $key = 'id', $insertId = true); This function executes **INSERT ... ON DUPLICATE KEY UPDATE** query. Sometimes you need different value for insert and update statements. You can do that by making the value array. If you need to to insert 0 and on update to add 1 you can do this: $arr =array( 'id' => 1, 'counter' => array( 'insert' => 0, 'update' => array('counter + 1') // no escaping or quotting ); $db->execInsertUpdate('table', $arr); So if record with id doesn't exist it'll insert values(1,0). If the record already exists and counter is 2 for example it will update record and increase counter to 3. When primary key is not autoincrement set $insertId to false otherwise this function calls **LAST_INSERT_ID()**. ==== simpleUpdate ==== * $table - table name * $fields_arr - associative array of values using field names as keys. * $condField - optional field to be used for WHERE clause * **return value** - last insert id public function simpleUpdate($table, $fields_arr, $condField = null); Prepares values with [[#prepareUpdate]] and executes the UPDATE query. result is last insert id. If $condField is present it is used as WHERE clause. If not - the first field is used for WHERE clause. Array must contain at least 2 fields. ==== escapeField ==== * $field - field name to be escaped * **return value** - escaped field public static function escapeField($field); if field name is string and consist of letters, numbers and "_" put ` around it. If not - it's a function or constant - return without formatting. ==== startTransaction ==== * **return value** - none public function startTransaction(); Starts a transaction ==== savepoint ==== * $savepoint - the savepoint name * **return value** - none public function savepoint($savepoint); Creates a savepoint within a transaction. Note: added in v1.0.1.1-alpha ==== commit ==== * **return value** - none public function commit(); Commits a transaction. ==== releaseSavepoint ==== * $savepoint - the savepoint name * **return value** - none public function releaseSavepoint($savepoint); Releases a savepoint within transaction. Note: added in v1.0.1.1-alpha ==== rollback ==== * $savepoint - the savepoint (string) to rollback to. If this is null, the whole transaction is rolled back. * **return value** - none public function rollback($savepoint = null); Rollback a transaction or savepoint. Changelog: * v1.0.1.1-alpha - added $savepoint parameter ==== close ==== * **return value** - none public function close(); Closes the connection to mysql.