public class MySQL

class.mysql.php #75

The MySQL class acts as a wrapper for connecting to the Database in Symphony. It utilises mysqli_* functions in PHP to complete the usual querying. As well as the normal set of insert, update, delete and query functions, some convenience functions are provided to return results in different ways. Symphony uses a prefix to namespace it's tables in a database, allowing it play nice with other applications installed on the database. An errors that occur during a query throw a DatabaseException. By default, Symphony logs all queries to be used for Profiling and Debug devkit extensions when a Developer is logged in. When a developer is not logged in, all queries and errors are made available with delegates.

Methods

void __destruct()

Magic function that will flush the MySQL log and close the MySQL connection when the MySQL class is removed or destroyed.

See Also
static void cleanFields(array $array)

This function will apply the cleanValue function to an associative array of data, encoding only the value, not the key. This function can handle recursive arrays. This function manipulates the given parameter by reference.

Parameters
  • $array arrayThe associative array of data to encode, this parameter is manipulated by reference.

See Also
static string cleanValue(string $value)

This function will clean a string using the mysqli_real_escape_string function taking into account the current database character encoding. Note that this function does not encode _ or %. If mysqli_real_escape_string doesn't exist, addslashes will be used as a backup option

Parameters
  • $value stringThe string to be encoded into an escaped SQL string

Returns

The escaped SQL string

boolean close()

Called when the script has finished executing, this closes the MySQL connection

boolean connect(string $host, string $user, string $password, string $port, null $database)

Creates a connect to the database server given the credentials. If an error occurs, a DatabaseException is thrown, otherwise true is returned

Parameters
  • $host stringDefaults to null, which MySQL assumes as localhost.

  • $user stringDefaults to null

  • $password stringDefaults to null

  • $port stringDefaults to 3306.

array debug(null|string $type)

Returns all the log entries by type. There are two valid types, error and debug. If no type is given, the entire log is returned, otherwise only log messages for that type are returned

Returns

An array of associative array's. Log entries of the error type return the query the error occurred on and the error number and message from MySQL. Log entries of the debug type return the the query and the start/stop time to indicate how long it took to run

boolean delete(string $table, string $where)

Given a table name and a WHERE statement, delete rows from the Database.

Parameters
  • $table stringThe table name, including the tbl prefix which will be changed to this Symphony's table prefix in the query function

  • $where stringA WHERE statement for this DELETE statement, defaults to null, which will delete all rows in the $table

integer determineQueryType(string $query)

Determines whether this query is a read operation, or if it is a write operation. A write operation is determined as any query that starts with CREATE, INSERT, REPLACE, ALTER, DELETE, UPDATE, OPTIMIZE, TRUNCATE or DROP. Anything else is considered to be a read operation which are subject to query caching.

Returns

self::__WRITE_OPERATION__ or self::__READ_OPERATION__

static void disableCaching()

Sets query caching to false, this will prepend all READOPERATION queries will SQLNO_CACHE.

static void disableLogging()

Disables query logging and profiling. Use this in low memory environments to reduce memory usage.

See Also
static void enableCaching()

Sets query caching to true, this will prepend all READOPERATION queries with SQLCACHE. Symphony by default enables caching. It can be turned off by setting the query_cache parameter to off in the Symphony config file.

See Also
static void enableLogging()

Enables query logging and profiling.

array fetch(string $query, string $index_by_column)

Returns an associative array that contains the results of the given $query. Optionally, the resulting array can be indexed by a particular column.

Parameters
  • $query stringThe full SQL query to execute. Defaults to null, which will use the _lastResult

  • $index_by_column stringThe name of a column in the table to use it's value to index the result by. If this is omitted (and it is by default), an array of associative arrays is returned, with the key being the column names

Returns

An associative array with the column names as the keys

array fetchCol(string $column, string $query)

Returns an array of values for a specified column in a given query. If no query is given, it will use the $this->_lastResult.

Parameters
  • $column stringThe column name in the query to return the values for

  • $query stringThe full SQL query to execute. Defaults to null, which will use the $this->_lastResult

Returns

If there is no results for the $query, an empty array will be returned otherwise an array of values for that given $column will be returned

array fetchRow(integer $offset, string $query)

Returns the row at the specified index from the given query. If no query is given, it will use the $this->_lastResult. If no offset is provided, the function will return the first row. This function does not imply any LIMIT to the given $query, so for the more efficient use, it is recommended that the $query have a LIMIT set.

Parameters
  • $offset integerThe row to return from the SQL query. For instance, if the second row from the result was required, the offset would be 1, because it is zero based.

  • $query stringThe full SQL query to execute. Defaults to null, which will use the $this->_lastResult

Returns

If there is no row at the specified $offset, an empty array will be returned otherwise an associative array of that row will be returned.

string|null fetchVar(string $column, integer $offset, string $query)

Returns the value for a specified column at a specified offset. If no offset is provided, it will return the value for column of the first row. If no query is given, it will use the $this->_lastResult.

Parameters
  • $column stringThe column name in the query to return the values for

  • $offset integerThe row to use to return the value for the given $column from the SQL query. For instance, if $column form the second row was required, the offset would be 1, because it is zero based.

  • $query stringThe full SQL query to execute. Defaults to null, which will use the $this->_lastResult

Returns

Returns the value of the given column, if it doesn't exist, null will be returned

void flush()

Resets the result, $this->_lastResult and $this->_lastQuery to their empty values. Called on each query and when the class is destroyed.

static void flushLog()

Sets the current $_log to be an empty array

static resource getConnectionResource()

Accessor for the current MySQL resource from PHP. May be useful for developers who want complete control over their database queries and don't want anything abstract by the MySQL class.

integer getInsertID(string $context, string $query, string $query_hash, float $execution_time)

Returns the last insert ID from the previous query. This is the value from an auto_increment field.

Returns

The last interested row's ID

string getPrefix()

Returns the prefix used by Symphony for this Database instance.

array getStatistics()

Returns some basic statistics from the MySQL class about the number of queries, the time it took to query and any slow queries. A slow query is defined as one that took longer than 0.0999 seconds This function is used by the Profile devkit

Returns

An associative array with the number of queries, an array of slow queries and the total query time.

boolean import(string $sql, boolean $force_engine)

Convenience function to allow you to execute multiple SQL queries at once by providing a string with the queries delimited with a ;

Parameters
  • $sql stringA string containing SQL queries delimited by ;

  • $force_engine booleanIf set to true, this will set MySQL's default storage engine to MyISAM. Defaults to false, which will use MySQL's default storage engine when tables don't explicitly define which engine they should be created with

Returns

If one of the queries fails, false will be returned and no further queries will be executed, otherwise true will be returned.

boolean insert(array $fields, string $table, boolean $updateOnDuplicate)

A convenience method to insert data into the Database. This function takes an associative array of data to input, with the keys being the column names and the table. An optional parameter exposes MySQL's ON DUPLICATE KEY UPDATE functionality, which will update the values if a duplicate key is found.

Parameters
  • $fields arrayAn associative array of data to input, with the key's mapping to the column names. Alternatively, an array of associative array's can be provided, which will perform multiple inserts

  • $table stringThe table name, including the tbl prefix which will be changed to this Symphony's table prefix in the query function

  • $updateOnDuplicate booleanIf set to true, data will updated if any key constraints are found that cause conflicts. By default this is set to false, which will not update the data and would return an SQL error

static boolean isCachingEnabled()

Returns boolean if query caching is enabled or not

static boolean isConnected()

Determines if a connection has been made to the MySQL server

static boolean isLoggingEnabled()

Returns boolean if logging is enabled or not

boolean query(string $query, string $type)

Takes an SQL string and executes it. This function will apply query caching if it is a read operation and if query caching is set. Symphony will convert the tbl_ prefix of tables to be the one set during installation. A type parameter is provided to specify whether $this->_lastResult will be an array of objects or an array of associative arrays. The default is objects. This function will return boolean, but set $this->_lastResult to the result.

Uses
  • PostQueryExecution
Parameters
  • $query stringThe full SQL query to execute.

  • $type stringWhether to return the result as objects or associative array. Defaults to OBJECT which will return objects. The other option is ASSOC. If $type is not either of these, it will return objects.

Returns

true if the query executed without errors, false otherwise

static integer queryCount()

Returns the number of queries that has been executed

void setCharacterEncoding(string $set)

This will set the character encoding of the connection for sending and receiving data. This function will run every time the database class is being initialized. If no character encoding is provided, UTF-8 is assumed.

Parameters
  • $set stringThe character encoding to use, by default this 'utf8'

See Also
void setCharacterSet(string $set)

This function will set the character encoding of the database so that any new tables that are created by Symphony use this character encoding

Parameters
  • $set stringThe character encoding to use, by default this 'utf8'

See Also
void setPrefix(string $prefix)

Symphony uses a prefix for all it's database tables so it can live peacefully on the same database as other applications. By default this is sym_, but it can be changed when Symphony is installed.

Parameters
  • $prefix stringThe table prefix for Symphony, by default this is sym_

void setTimeZone(string $timezone)

Sets the MySQL connection to use this timezone instead of the default MySQL server timezone.

Parameters
  • $timezone stringTimezone will human readable, such as Australia/Brisbane.

See Also
boolean tableContainsField(string $table, string $field)

This function takes $table and $field names and returns boolean if the $table contains the $field.

Parameters
  • $table stringThe table name

  • $field stringThe field name

Returns

true if $table contains $field, false otherwise

See Also
boolean tableExists(string $table)

This function takes $table and returns boolean if it exists or not.

Parameters
  • $table stringThe table name

Returns

true if $table exists, false otherwise

See Also
boolean update(array $fields, string $table, string $where)

A convenience method to update data that exists in the Database. This function takes an associative array of data to input, with the keys being the column names and the table. A WHERE statement can be provided to select the rows to update

Parameters
  • $fields arrayAn associative array of data to input, with the key's mapping to the column names.

  • $table stringThe table name, including the tbl prefix which will be changed to this Symphony's table prefix in the query function

  • $where stringA WHERE statement for this UPDATE statement, defaults to null which will update all rows in the $table

Symphony • Open Source XSLT CMS

Server Requirements

  • PHP 5.3-5.6 or 7.0-7.3
  • PHP's LibXML module, with the XSLT extension enabled (--with-xsl)
  • MySQL 5.5 or above
  • An Apache or Litespeed webserver
  • Apache's mod_rewrite module or equivalent

Compatible Hosts

Sign in

Login details