Pear DB Pear DB 新手入门指南教程

存放Pear的目录：

# cd /usr/local/lib

用“phpfi“口令登录:

# cvs -d :pserver:cvsread@cvs.php.net:/repository login

用以下命令得到所有的pear文件，同时也可以用来更新已经下载的文件。其他的参数有："today", "last month",等。我推荐用"last week"参数，因为一般bugs的提交和修改都是每周一次。 

# cvs -d :pserver:cvsread@cvs.php.net:/repository export -D "last week" php4/pear

编辑php.ini文件加上下面一段在include_path处： /usr/local/lib/php4/pear 如果没有修改的权限，可以通过这条语句在代码中实现： ini_set('include_path', 'path_to_pear');

<?php
 // 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 persistent connection:
 // $db = DB::connect($dsn, true);
 $db = DB::connect($dsn);

 // With DB::isError 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();
 ?>

     *  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 supplied DSN is in its fullest form:
      *
      *  phptype(dbsyntax)://username:password@protocol+hostspec/database
      *
      * Most variations are allowed:
      *
      *  phptype://username:password@protocol+hostspec:110//usr/db_file.db
      *  phptype://username:password@hostspec/database_name
      *  phptype://username:password@hostspec
      *  phptype://username@hostspec
      *  phptype://hostspec/database
      *  phptype://hostspec
      *  phptype(dbsyntax)
      *  phptype

mysql  -> MySQL
 pgsql  -> PostgreSQL
 ibase  -> InterBase
 msql   -> Mini SQL
 mssql  -> Microsoft SQL Server
 oci8   -> Oracle 7/8/8i
 odbc   -> ODBC (Open Database Connectivity)
 sybase -> SyBase
 ifx    -> Informix
 fbsql  -> FrontBase

<?php
 // 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 ($result->getMessage());
 }
 ....
 ?>

<?php
 // Once you have a valid DB Result object
 ...
 // Get each row of data on each iteration until
 // there is no more rows
 while ($row = $result->fetchRow()) {
     $id = $row[0];
 }
 ?>

<?php
 ...
 while ($result->fetchInto($row)) {
     $id = $row[0];
 }
 ?>

<?php
 $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 ordered array.
 $row = array (
     0 => <column "id" data>,
     1 => <column "name" data>,
     2 => <column "email" data>
 );

 $id = $row[0];

 //With $mode = DB_FETCHMODE_ASSOC
 //Returns an associative array with column names as array keys:
 $row = array (
     'id'    => <column "id" data>,
     'name'  => <column "name" data>,
     'email' => <column "email" data>
 );

 $id = $row['id'];

 //With $mode = DB_FETCHMODE_OBJECT
 //Returns a DB_row object with column names as properties:
 $row = db_row Object 
 (
     [id]    => <column "id" data>,
     [name]  => <column "name" data>,
     [email] => <column "email" data>
 )

 $id = $row->id;
 ?>

<?php
 ...
 // 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'];
 }
 ?>

<?php
 ...
 // the row to start fetching
 $from = 50;
 // how many results per page
 $res_per_page = 10;
 // the last row to fetch for this page
 $to = $from + $res_per_page;
 foreach (range($from, $to) as $rownum) {
     if (!$row = $res->fetchrow($fetchmode, $rownum)) {
         break;
     }
     $id = $row[0];
     ....
 }
 ?>

<?php
 ...
 $result = $db->query('SELECT * FROM clients');
 while ($row = $result->fetchRow()) {
     ...
 }
 $result->free();
 ?>

<?php
 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 = $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
 // -----------------------------------------------------------
 ?>

<?php
 ...
 $db = DB::connect($dsn);
 $sql = 'select * from clients';
 $res = $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 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';
 ?>

<?php
 ...
 // Get an ID (if the sequence doesn't exist, it will be created)
 $id = $db->nextID('mySequence');

 // Use the ID in your INSERT query
 $res = $db->query("INSERT INTO myTable (id,text) VALUES ($id,'foo')");
 ...
 ?>

<?php
 // 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 as $row) {
     $dbh->execute($sth, $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));
 ?>

<?php
 //examples here
 ?>

<?php
 /*
 * 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 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();
 ?>

<?php
 ...
 $res = $db->query('select * from no_table');
 if (DB::isError($res)) {
     // get the portable error string
     echo $res->getMessage();
 }
 ?>

<?php
 $sql = 'select * from no_table';
 if (DB::isError($res = $db->query($sql))) {
     // get the native backend error
     // and the last query
     echo $res->getDebugInfo();
 }
 ?>

<?php
 // 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');
 ...
 ?>

<?php
 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');
 $res = $db->query('select id from no_table');
 // at this point the execution is aborted and the error message is raisen
 ...
 ?>

<?php
 // 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 called with the error object as its first argument
 while ($row = $res->fetchRow()) {
     ...
 }
 ...
 ?>

<?php
 error_reporting (E_ALL ^ E_NOTICE);
 // this function will handle all errors reported by PHP
 function 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 them 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::setErrorHandling (PEAR_ERROR_CALLBACK, 'pear_error_handler');
 // force an error
 $db = DB::connect('pgsql://postgres@localhost/no_db');
 ...
 ?>