1 Introduction----------------------------------------------- --------------------------------------2
2. Download, install PEAR ---------------------------------------------------------------------------------------------------------- -----------------------------2
3 Using Pear DB ---------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------2
3.1.1 Connection, disconnect the database
3.1.2 Executing the Database
3.1.3 Get the data of SELECT
3.1.3.1 Functions for obtaining data
3.1.3.2 Select the format of obtaining data
3.1.3.3 Setting the format for obtaining data
3.1.3.4 Control Getting Data Quantity
3.1.3.5 Clear results, release variables
3.1.4 Quick Retrieve Data
3.1.5 Get more information from query (NumRows, Numcols, AffectedRows, TableInfo)
3.1.6 Automatic growth (SEQUENCES)
3.1.7 Prepare & Execute / ExcuteMultiple
3.1.8 AutoCommit, Commit and Rollback
4. A list of available methods ------------------------------------------- ------------------------------ 10
5 Error handling mechanism ---------------------------------------------- ---------------------------- 12
5.1 Get an error message from Pear DB ERROR
5.2 Debug Pear DB ErrorS
5.3 Automatic handling of errors
1 Introduction
This is a guiding how we use PEAR DB extension. Pear DB provides such a series of classes:
N database abstraction
N Advanced Error Processing Mechanism
N and other
2. Download, install PEAR
Since the Pear project is still in the development of intensive drums, the best way to get it is from CVS (PEAR DB issuing package has followed PHP)
4.0.6
The release is bundled later). So, we only need to put the root directory of the PEAR in the php.ini profile include_path. You can also set this setting: _SET ('include_path', '/ pear_base_dir').
The following is a STRP BY Step example:
To store PEAR directory: # cd / usr / local / lib Login with "phpfi" password: # cvs -d: pserver: cvsread@cvs.php.net: / repository login Get all PEAR files with the following command, can also Used to update the file already downloaded. Other parameters are: "Today", "Last Month", etc. I recommend "Last Week" parameters because general BUGS submission and modification are once a week. # CVS -D: PServer: cvsread@cvs.php.net: / repository export -d "Last Week" PHP4 / PEAR Edit php.ini file plus the following paragraph 1 in the include_path: / usr / local / lib / php4 / pear If there is no modified permissions, you can implement this statement in your code: ini_set ('incrude_path', 'PATH_TO_PEAR'); get a full document for PHP CVS
Note Pear DB required PHP version
4.0.4
These, and some other packages in PEAR, such as: XML Parser of the Pear Installer Script requires PHP4.0.5 or higher.
3. Using PEAR DB
3.1 Connection, disconnect the database
// the pear base directory must be in your include_path
Require_once 'db.php';
$ USER = 'foo';
$ Pass = 'bar';
$ host = 'localhost';
$ db_name = 'clients_db';
// Data Source Name: this is the universal connection string
$ dsn = "mysql: // $ user: $ pass @ $ host / $ db_name";
// DB :: Connect Will Return A Pear DB Object on success
// OR a pear db error Object On Error
// You can also set to true the second param
// if you want a personistent connection:
// $ db = db :: Connect ($ DSN, TRUE);
$ DB = DB :: Connect ($ DSN);
// with db :: iesrror you can differentiate betWeen an error OR
// a Valid connection.
IF (DB :: ISERROR ($ dB)) {
DIE ($ db-> getMessage ());
}
....
// You Can Disconnect from the Database with:
$ dB-> disconnect ();
?>
Data Source ($ DSN parameters in the previous example) have the following format: (copy from Pear / DB.php ParsedSn method)
* PhPTYPE: Database Backend Used in PHP (MySQL, ODBC ETC.)
* DBSYNTAX: Database Used with regards to SQL Syntax ETC.
* Protocol: Communication Protocol To Use (TCP, UNIX etc.) * HostSpec: host specification (Hostname [: port])
* Database: Database to Use on the dbms server
* Username: User Name for login
* Password: Password for login
*
* The Format of the support DSN IS in ITS Fullest Form:
*
* PhpoType (dbsyntax): // username: Password @ protocol hostspec / database
*
* MOST VARIATIONS is ALLOWED:
*
* PhpoType: // username: Password @ protocol hostspec: 110 // usr / db_file.db
* PhpoType: // username: password@ hostspec / database_name
* PhpoType: // username: Password @ hostspec
* PhpoType: // username @ hostspec
* PhpoType: // Hostspec / Database
* PhpoType: // HostSpec
* PhpoType (dbsyntax)
* PhpoType
Now supported databases (in the phpoType DSN section):
MySQL -> mysql
Pgsql -> PostgreSQL
Ibase -> Interbase
MSQL -> Mini SQL
MSSQL -> Microsoft SQL Server
OCI8-> Oracle
7/8/8
i
ODBC -> ODBC (Open Database Connectivity)
Sybase -> Sybase
IFX -> Informix
FBSQL -> FrontBase
Note Not all database features support, you can get a detailed list from the
3.2 Execute Database
// ONCE you have a valid db object
...
$ SQL = "SELECT * from clients";
// if the query is a "SELECT", $ db-> query Will Return
// a db Result Object on success.
// Else IT Simply Will Return A DB_OK
// ON Failure It Will Return A DB Error Object.
$ result = $ db-> query ($ SQL);
// ALWAYS CHECK THAT $ Result Is Not An Error
IF (DB :: ISERROR ($ results)) {
Die ($ results-> getMessage ());
}
....
?>
3.3 Get the data of SELECT
3.3.1 Getting Data Functions
// ONCE you have a valid db results Object
...
// Get Each Row of Data On Each Iteration Until
// there is no more rows
While ($ row = $ result-> fetchrow ()) {$ ID = $ row [0];
}
?>
In addition to fetchrow (), you can also use Fetchinto () to insert the value of $ ROW directly.
...
While ($ results-> fetchinto ($ row) {
$ ID = $ row [0];
}
?>
3.3.2 Select the format of the acquisition data
The acquisition mode has DB_FETCHMODE_ORDERED, DB_FETCHMODE_ASSOC and DB_FETCHMODE_OBJECT.
Example of the result of the acquisition data method returned:
$ RES = $ DB-> Query ('SELECT ID, NAME, Email from Users');
$ ROW = $ RES-> Fetchrow ($ MODE);
// with $ mode = db_fetchmode_ordered
// The default behavior is to return an order array.
$ row = array (
0 =>
1 =>
2 =>
);
$ ID = $ row [0];
// with $ mod = db_fetchmode_assoc
// Returns An Associative Array With Column Names as Array Keys:
$ row = array (
'id' =>
'name' =>
'email' => ); $ ID = $ row ['id']; // with $ mode = db_fetchmode_object // Returns A db_row Object with column names as profment: $ row = db_row object ( [ID] => [Name] => [email] => ) $ ID = $ row-> id; ?> 3.3.3 Setting the format for obtaining data You can use the fetchrow () / fetchinto () method or set a default mode for your DB instance.
... // 1) Set The Mode Per Call: While ($ row = $ result-> fetchrow (db_fetchmode_assoc) { [..] } While ($ Result-> Fetchinto ($ ROW, DB_FETCHMODE_ASSOC) { [..] } // 2) Set The Mode for All Calls: $ DB = DB :: Connect ($ DSN); // this Will Set a default fetchmode for this pear db instance // (for all queries) $ dB-> setfetchmode (db_fetchmode_assoc); $ result = $ db-> query (...); While ($ row = $ result-> fetchrow ()) { $ ID = $ row ['id']; } ?> 3.3.4 Control Getting Data Quantity At the same time, PEAR DB obtains data with additional parameters, you can use a numeric parameter to get the required amount of data. This is especially useful at this time you just need to get a part of the data (such as when making a paging program)
... // the row to start fetching $ from = 50; //how yfults per page $ RES_PER_PAGE = 10; // the last row to fetch for this page $ to = $ from $ res_per_page; FOREACH ($ from, $ to) AS $ ROWNUM) { IF (! $ row = $ res-> fetchrow ($ FETCHMODE, $ ROWNUM) { Break; } $ ID = $ row [0]; .... } ?> 3.3.5 Clear results, release variables When you complete the query, you can use the free () method to end:
... $ results = $ db-> query ('select * from clients'); While ($ row = $ result-> fetchrow ()) { ... } $ results-> free (); ?> 3.4 Quick Retrieve Data When you no longer want to use the fetchrow () method to get the data, Pear DB provides some special ways to return to the desired data via the SQL statement. These methods are: getone, getrow, getCol, getAssoc and getall. There are some examples:
Require_once 'db.php'; $ DB = DB :: Connect ('pgsql: // postgres @ UNIX localhost / clients_db'); / / -------------------------------------------------------------------------------------------- ----------- // Getone Retrieves The First Result of The First Column // from a query $ nuMrows = $ db-> getone ('select count (id) from clients "; / / -------------------------------------------------------------------------------------------- ----------- // GetRow Will Fetch The First Row and return it as an array $ SQL = 'SELECT Name, Address, Phone from Clients Where ID = 1'; IF (is_ARRAY ($ row)) {$ ar = $ db-> getrow ($ SQL)) List ($ Name, $ Address, $ Phone) = $ row; } / / -------------------------------------------------------------------------------------------- ----------- // GetCol Will Return an Array with the data of the // SELECTED Column. It accepts the column number to retrieve // as the second param. // The next sentence Could Return for Example: // $ all_client_names = array ('stig', 'jon', 'colin "); $ all_client_names = $ db-> getcol ('select name from clients "; / / -------------------------------------------------------------------------------------------- ----------- // Other functions are: getAssoc () and getAll (). // for the moment refer to their in-line documentation // at pear / db / common.php / / -------------------------------------------------------------------------------------------- ----------- ?> "Get * () series method" can do a lot of things for you, including: initiating a query, getting data, and clearance results. Note that all PEAR DB functions may return a Pear DB_ERROR object. 3.5 Get more information from query (Numrows, Numcols, AffectedRows, TableInfo) More useful data information can be obtained from the query result via the PEAR DB. These methods are: NumRows (): The number of all data is returned through a "SELECT" query. Numcols (): Returns all columns through a "SELECT" query. AffectedRows (): Operate through ("INSERT", "Update" or "delete") operation returns all affected data lines. TableInfo (): Returns an array containing data information through a "SELECT" query. Example:
... $ DB = DB :: Connect ($ DSN); $ SQL = 'SELECT * from Clom Cliants'; $ RES = $ DB-> Query ($ SQL); // don't forget to check if the return result from your // Action is a pear error Object. if you get a error message // Like 'DB_ERROR: Database Not Capable', Means That // Your Database Backend Doesn't Support this Action. // // Number of Rows Echo $ RES-> Numrows (); // Number of cols Echo $ RES-> Numcols (); // Table Info Print_r ($ RES-> TableInfo ()); // affected rows $ SQL = "Delete from Clients"; // Remember That this Statement Won't return A Result Object $ dB-> query ($ SQL); Echo 'i Have deleted'. $ db-> affectedRows (). 'clients' ?> 3.6 Automatic growth (SEQUENCES) SEQUENCES provides a unique ID identifier for the data line. If you are familiar with MySQL, you can imagine it as Auto_Increment. It is very simple, first you get an ID, then insert the data you need to record in this ID. You can set more Sequences for your table, just ensure the same sequence in any special table.
... // Get An ID (if the sequence doesn't exist, it will be create) $ ID = $ db-> nextId ('mysequence "; // use the ID in your insert query $ RES = $ DB-> Query ("INSERT INTO MyTable (ID, Text) Values ($ ID, 'FOO')") ... ?> 3.7 Prepare & Execute / Excutemultiple
// unnedested code !!! // // EXAMPLE INSERTING DATA $ alldata = array ( Array (1, 'One', 'En'), Array (2, 'two', 'to'), Array (3, 'Three', 'TRE'), Array (4, 'Four', 'Fire') ); $ sth = $ dbh-> prepare ("INSERT INTO NUMBERS VALUES (?,?,?)"; Foreach ($ alldata as $ row { $ dbh-> execute ($ st, $ row); } // Here's An Example of a File PlaceHolder: $ myfile = "/tmp/image.jpg"; $ sth = $ dbh-> prepare ('INSERT INTO IMAGES (?, &)); $ dbh-> Execute ($ Sth, Array ("this is me", $ myfile); // after i commit a bugfix That i Have ON My Laptop, You Can Use // Parameter Arrays in the getxxx methods TOO: $ VER = $ dbh-> getone ("SELECT StableVersion from packages where name =?", Array ($ package)); ?> 3.8 AutoCommit, Commit and Rollback
// Examples Here ?> 4. List of available methods
/ * * From the db_ (driver) Objects * / // Get The Object with, IE: $ DB = DB :: Connect ('mysql: // user: pass @ localhost / my_db'); // set options $ dB-> setErrorhandling (); $ db-> setfetchmode (); // information $ dB-> affectedRows (); $ dB-> TableInfo (); // Database manipulation $ db-> query (); // Data Fetch $ db-> nextId (); $ db-> getone (); $ db-> getrow (); $ db-> getcol (); $ dB-> getAssoc (); $ dB-> getAll (); // Place Holders and Execute Related $ dB-> quote (); $ dB-> prepare (); $ dB-> execute (); $ dB-> executemultiple (); // Transactions $ dB-> autocommit (); $ dB-> commit (); $ dB-> rollback (); // disconnection $ dB-> disconnect (); / * * From DB_Result Objects * / // Get The Object with, IE: $ RES = $ db-> query ('select * from fom foo'); // Data Fetch $ RES-> fetchrow (); $ rES-> fetchinto (); // Result Info $ RES-> Numcols (); $ RES-> Numrows (); $ RES-> TableInfo (); // free $ rES-> free (); / * * From DB_ERROR OBJECTS * / // Get The Object with, IE: $ error = $ db-> query ('select * from no_table "); $ Error-> getMessage (); $ Error-> getDebuginfo (); $ Error-> toString (); ?> 5. Error handling mechanism 5.1. Get the error message from Pear DB Error All errors returned from Pear DB are pear errors. This has a method to collect:
... $ RES = $ db-> query ('select * from no_table "); IF (DB :: ISERROR ($ RES)) { // get the portable error String Echo $ RES-> GetMessage (); } ?> 4.2 Debug Pear DB Errors Pear DB uses a lightweight error message system to report an error to the user. It has brought great advantages to other languages, simply translating the error message into other languages or for a special error. But for developers, these tips are provided with very useful information. If you want to get a real data to process an error, you can use the getDebuginfo () method:
$ SQL = 'SELECT * from NO_TABLE'; IF (DB :: ISERROR ($ RES = $ DB-> Query)) { // Get the native backend error // and the last query Echo $ RES-> getDebuginfo (); ?> When a PHP function is wrong, an error prompt is printed. This mechanism in the PEAR is blocked. But sometimes you may need to capture some error messages in your code. You can use the set_error_handler PHP function to get information from PHP Manual. Simple example:
// What Messages to Report ERROR_REPORTING (E_ALL ^ E_NOTICE); // this function will handle all reported ErrorS Function my_ERROR_HANDLER ($ Errno, $ Errstr, $ Errfile, $ Errline) { Echo "in $ Errfile, Line: $ Errline / N } Set_error_handler ('my_error_handler); $ DB = DB :: Connect ('pgsql: // postgres @ localhost / no_db'); ... ?> 5.3 Automatic handling of errors As you can see, Pear DB provides a wide range of error detection and reporting mechanisms, which must be checked by the returned data results, is wrong. Pear DB also takes care of us to avoid this painful job, providing a flexible system that automatically calls the corresponding measures when an error occurs. These possible measures include: Returns the error object (pear_error_return). This is the default. Print Error (Pear_ERROR_PRINT) Print Error Information and ignore the execution (pear_error_die) Use the PHP function trigger_ERROR () to list the error object to a function or class method ( Pear_error_callback) Simple example:
Require_once 'db.php'; // set the default action to take On Error Pear :: seterrorhandling (pear_error_die); // from here you don't need need to check errors any more $ db = db :: connect ('pgsql: // postgres @ localhost / my_database "); $ RES = $ db-> query ('select id from no_table "); // at this point the execution is aborted and the error message is raisen ... ?> Advanced example:
// define the app environment (this is: what errors you want to output) Define ('Debug_env', true); // this function will handle all ErrorS Function Handle_pear_ERROR ($ Error_Obj) { // be verbose While Developing The Application IF (debug_env) { Die ($ error_obj-> getMessage (). "/ n". $ error_obj-> getDebugInfo ()); // Dump a silly message if the site is in production } Else { Die ('Sorry You Request Can Not Be Processed Now. Try Again Later'); } Require_once 'db.php'; // on error, call the "handle_pear_error" Function BACK // you can also use an Object As Pear Error Handler SO: // seterrorhandling (pear_error_callback, array ($ Object, 'Method_name "); Pear :: seterrorhandling (pear_error_callback, 'Handle_pear_error'); $ DB = DB :: Connect ('pgsql: // postgres @ localhost / site_db'); $ RES = $ db-> query ('select id from no_table "); // at this point the execution is aborted and the "handle_pear_error" // function is caled with the error Object As ITS First Argument While ($ row = $ r-> fetchrow ()) { ... } ... ?> The following provides a good idea for the extended error mechanism:
ERROR_REPORTING (E_ALL ^ E_NOTICE); // this function will handle all ilrors reported by PHP Function PHP_ERROR_HANDLER ($ Errno, $ Errstr, $ Errfile, $ Errline) { Die ("IN $ Errfile, Line: $ Errline / N } Set_error_handler ('php_error_handler); // this function will catch errors generated by Pear, // transform it to php errors and trigger the to the php_error_handler Function pear_error_handler ($ Err_Obj) { $ error_string = $ err_obj-> getMessage (). ' Trigger_ERROR ($ Error_String, E_USER_ERROR); } Require 'db.php'; Pear :: seterrorhandling (pear_error_callback, 'pear_error_handler); // force an error $ DB = DB :: Connect ('pgsql: // postgres @ localhost / no_db'); ... ?> Finished>
$ Errstr
$ Errstr");
'. $ error_obj-> getDebugInfo ();
