Pear DB Novice Getting Started (Original: "Quick Start Guide To Pear DB")
Translation: Gangchen@wiscom.com.cn
?
table of Contents
1 Introduction---------------------------------------- ---------------------------------------------2
2. ??????? Download, install PEAR ------------------------------------- ------------------------------------2
3 ????????? Using Pear DB ----------------------------------- ---------------------------------------2
3.1.1 ?? Connection, disconnect the database
3.1.2 ?? Execute Database
3.1.3 ?? Get the data of SELECT
3.1.3.1? Get data function
3.1.3.2? Select the format of obtaining data
3.1.3.3? Set the format for obtaining data
3.1.3.4? Controls the number of data acquisition data
3.1.3.5? Clear results, release variables
3.1.4 ?? Quick Retrieve Data
3.1.5? Numrows, Numcols, AffectedRows, TableInfo
3.1.6 ?? SEQUENCES
3.1.7 ?? prepare & Execute / ExcuteMultiple
3.1.8? AutoCommit, Commit and Rollback
4. ??????? Usage method list ------------------------------------ ------------------------------------- 10
5 ????????? Error handling mechanism ----------------------------------- -------------------------------------- 12
5.1 ??????? get an error message from Pear DB ERROR
5.2 ???????? Debug Pear DB ErrorS
5.3 ??????? Passing automatic processing
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:
Store the directory of the PEAR:
# cd / usr / local / lib
Log in with "phpfi" password:
# CVS -D: PServer: cvsread@cvs.php.net: / repository login
Get all PEAR files with the following command, and can also be used to update files that have been 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 the code: INI_SET ('Include_path', 'Path_TO_Pear');
Get full documentation 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? '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 Persistent 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 :: isrror ($ 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 backed 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 IN ITS Fullest Form:? ???? * ????? * ?? phpoType (dbsyntax): // username: Password @ protocol hostspec / database ????? * ????? * Most Variations are allowed: ???? ? * ????? * ?? phpoType: // username: password @ protocol hostspec: 110 // usr / db_file.db ????? * ?? phpoType: // username: password @ hostspec / data_name? ???? * ?? phpoType: // username: password @ hostspec ????? * ?? phpoType: // username @ hostspec ????? * ?? phpoType: // Hostspec / Database ???? ? * ?? phpoType: // HostSpec ????? * ?? phpoType (dbsyntax) ????? * ?? phpo now supports the database (in the phpType 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 that all database features are supported,
The root directory> / db / status gets a detailed list.
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 ($ result))? {???????? Die? ($ Results-> getMessage ());} ....?>
?
?
3.3 ??????? I got the data of SELECT 3.3.1 ?? Get data function
?
// ONCE you have a valid db results Object ... // Get Each Row Of Data on each ity ity? ($ Row? =? $ Results-> fetchrow ())? {?? ??? $ ID? =? $ row [0];}?>
?
In addition to fetchrow (), you can also use Fetchinto () to insert the value of $ ROW directly.
?
... While? ($ results)? {???? $ ID? =? $ row [0];}?>
?
?
3.3.2 ?? Select the format for obtaining 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:
?
$ r? =? $ db-> query ('select ID, name, email from users "); $ row? =? $ r-> fetchrow ($ mode); // with $ mode = db_fetchmode_ordered // the default behavior Is to return an orderd array. $ row? =? array? (???? 0? =>?
?
?
3.3.3 ?? Set the format of 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? =? $ Results-> fetchrow (db_fetchmode_assoc))? {???? [..]} while? ($ Results-> 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); $ results? =? $ db-> query (...); while? ($ row? = $ results-> Fetchrow ())? {???? $ ID? =? $ row ['id'];}?>?
?
3.3.4 ?? Control acquisition 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 much results per page $ res_per_page? =? 10; // the last row to fetch for this page $ to? = $ from? ? $ r? ($ from, $ to)? ($ roun)? {???? f? ($ rotchrow ($ 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:
?
... $ result? =? $ db-> query ('select * from clients'); while? ($ row? = $ results-> fetchrow ())? {???? ...} $ Result -> 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 :: connection ('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 FirstROW 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? =? $ dB-> getRow ($ SQL))? { ???? list ($ name, $ address,? $ limited? =? $ 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 ??????? 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 clients'; $ r? =? $ db-> query ($ sql); // Don't forget to check if the returned 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 does not support this action // // Number of Rows Echo $ $ $ $ $ $ $ $ $ $ $ $ $ $ r> 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 $ r? =? $ db-> query ("INSERT? INTO? MyTable? (id, text)? Values? ($ ID, 'foo')"); ...?>
?
?
3.7 ???????? prepare & Execute / ExcuteMultiple
?
// untested 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? $ 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. ??????? A 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: $ R? =? $ DB-> Query ('SELECT * from fom foo');? // Data fetch $ res-> fetchrow (); $ res-> fetchinto (); // result info $ res-> numcols (); $ r > NumRows (); $ r -> TableInfo (); // free $ r -> 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 an error message from Pear DB ERROR
All errors returned from Pear DB are pear errors. This has a method to collect:
?
... $ rs? =? $ db-> query ('select * from no_table'); if? (db :: iesrror ($ r))? {???? // Get the portable error string ??? ? Echo? $ r -> 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 ($ R? =? $ DB-> Query ($ SQL)))? {???? // Get The Native Backend Error? ??? / a? $ $ r ???? echo? $} ();}?>?
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 $ Errstr";} set_error_handler? ('my_error_handler'); $ dB? =? DB :: Connect ('pgsql: // postgres @ localhost / no_db '); ...?>
?
?
5.3 ?? Passing automatic processing
?????? As you can see, Pear DB provides a wide range of error detection and reporting mechanisms, which must check the return 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 the error message and ignore the execution (pear_ERROR_DIE)
Use a PHP function trigger_error () to list the error (pear_error_trigger)
Pass the error object to a function or class (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 to check errors any more $ db? =? DB :: Connect 'pgsql: // postgres @ localhost / my_database'); $ R? =? $ 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 error "define? ('Debug_env',? True); // this function will handle all errors function? Handle_pear_error? ($ Error_obj)? {???? // be verbose while development 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 :: SETEERRORHANDLING (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? =? $ {???? ...} ...??>?
The following provides a good idea for the extended error mechanism:
?
ERROR_REPORTING? (E_ALL? ^? e_notice; // this function will handle all error? php_error_handler? ($ Errno,? $ Errstr,? $ Errfile,? $ Errline)? {???? Die? ( "IN? $ ERRFILE,? LINE:? $ Errline / N $ Errstr");} set_error_handler? ('Php_error_handler'); // this function will catch errors generated by pear, // transform it to php errors and trigger the To PHP ERRORS AND TOGER TO THE PHP_ERROR_HANDLER FUNCTION? Pear_Error_Handler? ($ ERR_OBJ)? {???? $ error_string? =? $ err_obj-> getMessage ()?.? '?? $ error_obj-> getDebugInfo (); ???? Trigger_ERROR? ($ Error_String,? E_USER_ERROR);} Require? 'db.php'; pear :: setrrorhandling? (pear_error_callback,? 'pear_error_handler'); // force an error $ db? =? DB :: Connect ('pgsql: / / postgres @ localhost / no_db '); ...?>?
