PHP database learning detailed explanation of the basic usage of PDO

PHP PDO is an important component in PHP database extension. It provides a unified API for PHP and various relational databases. Using PDO can make our programs more secure, efficient, and easy to maintain.

In this article, we will introduce the usage of PHP PDO, including how to connect to the database, execute SQL statements, transaction processing, etc.

1. Connect to the database

Connecting to the database is a key step in using PDO for database operations. Before connecting to the database, you need to prepare the database address. User name, password, corresponding database name and other information.

The code to connect to the database is as follows:

// 数据库连接信息，根据实际情况修改
$db_host = 'localhost'; // 数据库地址
$db_name = 'mydatabase'; // 数据库名称
$db_user = 'root'; // 数据库用户名
$db_password = 'password'; // 数据库密码

// 创建PDO对象
try {
    $pdo = new PDO("mysql:host={$db_host};dbname={$db_name}", $db_user, $db_password);
} catch (PDOException $e) {
    echo '数据库连接失败：' . $e->getMessage();
    exit;
}

2. Execute the SQL statement

After connecting to the database, we need to To operate, you must first learn how to execute SQL statements. PDO provides multiple methods for executing SQL statements. The more commonly used methods are query() and prepare().

1. Use query() method to execute SQL statement

query() method can directly execute SQL statement. After the statement is executed successfully, it returns PDOStatement object.

For example, if we want to query the data in a user table (user), the code is as follows:

$sql = "SELECT * FROM user"; // SQL语句
$stmt = $pdo->query($sql); // 执行SQL语句，返回PDOStatement对象

// 获取查询结果
while ($row = $stmt->fetch()) {
    print_r($row);
}

2. Use the prepare() method to execute the SQL statement

prepare()The method can prepare a SQL statement and use variable binding to execute the SQL statement. Using variable binding can prevent SQL injection attacks.

The code is as follows:

$sql = "SELECT * FROM user WHERE id = ?"; // SQL语句，使用?作为占位符
$stmt = $pdo->prepare($sql); // 准备SQL语句
$id = 1; // 赋值给变量$id
$stmt->execute([$id]); // 执行SQL语句并传入变量值

// 获取查询结果
while ($row = $stmt->fetch()) {
    print_r($row);
}

3. Transaction processing

In database operations, some operations need to perform multiple operations at the same time SQL statement, this time you need to use transaction processing. Transaction processing must ensure that all operations are successful before committing, otherwise it will be rolled back to the state before the transaction started.

The code is as follows:

$pdo->beginTransaction(); // 开启事务

// 定义SQL语句
$sql1 = "DELETE FROM user WHERE id = ?";
$sql2 = "INSERT INTO user (uname, age) VALUES (?, ?)";

// 准备SQL语句
$stmt1 = $pdo->prepare($sql1);
$stmt2 = $pdo->prepare($sql2);

// 赋值给变量
$id = 1; // 要删除的数据ID
$uname = 'test'; // 新增数据的用户名
$age = 18; // 新增数据的年龄

// 执行SQL语句
$stmt1->execute([$id]);
$stmt2->execute([$uname, $age]);

// 提交事务
$pdo->commit();

The above is the basic usage of PHP PDO. For more advanced applications, you can also use some advanced functions of PDO, such as prepared statements and named bindings. I hope this article can help you use PDO in PHP development.

The above is the detailed content of PHP database learning detailed explanation of the basic usage of PDO. For more information, please follow other related articles on the PHP Chinese website!