Learn PHP PDO: How to use PDO to connect to the database and execute SQL statements

Learn PHP PDO: How to use PDO to connect to the database and execute SQL statements

In PHP development, interacting with the database is a very common requirement. PHP PDO (PHP Data Objects) is a high-level abstract interface for database operations. It provides a universal method of accessing the database and can interact with various database systems, including MySQL, PostgreSQL, SQLite, etc.

This article will introduce how to use the PDO extension in PHP to connect to the database and execute SQL statements. We will first learn how to connect to the database and then learn how to perform SQL queries, inserts, updates, and delete operations.

1. Connect to the database

First, we need to create a PDO object to connect to the database. The following is a sample code to connect to the database:

<?php
$host = 'localhost';
$dbname = 'dbname';
$user = 'username';
$pass = 'password';

try {
    $pdo = new PDO("mysql:host=$host;dbname=$dbname", $user, $pass);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    echo "Database connection successful!";
} catch(PDOException $e) {
    echo "Database connection failed:" . $e->getMessage();
}
?>

In the above code, we first define the host name, database name, user name and password of the database. Then we use the PDO constructor to create a PDO object and set the error handling mode to throw an exception. If the connection is successful, "Database connection successful!" will be output. If the connection fails, specific information about the connection failure will be output.

2. Execute SQL query

Next, we will learn how to use PDO to perform SQL query operations. Here is a simple query example:

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

while ($row = $stmt->fetch()) {
    echo $row['username'] . "<br>";
}
?>

In the above code, we define a SQL query statement, and then use the $pdo->query($sql) method to execute the query and store the results in$stmt variable. Then use the fetch() method to get the data row by row from the result set and output the user name.

3. Perform SQL insert, update and delete operations

In addition to query operations, PDO can also perform insert, update and delete operations. Here is a simple insert example:

<?php
$sql = "INSERT INTO users (username, email) VALUES ('john_doe', 'john@example.com')";
$pdo->exec($sql);
echo "User added successfully!";
?>

In the above code, we define an insert statement and then use the $pdo->exec() method to perform the insert operation. Finally output "User added successfully!".

In addition to insert operations, PDO can also perform update and delete operations. Here is a simple update example:

<?php
$user_id = 1;
$new_username = 'jane_doe';

$sql = "UPDATE users SET username = '$new_username' WHERE id = $user_id";
$pdo->exec($sql);
echo "Username updated successfully!";
?>

In the above code, we define an update statement and then use the $pdo->exec() method to perform the update operation. Finally output "Username updated successfully!".

The deletion operation is similar. Here is a simple deletion example:

<?php
$user_id = 1;

$sql = "DELETE FROM users WHERE id = $user_id";
$pdo->exec($sql);
echo "User deleted successfully!";
?>

In the above code, we define a delete statement and then use the $pdo->exec() method to perform the delete operation. Finally output "User deleted successfully!".

Summary

This article introduces how to use the PDO extension in PHP to connect to the database and execute SQL statements, including query, insert, update and delete operations. By learning PDO, you can interact with the database more efficiently and safely, avoiding security issues such as SQL injection. I hope this article is helpful to you, and I wish you better achievements in PHP development!

The above is the detailed content of Learn PHP PDO: How to use PDO to connect to the database and execute SQL statements. For more information, please follow other related articles on the PHP Chinese website!