PHP Class MySQL

Inheritance: implements DB
Show file Open project: kimai/kimai Class Usage Examples

Public Properties

Property Type Description
$ThrowExceptions Determines if an error throws an exception
$mysql_link class-internal variables - do not change

Public Methods

Method Description
AutoInsertUpdate ( string $tableName, array $valuesArray, array $whereArray ) : boolean Automatically does an INSERT or UPDATE depending if an existing record exists in a table
BeginningOfSeek ( ) : boolean Returns true if the internal pointer is at the beginning of the records
BuildSQLDelete ( string $tableName, array $whereArray = null ) : string [STATIC] Builds a SQL DELETE statement
BuildSQLInsert ( string $tableName, array $valuesArray ) : string [STATIC] Builds a SQL INSERT statement
BuildSQLSelect ( string $tableName, array $whereArray = null, array/string $columns = null, array/string $sortColumns = null, boolean $sortAscending = true, integer/string $limit = null ) : string Builds a simple SQL SELECT statement
BuildSQLUpdate ( string $tableName, array $valuesArray, array $whereArray = null ) : string [STATIC] Builds a SQL UPDATE statement
BuildSQLWhereClause ( array $whereArray ) : string [STATIC] Builds a SQL WHERE clause from an array.
Close ( ) : object Close current MySQL connection
DeleteRows ( string $tableName, array $whereArray = null ) : boolean Deletes rows in a table based on a WHERE filter (can be just one or many rows based on the filter)
EndOfSeek ( ) : boolean Returns true if the internal pointer is at the end of the records
Error ( ) : string Returns the last MySQL error as text
ErrorNumber ( ) : integer Returns the last MySQL error as a number
GetBooleanValue ( mixed $value ) : boolean [STATIC] Converts any value of any datatype into boolean (true or false)
GetColumnComments ( string $table ) : array Returns the comments for fields in a table into an array or NULL if the table has not got any fields
GetColumnCount ( string $table = "" ) : integer This function returns the number of columns or returns FALSE on error
GetColumnDataType ( string $column, string $table = "" ) : string This function returns the data type for a specified column. If the column does not exists or no records exist, it returns FALSE
GetColumnID ( string $column, string $table = "" ) : integer This function returns the position of a column
GetColumnLength ( string $column, string $table = "" ) : integer This function returns the field length or returns FALSE on error
GetColumnName ( string $columnID, string $table = "" ) : integer This function returns the name for a specified column number. If the index does not exists or no records exist, it returns FALSE
GetColumnNames ( string $table = "" ) : array Returns the field names in a table or query in an array
GetHTML ( boolean $showCount = true, string $styleTable = null, string $styleHeader = null, string $styleData = null ) : string This function returns the last query as an HTML table
GetJSON ( ) : string Returns the last query as a JSON document
GetLastInsertID ( ) : integer Returns the last autonumber ID field from a previous INSERT query
GetLastSQL ( ) : string Returns the last SQL statement executed
GetTables ( ) : array This function returns table names from the database into an array. If the database does not contains any tables, the returned value is FALSE
GetXML ( ) : string Returns the last query as an XML Document
HasRecords ( string $sql = "" ) : boolean Determines if a query contains any rows
InsertRow ( string $tableName, array $valuesArray ) : integer Inserts a row into a table in the connected database
IsConnected ( ) : boolean Determines if a valid connection to the database exists
IsDate ( date/string $value ) : boolean [STATIC] Determines if a value of any data type is a date PHP can convert
Kill ( $message = "" ) Stop executing (die/exit) and show last MySQL error message
MoveFirst ( ) : boolean Seeks to the beginning of the records
MoveLast ( ) : boolean Seeks to the end of the records
Open ( string $database = null, string $server = null, string $username = null, string $password = null, string $charset = null, boolean $pcon = false ) : boolean Connect to specified MySQL server
Query ( string $sql ) : object Executes the given SQL query and returns the records
QueryArray ( string $sql, integer $resultType = MYSQLI_BOTH ) : array Executes the given SQL query and returns a multi-dimensional array
QuerySingleRow ( string $sql ) : object Executes the given SQL query and returns only one (the first) row
QuerySingleRowArray ( string $sql, integer $resultType = MYSQLI_BOTH ) : array Executes the given SQL query and returns the first row as an array
QuerySingleValue ( string $sql ) : mixed Executes a query and returns a single value. If more than one row is returned, only the first value in the first column is returned.
QueryTimed ( string $sql ) : object Executes the given SQL query, measures it, and saves the total duration in microseconds
Records ( ) : object Returns the records from the last query
RecordsArray ( integer $resultType = MYSQLI_BOTH ) : Records Returns all records from last query and returns contents as array or FALSE on error
Release ( ) : boolean Frees memory used by the query results and returns the function result
Row ( integer $optional_row_number = null ) : object Reads the current row and returns contents as a PHP object or returns false on error
RowArray ( integer $optional_row_number = null, integer $resultType = MYSQLI_BOTH ) : array Reads the current row and returns contents as an array or returns false on error
RowCount ( ) : integer Returns the last query row count
SQLBooleanValue ( mixed $value, mixed $trueValue, mixed $falseValue, string $datatype = self::SQLVALUE_TEXT ) : string [STATIC] Converts a boolean into a formatted TRUE or FALSE value of choice
SQLFix ( string $value ) : string Returns string suitable for SQL NOTE: This is no longer a static call because of a conflict with mysqli_real_escape_string.
SQLValue ( mixed $value, string $datatype = self::SQLVALUE_TEXT ) : string [STATIC] Formats any value into a string suitable for SQL statements (NOTE: Also supports data types returned from the gettype function)
Seek ( integer $row_number ) : object Sets the internal database pointer to the specified row number and returns the result
SeekPosition ( ) : integer Returns the current cursor row location
SelectDatabase ( string $database, string $charset = "" ) : boolean Selects a different database and character set
SelectRows ( string $tableName, array $whereArray = null, array/string $columns = null, array/string $sortColumns = null, boolean $sortAscending = true, integer/string $limit = null ) : boolean Gets rows in a table based on a WHERE filter
SelectTable ( string $tableName ) : boolean Retrieves all rows in a specified table
TimerDuration ( integer $decimals = 4 ) : Float Returns last measured duration (time between TimerStart and TimerStop)
TimerStart ( ) Starts time measurement (in microseconds)
TimerStop ( ) Stops time measurement (in microseconds)
TransactionBegin ( ) : boolean Starts a transaction
TransactionEnd ( ) : boolean Ends a transaction and commits the queries
TransactionRollback ( ) : boolean Rolls the transaction back
TruncateTable ( string $tableName ) : boolean Truncates a table removing all data
UpdateRows ( string $tableName, array $valuesArray, array $whereArray = null ) : boolean Updates rows in a table based on a WHERE filter (can be just one or many rows based on the filter)
__construct ( boolean $connect = true, string $database = null, string $server = null, string $username = null, string $password = null, string $charset = null ) Constructor: Opens the connection to the database
__destruct ( ) Destructor: Closes the connection to the database

Private Methods

Method Description
BuildSQLColumns ( $columns, boolean $addQuotes = true, boolean $showAlias = true ) : string [STATIC] Builds a comma delimited list of columns for use with SQL
ResetError ( ) Clears the internal variables from any error information
SetError ( string $errorMessage = "", integer $errorNumber ) Sets the local variables with the last error information

Method Details

AutoInsertUpdate() public method

Automatically does an INSERT or UPDATE depending if an existing record exists in a table
public AutoInsertUpdate ( string $tableName, array $valuesArray, array $whereArray ) : boolean
$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, ect)
$whereArray 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, ect).
return boolean Returns TRUE on success or FALSE on error

BeginningOfSeek() public method

Returns true if the internal pointer is at the beginning of the records
public BeginningOfSeek ( ) : boolean
return boolean TRUE if at the first row or FALSE if not

BuildSQLDelete() public static method

[STATIC] Builds a SQL DELETE statement
public static BuildSQLDelete ( string $tableName, array $whereArray = null ) : string
$tableName string The name of the table
$whereArray array (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, ect). If not specified then all values in the table are deleted.
return string Returns the SQL DELETE statement

BuildSQLInsert() public static method

[STATIC] Builds a SQL INSERT statement
public static BuildSQLInsert ( string $tableName, array $valuesArray ) : string
$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, ect)
return string Returns a SQL INSERT statement

BuildSQLSelect() public static method

Builds a simple SQL SELECT statement
public static BuildSQLSelect ( string $tableName, array $whereArray = null, array/string $columns = null, array/string $sortColumns = null, boolean $sortAscending = true, integer/string $limit = null ) : string
$tableName string The name of the table
$whereArray array (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, ect)
$columns array/string
$sortColumns array/string
$sortAscending boolean (Optional) TRUE for ascending; FALSE for descending This only works if $sortColumns are specified
$limit integer/string
return string Returns a SQL SELECT statement

BuildSQLUpdate() public static method

[STATIC] Builds a SQL UPDATE statement
public static BuildSQLUpdate ( string $tableName, array $valuesArray, array $whereArray = null ) : string
$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, ect)
$whereArray array (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, ect). If not specified then all values in the table are updated.
return string Returns a SQL UPDATE statement

BuildSQLWhereClause() public static method

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.
public static BuildSQLWhereClause ( array $whereArray ) : string
$whereArray 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, ect)
return string Returns a string containing the SQL WHERE clause

Close() public method

Close current MySQL connection
public Close ( ) : object
return object Returns TRUE on success or FALSE on error

DeleteRows() public method

Deletes rows in a table based on a WHERE filter (can be just one or many rows based on the filter)
public DeleteRows ( string $tableName, array $whereArray = null ) : boolean
$tableName string The name of the table
$whereArray array (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, ect). If not specified then all values in the table are deleted.
return boolean Returns TRUE on success or FALSE on error

EndOfSeek() public method

Returns true if the internal pointer is at the end of the records
public EndOfSeek ( ) : boolean
return boolean TRUE if at the last row or FALSE if not

Error() public method

Returns the last MySQL error as text
public Error ( ) : string
return string Error text from last known error

ErrorNumber() public method

Returns the last MySQL error as a number
public ErrorNumber ( ) : integer
return integer Error number from last known error

GetBooleanValue() public static method

[STATIC] Converts any value of any datatype into boolean (true or false)
public static GetBooleanValue ( mixed $value ) : boolean
$value mixed Value to analyze for TRUE or FALSE
return boolean Returns TRUE or FALSE

GetColumnComments() public method

Returns the comments for fields in a table into an array or NULL if the table has not got any fields
public GetColumnComments ( string $table ) : array
$table string Table name
return array An array that contains the column comments

GetColumnCount() public method

This function returns the number of columns or returns FALSE on error
public GetColumnCount ( string $table = "" ) : integer
$table string (Optional) If a table name is not specified, the column count is returned from the last query
return integer The total count of columns

GetColumnDataType() public method

This function returns the data type for a specified column. If the column does not exists or no records exist, it returns FALSE
public GetColumnDataType ( string $column, string $table = "" ) : string
$column string Column name or number (first column is 0)
$table string (Optional) If a table name is not specified, the last returned records are used
return string MySQL data (field) type

GetColumnID() public method

This function returns the position of a column
public GetColumnID ( string $column, string $table = "" ) : integer
$column string Column name
$table string (Optional) If a table name is not specified, the last returned records are used.
return integer Column ID

GetColumnLength() public method

This function returns the field length or returns FALSE on error
public GetColumnLength ( string $column, string $table = "" ) : integer
$column string Column name
$table string (Optional) If a table name is not specified, the last returned records are used.
return integer Field length

GetColumnName() public method

This function returns the name for a specified column number. If the index does not exists or no records exist, it returns FALSE
public GetColumnName ( string $columnID, string $table = "" ) : integer
$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.
return integer Field Length

GetColumnNames() public method

Returns the field names in a table or query in an array
public GetColumnNames ( string $table = "" ) : array
$table string (Optional) If a table name is not specified, the last returned records are used
return array An array that contains the column names

GetHTML() public method

This function returns the last query as an HTML table
public GetHTML ( boolean $showCount = true, string $styleTable = null, string $styleHeader = null, string $styleData = null ) : string
$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) Style information for the table
$styleHeader string (Optional) Style information for the header row
$styleData string (Optional) Style information for the cells
return string HTML containing a table with all records listed

GetJSON() public method

Returns the last query as a JSON document
public GetJSON ( ) : string
return string JSON containing all records listed

GetLastInsertID() public method

Returns the last autonumber ID field from a previous INSERT query
public GetLastInsertID ( ) : integer
return integer ID number from previous INSERT query

GetLastSQL() public method

Returns the last SQL statement executed
public GetLastSQL ( ) : string
return string Current SQL query string

GetTables() public method

This function returns table names from the database into an array. If the database does not contains any tables, the returned value is FALSE
public GetTables ( ) : array
return array An array that contains the table names

GetXML() public method

Returns the last query as an XML Document
public GetXML ( ) : string
return string XML containing all records listed

HasRecords() public method

Determines if a query contains any rows
public HasRecords ( string $sql = "" ) : boolean
$sql string [Optional] If specified, the query is first executed Otherwise, the last query is used for comparison
return boolean TRUE if records exist, FALSE if not or query error

InsertRow() public method

Inserts a row into a table in the connected database
public InsertRow ( string $tableName, array $valuesArray ) : integer
$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, ect)
return integer Returns last insert ID on success or FALSE on failure

IsConnected() public method

Determines if a valid connection to the database exists
public IsConnected ( ) : boolean
return boolean TRUE idf connectect or FALSE if not connected

IsDate() public static method

[STATIC] Determines if a value of any data type is a date PHP can convert
public static IsDate ( date/string $value ) : boolean
$value date/string
return boolean Returns TRUE if value is date or FALSE if not date

Kill() public method

Stop executing (die/exit) and show last MySQL error message
public Kill ( $message = "" )

MoveFirst() public method

Seeks to the beginning of the records
public MoveFirst ( ) : boolean
return boolean Returns TRUE on success or FALSE on error

MoveLast() public method

Seeks to the end of the records
public MoveLast ( ) : boolean
return boolean Returns TRUE on success or FALSE on error

Open() public method

Connect to specified MySQL server
public Open ( string $database = null, string $server = null, string $username = null, string $password = null, string $charset = null, boolean $pcon = false ) : boolean
$database string (Optional) Database name
$server string (Optional) Host address
$username string (Optional) User name
$password string (Optional) Password
$charset string (Optional) Character set
$pcon boolean (Optional) Persistant connection
return boolean Returns TRUE on success or FALSE on error

Query() public method

Executes the given SQL query and returns the records
public Query ( string $sql ) : object
$sql string The query string should not end with a semicolon
return object PHP '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)

QueryArray() public method

Executes the given SQL query and returns a multi-dimensional array
public QueryArray ( string $sql, integer $resultType = MYSQLI_BOTH ) : array
$sql string The query string should not end with a semicolon
$resultType integer (Optional) The type of array Values can be: MYSQLI_ASSOC, MYSQLI_NUM, MYSQLI_BOTH
return array A multi-dimensional array containing all the data returned from the query or FALSE on all errors

QuerySingleRow() public method

Executes the given SQL query and returns only one (the first) row
public QuerySingleRow ( string $sql ) : object
$sql string The query string should not end with a semicolon
return object PHP resource object containing the first row or FALSE if no row is returned from the query

QuerySingleRowArray() public method

Executes the given SQL query and returns the first row as an array
public QuerySingleRowArray ( string $sql, integer $resultType = MYSQLI_BOTH ) : array
$sql string The query string should not end with a semicolon
$resultType integer (Optional) The type of array Values can be: MYSQLI_ASSOC, MYSQLI_NUM, MYSQLI_BOTH
return array An array containing the first row or FALSE if no row is returned from the query

QuerySingleValue() public method

Executes a query and returns a single value. If more than one row is returned, only the first value in the first column is returned.
public QuerySingleValue ( string $sql ) : mixed
$sql string The query string should not end with a semicolon
return mixed The value returned or FALSE if no value

QueryTimed() public method

Executes the given SQL query, measures it, and saves the total duration in microseconds
public QueryTimed ( string $sql ) : object
$sql string The query string should not end with a semicolon
return object PHP '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

Records() public method

Returns the records from the last query
public Records ( ) : object
return object PHP 'mysql result' resource object containing the records for the last query executed

RecordsArray() public method

Returns all records from last query and returns contents as array or FALSE on error
public RecordsArray ( integer $resultType = MYSQLI_BOTH ) : Records
$resultType integer (Optional) The type of array Values can be: MYSQLI_ASSOC, MYSQLI_NUM, MYSQLI_BOTH
return Records in array form

Release() public method

Frees memory used by the query results and returns the function result
public Release ( ) : boolean
return boolean Returns TRUE on success or FALSE on failure

Row() public method

Reads the current row and returns contents as a PHP object or returns false on error
public Row ( integer $optional_row_number = null ) : object
$optional_row_number integer (Optional) Use to specify a row
return object PHP object or FALSE on error

RowArray() public method

Reads the current row and returns contents as an array or returns false on error
public RowArray ( integer $optional_row_number = null, integer $resultType = MYSQLI_BOTH ) : array
$optional_row_number integer (Optional) Use to specify a row
$resultType integer (Optional) The type of array Values can be: MYSQLI_ASSOC, MYSQLI_NUM, MYSQLI_BOTH
return array Array that corresponds to fetched row or FALSE if no rows

RowCount() public method

Returns the last query row count
public RowCount ( ) : integer
return integer Row count or FALSE on error

SQLBooleanValue() public static method

[STATIC] Converts a boolean into a formatted TRUE or FALSE value of choice
public static SQLBooleanValue ( mixed $value, mixed $trueValue, mixed $falseValue, string $datatype = self::SQLVALUE_TEXT ) : string
$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
return string SQL formatted value of the specified data type

SQLFix() public method

Also, please note that the SQLUnfix function was removed for compatibility.
public SQLFix ( string $value ) : string
$value string
return string SQL formatted value

SQLValue() public static method

[STATIC] Formats any value into a string suitable for SQL statements (NOTE: Also supports data types returned from the gettype function)
public static SQLValue ( mixed $value, string $datatype = self::SQLVALUE_TEXT ) : string
$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
return string

Seek() public method

Sets the internal database pointer to the specified row number and returns the result
public Seek ( integer $row_number ) : object
$row_number integer Row number
return object Fetched row as PHP object

SeekPosition() public method

Returns the current cursor row location
public SeekPosition ( ) : integer
return integer Current row number

SelectDatabase() public method

Selects a different database and character set
public SelectDatabase ( string $database, string $charset = "" ) : boolean
$database string Database name
$charset string (Optional) Character set (i.e. utf8)
return boolean Returns TRUE on success or FALSE on error

SelectRows() public method

Gets rows in a table based on a WHERE filter
public SelectRows ( string $tableName, array $whereArray = null, array/string $columns = null, array/string $sortColumns = null, boolean $sortAscending = true, integer/string $limit = null ) : boolean
$tableName string The name of the table
$whereArray array (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, ect)
$columns array/string
$sortColumns array/string
$sortAscending boolean (Optional) TRUE for ascending; FALSE for descending This only works if $sortColumns are specified
$limit integer/string
return boolean Returns records on success or FALSE on error

SelectTable() public method

Retrieves all rows in a specified table
public SelectTable ( string $tableName ) : boolean
$tableName string The name of the table
return boolean Returns records on success or FALSE on error

TimerDuration() public method

Returns last measured duration (time between TimerStart and TimerStop)
public TimerDuration ( integer $decimals = 4 ) : Float
$decimals integer (Optional) The number of decimal places to show
return Float Microseconds elapsed

TimerStart() public method

Starts time measurement (in microseconds)
public TimerStart ( )

TimerStop() public method

Stops time measurement (in microseconds)
public TimerStop ( )

TransactionBegin() public method

Starts a transaction
public TransactionBegin ( ) : boolean
return boolean Returns TRUE on success or FALSE on error

TransactionEnd() public method

Ends a transaction and commits the queries
public TransactionEnd ( ) : boolean
return boolean Returns TRUE on success or FALSE on error

TransactionRollback() public method

Rolls the transaction back
public TransactionRollback ( ) : boolean
return boolean Returns TRUE on success or FALSE on failure

TruncateTable() public method

Truncates a table removing all data
public TruncateTable ( string $tableName ) : boolean
$tableName string The name of the table
return boolean Returns TRUE on success or FALSE on error

UpdateRows() public method

Updates rows in a table based on a WHERE filter (can be just one or many rows based on the filter)
public UpdateRows ( string $tableName, array $valuesArray, array $whereArray = null ) : boolean
$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, ect)
$whereArray array (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, ect). If not specified then all values in the table are updated.
return boolean Returns TRUE on success or FALSE on error

__construct() public method

Constructor: Opens the connection to the database
public __construct ( boolean $connect = true, string $database = null, string $server = null, string $username = null, string $password = null, string $charset = null )
$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

__destruct() public method

Destructor: Closes the connection to the database
public __destruct ( )

Property Details

$ThrowExceptions public property

Determines if an error throws an exception
public $ThrowExceptions