Ultimate MySQL Wrapper Class

 Methods

UPSERT a row

AutoInsertUpdate(string $tableName, array $valuesArray, array | string $whereArray) : boolean
API

Automatically does an INSERT or UPDATE depending on whether a record already exists in a table.

Parameters

$tableName

string

The name of the table

$valuesArray

array

An associative array containing the column names as keys and values as data. The values must be SQL ready (i.e. quotes around strings, formatted dates, etc.)

$whereArray

arraystring

An associative array containing the column names as keys and values as data. The values must be SQL ready (i.e. quotes around strings, formatted dates, etc.).
This parameter may alternatively be a string, in which case it is used verbatim for the WHERE clause of the query. This is useful when advanced queries are constructed.

Returns

booleanReturns TRUE on success or FALSE on error

Test if the internal pointer is at the start of the record set

BeginningOfSeek() : boolean
API

Returns true if the internal pointer is at the beginning of the record set produced by the last query.

Returns

booleanTRUE if at the first row or FALSE if not

Examples

if ($db->BeginningOfSeek())
{
    echo "We are at the beggining of the record set";
}

Builds a SQL DELETE statement

BuildSQLDelete(string $tableName, array | string $whereArray) : string
API

Parameters

$tableName

string

The name of the table

$whereArray

arraystring

(Optional) An associative array containing the column names as keys and values as data. The values must be SQL ready (i.e. quotes around strings, formatted dates, etc.). If not specified then all values in the table are deleted.
This parameter may alternatively be a string, in which case it is used verbatim for the WHERE clause of the query. This is useful when advanced queries are constructed.

Returns

stringReturns the SQL DELETE statement

Examples

// Let's create an array for the example
// $arrayVariable["column name"] = formatted SQL value
$filter["ID"] = MySQL::SQLValue(7, MySQL::SQLVALUE_NUMBER);
// Echo out the SQL statement
echo MySQL::BuildSQLDelete("MyTable", $filter);

Builds a SQL INSERT statement

BuildSQLInsert(string $tableName, array $valuesArray) : string
API

Parameters

$tableName

string

The name of the table

$valuesArray

array

An associative array containing the column names as keys and values as data. The values must be SQL ready (i.e. quotes around strings, formatted dates, etc.)

Returns

stringReturns a SQL INSERT statement

Examples

// Let's create an array for the example
// $arrayVariable["column name"] = formatted SQL value
$values["Name"] = MySQL::SQLValue("Violet");
$values["Age"] = MySQL::SQLValue(777, MySQL::SQLVALUE_NUMBER);
// Echo out the SQL statement
echo MySQL::BuildSQLInsert("MyTable", $values);

Builds a simple SQL SELECT statement

BuildSQLSelect(string $tableName, array | string $whereArray, array | string $columns, array | string $sortColumns, integer | string $limit) : string
API

note Any of the parameters $whereArray, $columns, $sortColumns or $limit may alternatively be a string, in which case these are used verbatim in the query. This is useful when advanced queries are constructed.

Parameters

$tableName

string

The name of the table

$whereArray

arraystring

(Optional) An associative array containing the column names as keys and values as data. The values must be SQL ready (i.e. quotes around strings, formatted dates, etc.)

$columns

arraystring

(Optional) The column or list of columns to select

$sortColumns

arraystring

(Optional) Column or list of columns to sort by. Column names may be prefixed by a plus(+) or minus(-) to indicate sort order. Default is ASCending for each column.

$limit

integerstring

(Optional) The limit of rows to return

Returns

stringReturns a SQL SELECT statement

Examples

// Let's create an array for the example
// $arrayVariable["column name"] = formatted SQL value
$values["Name"] = MySQL::SQLValue("Violet");
$values["Age"] = MySQL::SQLValue(777, MySQL::SQLVALUE_NUMBER);
// Echo out the SQL statement
echo MySQL::BuildSQLSelect("MyTable", $values);

Builds a SQL UPDATE statement

BuildSQLUpdate(string $tableName, array $valuesArray, array | string $whereArray) : string
API

Parameters

$tableName

string

The name of the table

$valuesArray

array

An associative array containing the column names as keys and values as data. The values must be SQL ready (i.e. quotes around strings, formatted dates, etc.)

$whereArray

arraystring

(Optional) An associative array containing the column names as keys and values as data. The values must be SQL ready (i.e. quotes around strings, formatted dates, etc.). If not specified then all values in the table are updated.
This parameter may alternatively be a string, in which case it is used verbatim for the WHERE clause of the query. This is useful when advanced queries are constructed.

Returns

stringReturns a SQL UPDATE statement

Examples

// Let's create two arrays for the example
// $arrayVariable["column name"] = formatted SQL value
$values["Name"] = MySQL::SQLValue("Violet");
$values["Age"] = MySQL::SQLValue(777, MySQL::SQLVALUE_NUMBER);
$filter["ID"] = MySQL::SQLValue(10, MySQL::SQLVALUE_NUMBER);
// Echo out some SQL statements
echo MySQL::BuildSQLUpdate("Test", $values, $filter)";

Construct a value string suitable for incorporation anywhere in a SQL query.

BuildSQLValue(\arbitrary $value) : string
STATICAPI

This methos invokes self::SQLValue() under the hood.

Parameters

$value

\arbitrary

The value to be checked and processed. Usually this would be a string, but any other type which can be cast to a string is fine as well.

Returns

stringReturns a string containing the SQL query ready value.

Builds a SQL WHERE clause from an array.

BuildSQLWhereClause(array | string $whereArray) : string
API

If a key is specified, the key is used at the field name and the value as a comparison. If a key is not used, the value is used as the clause.

Parameters

$whereArray

arraystring

An associative array containing the column names as keys and values as data. The values must be SQL ready (i.e. quotes around strings, formatted dates, etc.).
This parameter may alternatively be a string, in which case it is returned verbatim. This is useful when advanced queries are constructed and this method is invoked internally.

Returns

stringReturns a string containing the SQL WHERE clause

Close current MySQL connection

Close() : object
API

Returns

objectReturns TRUE on success or FALSE on error

Examples

$db->Close();

Creates a new database and sets up the root access for the database.

CreateDatabase(string $database, string $charset, string $collation, string $admin_user, string $admin_pass) : boolean
API

Parameters

$database

string

Database name

$charset

string

(Optional) Character set (i.e. utf8)

$collation

string

(Optional) Character set collation (i.e. utf8_unicode_ci)

$admin_user

string

(Optional) Database admin user name

$admin_pass

string

(Optional) Database admin password

Returns

booleanReturns TRUE on success or FALSE on error

Delete selected rows.

DeleteRows(string $tableName, array | string $whereArray) : boolean
API

Deletes rows in a table based on a WHERE filter (can be just one or many rows based on the filter).

Parameters

$tableName

string

The name of the table

$whereArray

arraystring

(Optional) An associative array containing the column names as keys and values as data. The values must be SQL ready (i.e. quotes around strings, formatted dates, etc.). If not specified then all values in the table are deleted.
This parameter may alternatively be a string, in which case it is used verbatim for the WHERE clause of the query. This is useful when advanced queries are constructed.

Returns

booleanReturns TRUE on success or FALSE on error

Examples

// $arrayVariable["column name"] = formatted SQL value
$filter["ID"] = 7;
// Execute the delete
$result = $db->DeleteRows("MyTable", $filter);
// If we have an error
if (!$result)
{
    // Show the error and kill the script
    $db->Kill();
}

Dump the entire database as an SQL script

Dump(string $tables, boolean $with_sql_comments, boolean $with_structure, boolean $with_data, boolean $with_drops_and_truncates, boolean $alter_database) : string
API

Produces a SQL script representing the dump of the entire database (when no (optional, comma-separated set of) tables has been specified as a method argument) or just the specified (comma separated set of) tables. You may choose to have either the database/table structure or the records dumped. Or both, for a full-fledged database/table dump which can serve as a db/table backup/restore script later on.

Parameters

$tables

string

[Optional] Comma separated list of tables. When none are specified, the entire database is assumed (this is the default).

$with_sql_comments

boolean

[Optional] Include SQL comments in the generated script (default: TRUE).

$with_structure

boolean

[Optional] Whether to include the table structure creation (and tear-down) SQL statements in the generated script (default: TRUE).

$with_data

boolean

[Optional] Whether to include the table rows (data) in the generated script (default: TRUE).

$with_drops_and_truncates

boolean

[Optional] Whether to include the apropriate DROP TABLE and/or TRUNCATE TABLE statements in the generated script (default: TRUE).

$alter_database

boolean

[Optional] Whether to include the apropriate ALTER DATABASE statement in the generated script to set the default database charset and collation (default: TRUE).

Returns

stringthe generated SQL script, boolean FALSE when a query error occurred.

Returns true if the internal pointer is at the end of the records

EndOfSeek() : boolean
API

Returns

booleanTRUE if at the last row or FALSE if not

Examples

if ($db->EndOfSeek())
{
    echo "We are at the end of the record set";
}

Return the last MySQL error as text

Error() : string
API

note The returned error description string is appended with the error number itself as ' (#error_number)'.

Returns

stringError text from last known error

Examples

if (!$db->Query("SELECT * FROM Table"))
{
    echo $db->Error();   // Shows the error
}
if ($db->Error()) $db->Kill();

Return the last MySQL error as a number

ErrorNumber() : integer
API

Returns

integerError number from last known error

Examples

if ($db->ErrorNumber() <> 0)
{
    $db->Kill();   // show the error message
}

Convert any value of any datatype into boolean (true or false)

GetBooleanValue(mixed $value) : boolean
STATICAPI

Parameters

$value

mixed

Value to analyze for TRUE or FALSE

Returns

booleanReturns TRUE or FALSE

Examples

echo (MySQL::GetBooleanValue("Y") ? "True" : "False");
echo (MySQL::GetBooleanValue("no") ? "True" : "False");
echo (MySQL::GetBooleanValue("TRUE") ? "True" : "False");
echo (MySQL::GetBooleanValue(1) ? "True" : "False");

Return the comments for fields in a table

GetColumnComments(string $table) : array
API

Parameters

$table

string

Table name

Returns

arrayAn array that contains the column comments or FALSE on error.

Examples

$columns = $db->GetColumnComments("MyTable");
foreach ($columns as $column => $comment)
{
    echo $column . " = " . $comment . "<br />\n";
}

Get the number of columns

GetColumnCount(string $table) : integer
API

Parameters

$table

string

(Optional) If a table name is not specified, the column count is returned from the last query

Returns

integerThe total count of columns or FALSE on error

Examples

echo "Total Columns: " . $db->GetColumnCount("MyTable");

Return the data type for a specified column

GetColumnDataType(\intege | string $column, string $table) : string
API

Parameters

$column

\integestring

Column name or number (first column is 0)

$table

string

(Optional) If a table name is not specified, the last returned records are used

Returns

stringThe MySQL data (field) type. If the column does not exist or no records exist, return FALSE.

Examples

echo "Type: " . $db->GetColumnDataType("FirstName", "Customer");

Return the position of a column

GetColumnID(string $column, string $table) : integer
API

Parameters

$column

string

Column name

$table

string

(Optional) If a table name is not specified, the last returned records are used.

Returns

integerColumn ID or FALSE on error.

Examples

echo "Column Position: " . $db->GetColumnID("FirstName", "Customer");

Return the field length

GetColumnLength(string $column, string $table) : integer
API

Parameters

$column

string

Column name

$table

string

(Optional) If a table name is not specified, the last returned records are used.

Returns

integerField length or FALSE on error.

Examples

echo "Length: " . $db->GetColumnLength("FirstName", "Customer");

Return the field name for a specified column number

GetColumnName(string $columnID, string $table) : string
API

Parameters

$columnID

string

Column position (0 is the first column)

$table

string

(Optional) If a table name is not specified, the last returned records are used.

Returns

stringThe field name for a specified column number. If the given column index number is invalid (does not exist) or no records exist, return FALSE.

Examples

echo "Column Name: " . $db->GetColumnName(0);

Return the field names in a table or query as an array

GetColumnNames(string $table) : array
API

Parameters

$table

string

(Optional) If a table name is not specified, the last returned records are used

Returns

arrayAn array that contains the column names or FALSE on error.

Examples

$columns = $db->GetColumnNames("MyTable");
foreach ($columns as $columnName)
{
    echo $columnName . "<br />\n";
}

Return the last query as a HTML table

GetHTML(boolean $showCount, string $styleTable, string $styleHeader, string $styleData) : string
API

Parameters

$showCount

boolean

(Optional) TRUE if you want to show the row count, FALSE if you do not want to show the count

$styleTable

string

(Optional) table tag attributes, e.g. styling

$styleHeader

string

(Optional) header row tag attributes

$styleData

string

(Optional) cell tag attributes

Returns

stringHTML containing a table with all records listed or FALSE on error

Examples

$db->Query("SELECT * FROM Customer");
echo $db->GetHTML();

Return the last query as a JSON document

GetJSON() : string
API

Returns

stringJSON containing all records listed

Return the last autonumber ID field from a previous INSERT query

GetLastInsertID() : integer
API

Returns

integerID number from previous INSERT query

Examples

$sql = "INSERT INTO Employee (Name) Values ('Bob')";
if (!$db->Query($sql))
{
    $db->Kill();
}
echo "Last ID inserted was: " . $db->GetLastInsertID();

Return the last SQL statement executed

GetLastSQL() : string
API

Returns

stringCurrent SQL query string

Examples

$sql = "INSERT INTO Employee (Name) Values ('Bob')";
if (!$db->Query($sql)) $db->Kill();
echo $db->GetLastSQL();

Return a few database statistics in an array.

GetStatistics() : array
API

Returns

arrayReturns an array of statistics values on success or FALSE on error.

Get all table names from the database

GetTables(string $filter) : array
API

Parameters

$filter

string

[Optional] Comma separated list of acceptable table names: no other table will be listed in the results. Alternatively, when no filter is specified, all tables are listed. This is the default behaviour of this method.

Returns

arrayAn array that contains the table names. If the database does not contain any tables, the returned value is FALSE.

Examples

$tables = $db->GetTables();
foreach ($tables as $table)
{
    echo $table . "<br />\n";
}

Return the last query as an XML Document

GetXML() : string
API

Returns

stringXML containing all records listed

Determines if a query contains any rows

HasRecords(string $sql) : boolean
API

Parameters

$sql

string

[Optional] If specified, the query is first executed Otherwise, the last query is used for comparison

Returns

booleanTRUE if records exist, FALSE if not or query error

Inserts a row into a table in the connected database

InsertRow(string $tableName, array $valuesArray) : integer
API

Parameters

$tableName

string

The name of the table

$valuesArray

array

An associative array containing the column names as keys and values as data. The values must be SQL ready (i.e. quotes around strings, formatted dates, etc.)

Returns

integerReturns last insert ID on success or FALSE on failure

Examples

// $arrayVariable["column name"] = formatted SQL value
$values["Name"] = MySQL::SQLValue("Violet");
$values["Age"]  = MySQL::SQLValue(777, MySQL::SQLVALUE_NUMBER);
// Execute the insert
$result = $db->InsertRow("MyTable", $values);
// If we have an error
if (!$result)
{
    // Show the error and kill the script
    $db->Kill();
}
else
{
    // No error, show the new record's ID
    echo "The new record's ID is: " . $result;
}

Determines if a valid connection to the database exists

IsConnected() : boolean
API

Returns

booleanTRUE idf connectect or FALSE if not connected

Determines if a value of any data type is a date PHP can convert

IsDateStr(string $value) : boolean
STATICAPI

Parameters

$value

string

Returns

booleanReturns TRUE if value is date or FALSE if not date

Examples

if (MySQL::IsDate("January 1, 2000"))
{
    echo "valid date";
}

Stop executing (die/exit) and show last MySQL error message

Kill(string $message, boolean $prepend_message) 
API

Parameters

$message

string

The message to display on exit

$prepend_message

boolean

(Optional) Whether the message should be shown as-is (FALSE) or followed by the last error message/description (TRUE) (Default: TRUE)

Examples

// Stop executing the script and show the last error
$db->Kill();

Seeks to the beginning of the records

MoveFirst() : boolean
API

Returns

booleanReturns TRUE on success or FALSE on error

Examples

$db->MoveFirst();
while (!$db->EndOfSeek())
{
    $row = $db->Row();
    echo $row->ColumnName1 . " " . $row->ColumnName2 . "\n";
}

Seeks to the end of the records

MoveLast() : boolean
API

Returns

booleanReturns TRUE on success or FALSE on error

Examples

$db->MoveLast();

Get last error message as HTML string

MyDyingMessage(string $message, boolean $prepend_message) : string
API

Return the error message ready for throwing back out to the client side while dying, a.k.a. Kill() without the death nor the echo'ing.

Parameters

$message

string

The message to display on exit

$prepend_message

boolean

(Optional) Whether the message should be shown as-is (FALSE) or followed by the last error message/description (TRUE) (Default: TRUE)

Returns

stringReturn the error message as a HTML string; when the $this->InDevelopmentEnvironment configuration member has been set, the offending query is included for enhanced diagnostics.

Connect to specified MySQL server

Open(string $database, string $server, string $username, string $password, string $charset, string $collation, boolean $pcon) : boolean
API

Parameters

$database

string

(Optional) Database name

$server

string

(Optional) Host address

$username

string

(Optional) User name

$password

string

(Optional) Password

$charset

string

(Optional) Character set

$collation

string

(Optional) Character set collation

$pcon

boolean

(Optional) Persistant connection

Returns

booleanReturns TRUE on success or FALSE on error

Examples

if (!$db->Open("MyDatabase", "localhost", "user", "password"))
{
    $db->Kill();
}

Executes the given SQL query and returns the records

Query(string $sql) : object
API

Parameters

$sql

string

The query string should not end with a semicolon

Returns

objectPHP 'mysql result' resource object containing the records on SELECT, SHOW, DESCRIBE or EXPLAIN queries and returns; TRUE or FALSE for all others i.e. UPDATE, DELETE, DROP AND FALSE on all errors (setting the local Error message).

Examples

if (!$db->Query("SELECT * FROM Table")) echo $db->Kill();

Executes the given SQL query and returns a multi-dimensional array

QueryArray(string $sql, integer $resultType) : array
API

Parameters

$sql

string

The query string should not end with a semicolon

$resultType

integer

(Optional) The type of array Values can be: MYSQL_ASSOC, MYSQL_NUM, MYSQL_BOTH

Returns

arrayA multi-dimensional array containing all the data returned from the query or FALSE on all errors

Executes the given SQL query

QueryObjects(string $sql) : array
API

Executes the given SQL query and returns an array of objects, where each record is an object with the columns serving as object member variables.

Parameters

$sql

string

The query string should not end with a semicolon

Returns

arrayAn array of record objects containing all the data returned from the query or FALSE on all errors

Executes the given SQL query and returns only one (the first) row

QuerySingleRow(string $sql) : object
API

Parameters

$sql

string

The query string should not end with a semicolon

Returns

objectPHP resource object containing the first row or FALSE if no row is returned from the query

Executes the given SQL query and returns the first row as an array

QuerySingleRowArray(string $sql, integer $resultType) : array
API

Parameters

$sql

string

The query string should not end with a semicolon

$resultType

integer

(Optional) The type of array Values can be: MYSQL_ASSOC, MYSQL_NUM, MYSQL_BOTH

Returns

arrayAn array containing the first row or FALSE if no row is returned from the query

Executes a query and returns a single value.

QuerySingleValue(string $sql) : mixed
API

If more than one row is returned, only the first value in the first column is returned.

Parameters

$sql

string

The query string should not end with a semicolon

Returns

mixedThe value returned or FALSE if no value

Executes the given SQL query, measures it, and saves the total duration in microseconds

QueryTimed(string $sql) : object
API

Parameters

$sql

string

The query string should not end with a semicolon

Returns

objectPHP 'mysql result' resource object containing the records on SELECT, SHOW, DESCRIBE or EXPLAIN queries and returns TRUE or FALSE for all others i.e. UPDATE, DELETE, DROP.

Examples

$db->QueryTimed("SELECT * FROM MyTable");
echo "Query took " . $db->TimerDuration() . " microseconds";

Returns the records from the last query

Records() : object
API

Returns

objectPHP 'mysql result' resource object containing the records for the last query executed

Examples

$records = $db->Records();

Returns all records from the last query as array of arrays

RecordsArray(integer $resultType) : array
API

Returns all records from the last query and returns the contents as an array of records where each record is presented as an array of columns (fields).

Parameters

$resultType

integer

(Optional) The type of array representing one record Values can be: MYSQL_ASSOC, MYSQL_NUM, MYSQL_BOTH. (Default: MYSQL_ASSOC)

Returns

arrayRecords in array form or FALSE on error. May return an EMPTY array when no records are available.

Examples

$myArray = $db->RecordsArray(MYSQL_ASSOC);

Returns all records from the last query as array of objects

RecordsObjects() : array
API

Returns all records from the last query and returns the contents as an array of record objects (where each record is an object with each column as an attribute (data member variable)).

Returns

arrayRecords in object form or FALSE on error. May return an EMPTY array when no records are available.

Frees memory used by the query results and returns the query execution result.

Release(resource $result) : boolean
API

warning It is an (non-fatal) error to Release() a query result more than once.

Parameters

$result

resource

(Optional) the result originally returned by any previous SQL query.

Returns

booleanReturns TRUE on success or FALSE on failure

Examples

$db->Release();

Reads the current row and returns contents as a PHP object

Row(integer $optional_row_number) : object
API

Parameters

$optional_row_number

integer

(Optional) Use to specify a row

Returns

objectPHP object or FALSE on error

Examples

$db->MoveFirst();
while (!$db->EndOfSeek())
{
    $row = $db->Row();
    echo $row->ColumnName1 . " " . $row->ColumnName2 . "\n";
}

Reads the current row and returns contents as an array

RowArray(integer $optional_row_number, integer $resultType) : array
API

Parameters

$optional_row_number

integer

(Optional) Use to specify a row

$resultType

integer

(Optional) The type of array Values can be: MYSQL_ASSOC, MYSQL_NUM, MYSQL_BOTH

Returns

arrayArray that corresponds to the fetched row or FALSE on error or when no rows are available.

Examples

for ($index = 0; $index < $db->RowCount(); $index++)
{
    $val = $db->RowArray($index);
}

Returns the last query row count

RowCount() : integer
API

Returns

integerRow count or FALSE on error

Examples

$db->Query("SELECT * FROM Customer");
echo "Row Count: " . $db->RowCount();

Converts a boolean into a formatted TRUE or FALSE value of choice

SQLBooleanValue(mixed $value, mixed $trueValue, mixed $falseValue, string $datatype) : string
STATICAPI

Parameters

$value

mixed

value to analyze for TRUE or FALSE

$trueValue

mixed

value to use if TRUE

$falseValue

mixed

value to use if FALSE

$datatype

string

Use SQLVALUE constants or the strings: string, text, varchar, char, boolean, bool, Y-N, T-F, bit, date, datetime, time, integer, int, number, double, float

Returns

stringSQL formatted value of the specified data type on success or FALSE on error

Examples

echo MySQL::SQLBooleanValue(false, "1", "0", MySQL::SQLVALUE_NUMBER);
echo MySQL::SQLBooleanValue($test, "Jan 1, 2007 ", "2007/06/01", MySQL::SQLVALUE_DATE);
echo MySQL::SQLBooleanValue("ON", "Ya", "Nope");
echo MySQL::SQLBooleanValue(1, '+', '-');

Returns string suitable for inclusion in a SQL query

SQLFix(string $value) : string
STATICAPI

The returned string representing the $value will be properly escaped and filtered to use as part of a constructed SQL query.

Parameters

$value

string

Returns

stringSQL formatted value

Examples

$value = MySQL::SQLFix("\hello\ /world/");
echo $value . "\n" . MySQL::SQLUnfix($value);

Returns MySQL string as normal string

SQLUnfix(string $value) : string
STATICAPI

warning Do NOT use on columns returned by a database query: such data has already been adequately processed by MySQL itself. The only probable place where the SQLUnfix() method MAY be useful is when DIRECTLY accessing strings produced by the SQLValue() method.

Parameters

$value

string

Returns

string

Examples

$value = MySQL::SQLFix("\hello\ /world/");
echo $value . "\n" . MySQL::SQLUnfix($value);

Formats any value into a string suitable for SQL statements

SQLValue(mixed $value, string $datatype) : string
STATICAPI

note Also supports data types returned from the gettype function.

Parameters

$value

mixed

Any value of any type to be formatted to SQL

$datatype

string

Use SQLVALUE constants or the strings: 'string', 'text', 'varchar', 'char', 'boolean', 'bool', 'Y-N', 'T-F', 'bit', 'date', 'datetime', 'time', 'integer', 'int', 'number', 'double', 'float'

Returns

stringThe properly quoted and escaped/filtered value as a string which can be safely included in a generated SQL query.

Examples

echo MySQL::SQLValue("it's a string", "text");
$sql = "SELECT * FROM Table WHERE Field1 = " . MySQL::SQLValue("123", MySQL::SQLVALUE_NUMBER);
$sql = "UPDATE Table SET Field1 = " . MySQL::SQLValue("July 4, 2007", MySQL::SQLVALUE_DATE);

Sets the internal database pointer to the specified row number and returns the result

Seek(integer $row_number) : object
API

Parameters

$row_number

integer

Row number

Returns

objectFetched row as PHP object on success or FALSE on error

Examples

$db->Seek(0);   // Move to the first record

Returns the current cursor row location

SeekPosition() : integer
API

Returns

integerCurrent row number

Examples

echo "Current Row Cursor : " . $db->GetSeekPosition();

Returns a multidimensional array of rows from a table based on a WHERE filter

SelectArray(string $tableName, array | string $whereArray, array | string $columns, array | string $sortColumns, integer | string $limit, integer $resultType) : array
API

note Any of the parameters $whereArray, $columns, $sortColumns or $limit may alternatively be a string, in which case these are used verbatim in the query. This is useful when advanced queries are constructed.

Parameters

$tableName

string

The name of the table

$whereArray

arraystring

(Optional) An associative array containing the column names as keys and values as data. The values must be SQL ready (i.e. quotes around strings, formatted dates, etc.)

$columns

arraystring

(Optional) The column or list of columns to select

$sortColumns

arraystring

(Optional) Column or list of columns to sort by. Column names may be prefixed by a plus(+) or minus(-) to indicate sort order. Default is ASCending for each column.

$limit

integerstring

(Optional) The limit of rows to return

$resultType

integer

(Optional) The type of array Values can be: MYSQL_ASSOC, MYSQL_NUM, MYSQL_BOTH

Returns

arrayA multi-dimensional array containing all the data returned from the query or FALSE on all errors

Selects a different database and character set

SelectDatabase(string $database, string $charset) : boolean
API

Parameters

$database

string

Database name

$charset

string

(Optional) Character set, e.g. 'utf8'. (Default: NULL)

Returns

booleanReturns TRUE on success or FALSE on error

Examples

$db->SelectDatabase("DatabaseName");

Returns an array of row (= record) objects from a table based on a WHERE filter

SelectObjects(string $tableName, array | string $whereArray, array | string $columns, array | string $sortColumns, integer | string $limit) : array
API

note Any of the parameters $whereArray, $columns, $sortColumns or $limit may alternatively be a string, in which case these are used verbatim in the query. This is useful when advanced queries are constructed.

Parameters

$tableName

string

The name of the table

$whereArray

arraystring

(Optional) An associative array containing the column names as keys and values as data. The values must be SQL ready (i.e. quotes around strings, formatted dates, etc.)

$columns

arraystring

(Optional) The column or list of columns to select

$sortColumns

arraystring

(Optional) Column or list of columns to sort by. Column names may be prefixed by a plus(+) or minus(-) to indicate sort order. Default is ASCending for each column.

$limit

integerstring

(Optional) The limit of rows to return

Returns

arrayAn array of record objects containing all the data returned from the query or FALSE on all errors

Gets rows in a table based on a WHERE filter

SelectRows(string $tableName, array | string $whereArray, array | string $columns, array | string $sortColumns, integer | string $limit) : boolean
API

note Any of the parameters $whereArray, $columns, $sortColumns or $limit may alternatively be a string, in which case these are used verbatim in the query. This is useful when advanced queries are constructed.

Parameters

$tableName

string

The name of the table

$whereArray

arraystring

(Optional) An associative array containing the column names as keys and values as data. The values must be SQL ready (i.e. quotes around strings, formatted dates, etc.)

$columns

arraystring

(Optional) The column or list of columns to select

$sortColumns

arraystring

(Optional) Column or list of columns to sort by. Column names may be prefixed by a plus(+) or minus(-) to indicate sort order. Default is ASCending for each column.

$limit

integerstring

(Optional) The limit of rows to return

Returns

booleanReturns records on success or FALSE on error

Examples

// $arrayVariable["column name"] = formatted SQL value
$filter["Color"] = MySQL::SQLValue("Violet");
$filter["Age"]   = MySQL::SQLValue(777, MySQL::SQLVALUE_NUMBER);
// Execute the select
$result = $db->SelectRows("MyTable", $filter);
// If we have an error
if (!$result)
{
    // Show the error and kill the script
    $db->Kill();
}

Returns a single (first) row from a table based on a WHERE filter

SelectSingleRow(string $tableName, array | string $whereArray, array | string $columns, array | string $sortColumns, integer | string $limit) : object
API

note Any of the parameters $whereArray, $columns, $sortColumns or $limit may alternatively be a string, in which case these are used verbatim in the query. This is useful when advanced queries are constructed.

Parameters

$tableName

string

The name of the table

$whereArray

arraystring

(Optional) An associative array containing the column names as keys and values as data. The values must be SQL ready (i.e. quotes around strings, formatted dates, etc.)

$columns

arraystring

(Optional) The column or list of columns to select

$sortColumns

arraystring

(Optional) Column or list of columns to sort by. Column names may be prefixed by a plus(+) or minus(-) to indicate sort order. Default is ASCending for each column.

$limit

integerstring

(Optional) The limit of rows to return

Returns

objectPHP resource object containing the first row or FALSE if no row is returned from the query

Returns a single (first) row as an array from a table based on a WHERE filter

SelectSingleRowArray(string $tableName, array | string $whereArray, array | string $columns, array | string $sortColumns, integer | string $limit, integer $resultType) : array
API

note Any of the parameters $whereArray, $columns, $sortColumns or $limit may alternatively be a string, in which case these are used verbatim in the query. This is useful when advanced queries are constructed.

Parameters

$tableName

string

The name of the table

$whereArray

arraystring

(Optional) An associative array containing the column names as keys and values as data. The values must be SQL ready (i.e. quotes around strings, formatted dates, etc.)

$columns

arraystring

(Optional) The column or list of columns to select

$sortColumns

arraystring

(Optional) Column or list of columns to sort by. Column names may be prefixed by a plus(+) or minus(-) to indicate sort order. Default is ASCending for each column.

$limit

integerstring

(Optional) The limit of rows to return

$resultType

integer

(Optional) The type of array Values can be: MYSQL_ASSOC, MYSQL_NUM, MYSQL_BOTH

Returns

arrayAn array containing the first row or FALSE if no row is returned from the query

Returns a single value from from the first row SELECTed from a table based on a WHERE filter.

SelectSingleValue(string $tableName, array | string $whereArray, array | string $columns, array | string $sortColumns, integer | string $limit) : mixed
API

note Any of the parameters $whereArray, $columns, $sortColumns or $limit may alternatively be a string, in which case these are used verbatim in the query. This is useful when advanced queries are constructed.

Parameters

$tableName

string

The name of the table

$whereArray

arraystring

(Optional) An associative array containing the column names as keys and values as data. The values must be SQL ready (i.e. quotes around strings, formatted dates, etc.)

$columns

arraystring

(Optional) The column or list of columns to select

$sortColumns

arraystring

(Optional) Column or list of columns to sort by. Column names may be prefixed by a plus(+) or minus(-) to indicate sort order. Default is ASCending for each column.

$limit

integerstring

(Optional) The limit of rows to return

Returns

mixedThe value returned or FALSE if no value

Retrieves all rows in a specified table

SelectTable(string $tableName) : boolean
API

Parameters

$tableName

string

The name of the table

Returns

booleanReturns an array of records (each an object where the columns are individual object member variables) on success or FALSE on error

Returns last measured duration (time between TimerStart and TimerStop)

TimerDuration(integer $decimals) : Float
API

Parameters

$decimals

integer

(Optional) The number of decimal places to show (Default: 4)

Returns

FloatMicroseconds elapsed

Examples

$db->TimerStart();
// Do something or run some queries
$db->TimerStop();
echo $db->TimerDuration(2) . " microseconds";

Starts time measurement (in microseconds)

TimerStart() 
API

Examples

$db->TimerStart();
// Do something or run some queries
$db->TimerStop();
echo $db->TimerDuration() . " microseconds";

Stops time measurement (in microseconds)

TimerStop() 
API

Examples

$db->TimerStart();
// Do something or run some queries
$db->TimerStop();
echo $db->TimerDuration() . " microseconds";

Starts a transaction

TransactionBegin() : boolean
API

Returns

booleanReturns TRUE on success or FALSE on error

Examples

$sql = "INSERT INTO MyTable (Field1, Field2) Values ('abc', 123)";
$db->TransactionBegin();
if ($db->Query($sql))
{
    $db->TransactionEnd();
    echo "Last ID inserted was: " . $db->GetLastInsertID();
}
else
{
    $db->TransactionRollback();
    echo "Query Failed";
}

Ends a transaction and commits the queries

TransactionEnd() : boolean
API

Returns

booleanReturns TRUE on success or FALSE on error

Examples

$sql = "INSERT INTO MyTable (Field1, Field2) Values ('abc', 123)";
$db->TransactionBegin();
if ($db->Query($sql))
{
    $db->TransactionEnd();
    echo "Last ID inserted was: " . $db->GetLastInsertID();
}
else
{
    $db->TransactionRollback();
    echo "Query Failed";
}

Rolls the transaction back

TransactionRollback() : boolean
API

Returns

booleanReturns TRUE on success or FALSE on failure

Examples

$sql = "INSERT INTO MyTable (Field1, Field2) Values ('abc', 123)";
$db->TransactionBegin();
if ($db->Query($sql))
{
    $db->TransactionEnd();
    echo "Last ID inserted was: " . $db->GetLastInsertID();
}
else
{
    $db->TransactionRollback();
    echo "Query Failed";
}

Truncates a table removing all data

TruncateTable(string $tableName) : boolean
API

Parameters

$tableName

string

The name of the table

Returns

booleanReturns TRUE on success or FALSE on error

Update selected rows

UpdateRow(string $tableName, array $valuesArray, array | string $whereArray) : boolean
API

Updates rows in a table based on a WHERE filter (can be just one or many rows based on the filter).

Parameters

$tableName

string

The name of the table

$valuesArray

array

An associative array containing the column names as keys and values as data. The values must be SQL ready (i.e. quotes around strings, formatted dates, etc.)

$whereArray

arraystring

(Optional) An associative array containing the column names as keys and values as data. The values must be SQL ready (i.e. quotes around strings, formatted dates, etc.). If not specified then all values in the table are updated.
This parameter may alternatively be a string, in which case it is used verbatim for the WHERE clause of the query. This is useful when advanced queries are constructed.

Returns

booleanReturns TRUE on success or FALSE on error

Examples

// Create an array that holds the update information
// $arrayVariable["column name"] = formatted SQL value
$update["Name"] = MySQL::SQLValue("Bob");
$update["Age"]  = MySQL::SQLValue(25, MySQL::SQLVALUE_NUMBER);
// Execute the update where the ID is 1
if (!$db->UpdateRows("test", $values, array("id" => 1))) $db->Kill();

Constructor: Opens the connection to the database

__construct(boolean $connect, string $database, string $server, string $username, string $password, string $charset, string $collation) 

Parameters

$connect

boolean

(Optional) Auto-connect when object is created

$database

string

(Optional) Database name

$server

string

(Optional) Host address

$username

string

(Optional) User name

$password

string

(Optional) Password

$charset

string

(Optional) Character set

$collation

string

(Optional) Character set collation

Examples

$db = new MySQL();
$db = new MySQL(true, "database");
$db = new MySQL(true, "database", "localhost", "username", "password");

Destructor: Closes the connection to the database

__destruct() 

Builds a comma delimited list of columns for use with a SQL query

BuildSQLColumns(array | string $columns, boolean $addQuotes, boolean $showAlias, boolean $withSortMarker) : string

This method can be used to construct a SELECT, FROM or SORT BY section of an SQL query.

Parameters

$columns

arraystring

Either an array containing the column names or a string. The latter is used when, for example, constructing 'advanced' queries with SUM(*) or other expressions in the SELECT fieldset section.

$addQuotes

boolean

(Optional) TRUE to add quotes

$showAlias

boolean

(Optional) TRUE to show column alias

$withSortMarker

boolean

(Optional) TRUE when the field list is meant for an ORDER BY clause; fields may be prefixed by a plus(+) or minus(-) to indicate sort order. Default is ASCending for each field.

Returns

stringReturns the constructed SQL column list on success or NULL on failure

Clears the internal variables from any error information

ResetError() 
API

Sets the local variables with the first error information

SetError(string $errorMessage, integer $errorNumber) 
API

Parameters

$errorMessage

string

The error description

$errorNumber

integer

The error number

 Properties

 

Provide minimal or extended error information

$InDevelopmentEnvironment : boolean
API

Determines if the code is running in a development or production environment: error diagnostics information is far more elaborate in a development environment setting to aid problem analysis and resolution.

api
 

Determines if an error throws an exception

$ThrowExceptions : boolean
API

api
 

current row

$active_row 

 

optional character set (i.e.

$db_charset 

utf8)

 

optional character set collation (i.e.

$db_charsetcollation 

utf8_unicode_ci)

 

database name

$db_dbname 

 

server name

$db_host 

 

password

$db_pass 

 

use persistent connection?

$db_pcon 

 

user name

$db_user 

 

last mysql error string

$error_desc 

 

last mysql error number

$error_number 

 

used for transactions

$in_transaction 

 

last id of record inserted

$last_insert_id 

 

last mysql query result

$last_result 

 

last mysql query

$last_sql 

   

tracks the number of queries executed through this instance

$query_count 

 

holds the difference in time

$time_diff 

 

start time for the timer

$time_start 

 Constants

 

SQLVALUE_BIT

SQLVALUE_BIT 

 

SQLVALUE_BOOLEAN

SQLVALUE_BOOLEAN 

 

SQLVALUE_DATE

SQLVALUE_DATE 

 

SQLVALUE_DATETIME

SQLVALUE_DATETIME 

 

SQLVALUE_ENUMERATE

SQLVALUE_ENUMERATE 

 

SQLVALUE_NUMBER

SQLVALUE_NUMBER 

 

SQLVALUE_TEXT

SQLVALUE_TEXT 

 

SQLVALUE_TIME

SQLVALUE_TIME 

 

SQLVALUE_T_F

SQLVALUE_T_F 

 

SQLVALUE_Y_N

SQLVALUE_Y_N