Use PDO in PHP

About PDO
PDO is a "database access abstraction layer" that is used to unify various databases (MySQL, MSSQL, Oracle, DB2, PostgreSQL ......) Allows you to easily switch between different databases, making porting between databases easy.

PDO driver

 

Support PDO drivers and corresponding database lists
Driver name	Database to be accessed
PDO_DBLIB	FreeTDS/Microsoft SQL Server/Sybase
PDO_FIREBIRD	Firebird/Interbase 6
PDO_MYSQL	MySQL
PDO_OCI	Oracle
PDO_ODBC	ODBC v3
PDO_PGSQL	PostgreSQL
PDO_SQLITE	SQLite

 

PDO Installation
PDO is released in PHP 5.1. That is to say, PDO is not supported in versions earlier than 5.1. PDO is also the first choice and trend for PHP to process databases in the future.

The following describes how to install PDO:

1. Linux environment

To enable the PDO driver support for MySQL in Linux, you must add the following to the configure command when installing the source code package of PHP (Version 5.1 and later:

-- With-pdo-mysql =/usr/local/mysql // "/usr/local/mysql" is the installation directory of the MySQL server.
-- With-pdo-mysql =/usr/local/mysql // "/usr/local/mysql" is the installation directory of the MySQL server.
2. Windows

Modify the php. ini file and find the following file. Remove the previous ";" (semicolon: represents the comment!

; Extension = php_pdo.dll // All PDO drivers share the extension, which must have
; Extension = php_pdo_mysql.dll // use this line in MySQL
; Extension = php_pdo_mssql.dll // use this line with MSSQL
; Extension = php_pdo.dll // All PDO drivers share the extension, which must have
; Extension = php_pdo_mysql.dll // use this line in MySQL
; Extension = php_pdo_mssql.dll // use this line with MSSQL
Save the php. ini file, restart the Apache server, and view the phpinfo () function. The installation is successful.

 

 

Note: In Windows, the configuration may fail. In this case, install php_pdo_mysql.dll, php_pdo.dll ...... To the Windows installation path.

 

Create a PDO object
The PDO constructor prototype is as follows:

_ Construct (string dsn [, string db_user [, string db_pwd [, array driver_options])
_ Construct (string dsn [, string db_user [, string db_pwd [, array driver_options]) parameter description:
1. dsn (data source name): data source name, which defines the database and driver used;

A. DSN connecting to the MySQL database: mysql: host = localhost; dbname = test // host name: localhost; Database Name: test

B. DSN: oci: dbname = // localhost: 1521/test // host name: localhost; port: 1521; Database Name: test

...... For more DSN information, see the PHP manual.

2. db_user: database user name;

3. db_pwd: Database Password;

4. driver_options: An array used to specify all additional options required for connection

PDO is used to specify all additional options required for the connection.

Option name	DescriptionDescription
PDO: ATTR_AUTOCOMMIT	Determine whether the automatic submission function is disabled by PDO. Set FALSE to disabled.
PDO: ATTR_CASE	Convert the case sensitivity of table field characters obtained by PDO, or use column information as is
PDO: ATTR_ERRMODE	Set error handling mode
PDO: ATTR_PERSISTENT	Determines whether the connection is a persistent connection. The default value is FALSE.
PDO: ATTR_ORACLE_NULLS	Converts the returned NULL string to the SQL NULL String.
PDO: ATTR_PREFETCH	Sets the data size that the application obtains in advance, in K bytes.
PDO: ATTR_TIMEOUT	Set the wait time before timeout (in seconds)
PDO: ATTR_SERVER_INFO	Contains server information unique to the database
PDO: ATTR_SERVER_VERSION	Contains information related to the database server version number.
PDO: ATTR_CLIENT_VERSION	Contains information related to the database Client Version Number.
PDO: ATTR_CONNECTION_STATUS	Set the wait time before timeout (in seconds)

 

Call the PDO Constructor (connect to the database)
<? Php
Try {
$ Pdo = new PDO ('mysql: host = localhost; dbname = test', 'root', '000000', array (PDO: ATTR_PERSISTENT => true ));
} Catch (PDOException $ e ){
Exit ('database connection failed, error message: '. $ e-> getMessage ());
}
?>
<? Php
Try {
$ Pdo = new PDO ('mysql: host = localhost; dbname = test', 'root', '000000', array (PDO: ATTR_PERSISTENT => true ));
} Catch (PDOException $ e ){
Exit ('database connection failed, error message: '. $ e-> getMessage ());
}
?>

Member methods of PDO objects
Member methods in the PDO object

 

Method Name	DescriptionDescription
GetAttribute ()	Obtains the attributes of a database connection object.
SetAttribute ()	Set attributes for a "database connection object"
ErrorCode ()	Get error code
ErrorInfo ()	Get error information
Exec ()	Process an SQL statement and return the affected number of rows.
Query ()	Process an SQL statement and return a "PDOStatement" Object
Quote ()	Add quotation marks for strings in an SQL statement
LastInsertId ()	Obtains the primary key value of the last data inserted to the table.
Prepare ()	Prepare the executed SQL statement
GetAvailableDrivers ()	Obtain a valid PDO drive name
BeginTransaction ()	Start a transaction, indicating the start point of rollback
Commit ()	Commit a transaction and execute SQL
Rollback ()	Roll back a transaction

 

Execute SQL statements using PDO

1. Use the PDO: exec () method

PDO: exec () is used in SQL INSERT, UPDATE, and DELETE. The number of affected rows is returned.

<? Php
Date_default_timezone_set ('prc ');
Header ('content-Type: text/html; Charset = UTF-8 ');
Try {
$ Pdo = new PDO ('mysql: host = localhost; dbname = test', 'root', '123 ');
$ Pdo-> setAttribute (PDO: ATTR_PERSISTENT, true); // set the database connection to persistent connection.
$ Pdo-> setAttribute (PDO: ATTR_ERRMODE, PDO: ERRMODE_EXCEPTION); // sets the throw Error
$ Pdo-> setAttribute (PDO: ATTR_ORACLE_NULLS, true); // you can change the string to NULL to the SQL NULL.
$ Pdo-> query ('set NAMES utf8'); // sets the database encoding.
} Catch (PDOException $ e ){
Exit ('database connection error, error message: '. $ e-> getMessage ());
}
$ AddTime = date ('Y-m-d H: I: s', time ());
$ SQL = "INSERT INTO think_user (userName, email, age, addTime) VALUES ('liquor method ', 'jiujinfazi @ sina.com.cn', '28', '{$ addTime }') ";
$ Row = $ pdo-> exec ($ SQL );
If ($ row ){
Echo 'added successfully ';
} Else {
Echo 'add failed ';
}
?>
<? Php
Date_default_timezone_set ('prc ');
Header ('content-Type: text/html; Charset = UTF-8 ');
Try {
$ Pdo = new PDO ('mysql: host = localhost; dbname = test', 'root', '123 ');
$ Pdo-> setAttribute (PDO: ATTR_PERSISTENT, true); // set the database connection to persistent connection.
$ Pdo-> setAttribute (PDO: ATTR_ERRMODE, PDO: ERRMODE_EXCEPTION); // sets the throw Error
$ Pdo-> setAttribute (PDO: ATTR_ORACLE_NULLS, true); // you can change the string to NULL to the SQL NULL.
$ Pdo-> query ('set NAMES utf8'); // sets the database encoding.
} Catch (PDOException $ e ){
Exit ('database connection error, error message: '. $ e-> getMessage ());
}
$ AddTime = date ('Y-m-d H: I: s', time ());
$ SQL = "INSERT INTO think_user (userName, email, age, addTime) VALUES ('liquor method ', 'jiujinfazi @ sina.com.cn', '28', '{$ addTime }') ";
$ Row = $ pdo-> exec ($ SQL );
If ($ row ){
Echo 'added successfully ';
} Else {
Echo 'add failed ';
}
?>
2. Use the PDO: query () method

PDO: the query () method is used for SELECT queries in SQL. If this method is successfully executed, a PDOStatement object is returned, and the number of affected rows can be returned using the rowCount () method.

<? Php
Header ('content-Type: text/html; Charset = UTF-8 ');
Try {
$ Pdo = new PDO ('mysql: host = localhost; dbname = test', 'root', '123 ');
$ Pdo-> setAttribute (PDO: ATTR_PERSISTENT, true); // set the database connection to persistent connection.
$ Pdo-> setAttribute (PDO: ATTR_ERRMODE, PDO: ERRMODE_EXCEPTION); // sets the throw Error
$ Pdo-> setAttribute (PDO: ATTR_ORACLE_NULLS, true); // you can change the string to NULL to the SQL NULL.
$ Pdo-> query ('set NAMES utf8'); // sets the database encoding.
} Catch (PDOException $ e ){
Exit ('database connection error, error message: '. $ e-> getMessage ());
}
$ SQL = "SELECT userName, email, age, addTime FROM think_user ";
Try {
$ Result = $ pdo-> query ($ SQL );
Foreach ($ result as $ row ){
Echo $ row ['username']. "\ t ". $ row ['email ']. "\ t ". $ row ['age']. "\ t ". $ row ['addtime']. '<br/> ';
}
Echo 'total '. $ result-> rowCount ().' bar ';
} Catch (PDOException $ e ){
Exit ($ e-> getMessage ());
}
?>
<? Php
Header ('content-Type: text/html; Charset = UTF-8 ');
Try {
$ Pdo = new PDO ('mysql: host = localhost; dbname = test', 'root', '123 ');
$ Pdo-> setAttribute (PDO: ATTR_PERSISTENT, true); // set the database connection to persistent connection.
$ Pdo-> setAttribute (PDO: ATTR_ERRMODE, PDO: ERRMODE_EXCEPTION); // sets the throw Error
$ Pdo-> setAttribute (PDO: ATTR_ORACLE_NULLS, true); // you can change the string to NULL to the SQL NULL.
$ Pdo-> query ('set NAMES utf8'); // sets the database encoding.
} Catch (PDOException $ e ){
Exit ('database connection error, error message: '. $ e-> getMessage ());
}
$ SQL = "SELECT userName, email, age, addTime FROM think_user ";
Try {
$ Result = $ pdo-> query ($ SQL );
Foreach ($ result as $ row ){
Echo $ row ['username']. "\ t ". $ row ['email ']. "\ t ". $ row ['age']. "\ t ". $ row ['addtime']. '<br/> ';
}
Echo 'total '. $ result-> rowCount ().' bar ';
} Catch (PDOException $ e ){
Exit ($ e-> getMessage ());
}
?>

Support for pre-processing statements by PDO

From Lee.'s column