Shows short description for file mysqldb_class039.zip
<< BackFilename: | mysqldb_class039.zip (141,757 bytes) |
Released: | 07th August 2010, 7,978 downloads |
Category: | PHP |
Click to download |
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 $