PHP development tips: How to use PDO to operate MySQL database

PHP development skills: How to use PDO to operate MySQL database

Introduction:
In PHP development, database operation is one of the very common requirements. In order to improve code quality and security, we often use PDO (PHP Data Objects) to operate databases, especially MySQL databases. This article will introduce how to use PDO for MySQL database operations and provide some practical code examples.

1. Introduction to PDO
PDO is a lightweight and flexible database extension of PHP. It provides a unified interface to connect and operate various databases. PDO abstracts the details of database operations, allowing developers to focus more on business logic without caring about the underlying database implementation. At the same time, PDO also provides some powerful functions, such as prepared statements, transaction management, and error handling.

2. Use PDO to connect to the MySQL database
Before starting the database operation, we need to create a PDO instance and connect to the MySQL database. The following is a simple example of connecting to a MySQL database:

<?php
$dsn = 'mysql:host=localhost;dbname=test';
$username = 'root';
$password = '';

try {
    $pdo = new PDO($dsn, $username, $password);
    echo 'Connection successful';
} catch (PDOException $e) {
    echo 'Connection failed: ' . $e->getMessage();
}
?>

In the above example, the $dsn variable is used to specify the database type, host name and database name, and $username and $password are used to connect to MySQL respectively. Database username and password. By using PDO's constructor, we can create a PDO instance and connect to the MySQL database. If the connection is successful, "Connection successful" will be output, otherwise an error message indicating that the connection failed will be output.

3. Execute database query
After connecting to the database, we can use PDO to execute SQL queries. First, we can use the query() method to execute a simple query statement and obtain the result set. The following is an example:

<?php
$sql = "SELECT * FROM users";
$stmt = $pdo->query($sql);

while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    echo $row['username'] . ', ' . $row['email'] . '<br>';
}
?>

In the above example, we used the SELECT statement to query all records of the users table. Through the fetch() method and the PDO::FETCH_ASSOC constant, we can get the associative array of each row and output the user name and email address.

4. Use prepared statements
In order to improve the security and efficiency of queries, we should use prepared statements to process query parameters. Here is an example of using prepared statements:

<?php
$sql = "SELECT * FROM users WHERE id = :id";
$stmt = $pdo->prepare($sql);

$id = 1;
$stmt->bindParam(':id', $id);
$stmt->execute();

while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    echo $row['username'] . ', ' . $row['email'] . '<br>';
}
?>

In the above example, we have used prepared statements with named parameters. Use the prepare() method to create a prepared statement, and the bindParam() method to associate parameters with named placeholders. Before executing the query, we need to assign the parameters to the $id variable. Finally, execute the query and obtain the result set through the execute() method.

5. Transaction Management
Using PDO for transaction management can ensure the atomicity of a set of database operations, that is, either all executions are successful or all are rolled back. The following is an example of using PDO transaction management:

<?php
try {
    $pdo->beginTransaction();

    $sql1 = "UPDATE users SET balance = balance - 100 WHERE id = 1";
    $pdo->exec($sql1);

    $sql2 = "UPDATE users SET balance = balance + 100 WHERE id = 2";
    $pdo->exec($sql2);

    $pdo->commit();
    echo 'Transaction successful';
} catch (PDOException $e) {
    $pdo->rollback();
    echo 'Transaction failed: ' . $e->getMessage();
}
?>

In the above example, we start a database transaction through the beginTransaction() method, and execute two UPDATE statements in the transaction to update the user's account balance. If the transaction is executed successfully, the transaction is committed through the commit() method, otherwise the transaction is rolled back through the rollback() method and an error message is output.

6. Error handling
PDO provides some methods and constants to handle errors that may occur during database operations. We can use the errorCode() method to get the error code, and the errorInfo() method to get detailed error information. The following is a simple error handling example:

<?php
$stmt = $pdo->prepare("SELECT * FROM non_existent_table");
$stmt->execute();

if ($stmt->errorCode() != '00000') {
    $errorInfo = $stmt->errorInfo();
    echo 'Error: ' . $errorInfo[2];
}
?>

In the above example, we executed a query statement, but the queried table did not exist. Through the errorCode() method, we can check whether an error has occurred. If an error occurs, obtain detailed error information through the errorInfo() method and output the error message.

Summary:
This article introduces how to use PHP's PDO extension to operate the MySQL database. We learned how to connect to a MySQL database, execute queries, use prepared statements, transaction management and error handling. By using PDO, we can write more secure, efficient and maintainable database operation code.

By reading this article and using practical examples, I believe you have mastered the basic skills of using PDO to operate a MySQL database. With continued research and practice, you will be able to respond more flexibly to the needs of various database operations and develop better PHP applications.

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