nsDatabase

This is MySQL database driver. Currently NickSoft Class Library supports only mysql.

  public $totalTime = 0.0;

This is total time in seconds of all database queries. It is float value which contains microseconds info too.

  public $timeStart = 0.0;

This is the time since MySQL connection was open.

  public $lastQuery;

This is the last query executed.

  protected $dbLink = null;

This is MySQL database link resource. It can be set using import method

  public function __construct($dbHost,$dbName,$dbUser,$dbPass);

Constructor. Sets credentials. Note that connection is not opened on object creation.

- credentials need to be passed as array;

• return value: link id resource

  public function connect();

• $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.

• $dbName - Database name
• return value: -

  public function selectDb($dbName);

If not successful an exception is thrown.

• $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 -

• $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

Synonym for escape

• 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 - 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.

• $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.

• $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.

• $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.

• $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 <select>. example:

queryAssoc('SELECT id, name FROM groups', 'id', 'name');

• $query - the query to run.
• return value - associative array with results.

  public function queryAssocSimple($query);

lazy associative array: - first field is the key, second is the value. Good for <select>. example:

queryAssocSimple('SELECT id, name FROM groups'); // returns associative array where id field is used for key and name field is used for value

• $table - table name
• return value - result from SHOW COLUMNS FROM <table>

  public function listFields($table);

Get info about columns in $table.

• $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 <table> <result>

• $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 <table> SET <result> WHERE ...

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.

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.

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().

• $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.

• $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.

• return value - none

  public function startTransaction();

Starts a transaction

• $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

• return value - none

  public function commit();

Commits a transaction.

• $savepoint - the savepoint name
• return value - none

  public function releaseSavepoint($savepoint);

Releases a savepoint within transaction.

Note: added in v1.0.1.1-alpha

• $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

• return value - none

  public function close();

Closes the connection to mysql.