Tutorial on using PDO_PHP in PHP-PHP Tutorial-php.cn

PDO Introduction
PDO is a "database access abstraction layer" that unifies the access interfaces of various databases (MySQL, MSSQL, Oracle, DB2, PostgreSQL...) and can easily switch between different databases, allowing for transplantation between databases. Easy to implement.

PDO driver

支持PDO 的驱动及相应的数据库列表
驱动名	对应访问的数据库
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 was released with PHP 5.1, which means that versions before 5.1 do not support PDO. PDO is also the first choice and trend for PHP to handle databases in the future.

The following describes the installation of PDO:

1. Linux environment

In order to enable PDO program driver support for MySQL in a Linux environment, you need to add the following to the configure command when installing the source code package of PHP (version 5.1 or above):

--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 environment

Modify the php.ini file, find the following, and remove the ";" (semicolon: represents a comment) in front of it!

;extension=php_pdo.dll // An extension shared by all PDO drivers, this must have
;extension=php_pdo_mysql.dll // Use this line to use MySQL
;extension=php_pdo_mssql.dll // Use this line to use MSSQL
;extension=php_pdo.dll // An extension shared by all PDO drivers, this must have
;extension=php_pdo_mysql.dll // Use MySQL using this line
;extension=php_pdo_mssql.dll // Use this line to use MSSQL
Save the php.ini file, restart the Apache server, and check the phpinfo() function. If the following picture appears, the installation is successful.

Note: In Windows environment, sometimes the configuration may not be successful and the above picture will not appear. At this time, copy the php_pdo_mysql.dll, php_pdo.dll... and other files in the PHP installation extension to Windows in the system installation path.

Create PDO object
The prototype of PDO's construction method 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, 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 is: Localhost; port: 1521; database name is: test

…For more DSN, please refer to the PHP manual

2. db_user: database user name;

3. db_pwd: database password;

4. driver_options: is an array used to specify all additional options required for the connection

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

选项名	描述
PDO::ATTR_AUTOCOMMIT	确定PDO 是否关闭自动提交功能，设置FALSE 为关闭
PDO::ATTR_CASE	强制PDO 获取的表字段字符的大小写转换，或原样使用列信息
PDO::ATTR_ERRMODE	设置错误处理的模式
PDO::ATTR_PERSISTENT	确定连接是否为持久连接，默认为FALSE，不持久连接
PDO::ATTR_ORACLE_NULLS	将返回的空字符串转换为SQL 的NULL
PDO::ATTR_PREFETCH	设置应用程序提前获取的数据大小，以K 字节为单位
PDO::ATTR_TIMEOUT	设置超时之前的等待时间（秒为单位）
PDO::ATTR_SERVER_INFO	包含与数据库特有的服务器信息
PDO::ATTR_SERVER_VERSION	包含与数据库服务器版本号有关的信息
PDO::ATTR_CLIENT_VERSION	包含与数据库客户端版本号有关的信息
PDO::ATTR_CONNECTION_STATUS	设置超时之前的等待时间（秒为单位）

Call PDO constructor (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 of PDO objects
Member methods in PDO objects

方法名	描述
getAttribute()	获取一个“数据库连接对象”的属性
setAttribute()	为一个“数据库连接对象”设定属性
errorCode()	获取错误码
errorInfo()	获取错误信息
exec()	处理一条SQL 语句，并返回所影响的行数
query()	处理一条SQL 语句，并返回一个"PDOStatement" 对象
quote()	为某个SQL 中的字符串添加引号
lastInsertId()	获取插入到表中的最后一条数据的主键值
prepare()	负责准备执行的SQL 语句
getAvailableDrivers()	获取有效的PDO 驱动器名称
beginTransaction()	开始一个事务，标明回滚起始点
commit()	提交一个事务，并执行SQL
rollback()	回滚一个事务

Use PDO to execute SQL statements

1. Use PDO::exec() method

PDO::exec() method is mostly used in INSERT, UPDATE, and DELETE in SQL, and returns the number of affected rows

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); // Set the database connection to a persistent connection
            $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); // Set to throw an error
             $pdo->setAttribute(PDO::ATTR_ORACLE_NULLS, true); // Set when the string is empty, it is converted to NULL in SQL
             $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 ('Noriko Sakai','jiujinfazi@sina.com.cn','28','{$addTime}')";
$row = $pdo->exec($sql);
If ($row) {
echo 'Added successfully';
} 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); //Set the 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 when the string is empty, it is converted to SQL 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 ('Noriko Sakai','jiujinfazi@sina.com.cn','28','{$addTime}')";
$row = $pdo->exec($sql);
if ($row) {
echo 'Added successfully';
} else {
echo 'Add failed';
}
?>
2. Use PDO::query() method

The PDO::query() method is used in SELECT queries in SQL. If this method is executed successfully, a PDOStatement object will be returned. Use the rowCount() method to return the number of affected rows

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); // Set the database connection to a persistent connection
           $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); // Setup throws an error
              $pdo->setAttribute(PDO::ATTR_ORACLE_NULLS, true); // Set when the string is empty, it is converted to NULL in SQL
            $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() .'Bar';
} 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); //Set the 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 when the string is empty, it is converted to SQL 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() .'Bar';
} catch (PDOException $e) {
exit($e->getMessage());
}
?>

PDO support for prepared statements

Excerpted from Lee.’s column