Ultimate MySQL Wrapper Class for PHP 5.x
http://www.phpclasses.org/browse/package/3698.html
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
Nov 10, 2014 - Jeremy's modifications:
Added new functionality into BuildSQLWhereClause()
Rebuild IsDate()
GetHTML() is more secure now
SQLFix() and SQLUnfix() removed from this Class, use SQLValue() if needed
Added BackupTable()
Nov 16, 2014 - Fixes bug when mysql_num_fields() get parameter with wrong type
Nov 17, 2014 - Added parameter $formatTemplate into BackupTable()
Dec 26, 2014 - Added GetLastTables(), GetColumnDescriptions()
Usage:
include
(
"mysql.class.php"
);
$db
=
new
MySQL
()
;
$db
=
new
MySQL
(
true
,
"database")
;
$db
=
new
MySQL
(
true
,
"database"
,
"localhost"
,
"username"
,
"password")
;
Determines if an error throws an exception
Constructor: Opens the connection to the database
Example:
$db
=
new
MySQL
()
;
$db
=
new
MySQL
(
true
,
"database")
;
$db
=
new
MySQL
(
true
,
"database"
,
"localhost"
,
"username"
,
"password")
;
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
Example:
// 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
Example:
// 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
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 NULL if the table has not got any fields
Example:
$columns
=
$db
->
GetColumnComments
(
"MyTable"
);
foreach
(
$columns
as
$column
=>
$comment
)
{
echo
$column
.
" = "
.
$comment
.
"<br
/>\n"
;
}
This function returns the information about the columns in a given table or returns FALSE on error
Syntax:
If the data types differ from what you expect them to be based on a CREATE TABLE statement, note that MySQL sometimes changes data types when you create or alter a table.Using this you will be acknowledged with name, type, the column collation and comments, as well as the privileges you have for each column.
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 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 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 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
();
Returns the table names of the last SQL statement executed. Even when query is non execute correctly. For more information how this table coexist witch each other use EXPLAIN word on the last query
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"
;
}
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
(
"1.12.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"
;
}
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] 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 on Mon, 16 Jul 2007 by phpDocumentor 1.3.0RC3 and modified by Jeff L. Williams