How PHP uses PDO to operate the database

In Web development, the database is a very important component. Like other programming languages, the PHP language also has many ways to operate the database. Among them, PDO (PHP Data Object) is one of the common ways to operate databases in PHP. It provides a unified, simple, flexible and safe method to access different database systems. This article will introduce how PHP uses PDO to operate the database.

1. Advantages of PDO

The advantages of using PDO to operate the database are as follows:

1. The program has strong portability: using PDO to operate the database has better portability It is strong and can run on different databases without requiring major changes to the program.
2. High security: PDO supports processing command line parameters and can effectively prevent SQL injection attacks.
3. Object-oriented: PDO is object-oriented and can make OOP programming more convenient.
4. Good performance: PDO supports prepared statements and data binding, which can greatly improve query efficiency and thereby improve program performance.

2. Use PDO to connect to the database

Before you start using PDO to operate the database, you need to connect to the database first. The code to connect to the database is as follows:

//设置数据库连接信息
$dsn = 'mysql:host=localhost;dbname=testdb';
$username = 'root';
$password = 'root';
$options = [
    //错误模式：在发生错误时抛出异常
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    //默认返回关联数组
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
];
//创建PDO对象
$pdo = new PDO($dsn, $username, $password, $options);

Among them, $dsn represents database type, host name, database name and other information, $username represents user name, $password represents password, $options represents settings, such as setting error mode, Return data type, etc. When connecting to the database, you can also set other options, such as:

$options = [
    //设置字符集
    PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8',
    //关闭持久连接
    PDO::ATTR_PERSISTENT => false,
    //设置超时时间
    PDO::ATTR_TIMEOUT => 10,
    //设置返回数据类型为对象
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_OBJ,
    // 禁用预处理语句的模拟
    PDO::ATTR_EMULATE_PREPARES => false,
];

3. PDO implements addition, deletion, modification and query operations

After connecting to the database, you can perform addition, deletion, modification and query operations on the database. Commonly used operations include: inserting data, updating data, deleting data and querying data.

1. Insert data

The code to use PDO to insert data into the database is as follows:

//插入数据
$sql = "INSERT INTO `users` (`name`,`age`,`email`) VALUES (:name,:age,:email)";
$name = 'Tom';
$age = 23;
$email = 'tom@example.com';
$stmt = $pdo->prepare($sql);
$stmt->bindValue(':name', $name);
$stmt->bindValue(':age', $age);
$stmt->bindValue(':email', $email);
$stmt->execute();

Among them, $sql is the SQL statement to insert data, use The parameter binding method can avoid SQL injection attacks. The bindValue() method will bind the parameter to the placeholder with the specified name, and then call the execute() method to submit the query to the database.

2. Update data

The code to update data using PDO is as follows:

//更新数据
$sql = "UPDATE `users` SET `age` = :age WHERE `name` = :name";
$name = 'Tom';
$age = 24;
$stmt = $pdo->prepare($sql);
$stmt->bindValue(':name', $name);
$stmt->bindValue(':age', $age);
$stmt->execute();

Among them, $sql is the SQL statement to update the data, using parameter binding In a certain way, SQL injection attacks can be avoided. The bindValue() method will bind the parameter to the placeholder with the specified name, and then call the execute() method to submit the query to the database.

3. Delete data

The code to delete data using PDO is as follows:

//删除数据
$sql = "DELETE FROM `users` WHERE `name` = :name";
$name = 'Tom';
$stmt = $pdo->prepare($sql);
$stmt->bindValue(':name', $name);
$stmt->execute()

Among them, $sql is the SQL statement to delete data, using parameter binding In a certain way, SQL injection attacks can be avoided. The bindValue() method will bind the parameter to the placeholder with the specified name, and then call the execute() method to submit the query to the database.

4. Query data

The code for querying data using PDO is as follows:

//查询数据
$sql = "SELECT * FROM `users` WHERE `age` > :age";
$age = 20;
$stmt = $pdo->prepare($sql);
$stmt->bindValue(':age', $age);
$stmt->execute();
$rows = $stmt->fetchAll();

Among them, $sql is the SQL statement for querying data, using parameter binding In a certain way, SQL injection attacks can be avoided. The bindValue() method will bind the parameter to the placeholder with the specified name, and then call the execute() method to submit the query to the database. The fetchAll() method returns the query results as a multidimensional array.

Since PDO supports obtaining results of three types: associative array, numeric array and object, when querying the results, you need to set the type of fetch acquisition, for example:

//查询并获取关联数组
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
 
//查询并获取数字数组
$rows = $stmt->fetchAll(PDO::FETCH_NUM);
 
//查询并获取对象数组
$rows = $stmt->fetchAll(PDO::FETCH_OBJ);

4. PDO implementation Transaction processing

PDO supports transaction processing, and you can use the beginTransaction(), commit() and rollback() methods to implement transaction submission and rollback operations.

try {
    //开启事务
    $pdo->beginTransaction();
    //执行增删改操作语句
    $pdo->exec($sql1);
    $pdo->exec($sql2);
    $pdo->exec($sql3);
    //提交事务
    $pdo->commit();
} catch (Exception $e) {
    //回滚事务
    $pdo->rollback();
}

The above is a basic introduction to using PDO to operate the database. There are several points that need to be paid attention to when operating the database with PDO:

1. Using parameter binding can avoid SQL injection attacks.
2. Set the error mode of the PDO exception to EXCEPTION to easily catch the exception.
3. Using the prepare() method to execute operation statements can cache the result set to improve query efficiency.
4. Preprocessed statements and data binding can avoid repeated work of analyzing and optimizing statements, achieving higher query efficiency.
5. Enabling transaction processing can ensure data integrity and consistency.

In short, using PDO to operate the database is a relatively convenient and effective way, which can provide powerful database access capabilities for Web applications.

The above is the detailed content of How PHP uses PDO to operate the database. For more information, please follow other related articles on the PHP Chinese website!