Documentation for MySQL Class written by Sascha 'SieGeL' Pfalz
Last updated on 07-Aug-2010
-----------------------------------------------------------------------------
1. INTRODUCTION
~~~~~~~~~~~~~~~
This class was mainly created to to have a possibility to work with MySQL
databases via the same API my OCI8 class also provides, this makes code
sharing a lot easier and also allows to have a consistent coding model in
hand. Please note that the APIs differ in some calls, as MySQL and Oracle
are very different, too. I've tried to implement most of the API calls
identically between both classes.
This doc gives you an overview of all available methods and their usage.
Examples are provided in the "examples" directory.
2. REQUIREMENTS
~~~~~~~~~~~~~~~
To use this class you have to met the following requirements:
- PHP 4.x (PHP 5 is supported, too) with enabled "mysql" extension
- MySQL Database, version should not matter. Tested with 3.x, 4.0.x, 4.1.x and 5.x
3. INSTALLATION AND USAGE
~~~~~~~~~~~~~~~~~~~~~~~~~
Copy the supplied mysqldb_class.php to a directory of your choice, a good
place would be the inc/ directory of your project. Also copy the file
dbdefs.inc.php to the same directory you have copied the mysqldb_class.php
file.
The file "dbdefs.inc.php" serves as the configuration file for the class.
You may give an alternate path to this file in the constructor of this class.
The following defines can be set to use the class inside dbdefs.inc.php:
MYSQLDB_HOST - Hostname or IP address of target database.
MYSQLDB_PORT - Opt. Portnumber of target DB, defaults to 3306.
MYSQLDB_USER - MySQL Username used as default connection.
MYSQLDB_PASS - MySQL Password for useraccount above.
MYSQLDB_DATABASE - The schema name to use.
MYSQLAPPNAME - Name of your application. This is used in error
messages.
DB_ERRORMODE - How errors should be handled. Default is to show
only limited informations for safety reasons. See
description of setErrorHandling() for further
details.
MYSQLDB_ADMINEMAIL - Specify an email address to be used whenever an
error occures. This email is shown in error messages
and if MYSQLDB_SENTMAILONERROR is set also used to
sent out an automatic mail to that address in case
of an error.
MYSQLDB_SENTMAILONERROR - Flag indicating if the class should auto-send emails
to the defined EMail address whenever an error
occures. Set it to 1 to enable auto-sending, and set
it to 0 to disable this behavour.
MYSQLDB_MAIL_EXTRAARGS - Use this define to pass additional parameter to the
mail() command in SendmailOnError(). Some servers
might need to set the -f parameter when using PHP's
mail() command, and to allow this also here in the
class you can use this define. Default is unset.
MYSQLDB_USE_PCONNECT - if set to 1 persistant connections are used,
else standard connects are used. This can be set
also on a script-by-script basis via the method
setPConnect().
MYSQLDB_CHARACTERSET - You can set here the character set the class should
set to MySQL during the connect phase. This allows
to set the MySQL communication i.e. to 'utf8'.
If this define is not set the default character set
of MySQL is used.
MYSQLDB_TIME_NAMES - You can set here the default language used for date
and time translations. Specify here the values as
listed under the following url:
http://dev.mysql.com/doc/refman/5.0/en/locale-support.html
If this define is not set the default language of the
MySQL Server is used.
MYSQLDB_NEW_LINK - If you need NEW connections when supplying identical
credentials but using a different DB on the same server,
set this define to TRUE. Default value is FALSE,
this is also used by all previous releases of this class.
The file dbdefs.inc.php is automatically require()d by the class once you
instantiate it the first time.
To use the class you have to require() the class code, the rest is done
automatically when you first instantiate the class. Normally you may have one
PHP script which includes several others, here would be the ideal place to put
the require() statement for the class, i.e.:
---[SNIP]---
// ...Your require() statements
require("path/to/mysqldb_class.php");
// ..Rest of your code here
---[SNIP]---
Once this is done and you have added the proper values in dbdefs.inc.php you
can now start using the class, this would look like this for example:
---[SNIP]---
<?php
require("mysqldb_class.php");
$db = new db_MySQL;
$db->Connect();
$mver = $db->Version();
$db->Disconnect();
echo("Your MySQL Server is V".$mver);
?>
---[SNAP]---
As you can see in this example the dbdefs.inc.php file is automatically loaded
when you create the first instance of the DB object.
You can also use a different configfile by specifying a different path to
your config inside the constructor, like this:
---[SNIP]---
<?php
require("mysqldb_class.php");
$db = new db_MySQL('/path/to/my/own/config.inc.php');
$db->Connect();
$mver = $db->Version();
$db->Disconnect();
echo("Your MySQL Server is V".$mver);
?>
---[SNAP]---
4. METHOD OVERVIEW
~~~~~~~~~~~~~~~~~~
I've provided a auto-generated method overview inside the docs subfolder of
the distribution archive which was generated by phpDocumentor.
The class provides the following methods:
-----------------------------------------------------------------------------
db_MySQL db_MySQL ([mixed $extconfig = ''])
-----------------------------------------------------------------------------
This is the constructor of the class. Before you can use any of the class
functions you have to create a new instance of it.
Example:
$db = new db_MySQL;
You may also give an alternate path to the database definition file:
$db = new db_MySQL("/path/to/your/own/dbdefs.inc.php");
If you ommit the path to dbdefs.inc.php the class tries to include this file
from within the same directory where the class resides.
-----------------------------------------------------------------------------
integer AffectedRows ([mixed $extsock = -1])
-----------------------------------------------------------------------------
Returns the amount of affected rows based on previous DML operation. Note
the word DML (Data Manipulation Language) which implies that this method
only returns values for INSERT, UPDATE, DELETE or REPLACE commands! If no
external connection handle is given the internal saved handle is used.
-----------------------------------------------------------------------------
void checkSock ()
-----------------------------------------------------------------------------
Internal function that checks if the internal socket variable is populated.
If this is not the case class calls Print_Error() and prints out an error
stating "!!! NOT CONNECTED TO AN MYSQL DATABASE !!!".
-----------------------------------------------------------------------------
void Commit ()
-----------------------------------------------------------------------------
Commits a transaction. Note that this is only supported for transaction-
enabled storage engines like InnoDB; MyISAM tables are not transactional and
therefor this command simply does nothing.
-----------------------------------------------------------------------------
mixed Connect ([string $user = ''], [string $pass = ''], [string $host = ''],
[string $db = ''], [integer $port=0])
-----------------------------------------------------------------------------
Performs connection to a MySQL database server. Normally you do not have to
supply here any of the parameters, as these parameters are taken from the
dbdefs.inc.php file automatically.
If an error occures during the connect attempt the class either returns an
error code to the callee (if DB_ERRORMODE is set to DBOF_RETURN_ALL_ERRORS)
or prints out an error message and terminates execution.
If all goes well this method returns the connection handle. You do not have
to save this value, the class stores this handle internally and uses this
handle whenever you do not supply an handle.
-----------------------------------------------------------------------------
string ConvertMySQLDate (string $mysqldate, string $fmtstring)
-----------------------------------------------------------------------------
Converts a MySQL default Datestring (YYYY-MM-DD HH:MI:SS) into a strftime()
compatible format. You can use all format tags that strftime() supports, this
function simply converts the mysql date string into a timestamp which is then
passed to strftime together with your supplied format. If $fmtstring is empty
the class uses '%c' as default format string.
The converted datestring is then returned.
Please do not use this as default date converter, always use DATE_FORMAT()
inside a query whenever possible as this is much faster than using this
function! Only if you cannot use the MySQL SQL Date converting functions
consider using this function.
-----------------------------------------------------------------------------
array DescTable (string $tname)
-----------------------------------------------------------------------------
This method describes a given table and returns the structure of the table
as array. The following fields are returned:
0 => Column name
1 => Column type
2 => Column size
3 => Column flags
Please note that this method only returns basic informations about the
structure of a table, no constraints or other meta informations are returned.
See examples/desc_tables.php for an example how to use this method.
-----------------------------------------------------------------------------
void Disconnect ([mixed $other_sock = -1])
-----------------------------------------------------------------------------
Disconnects from MySQL database. If no external connection handle is given
the class disconnects the internal connection handle, else the supplied one.
-----------------------------------------------------------------------------
string EscapeString (string $str)
-----------------------------------------------------------------------------
Allows to escape a string before adding it to MySQL. For safety you should
always use this method before performing a query. Mainly if you plan to
save data from Web forms you MUST (!) escape all data, else SQL injection
maybe possible! This method also checks first if the magic_quotes_gpc()
setting is enabled and calls stripslashes() if it is activated.
-----------------------------------------------------------------------------
array FetchResult (mixed $result, [integer $resflag = MYSQL_ASSOC])
-----------------------------------------------------------------------------
Retrieves next row from statement handle $result and returns the data in
either numeric or associative array format depending on flag $resflag.
The statement handle is returned from QueryResult(). If no more data are
found it returns NULL. Classic usage is something like this:
---[SNIP]---
$stmt = $db->QueryResult("SELECT FOO FROM BAR ORDER BY FOO");
while($data = $db->FetchResult($stmt))
{
echo($data['FOO']);
}
$db->FreeResult($stmt);
---[SNAP]---
Default return format is always associative, if you want to have numeric
arrays you have to change the line above to $db->FetchResult($stmt,MYSQL_NUM)
-----------------------------------------------------------------------------
mixed FreeResult (mixed $result)
-----------------------------------------------------------------------------
After the last row is recieved from FetchResult() you should free the
statement handle with this function. PHP normally frees all allocated
resources automatically when the script terminates, but you should always
free all your own allocated resources yourself as this is good programming
practise.
-----------------------------------------------------------------------------
string GetClassVersion ()
-----------------------------------------------------------------------------
Returns the class Version. The format of the version string is MAJOR.MINOR
versionnumber, i.e. "0.35".
-----------------------------------------------------------------------------
mixed GetConnectionHandle ()
-----------------------------------------------------------------------------
Returns the internally saved connection handle as returned by Connect(). This
is useful if you want to use the mysql_* functions of PHP on an already
connected database handle. Returns -1 if no active connection handle exists.
-----------------------------------------------------------------------------
integer GetDebug ()
-----------------------------------------------------------------------------
Returns the current bitmask for debug handling. See SetDebug() for further
details about debugging with this class.
-----------------------------------------------------------------------------
integer GetErrno ([mixed $other_sock = -1])
-----------------------------------------------------------------------------
Returns the error code from the last SQL operation. You can pass your own
connection handle here if you want.
-----------------------------------------------------------------------------
string GetErrorText ([mixed $other_sock = -1])
-----------------------------------------------------------------------------
Returns the error description from the last SQL operation. You can pass your
own connection handle here if you want.
-----------------------------------------------------------------------------
float getmicrotime ()
-----------------------------------------------------------------------------
Internal function to measure times. Whenever the class performs any action
against the database server the time it took to perform the given action is
tracked and can be retrieved by calling GetQueryTime(). This is useful to
see how your queries perform.
-----------------------------------------------------------------------------
integer GetQueryCount ()
-----------------------------------------------------------------------------
Returns the current query counter. Whenever the class performs a query
against the database server an internal counter is incremented. This is
useful to track errors, as the Print_Error() function dumps out this value,
making it more easy to find the errornous query inside your scripts by simply
counting the queries down to the one where the error occures.
-----------------------------------------------------------------------------
float GetQueryTime ()
-----------------------------------------------------------------------------
Returns amount of time spend on queries executed by this class.
The format is "seconds.microseconds".
-----------------------------------------------------------------------------
array get_CharSet ()
-----------------------------------------------------------------------------
Method to return the current MySQL setting for the character_set variables.
Note that MySQL returns a list of settings, so this method returns all
character_set related settings as an associative array.
-----------------------------------------------------------------------------
string get_TimeNames ()
-----------------------------------------------------------------------------
Method to return the current MySQL setting for the lc_time_names variable.
-----------------------------------------------------------------------------
bool IsQueryWithResult(string $querystring)
-----------------------------------------------------------------------------
Tests if the given Query would return a result set, returns TRUE if it is one
of the filtered queries. Currently supported are:
SELECT,SHOW,EXPLAIN,DESCRIBE,OPTIMIZE,ANALYZE,CHECK
All other queries return FALSE.
-----------------------------------------------------------------------------
integer LastInsertId ([mixed $extsock = -1])
-----------------------------------------------------------------------------
Returns last used auto_increment id. Whenever you INSERT a row with an
auto_increment field defined in the underlying table, MySQL auto-increments
this field. With this method() you can retrieve the newly updated value.
If no external connection handle is given the internal handle is used.
-----------------------------------------------------------------------------
integer NumRows()
-----------------------------------------------------------------------------
Returns the number of rows in the result set.
Use this after a SELECT or SHOW etc. command has been executed.
For DML operations like INSERT, UPDATE, DELETE the method AffectedRows()
has to be used.
-----------------------------------------------------------------------------
array PerformNewInsert(string $table_name, array &$fields,[string $sql='INSERT'])
-----------------------------------------------------------------------------
Performs an INSERT or REPLACE statement from a given variable list.
The statements will be constructed as NEW Insert style, and aligned to the
"max_allowed_packet" boundary. This can dramatically improve bulk-inserts
compared to fire every INSERT statement one by one. Note that this method
ONLY (!) supports INSERT and REPLACE statements, all other types are not
supporting these NEW-STYLE SQL statements.
The array passed must be constructed with the keys defined as fieldnames and
the values as the corresponding values.
Looks like this:
$data[0]['fieldname1'] = 'value0/1';
$data[0]['fieldname2'] = 'value0/2';
$data[1]['fieldname1'] = 'value1/2';
$data[1]['fieldname2'] = 'value1/2';
NOTE: Database must be connected!
See also examples/new_insert.php for a working example of this method.
-----------------------------------------------------------------------------
void PrintDebug (string $msg)
-----------------------------------------------------------------------------
Depending on the current DEBUG setting the class dumps out debugging
informations either on screen, to the error.log of PHP or to both. If debug
is not enabled this function does nothing. This is extremly useful when
tracking errors, you can simply call SetDebug() with an debug level of your
choice before the query in question and the class dumps out what happens.
Example:
---[SNIP]---
..
$db->SetDebug(DBOF_DEBUGSCREEN);
$db->Query('SELECT FOO FROM BAR WHERE DUMMY=1');
..
..
---[SNAP]---
Would result in dumping out the Query on screen. See examples for further
details how to use this.
-----------------------------------------------------------------------------
void Print_Error ([string $ustr = ""], [mixed $var2dump = ""])
-----------------------------------------------------------------------------
This method serves as the general error handling method inside the class.
Normally this method dumps out the error occured together with additional
informations like used Variables and current query etc. After displaying
these informations this method calls exit() and terminates execution.
However you can modify this behavour with setErrorHandling(). If you have
defined DB_ERRORMODE = DBOF_RETURN_ALL_ERRORS no error message is shown,
instead the class returns the error code to you, and you have to handle
the error conditions on your own.
If you have set DBOF_SHOW_NO_ERRORS the class still displays an error message,
however the informations shown are limited so that an possible attacker does
not have all required informations in place to hack your site. This is also
default behavour. In development environments it may useful to use the third
flag DBOF_SHOW_ALL_ERRORS, in this mode all possible informations are shown
including the query that produces the error, etc.
-----------------------------------------------------------------------------
mixed Query (string $querystring, [integer $resflag = MYSQL_ASSOC],
integer $no_exit)
-----------------------------------------------------------------------------
Performs a single-row query and returns result, either as numeric or as
associative array, depending on the $resflag setting.
With the $no_exit flag you can selectively instruct the class NOT to exit
in case of an error (set to 1), even if your master define DB_ERRORMODE has
a different setting.
This method returns the result of the call as array whenever one of the
following SQL Commands is detected:
SELECT
SHOW
EXPLAIN
DESCRIBE
OPTIMIZE
ANALYZE
CHECK
For all other commands only the numeric value of the call result is returned.
Please remember that associative arrays are case-sensitive, you have to
specify the array index name exactly as specified inside the query!
-----------------------------------------------------------------------------
mixed QueryResult (string $querystring, integer $no_exit)
-----------------------------------------------------------------------------
Performs a multi-row query and returns a statement handle ready to pass to
FetchResult()/FreeResult().
With the $no_exit flag you can selectively instruct the class NOT to exit
in case of an error (set to 1), even if your master define DB_ERRORMODE has
a different setting.
-----------------------------------------------------------------------------
void Rollback ()
-----------------------------------------------------------------------------
Rolls back current transaction.
Note that this is only supported for transaction-enabled storage engines like
InnoDB; MyISAM tables are not transactional and therefor this command simply
does nothing.
-----------------------------------------------------------------------------
void SendMailOnError (integer $merrno, string $merrstr, string $uerrstr)
-----------------------------------------------------------------------------
Sends out an error email to the address defined under MYSQLDB_ADMINEMAIL.
Please note that an email is ONLY (!) sent if you have configured the define
MYSQLDB_SENTMAILONERROR = 1.
If both defines are enabled and an error occures the class automatically
sends an email, so that the coder will be notified about every error that
may have occured in his software. I'm using this feature on all my productive
sites to get a quick information whenever something happens.
The email looks like this:
---[SNIP]---
MySQLDB Class: Error occured on Sun, 11 Jun 2006 12:17:21 +0200 !!!
AFFECTED SERVER: n/a
USER AGENT: n/a
PHP SCRIPT: test.php
REMOTE IP ADDR: n/a
DATABASE DATA: username @ localhost
SQL ERROR MESSAGE: Access denied for user 'username'@'localhost' (using password: YES)
SQL ERROR CODE: 1045
QUERY COUNTER: 0
INFOTEXT: Connect(): Connection to localhost failed!
---[SNAP]---
If your server requires some special arguments when calling mail(), you can
use the define MYSQLDB_MAIL_EXTRAARGS and enter all the special arguments
you would normally also use as 4th parameter to the mail() command. Default
is undefined.
-----------------------------------------------------------------------------
void SetConnectionHandle (mixed $extsock)
-----------------------------------------------------------------------------
Allows to overwrite the internal socket by an external value. However you
REALLY should know what you are doing here, as the class does not track this
change, it simply overwrite the internal handle!
-----------------------------------------------------------------------------
void SetDebug (integer $state)
-----------------------------------------------------------------------------
Function allows debugging of SQL Queries inside your scripts.
$state can have these values:
- DBOF_DEBUGOFF = Turn off debugging
- DBOF_DEBUGSCREEN = Turn on debugging on screen
(every Query will be dumped on screen)
- DBOF_DEBUFILE = Turn on debugging on PHP errorlog
You can mix the debug levels by adding the according defines. Also you can
retrieve the current debug level setting by calling the method "GetDebug()".
-----------------------------------------------------------------------------
void setErrorHandling (integer $val)
-----------------------------------------------------------------------------
Allows to set the class handling of errors.
- DBOF_SHOW_NO_ERRORS = Show no security-relevant informations
- DBOF_SHOW_ALL_ERRORS = Show all errors (useful for develop)
- DBOF_RETURN_ALL_ERRORS = No error/autoexit, just return the mysql_error code.
-----------------------------------------------------------------------------
boolean setPConnect ($conntype)
-----------------------------------------------------------------------------
Change the connection method to either persistant connections or standard
connections.
Set $conntype = TRUE to activate Persistant connections.
Set $conntype = FALSE to deactivate persistant connections.
Default is standard connections.
-----------------------------------------------------------------------------
integer set_CharSet (string $charset)
-----------------------------------------------------------------------------
Method to set the character set of the current connection.
You must specify a valid character set name, else the class will report an error.
See http://dev.mysql.com/doc/refman/5.0/en/charset-charsets.html for a list
of supported character sets.
Return 1 on success, else failure.
-----------------------------------------------------------------------------
integer set_TimeNames (string $locale)
-----------------------------------------------------------------------------
Method to set the time_names setting of the MySQL Server.
Pass it a valid locale string to change the locale setting of MySQL.
Note that this is supported only since 5.0.25 of MySQL!
The locale string is something like 'de_DE' for example.
Returns 0 If an error occures or 1 if change was successful.
-----------------------------------------------------------------------------
string Version ()
-----------------------------------------------------------------------------
Returns Database Versionstring. If no active connection exists when calling
this function this method connects itself to the database, retrieve the
version string and disconnects afterwards. If an active connection exists
this connection is used and of course not terminated.
5. FINAL WORDS AND CONTACT ADDRESSES
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
I'm using this class now in several projects and never encountered any
problems. However we all know that no software is 100% bugfree, so if you
have found a bug or have suggestions or feature requests feel free to contact
me under one of the following addresses:
WWW: http://www.saschapfalz.de/contact.php
EMAIL: php <at> saschapfalz <dot> de
ICQ: 9691810
AIM: SieGeL2k2
MSN: sapf@live.de
You can contact me whenever you are:
- missing some functionality
- found a bug
- have usage questions
Feel free to get in touch with me, I'm not biting and really would love
to see some feedback for that class at all.
Happy coding!
-----------------------------------------------------------------------[EOF]-
$Id: README,v 1.13 2010/08/07 08:16:14 siegel Exp $
Click to download