Ultimate MySQL Wrapper Class for PHP 7 & PHP 8
Feb 02, 2007 - Written by Jeff Williams (Initial Release)
Feb 11, 2007 - Contributions from Frank P. Walentynowicz
Feb 21, 2007 - Contribution from Larry Wakeman
Feb 21, 2007 - Bug Fixes and PHPDoc
Mar 09, 2007 - Contribution from Nicola Abbiuso
Mar 22, 2007 - Added array types to RecordsArray and RowArray
Jul 01, 2007 - Class name change, constructor values, static methods, fixe
Jul 16, 2007 - Bug fix, removed test, major improvements in error handling
Aug 11, 2007 - Added InsertRow() and UpdateRows() methods
Aug 19, 2007 - Added BuildSQL static functions, DeleteRows(), SelectRows(),
IsConnected(), and ability to throw Exceptions on errors
Sep 07, 2007 - Enhancements to SQL SELECT (column aliases, sorting, limits)
Sep 09, 2007 - Updated SelectRows(), UpdateRows() and added SelectTable(),
TruncateTable() and SQLVALUE constants for SQLValue()
Oct 23, 2007 - Added QueryArray(), QuerySingleRow(), QuerySingleRowArray(),
QuerySingleValue(), HasRecords(), AutoInsertUpdate()
Oct 28, 2007 - Small bug fixes
Nov 28, 2007 - Contribution from Douglas Gintz
Jul 06, 2009 - GetXML() and GetJSON() contribution from Emre Erkan
and ability to use a blank password if needed
Aug 16, 2013 - Version 3.0 - Updated class to mysqli extension
Oct 26, 2022 - Created Github repository with the aim of making the class compatible with PHP 8
Nov 04, 2022 - Released the version 4.0 with the PHPUnit test cases and a lot of bug fix
Nov 06, 2022 - Version 4.1 - The library is now installable with Composer.
Nov 08, 2022 - Version 4.2 - Added the debug mode
Nov 10, 2022 - Version 4.3 - Composer version is now also compatible with PHP 7
Nov 12, 2022 - Version 4.4 - Improved the debug mode for the composer version
Feb 14, 2023 - Version 4.5 - PHP 8.2 compatible
Jan 23, 2024 - Version 4.6 - Bug fixes provided by Paul Pazderski + PHP 8.3 compatible
Usage (normal library)
include "mysql.class.php";
$db = new MySQL();
$db = new MySQL(true, "database");
$db = new MySQL(true, "database", "localhost", "username", "password");
Usage (composer)
require "vendor/autoload.php";
$db = new MySQL();
$db = new MySQL(true, "database");
$db = new MySQL(true, "database", "localhost", "username", "password");
Debug mode
The script looks for a file called .debugmysql (within the root directory or within the composer's vendor / module folder) and, if found, enters debug mode.
When debug mode is active, it writes all SQL queries executed inside the .debugmysql file.
Constructor: Opens the connection to the database
Example:
$db = new MySQL();
$db = new MySQL(true, "database");
$db = new MySQL(true, "database", "localhost", "username", "password");
Destructor: Closes the connection to the database
Automatically does an INSERT or UPDATE depending if an existing record exists in a table
Returns true if the internal pointer is at the beginning of the records
Example:
if ($db->BeginningOfSeek()) {
echo "We are at the beggining of the record set";
}
[STATIC] Builds a SQL DELETE statement
// 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);[STATIC] Builds a SQL INSERT statement
// 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
// 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);[STATIC] Builds a SQL UPDATE statement
// 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)";[STATIC] Builds a SQL WHERE clause from an array. 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.
Close current MySQL connection
Example:
$db->Close();
Deletes rows in a table based on a WHERE filter (can be just one or many rows based on the filter)
Example
// $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();
}
Returns true if the internal pointer is at the end of the records
Example:
if ($db->EndOfSeek()) {
echo "We are at the end of the record set";
}
Returns the last MySQL error as text
Example:
if (! $db->Query("SELECT * FROM Table")) {
echo $db->Error(); //Shows the error
}
if ($db->Error()) $db->Kill();
Returns the last MySQL error as a number
Example:
if ($db->ErrorNumber() <> 0) {
$db->Kill(); //show the error message
}
[STATIC] Converts any value of any datatype into boolean (true or false)
Example:
echo (MySQL::GetBooleanValue("Y") ? "True" : "False");
echo (MySQL::GetBooleanValue("no") ? "True" : "False");
echo (MySQL::GetBooleanValue("TRUE") ? "True" : "False");
echo (MySQL::GetBooleanValue(1) ? "True" : "False");
Returns the comments for fields in a table into an array or returns FALSE on error
Example:
$columns = $db->GetColumnComments("MyTable");
foreach ($columns as $column => $comment) {
echo $column . " = " . $comment . "<br />\n";
}
This function returns the number of columns or returns FALSE on error
Example:
echo "Total Columns: " . $db->GetColumnCount("MyTable");
This function returns the data type ID for a specified column. If the column does not exists or no records exist, it returns FALSE
Example:
echo "Type: " . $db->GetColumnDataType("FirstName", "Customer");
This function returns the data type name for a specified column. If the column does not exists or no records exist, it returns FALSE
Example:
echo "Type: " . $db->GetColumnDataTypeName("FirstName", "Customer");
This function returns the position of a column
Example:
echo "Column Position: " . $db->GetColumnID("FirstName", "Customer");
This function returns the field length or returns FALSE on error
Example:
echo "Length: " . $db->GetColumnLength("FirstName", "Customer");
This function returns the name for a specified column number. If the index does not exists or no records exist, it returns FALSE
Example:
echo "Column Name: " . $db->GetColumnName(0);
Returns the field names in a table in an array or NULL if the table has no fields
Example:
$columns = $db->GetColumnNames("MyTable");
foreach ($columns as $columnName) {
echo $columnName . "<br />\n";
}
This function returns the last query as an HTML table
Example:
$db->Query("SELECT * FROM Customer");
echo $db->GetHTML();
Returns the last query as a JSON document
Returns the last autonumber ID field from a previous INSERT query
Example:
$sql = "INSERT INTO Employee (Name) Values ('Bob')";
if (! $db->Query($sql)) {
$db->Kill();
}
echo "Last ID inserted was: " . $db->GetLastInsertID();
Returns the last SQL statement executed
Example:
$sql = "INSERT INTO Employee (Name) Values ('Bob')";
if (! $db->Query($sql)) {$db->Kill();}
echo $db->GetLastSQL();
This function returns table names from the database into an array. If the database does not contains any tables, the returned value is FALSE
Example:
$tables = $db->GetTables();
foreach ($tables as $table) {
echo $table . "<br />\n";
}
Returns the last query as an XML Document.
Determines if a query contains any rows
Inserts a row into a table in the connected database
Example
// $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;
}
[STATIC] Determines if a value of any data type is a date PHP can convert
Example
if (MySQL::IsDate("January 1, 2000")) {
echo "valid date";
}
Stop executing (die/exit) and show the last MySQL error message
Example:
//Stop executing the script and show the last error
$db->Kill();
Seeks to the beginning of the records
Example:
$db->MoveFirst();
while (! $db->EndOfSeek()) {
$row = $db->Row();
echo $row->ColumnName1 . " " . $row->ColumnName2 . "\n";
}
Seeks to the end of the records
Example:
$db->MoveLast();
Connect to specified MySQL server
Example
if (! $db->Open("MyDatabase", "localhost", "user", "password")) {
$db->Kill();
}
Executes the given SQL query and returns the records
Example:
if (! $db->Query("SELECT * FROM Table")) echo $db->Kill();
Executes the given SQL query and returns a multi-dimensional array
Executes the given SQL query and returns only one (the first) row
Executes the given SQL query and returns the first row as an array
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.
Executes the given SQL query, measures it, and saves the total duration in microseconds
Example
$db->QueryTimed("SELECT * FROM MyTable");
echo "Query took " . $db->TimerDuration() . " microseconds";
Returns the records from the last query
Example:
$records = $db->Records();
Returns all records from last query and returns contents as array or FALSE on error
Example
$myArray = $db->RecordsArray(MYSQL_ASSOC);
Frees memory used by the query results and returns the function result
Example:
$db->Release();
Reads the current row and returns contents as a PHP object or returns false on error
Example:
$db->MoveFirst();
while (! $db->EndOfSeek()) {
$row = $db->Row();
echo $row->ColumnName1 . " " . $row->ColumnName2 . "\n";
}
Reads the current row and returns contents as an array or returns false on error
Example:
for ($index = 0; $index < $db->RowCount(); $index++) {
$val = $db->RowArray($index);
}
Returns the last query row count
Example:
$db->Query("SELECT * FROM Customer");
echo "Row Count: " . $db->RowCount();
Sets the internal database pointer to the specified row number and returns the result
Example:
$db->Seek(0); //Move to the first record
Returns the current cursor row location
Example:
echo "Current Row Cursor : " . $db->GetSeekPosition();
Selects a different database and character set
Example:
$db->SelectDatabase("DatabaseName");
Gets rows in a table based on a WHERE filter
Example
// $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();
}
Retrieves all rows in a specified table
[STATIC] Converts a boolean into a formatted TRUE or FALSE value of choice
Example:
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, '+', '-');
[STATIC] Returns string suitable for SQL
Deprecated - instead use SQLValue($value, "text")
Example:
$value = MySQL::SQLFix("\hello\ /world/");
echo $value . "\n" . MySQL::SQLUnfix($value);
[STATIC] Returns MySQL string as normal string
Removed from version 3.0
Example:
$value = MySQL::SQLFix("\hello\ /world/");
echo $value . "\n" . MySQL::SQLUnfix($value);
[STATIC] Formats any value into a string suitable for SQL statements (NOTE: Also supports data types returned from the gettype function)
Example:
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);
Returns last measured duration (time between TimerStart and TimerStop)
Example:
$db->TimerStart();
// Do something or run some queries
$db->TimerStop();
echo $db->TimerDuration(2) . " microseconds";
Starts time measurement (in microseconds)
Example:
$db->TimerStart();
// Do something or run some queries
$db->TimerStop();
echo $db->TimerDuration() . " microseconds";
Stops time measurement (in microseconds)
Example:
$db->TimerStart();
// Do something or run some queries
$db->TimerStop();
echo $db->TimerDuration() . " microseconds";
Starts a transaction
Example:
$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
Example:
$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
Example:
$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
Updates a row in a table from the connected database
// 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
Documentation generated by phpDocumentor 1.3.0RC3 and modified by Jeff L. Williams