Introduction to the PDO of PHP

What is PDO?

The POD (PHP Data Object) extension is added in PHP5, and PHP6 connects the database with PDO, and all non-PDO extensions will be removed from the extension in PHP6. The extension provides PHP built-in class PDO to access the database, different databases use the same method name, to solve the problem of non-uniform database connection.

I was configured to do development under Windows.

The goal of PDO
Provides a lightweight, clear, and convenient API to unify the common features of various RDBMS libraries, without excluding more advanced features. Provides an optional, large degree of abstraction/compatibility through PHP scripting.

Features of PDO:

Performance. PDO has learned from the outset the successes and failures of existing database extensions. Because PDO's code is brand new, we have the opportunity to start designing performance again to take advantage of the latest features of PHP 5. Ability. PDO is designed to provide the basis for common database functionality while providing easy access to the unique features of the RDBMS. Simple. PDO is designed to make it easy to use your database. The API does not force you into your code, and it clearly shows the process of each function call. The runtime is extensible. The PDO extension is modular, allowing you to load drivers at run time for your database backend without having to recompile or reinstall the entire PHP program. For example, the Pdo_oci extension implements the Oracle database API instead of the PDO extension. There are also drivers for MySQL, PostgreSQL, ODBC, and Firebird, and more drivers are still being developed.

Installing PDO
I am here for the PDO extension under Windows development, if you want to install the configuration under Linux, please look elsewhere.
Version requirements:

php5.1 and later versions of the package have been brought in;

php5.0.x to pecl.php.net download, put in your extension library, PHP is located in the folder of the Ext folder;

The manual says that versions prior to 5.0 cannot run the PDO extension.

Configuration:
Modify your php.ini profile so that it supports PDO. (php.ini this thing is not understood, first figure out, to modify the call your Phpinfo () function shows the php.ini)

Extension=php_pdo.dll in front of the semicolon is removed, the other is a PHP configuration file comment symbol, this extension is necessary.

and down there.
; Extension=php_pdo.dll
; Extension=php_pdo_firebird.dll
; Extension=php_pdo_informix.dll
; Extension=php_pdo_mssql.dll
; Extension=php_pdo_mysql.dll
; Extension=php_pdo_oci.dll
; Extension=php_pdo_oci8.dll
; Extension=php_pdo_odbc.dll
; Extension=php_pdo_pgsql.dll
; Extension=php_pdo_sqlite.dll

The corresponding databases for each extension are:

Driver name supported databases

Pdo_dblib	Freetds/microsoft SQL Server/sybase
Pdo_firebird	Firebird/interbase 6
Pdo_informix	IBM Informix Dynamic Server
Pdo_mysql	MySQL 3.x/4.x
Pdo_oci	Oracle Call Interface
Pdo_odbc	ODBC v3 (IBM DB2, UnixODBC and Win32 ODBC)
Pdo_pgsql	PostgreSQL
Pdo_sqlite	SQLite 3 and SQLite 2

What kind of database would you like to use, as long as you put the appropriate pre-extension comment symbol ";" get rid of it.

Using PDO
I suppose you have already installed MySQL, if not installed, trouble first try to install, my is mysql5.0.22, night passers-by IS MySQL 4.0.26 can also use.

★ Database Connection:
We use the following example to analyze the PDO connection database,

$dbms = ' MySQL '; Database type Oracle with ODI, for developers, using different databases, just change this, don't remember so many functions $host = ' localhost ';//Database host name $dbName = ' Test '; The database used $user = ' root '; Database connection user Name $pass = "; The corresponding password $DSN = "$dbms: host= $host;d bname= $dbName"; // try{ $DBH =newpdo ($dsn, $user, $pass);//Initialize a PDO object to create a database connection object $DBH echo "Connection successful "; /* You can also perform a search operation foreach ($dbh->query (' SELECT * from FOO ') as$row) { Print_r ($row);//You can use Echo ($GLOBAL); To see these values } */ $DBH =null; }catch (pdoexception$e) { Die ("error!:". $e->getmessage (). " "); } The default is not a long connection, if you need a long database connection, you need to add one last parameter: Array (pdo::attr_persistent = True) becomes this: $db =newpdo ($dsn, $user, $pass, Array (pdo::attr_persistent=>true)); ?>

★ Database query:
We have made a query above, and we can use the following query:

$db->setattribute (Pdo::attr_case,pdo::case_upper); Setting properties $rs = $db->query ("SELECT * from foo"); $rs->setfetchmode (PDO::FETCH_ASSOC); $result _arr= $rs->fetchall (); Print_r ($result _arr); ?>

Because the SetAttribute () method is used, the two parameters are placed, and the field names are cast to uppercase. The following is a list of parameters with Pdo::setattribute ():

Pdo::attr_case: Force the column name to become a format, as detailed below (the second parameter):

Pdo::case_lower: Enforces that the column name is lowercase.

Pdo::case_natural: Column names are in the original way

Pdo::case_upper: Force column name to uppercase.

Pdo::attr_errmode: Error prompt.

Pdo::errmode_silent: The error message is not displayed, only the error code is displayed.

Pdo::errmode_warning: A warning error is displayed.

Pdo::errmode_exception: Throws an exception.

Pdo::attr_oracle_nulls (not just ORACLE valid, other databases are also valid):) Specifies the value that the null value returned by the database corresponds to in PHP.

Pdo::null_natural: no change.

Pdo::null_empty_string:empty STRING is converted Tonull.

Pdo::null_to_string:null is converted to an empty STRING.

Pdo::attr_stringify_fetches:convert numeric values to strings when fetching. Requiresbool.

Pdo::attr_statement_class:set user-supplied STATEMENT CLASS derived from pdostatement. Cannot is used with persistent PDO instances. Requiresarray (string classname, Array (mixed Constructor_args)).

Pdo::attr_autocommit (available in OCI, Firebird and MySQL): Whether to autocommit every single statement.

Pdo::mysql_attr_use_buffered_query (available in MYSQL): Use BUFFERED queries.

$rs->setfetchmode (PDO::FETCH_ASSOC) In the example, is a declaration of the return type of Pdostatement::setfetchmode ().
There are the following:
pdo::fetch_assoc--Associative Array Form
Pdo::fetch_num--Digital Index array form
Pdo::fetch_both--both array form, this is the default
Pdo::fetch_obj-In the form of an object, similar to the previous mysql_fetch_object ()

See the Manual for more return type declarations (pdostatement:: Method names).

★ INSERT, UPDATE, delete data,

$db->exec ("DELETE from ' Xxxx_menu ' where mid=43");

Simply summarize the above actions:

The query operation is mainly Pdo::query (), Pdo::exec (), PDO::p repare ().
Pdo::query () is primarily used for operations that have logged results returned, in particular the select operation,
Pdo::exec () is primarily for operations that do not have a result set, such as INSERT, UPDATE, delete, and so on, which returns the number of columns affected by the current operation.
PDO::p repare () is mainly pre-processing operations, need to $rs->execute () to perform preprocessing inside the SQL statement, this method can bind parameters, the function is relatively strong, not this article can be simple to understand, you can refer to manuals and other documents.

The main operations for getting result sets are: Pdostatement::fetchcolumn (), Pdostatement::fetch (), Pdostatement::fetchall ().
Pdostatement::fetchcolumn () is a field that gets the result that specifies the first record, and the default is the first field.
Pdostatement::fetch () is used to get a record,
Pdostatement::fetchall () is to get all recordsets into one, and get results can be set by Pdostatement::setfetchmode to the type that requires the result collection.

There are also two peripheral operations, one of which is Pdo::lastinsertid () and Pdostatement::rowcount (). Pdo::lastinsertid () is the last insert operation returned, and the primary key column type is the final self-increment ID.
Pdostatement::rowcount () is primarily used for pdo::query () and PDO::p Repare () for the result set affected by the delete, INSERT, update operation, for Pdo::exec () Method and select operation are not valid.



★ Transaction and Auto Commit

Now that you've connected to MySQL via PDO, you should understand how PDO manages transactions before you issue queries. If you have not touched a transaction before, you first need to know the 4 characteristics of the transaction: atomicity (atomicity), consistency (consistency), independence (isolation), and persistence (durability), which is ACID. In layman's words, for any work performed in a transaction, even if it is staged in stages, it is guaranteed that the work will be applied securely to the database and will not be affected by other connections when the work is committed. Transactional work can be automatically revoked on request (assuming you haven't committed it yet), making error handling in the script much easier.
A transaction is usually achieved by saving up a batch of changes and making it effective at the same time. The benefit of this is that the efficiency of these updates can be greatly improved. In other words, a transaction can make the script faster and may be more robust (although it is necessary to use the transaction correctly for this benefit).
Unfortunately, not every database supports transactions (MYSQL5 support transactions, Mysql4 I don't know), so when the connection is first opened, PDO needs to run in the so-called "autocommit (auto-commit)" mode. Autocommit mode means that if a database supports transactions, every query that you run has its own implicit transaction, and if the database does not support transactions, there is no such transaction for each query. If you need a transaction, you must use the Pdo::begintransaction () method to start a transaction. If the underlying driver does not support transactions, a pdoexception will be thrown (regardless of the error handling setting: This is always a critical error state). In a transaction, you can use Pdo::commit () or pdo::rollback () to end the transaction, depending on whether the code running in the transaction was successful.
When the script finishes, or when a connection is about to be closed, if there is an unfinished transaction, then PDO will automatically roll back the transaction. This is a security measure that helps to avoid inconsistencies when the script ends abnormally?? If the transaction is not committed explicitly, then it is assumed that there will be inconsistencies somewhere, so a rollback is performed to ensure the security of the data.

Examples from http://www.ibm.com/developerworks/cn/db2/library/techarticles/dm-0505furlong/index.html try{ $DBH =new PDO (' odbc:sample ', ' db2inst1 ', ' ibmdb2 ', Array (pdo_attr_persistent=>true)); echo "connected\n"; $DBH->setattribute (pdo_attr_errmode,pdo_errmode_exception); $DBH->begintransaction (); $DBH->exec ("insert into the staff (ID, first, last) values (at $, ' Joe ', ' Bloggs ')"); $DBH->exec ("INSERT into Salarychange (ID, amount, changedate) VALUES (50000, now ()) "); $DBH->commit (); }catch (Exception $e) { $DBH->rollback (); echo "Failed:". $e->getmessage (); }

In the example above, let's say we create a set of entries for a new employee that has an ID number, which is 23. In addition to entering this person's basic data, we also need to record the employee's salary. The two updates are simple to complete, but by including the two updates in the BeginTransaction () and commit () calls, you can ensure that the changes are not visible to others until the changes are complete. If an error occurs, the catch block can roll back all changes that have occurred since the start of the transaction and print an error message.

It is not always important to make updates in a transaction. You can also emit complex queries to extract data, and you can use that information to build more updates and queries. When a transaction is active, it can be ensured that other people cannot make changes while the work is in progress. In fact, this is not 100% correct, but if you have not heard of the matter before, this introduction is also possible.

★ Preprocessing statements and stored procedures

Many more mature databases support the concept of preprocessing statements. What is a preprocessing statement? You can think of preprocessing statements as a compiled template of the SQL you want to run, which can be customized using variable parameters. Preprocessing statements can provide two major benefits:

A query needs to be parsed (or prepared) only once, but it can be executed multiple times with the same or different parameters. When the query is ready, the database parses, compiles, and optimizes the plan for executing the query. For complex queries, this process takes a long time, and if you need to repeat the same query multiple times with different parameters, the process will greatly reduce the speed of your application. By using preprocessing statements, you can avoid repeating the parse/compile/optimize cycle. In short, preprocessing statements use fewer resources and thus run faster. The parameters provided to the preprocessing statements do not need to be enclosed in quotation marks, and the driver handles these. If the application exclusively uses preprocessing statements, you can ensure that no SQL intrusion occurs. (However, if you still have other parts of the query built on untrusted input, there is still a risk).

Preprocessing statements are so useful that PDO actually breaks the rule set in Target 4: If the driver does not support preprocessing statements, PDO will emulate the preprocessing statements.


Examples: Examples of PDO applications:

$dbms = ' mysql ';//database type for Oracle with ODI, for developers, using different databases, just change this, don't remember so many functions $host = ' localhost ';//Database host name $dbName = ' test ';//Database used $user = ' root ';//database connection user name $pass = ";//the corresponding password $DSN = "$dbms: host= $host;d bname= $dbName"; classdbextendspdo{ Publicfunction__construct () { try{ Parent::__construct ("$GLOBALS [DSN]", $GLOBALS [' User '], $GLOBALS [' Pass ']; }catch (pdoexception$e) { Die ("Error:". $e->__tostring (). " "); } } Publicfinalfunctionquery ($sql) { try{ Returnparent::query ($this->setstring ($sql)); }catch (pdoexception$e) { Die ("Error:". $e->__tostring (). " "); } } Privatefinalfunctionsetstring ($sql) { echo "I have to deal with $sql"; Return$sql; } } $db =newdb (); $db->setattribute (Pdo::attr_case,pdo::case_upper); foreach ($db->query (' SELECT * from Xxxx_menu ') as$row) { Print_r ($row); } $db->exec (' DELETE from ' Xxxx_menu ' where mid=43 '); ?>

