Using PDO for database operations

In the process of website development, it is very common to use a database to store and operate data. In order to connect to the database and perform operations, we usually use a combination of PHP and MySQL. However, for PHP, there are two different ways to use MySQL for database operations: MySQLi and PDO. In this article, we will explore how to use PDO for database operations.

PDO refers to PHP Data Object, which is an extension of PHP that helps PHP developers connect and operate databases in an object-oriented manner. PDO supports a variety of databases, and MySQL is just one of them. For developers, using PDO for database operations has the following benefits:

1. PDO is object-oriented and easy to understand and use;
2. PDO is more secure than MySQLi and can avoid SQL Injection attack;
3. PDO is compatible with a variety of databases and can be operated using the same functions in different database systems;
4. PDO provides more error handling and debugging options, which helps Developers debug and optimize code.

Let’s start with how to use PDO for database operations:

1. Connecting to the database

Connecting to the database is the first step to perform database operations. We use PDO to connect to the MySQL database. To connect to the MySQL database, you need to pass the following parameters:

$host: database host address, usually localhost or 127.0.0.1
$dbname: database name
$username: username to connect to the database
$password: Password for connecting to the database

// Database connection parameters
$host = 'localhost';
$dbname = 'my_database';
$username = 'my_username';
$password = 'my_password';

// Use PDO to connect to the database
try {

$conn = new PDO("mysql:host=$host;dbname=$dbname", $username, $password);  

} catch(PDOException $e) {

echo "数据库连接失败：" . $e->getMessage();  

}

2. Execute SQL query

PDO uses the query() method to execute SQL query statements and returns a PDOStatement object, which can use the fetch() method to extract data.

Example:

//Query all users
$sql = "SELECT * FROM users";
$stmt = $conn->query($sql);

// Output query results
while($row = $stmt->fetch()) {

echo $row['id'] . "    " . $row['name'] . "

";
}

3. Insert data

To insert data, you need to execute the INSERT statement. Here we use the prepare() and execute() methods to perform the insert operation.

Example:

// Insert new users
$sql = "INSERT INTO users (name, email) VALUES (:name, :email)";
$stmt = $conn->prepare($sql);
$stmt->bindParam(':name', $name);
$stmt->bindParam(':email', $email);

// Enter new user information
$name = 'amy';
$email = 'amy@example.com';

//Execute insertion
$stmt->execute();

/ / Output the insertion result
echo "The ID of the new user is: " . $conn->lastInsertId();

4. Update data

To update the data, You need to execute the UPDATE statement, and also use the prepare() and execute() methods to perform the update operation.

Example:

//Update user email
$sql = "UPDATE users SET email = :email WHERE id = :id";
$stmt = $conn->prepare($sql);
$stmt->bindParam(':email', $email);
$ stmt->bindParam(':id', $id);

// Enter update information
$email = 'newemail@example.com';
$id = 1;

//Execute update
$stmt->execute();

5. Delete data

To delete data, you need to execute the DELETE statement, the same Use the prepare() and execute() methods to perform deletion operations.

Example:

//Delete users
$sql = "DELETE FROM users WHERE id = :id";
$stmt = $conn->prepare($sql );
$stmt->bindParam(':id', $id);

// Enter deletion user information
$id = 1;

// Execute Delete
$stmt->execute();

Finally, we need to use the close() method to close the database connection after the database operation is completed.

Example:

//Close PDO connection
$conn = null;

Summary

In this article, we learned how to use PDO Perform database operations. PDO is a safe, convenient and highly compatible database operation method, which can improve our development efficiency and code quality in practical applications. Before performing database operations, we need to introduce PDO extensions and pass parameters for connecting to the database. After that, we can use query(), prepare(), execute() and other methods to perform SQL queries, inserts, updates and deletes. Finally, we need to close the database connection using the close() method.

The above is the detailed content of Using PDO for database operations. For more information, please follow other related articles on the PHP Chinese website!