Using pdo_php Tutorial in PHP

About PDO
PDO is a "database access abstraction Layer" that unifies various databases (MySQL, MSSQL, Oracle, DB2, PostgreSQL ...). ), it is easy to switch between different databases, which makes porting between databases easy to implement.

PDO Driver

Support for PDO driver and corresponding database list
Driver name	The database that corresponds to the access
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, which means that the version prior to 5.1 does not support PDO, and PDO is the first choice and trend for future PHP processing databases.

The installation of PDO is described below:

1. Linux Environment

In the Linux environment to enable the PDO program driver support for MySQL, you need to install PHP (5.1 or more versions) of the source code package, add to the Configure command:

--with-pdo-mysql=/usr/local/mysql//"/usr/local/mysql" is the installation directory for MySQL server
--with-pdo-mysql=/usr/local/mysql//"/usr/local/mysql" is the installation directory for MySQL server
2. Windows Environment

Modify the php.ini file to find the following, put the front ";" (Semicolon: On behalf of the comment) remove!

; Extension=php_pdo.dll//All PDO driver shared extensions, this must have
; Extension=php_pdo_mysql.dll//Use this line with MySQL
; Extension=php_pdo_mssql.dll//Use MSSQL to use this line
; Extension=php_pdo.dll//All PDO driver shared extensions, this must have
; Extension=php_pdo_mysql.dll//Use this line with MySQL
; Extension=php_pdo_mssql.dll//Use MSSQL to use this line
Save the php.ini file, restart the Apache server, and view the Phpinfo () function, stating that the installation was successful.

Note: In a Windows environment, it is sometimes possible to configure a phenomenon that is not successful and does not occur. At this point, the PHP installation extension in the Php_pdo_mysql.dll, Php_pdo.dll ... Files are copied to the system installation path under Windows.

Create a PDO object
The construction method of PDO 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): The source name, define the database and driver used;

A. The dsn:mysql:host=localhost;dbname=test//hostname connected to the MySQL database is: localhost; the database name is: Test

B. The dsn:oci:dbname=//localhost:1521/test//hostname connected to the Oracle database is: localhost; port: 1521; The database name is: Test

...... For more DSN Please refer to PHP manual

2, Db_user: Database user name;

3, Db_pwd: Database password;

4. Driver_options: is an array that specifies all the additional options required for the connection

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

Option name	Description described
Pdo::attr_autocommit	Determine if PDO turns off auto-commit, set false to Off
Pdo::attr_case	Force PDO to get the case conversion of table field characters, or use column information as-is
Pdo::attr_errmode	Setting the mode of error handling
Pdo::attr_persistent	Determine if the connection is a persistent connection, false by default, not persistent
Pdo::attr_oracle_nulls	Converts the returned empty string to NULL for SQL
Pdo::attr_prefetch	Sets the size of the data that the application obtains in advance, in K-byte units
Pdo::attr_timeout	Wait time (in seconds) before setting time-out
Pdo::attr_server_info	Contains server information specific to the database
Pdo::attr_server_version	Contains information about the database server version number
Pdo::attr_client_version	Contains information about the database client version number
Pdo::attr_connection_status	Wait time (in seconds) before setting time-out

Call the PDO construction method (connect to database)
try {
$pdo = new PDO (' Mysql:host=localhost;dbname=test ', ' root ', ' 1715544 ', Array (pdo::attr_persistent=>true));
} catch (Pdoexception $e) {
Exit (' Database connection failed, error message: '. $e->getmessage ());
}
?>
try {
$pdo = new PDO (' Mysql:host=localhost;dbname=test ', ' root ', ' 1715544 ', Array (pdo::attr_persistent=>true));
} catch (Pdoexception $e) {
Exit (' Database connection failed, error message: '. $e->getmessage ());
}
?>

Member Methods for PDO objects
Member methods in a PDO object

tr> td> start a transaction that indicates the rollback start point

method name	stroke described
getattribute ()	Gets the properties of a database connection object
setattrib Ute ()	set properties for a database connection object
errorCode ()	get error code
Errorin Fo ()	get error message
exec ()	processes an SQL statement and returns the number of rows affected
query () /td>	processes an SQL statement and returns a "Pdostatement" Object
quote ()	add quotation marks to a string in a SQL
Lastinsertid ()	gets the primary key value of the last data inserted into the table
Prepare ()	Prepare SQL statements for execution
getavailabledrivers ()	get a valid PDO drive name
begintransaction ()
commit ()	commit a transaction and execute SQL
rollback ()	rolling back a transaction

Execute SQL statements using PDO

1. Using the Pdo::exec () method

The Pdo::exec () method is used in SQL to INSERT, UPDATE, DELETE, return the number of rows affected

Date_default_timezone_set (' PRC ');
Header (' content-type:text/html; Charset=utf-8 ');
try {
$pdo = new PDO (' Mysql:host=localhost;dbname=test ', ' root ', ' 1715544 ');
$pdo->setattribute (Pdo::attr_persistent, true); To set a database connection as a persistent connection
$pdo->setattribute (Pdo::attr_errmode, pdo::errmode_exception); Set throw error
$pdo->setattribute (Pdo::attr_oracle_nulls, true); Set NULL to convert the string to SQL when null
$pdo->query (' SET NAMES utf8 '); Set 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 (' Sakai ', ' jiujinfazi@sina.com.cn ', ' I ', ' {$addTime} ' )";
$row = $pdo->exec ($sql);
if ($row) {
Echo ' Add success ';
} else {
Echo ' Add failed ';
}
?>
Date_default_timezone_set (' PRC ');
Header (' content-type:text/html; Charset=utf-8 ');
try {
$pdo = new PDO (' Mysql:host=localhost;dbname=test ', ' root ', ' 1715544 ');
$pdo->setattribute (Pdo::attr_persistent, true); To set a database connection as a persistent connection
$pdo->setattribute (Pdo::attr_errmode, pdo::errmode_exception); Set throw error
$pdo->setattribute (Pdo::attr_oracle_nulls, true); Set NULL to convert the string to SQL when null
$pdo->query (' SET NAMES utf8 '); Set 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 (' Sakai ', ' jiujinfazi@sina.com.cn ', ' I ', ' {$addTime} ' )";
$row = $pdo->exec ($sql);
if ($row) {
Echo ' Add success ';
} else {
Echo ' Add failed ';
}
?>
2. Using the Pdo::query () method

The Pdo::query () method is used on the SELECT query in SQL. If the method executes successfully, a Pdostatement object is returned, and the number of rows affected can be returned using the RowCount () method

Header (' content-type:text/html; Charset=utf-8 ');
try {
$pdo = new PDO (' Mysql:host=localhost;dbname=test ', ' root ', ' 1715544 ');
$pdo->setattribute (Pdo::attr_persistent, true); To set a database connection as a persistent connection
$pdo->setattribute (Pdo::attr_errmode, pdo::errmode_exception); Set throw error
$pdo->setattribute (Pdo::attr_oracle_nulls, true); Set NULL to convert the string to SQL when null
$pdo->query (' SET NAMES utf8 '); Set 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 ']. '
';
}
Echo ' Total '. $result->rowcount (). ' Article ';
} catch (Pdoexception $e) {
Exit ($e->getmessage ());
}
?>
Header (' content-type:text/html; Charset=utf-8 ');
try {
$pdo = new PDO (' Mysql:host=localhost;dbname=test ', ' root ', ' 1715544 ');
$pdo->setattribute (Pdo::attr_persistent, true); To set a database connection as a persistent connection
$pdo->setattribute (Pdo::attr_errmode, pdo::errmode_exception); Set throw error
$pdo->setattribute (Pdo::attr_oracle_nulls, true); Set NULL to convert the string to SQL when null
$pdo->query (' SET NAMES utf8 '); Set 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 ']. '
';
}
Echo ' Total '. $result->rowcount (). ' Article ';
} catch (Pdoexception $e) {
Exit ($e->getmessage ());
}
?>

PDO support for pre-processing statements

Excerpt from Lee's column

http://www.bkjia.com/PHPjc/478465.html www.bkjia.com true http://www.bkjia.com/PHPjc/478465.html techarticle PDO Introduction PDO is a database access abstraction layer, which functions as a unified access interface for various databases (MySQL, MSSQL, Oracle, DB2, PostgreSQL) and can be easily between different databases ...

