In-depth analysis of php pdo configuration and usage

The PDO (PHP Data Object) extension is added to PHP5. By default, PDO is identified in PHP6 to connect to the database. All non-PDO extensions will be removed from the extension in PHP6. This extension provides PHP built-in PDO class for accessing the database. Different databases use the same method name to solve the problem of inconsistent database connections.
I configured it for development in windows.
The goal of PDO is to provide a lightweight, clear, and convenient API that unifies the common features of different RDBMS libraries, but does not exclude more advanced features.
PHP scripts provide optional abstract/compatibility.
PDO features: performance. From the very beginning, PDO learned from the success and failure of existing database expansion. Because the PDO code is brand new, we have the opportunity to design performance again to take advantage of the latest features of PHP 5.
Capability.
PDO is designed to provide common database functions as the basis and provide convenient access to the unique functions of RDBMS.
Simple.
PDO is designed to make it easy to use databases. The API does not forcibly intervene in your code and clearly shows the process of calling each function.
It can be expanded during runtime.

The PDO extension is modular, allowing you to load drivers at the backend of your database at runtime without re-compiling or re-installing the entire PHP program. For example, the PDO_OCI extension replaces the PDO extension to implement the Oracle database API. There are also some drivers for MySQL, PostgreSQL, ODBC, and Firebird. More drivers are still under development. ■

Install PDO

Version requirements:

Php5.1 and later versions are included in the program package;
Php5.0.x is downloaded to pecl.php.net and put it in your Extension Library, which is the ext folder of the PHP folder;
In the manual, versions earlier than 5.0 cannot run PDO extensions.

Configuration (Windows ):

Modify your php. ini configuration file to support pdo. (php. if you do not understand ini, first make it clear that you need to modify the php code displayed by calling your phpinfo () function. ini)
Set
Remove the semicolon before extension = php_pdo.dll, which is the annotation symbol of the php configuration file. This extension is required.

Down

; 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

Find extension = php_pdo.dll and extension = php_pdo_mysql.dll, and remove the comment ";". The configuration content of the modified two lines is as follows:

Extension = php_pdo.dll
Extension = php_pdo_mysql.dll

The databases corresponding to 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

Which Database do you want to use? You just need to remove the annotator ";" before the corresponding extension.

Configuration (Linux ):

Add:

-- Enable-pdo -- with-pdo-sqlite -- with-pdo-mysql =/usr/local/mysql/bin/mysql_config

Restart apache or iis and create a test page, which is the phpinfo function. You can check whether the PDO module is enabled.

Use PDO (Mysql database example)

Database connection:

We use the following example to analyze the PDO connection to the database and create a PDO_config.php file. When using this file, we can directly include:
<? Php
// OCI is used for Oracle databases. For developers, you only need to modify the following when using different databases:
$ Dbms = 'mysql ';
$ Host = 'localhost ';
// Database host name
$ DbName = 'Test _ test_test ';
// Used database
$ User = 'root ';
// Database connection username
$ Pass = '';
// Password
$ Dsn = "$ dbms: host = $ host; dbname = $ dbName ";

Try {

// Initialize a PDO object, that is, the database connection object $ dbh:
$ Dbh = new PDO ($ dsn, $ user, $ pass );
// Echo "connection successful <br/>";/* you can perform a search operation
Foreach ($ dbh-> query ('select * from Foo') as $ row ){
Print_r ($ row );
// You can use echo ($ GLOBAL); to view these values

 }
$ Dbh = null;
} Catch (PDOException $ e ){
Die ("Error! : ". $ E-> getMessage ()." <br/> ");
}
/*
By default, this is not a persistent connection. If you need a persistent connection to the database, you must add the following parameter: array (PDO: ATTR_PERSISTENT => true:
$ Db = newPDO ($ dsn, $ user, $ pass, array (PDO: ATTR_PERSISTENT => true ));
*/
$ Db = new PDO ($ dsn, $ user, $ pass );
// Create a database connection object $ db
?>

Database query:

We have performed a query above, and we can also use the following query:
<? Php
Include ("./PDO_config.php ");

$ Db-> setAttribute (PDO: ATTR_CASE, PDO: CASE_NATURAL );

// Set attributes
$ Rs = $ db-> query ("SELECT * FROM yourtable ");
$ Rs-> setFetchMode (PDO: FETCH_ASSOC );
$ Result_arr = $ rs-> fetchAll ();

// Obtain all record sets to one variable
Print_r ($ result_arr );

?>

Because the setAttribute () method is used, place the two parameters and forcibly convert the field name to uppercase.

The following lists the parameters with multiple PDO: setAttribute:

PDO: ATTR_CASE:
Force a column name to be in the following format (the second parameter ):
PDO: CASE_LOWER
The column name must be in lower case.
PDO: CASE_NATURAL
The column names follow the original method.
PDO: CASE_UPPER
Force column name to uppercase
PDO: ATTR_ERRMODE
Error message
PDO: ERRMODE_SILENT
No error message is displayed. Only error codes are displayed.
PDO: ERRMODE_WARNING
Show warning error
PDO: ERRMODE_EXCEPTION
Throw an exception
PDO: ATTR_ORACLE_NULLS (not only for ORACLE, but also for other databases ):)
Specifies the value of the NULL value returned by the database in php.
PDO: NULL_NATURAL:
Unchanged.
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 be 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.

In the example

$ Rs-> setFetchMode (PDO: FETCH_ASSOC );
Is PDOStatement: setFetchMode (), which declares the return type.

There are:

PDO: FETCH_ASSOC-join array form
PDO: FETCH_NUM-numeric index array format
PDO: FETCH_BOTH-both arrays are available, which is the default
PDO: FETCH_OBJ-follow the object form, similar to the previous mysql_fetch_object () more return type declaration (PDOStatement: method name) see the manual.★Insert, update, and delete data,
$ Db-> exec ("delete from 'xxxx _ menu 'where mid = 43 & Prime;); briefly summarize the above operations: the query operations are mainly PDO :: query (), PDO: exec (), PDO: prepare ()
PDO: query () is mainly used for operations that return records, especially SELECT operations,
PDO: exec () is mainly used for operations that do not return result sets, such as INSERT, UPDATE, DELETE, and so on. It returns the number of columns affected by the current operation.
PDO: prepare () is mainly used for preprocessing. You need to use $ rs-> execute () to execute the SQL statements in the preprocessing. This method can bind parameters and has powerful functions, this document is not a simple description. You can refer to the manual and other documents. To obtain a result set, perform the following operations: PDOStatement: fetchColumn (), PDOStatement: fetch () PDOStatement: fetchALL ().
PDOStatement: fetchColumn () is a field of the first record specified in the result. The default value is the first field.
PDOStatement: fetch () is used to obtain a record,
PDOStatement: fetchAll () is to obtain all the record sets to one. You can use PDOStatement: setFetchMode to set the type of the desired result set. There are also two peripheral operations: PDO: lastInsertId () and PDOStatement: rowCount (). PDO: lastInsertId () is the last insert operation, and the primary key column type is the last auto-increment ID of auto-increment.
PDOStatement: rowCount () is the result set affected by the DELETE, INSERT, and UPDATE operations on PDO: query () and PDO: prepare :: the exec () method and SELECT operation are invalid.
At this point, you have connected to mysql through PDO. Before sending a query, you should understand how PDO manages the transaction. If you have never touched on a transaction before, you must first understand the four features of the transaction: Atomicity, Consistency, Isolation, and Durability ), ACID. In layman's words, any work performed in a transaction can be safely applied to databases even if it is executed in stages, when a job is submitted, it is not affected by other connections. Transactional work can be automatically revoked based on the request (assuming you have not submitted it), which makes it easier to handle errors in the script.
Transactions are usually implemented by saving up a batch of changes to make them take effect at the same time. This can greatly improve the efficiency of these updates. In other words, a transaction can make the script faster and more robust (but you need to use the transaction correctly to get this benefit ).
Unfortunately, not every Database supports transactions (Mysql5 supports transactions, I don't know about mysql4), so when the connection is opened for the first time, PDO needs to run in the so-called "auto-commit" mode. The automatic commit mode means that if the database supports transactions, each query you run has its own implicit transaction. If the database does not support transactions, no such transaction exists 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 processing settings: this is always a serious 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 is successful.
When the script ends, or when a connection is about to be closed, if there is an unfinished transaction, PDO will automatically roll back the transaction. This is a security measure that helps avoid inconsistencies when the script ends abnormally-if the transaction is not explicitly committed, it is assumed that there will be inconsistencies somewhere, therefore, perform rollback to ensure data security.