How to use PDO for database operations in PHP

PHP has many methods to connect and operate databases, among which PDO (PHP Data Objects) is a very powerful and popular method. PDO provides a universal way to connect to and operate almost all types of databases, including MySQL, PostgreSQL, Oracle, etc.

In this article, we will introduce how to use PDO for database operations in PHP. We will focus on how to connect to the database, insert, update and delete records, and query the database.

1. Connect to the database

First, we need to create a PDO connection. We need to provide information such as the host name, database name, user name and password of the database. For example, the code to connect to a local MySQL database is as follows:

$dsn = 'mysql:host=localhost;dbname=my_database';
$username = 'my_username';
$password = 'my_password';

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

2. Insert records

Once we are connected to the database, we can start inserting records. We need to prepare a SQL statement that PDO will accept and send to the database for execution. For example, assuming we have a table named "users", which contains id, name and email fields, we can insert a record with the following code:

$name = 'John Smith';
$email = 'john.smith@example.com';

$stmt = $pdo->prepare("INSERT INTO users (name, email) VALUES (?, ?)");

$stmt->bindParam(1, $name);
$stmt->bindParam(2, $email);

$stmt->execute();

In this example, we pass prepare() Method prepares a SQL statement and sends the statement to the database. We bind our variable using bindParam() method and execute the statement using execute() method.

3. Update records

We can also use PDO to update records. We need to prepare a SQL statement and send it to the database for execution. For example, we can use the following code to update the name and email of the record with id 1:

$id = 1;
$name = 'Jane Doe';
$email = 'jane.doe@example.com';

$stmt = $pdo->prepare("UPDATE users SET name = ?, email = ? WHERE id = ?");

$stmt->bindParam(1, $name);
$stmt->bindParam(2, $email);
$stmt->bindParam(3, $id);

$stmt->execute();

In this example, we use the UPDATE statement to update the record. We bind our variable using bindParam() method and execute the statement using execute() method.

4. Delete records

We can also use PDO to delete records. We need to prepare a SQL statement and send it to the database for execution. For example, we can delete the record with id 1 using the following code:

$id = 1;

$stmt = $pdo->prepare("DELETE FROM users WHERE id = ?");

$stmt->bindParam(1, $id);

$stmt->execute();

In this example, we deleted the record using the DELETE FROM statement. We bind our variable using bindParam() method and execute the statement using execute() method.

5. Query data

Finally, we can use PDO to query data. We need to prepare a SQL statement and send it to the database for execution. For example, we can query all user records using the following code:

$stmt = $pdo->prepare("SELECT * FROM users");
$stmt->execute();

$users = $stmt->fetchAll(PDO::FETCH_ASSOC);

In this example, we query all user records using the SELECT statement. We use the fetchAll() method to get the array of records and store it in the $users variable.

PDO also provides other types of fetch() methods that can return a single record or return data in a different format. You can choose the most suitable method according to your needs.

Conclusion

Using PDO in PHP for database operations is very convenient and powerful. PDO provides a versatile way to connect to and operate on almost any type of database. In this article, we covered how to connect to a database, insert, update, and delete records, and query the database. Hope this article helps you!

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