PHP learning notes-exception capture and handling of pdophp learning notes

1. What is PDO? PDO (PHP Data Object) is a lightweight PHP Data connection expansion with compatible interfaces. It is an official php pecl Library released with PHP 5.1, PHP 5's object-oriented support is required, so it cannot be used in earlier versions. It provides a data access abstraction layer that is independent of the specific database type and provides a unified operation interface for the databases it supports. Currently, the supported databases include Cubrid , Freetds/Microsoft SQL Server/SYBASE , Firebird/Interbase 6 , IBM DB2 , IBM Informix Dynamic Server , MySQL 3.x/ 4.x/ 5.x , Oracle call interface , ODBC V3 (IBM DB2, unixodbc and Win32 ODBC) , PostgreSQL , SQLite 3 and SQLite 2 , Microsoft SQL Server/SQL azure. Because PDO is a unified database operation interface at the underlying layer, it can be used to implement more advanced database operations, such as storage process scheduling. 2. PDO instance The following example shows how to use PDO to connect to the SQLite database by page. The query result is output as JSON data.

<? PHP
 $ Cat = Isset ( $ _ Get ['Cat'])?$ _ Get ['Cat']: "1 ";
 $ PG = Isset ( $ _ Get ['Pg '])? $ _ Get ['Pg ']: "1 ";

 $ Limit = 10;
 $ Dbname = 'Shelf. SQLite ';
 Try {
 $ DB = New PDO ("SQLite :". $ Dbname );
$…… = $ DB -> Prepare ('select * from book where cat_id =: Id limit: Offset,: Limit ', Array (
PDO: attr_cursor => PDO: cursor_fwdonly
));

 $ Result = $…… -> Execute ( Array (
': Id' => $ Cat ,
': Offset' => ( $ PG -1 )* $ Limit ,
': Limit' => $ Limit 
));
 $ List = Array ();
 $ Query = $ DB -> Query ('select count (*) from book where cat_id = '. $ Cat )-> Fetch (); //  Only 1 row  
  $ List ["Count"] = $ Query [0];
If ( $ Result ){
 While ( $ Row = $…… -> Fetch (PDO: fetch_assoc )){
 $ List ["Books"] [] = $ Row ;
}
} Else {
 Print_r ( $ DB -> Errorinfo ());
}

$ DB = Null ;

 Echo   Str_replace ('\/', '/', Json_encode ( $ List ));

} Catch (Pdoexception $ Ex ){
 Print_r ( $ Ex );
}
?>

3. constants in PDOSome static constants are defined in the PDO library. These constants are called in the form of PDO: <Name>. For example, prepare () statements are often used as follows:

 
$ Query=$ DB-> Prepare ('select * from book where cat_id =: Id limit: Offset,: Limit ',Array(
PDO: attr_cursor => PDO: cursor_fwdonly
));
 

Here, PDO: attr_cursor and PDO: cursor_fwdonly are all PDO constants. Here, we set the database cursor type to forward only.

4. Connection and connection management in PDO connections in PDO are created by creating instances of the PDO class. You must provide parameters such as the data source name (DSN) and the optional user name and password. In this process, it is worth noting that if an exception occurs, the Zend engine of PHP displays the specific error information by default, which brings about a problem: connection information (data location, user name, password, etc.) may be leaked. Therefore, to prevent such unfortunate events, you must explicitly capture exceptions, whether using try... catch statements or using set_exception_handler () functions to hide some sensitive data. The difference is that the set_exception_handler () after Code is executed, try... in the catch format, the code after the exception will continue to be executed, as try... the catch statement is generally intended (for more information, see exception capture and handling in PHP learning notes ) .

<? PHP
$ DB=NewPDO ('mysql: host = localhost; dbname = test ',$ User,$ Pass);
//Use the new database connection.
//......
//The connection is active in the lifecycle of the PDO instance.The connection should be closed after use. If php does not close the connection at the end of the code, it will occupy part of the memory.
$ DB=Null;
?>
 

Of course, this is not the case. Sometimes we may need a permanent connection. The specific method is to add a parameter to the PDO constructor:

<? PHP
$ DB=NewPDO ('mysql: host = localhost; dbname = test ',$ User,$ Pass,Array(
PDO: attr_persistent =>True
));
?>
 

A permanent connection can span the code. It is cached instead of being closed when the code is executed, so that another piece of code with the same permissions can be reused. In this way, you do not have to create a new connection every time, saving a lot of things and speeding up the website. 5. query operations in PDO: exec/Query/Prepared statement There are three methods to perform query operations in PDO: exec, query, and prepared statement. The three methods have their own advantages and disadvantages. First, Exec. (1) PDO: exec () is generally used to execute an SQL statement once and return the number of rows affected by the query. It is not applicable to select statements. If you need to use a SELECT statement at one time, you can use PDO: Query (); or multiple statements. If you need to use it multiple times, consider using PDO: Prepare ().

(2)PDO: Query () is used to execute a SELECT statement.Pdostatement: Fetch () statement to retrieve the result, otherwise the next PDO: Query () operation will report an error. In2. PDO instanceTo obtain the total amount of data to be queried, the PDO: Query () statement is used.

(3)Pdostatement indicates a prepared statement. After execution, a group of associated arrays is returned.If a type of query (the query structure is similar but the specific parameters are different) needs to be parsed and executed multiple times, you can use prepared statement first to prepare for the execution of the specific query and avoidAnalysis, compilation, and optimizationTo reduce the resource usage and improve the running efficiency. By performing the prepare operation on the database, the pdostatement data type is returned, and further operations such as execute and fetch are carried out on the basis.

 $…… = $ DB -> Prepare ('select * from book where cat_id =: Id limit: Offset,: Limit ', Array (
PDO: attr_cursor => PDO: cursor_fwdonly
));
 //  Use $ limit1 to get a result.  
  $ Result1 = $…… -> Execute ( Array (
': Id' => $ Cat ,
': Offset' => ( $ PG -1 )* $ Limit1 ,
': Limit' => $ Limit1 
));

 //  Use $ limit2 to get another result.  
  $ Result2 = $…… -> Execute ( Array (
': Id' => $ Cat ,
': Offset' => ($ PG -1 )* $ Limit2 ,
': Limit' => $ Limit2 
));

Another benefit of using prepared statement is that no quotation marks are used in the statement. The PDO driver has automatically completed this operation to prevent the risk of SQL injection attacks. The query statement can contain the name (: Name) and question mark (?) Parameters placeholder, respectively, will be passed into the value with associated array and indexed array.

 //  Input parameters with location parameters  
  $ Stmt = $ DBH -> Prepare ("insert into registry (name, value) values (?, ?) ");
 $ Stmt -> Bindparam (1, $ Name );
 $ Stmt -> Bindparam (2, $ Value );

 //  Input parameters by name  
  $ Stmt = $ DBH -> Prepare ("insert into registry (name, value) values (: name,: Value )");
 $ Stmt -> Bindparam (': name ',$ Name );
 $ Stmt -> Bindparam (': value ', $ Value );

 $ Name = 'One ';
 $ Value = 1;
 $ Stmt -> Execute ();

 //  //////////////////////////////////////// ///
// It can also be implemented in this way
// Use the location parameter, indexed Array  
  $ Stmt = $ DBH -> Prepare ("insert into registry (name, value) values (?, ?) ");
 $ Name = 'One ';
 $ Value = 1;
 $ Stmt -> Execute ( Array ( $ Name , $ Value ));

 //  Input parameters by name, associated array  
  $ Stmt = $ DBH -> Prepare ("insert into registry (name, value) values (: name,: Value )");
 $ Name = 'One ';
 $ Value = 1;
 $ Stmt -> Execute ( Array (': Name' => $ Name , ': Value' => $ Value ));

Special note: The placeholder in the query statement should occupy the position of the entire value. If there is a fuzzy query symbol, do this:

  //   placeholder must be used in the place of the whole value   
   $ stmt  =  $ DBH -> prepare (" select * from registry where name like? "); 
  $ stmt -> execute ( array  (" % $ _ Get  [name] % ")); 
  //   here is the problem.   
   $ stmt  =  $ DBH -> prepare (" select * from Registry where name like '%? % '"); 
  $ stmt -> execute ( array  ($ _ Get  ['name']); 

This article is from pinocchioatbeijing . (focus on front-end technologies to pursue beautiful life) blog in the blog Park, Article URL: http://www.cnblogs.com/pinocchioatbeijing/archive/2012/03/20/2407869.html, reprinted please note, and you are welcome to give me some advice.