PDO Learning Notes

1. What is PDO?

PDO (PHP data Object) is a lightweight, interface-compatible PHP data connection extension, is a PHP official PECL library, released with PHP 5.1, requires PHP 5 object-oriented support, and therefore not available on earlier versions. It provides the data to the abstraction layer, has the advantage independent of the specific database type, provides the unified operation interface for the database which it supports. The currently supported databases areCubrid,freetds/microsoft SQL server/sybase,firebird/interbase 6,IBM DB2,IBM Informix Dynamic Serv Er,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, and more. Since PDO is a unified database operation interface implemented at the bottom level, it enables more advanced database operations, such as the scheduling of stored procedures, and so on. 2. PDO InstancesThe following is an example of using PDO to connect to a SQLite database for paging, and the results of the query are output as JSON data.

 <?php 
 $cat = isset ($_get[' cat ') "$_get[' Cat ']:" 1 "; 
 $PG = Isset ($_get[' pg ')? $_get[' PG ']: "1"; 
 
 $limit = ten; 
 $dbname = ' shelf.sqlite '; 
 try {
 $db = new PDO ("SQLite:".) $dbname); 
 $sth = $db->prepare (' select * from book where Cat_id=:id limit:offset,: ' Limit ', Array (
 PDO:: Attr_cursor => ; PDO:: Cursor_fwdonly 
)); 
 
 $result = $sth->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 = $sth->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, which are called in PDO:: <NAME>. This is often used in prepare () statements, for example:

$query = $db->prepare (' select * ' from the 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 the CURSOR type of the database is set to forward only. 4. Connection and connection management in PDOThe connection in PDO is established by creating an instance of the PDO class. You need to provide data source name (DSN) and optional user name and password parameters when creating. In this process it is worth noting that if an exception occurs, PHP's Zend Engine default action is to display the specific error message, which poses a problem: The connection information (data location, user name, password, etc.) may be compromised. Therefore, to prevent such unfortunate things from happening, be sure to catch the exception explicitly, whether it is using the Try...catch statement or the Set_exception_handler () function, to hide some sensitive data. The difference is that the execution of the code after calling Set_exception_handler () terminates, and in the form of Try...catch, the code after the exception continues to execute, just as the try ... The original meaning of the Catch statement is general (more please: PHP Learning note exception capture and processing).

<?php
$db = new PDO (' Mysql:host=localhost;dbname=test ', $user, $pass);
Use the newly established database connection.
//... ...
The connection is active in the life cycle of the PDO instance. This connection should be closed after use, and if you do not do this, PHP closes the connection at the end of the code and consumes a portion of the memory.
$DB = null;
?>

Of course, things are not always the case, and sometimes we may need a permanent connection. This is done by adding a parameter to the PDO constructor:

<?php
$db = new PDO (' Mysql:host=localhost;dbname=test ', $user, $pass, Array (
    Pdo::attr_persistent = True
));
?>

A permanent connection can span code, is not closed when one code is executed, and is cached for reuse by another piece of code with the same permissions. This eliminates the need to create a new connection every time, save a lot of things not to say, but also to speed up the site. 5. Query operation in PDO: exec/query/prepared statement There are three ways to perform query operations in PDO, using Exec, query, and prepared statement, respectively. Three methods have pros and cons, first of all, say exec. (1) pdo::exec () is typically used to execute the SQL statement once, returning the number of rows affected by the query. It does not apply to the SELECT statement, and if it is required to be a SELECT statement once, it can be used with pdo::query () or a multiple-use statement, and if there are multiple use requirements, consider using PDO::p repare (). (2) Pdo::query () is used to execute a SELECT statement, and the result should be removed using the Pdostatement::fetch () statement, or the next pdo::query () will be immediately given an error. In 2.PDO Instancessection, the Pdo::query () statement is used to get the total amount of data queried.
(3) Pdostatement represents a prepared statement statement, and after execution returns the result of a set of associative arrays. If a class of queries (similar to query structure and specific parameters) need to be parsed and executed many times, you can first use prepared statement, which can be prepared for the execution of specific queries, avoid the analysis, compilation, optimization of the cycle, will reduce the resource occupancy rate, thereby improving operational efficiency. By prepare the database, the Pdostatement data type is returned, which allows for further operations such as execute, fetch, and so on.

$sth = $db->prepare (' select * ' from the book where Cat_id=:id limit:offset,: ' Limit ', array (
PDO:: Attr_cursor = PDO:: cursor_fwdonly
));
Get a result with $limit1
$result 1 = $sth->execute (Array (
        ': id ' = $cat,
        ': Offset ' = ($PG-1) * $limit 1,
        ': Limit ' = $limit 1
    ));

Use $limit2 to get another result
$result 2 = $sth->execute (Array (
        ': id ' = $cat,
        ': Offset ' = ($PG-1) * $limit 2,
        ': Limit ' = $limit 2
    ));

Another benefit of using prepared statement is that the quotes are no longer used in the statement, and PDO driver has done this automatically to prevent the risk of SQL injection attacks. The query statement can use the parameter placeholders that contain the name (: name) and the question mark (?), and the values will be passed in with the associated array and the indexed array, respectively.

Parameter with positional parameters
$stmt = $dbh->prepare ("INSERT into REGISTRY (name, value) VALUES (?,?)");
$stmt->bindparam (1, $name);
$stmt->bindparam (2, $value);

Passing in 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 ();

/////////////////////////////////////////////
can also be implemented as such
Parameter with positional parameters, indexed array
$stmt = $dbh->prepare ("INSERT into REGISTRY (name, value) VALUES (?,?)");
$name = ' one ';
$value = 1;
$stmt->execute (Array ($name, $value));

Pass in the parameter 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 attention: The placeholder in the query statement should be the position that occupies the entire value, and if there is a symbol for the fuzzy query, you should do so:

Placeholder must is used in the place of the whole value
$stmt = $dbh->prepare ("select * from REGISTRY where name is like?");
$stmt->execute (Array ("%$_get[name]%"));

So there's a problem here.
$stmt = $dbh->prepare ("select * from REGISTRY where name like '%?% '");
$stmt->execute (Array ($_get[' name '));

PDO Learning Notes