How to use PDO database to add, delete, modify and check

In Internet application development, database is one of the skills that every developer must master. As the most popular programming language for developing dynamic web applications, PHP is also important in handling database operations. PDO is a way of handling database operations in PHP extensions. The following will introduce how to use PDO to implement basic database addition, deletion, modification and query operations.

1. Connect to the database

Before using PHP to perform database operations, you need to connect to the database. To connect to the database, you need to tell PDO what type of database you need to connect to, the address and port of the server, the name of the database, user name, password and other information. You can use the following code to establish a connection:

$dsn = 'mysql:host=localhost;dbname=testdb';  // 服务器地址和数据库名称
$username = 'root';  // 用户名
$password = '123456';  // 密码

try{
    $dbh = new PDO($dsn, $username, $password);  // 创建PDO对象
}catch(PDOException $e){
    echo 'Connection failed: ' . $e->getMessage();  // 连接失败时输出异常信息
}

The $dsn variable should contain the type of database (mysql), the address of the server (localhost) and the name of the database to be connected (testdb). To connect to other types of databases The value of $dsn can be modified as needed. When successfully connected to the database, you can call the method of the PDO object to execute the SQL statement.

2. Insert data

Inserting new data into the database is a very common operation in developing web applications. The following code demonstrates how to use PDO to insert new data:

$sql = "INSERT INTO `users` (`username`, `password`) VALUES (?, ?)";
$stmt = $dbh->prepare($sql);  // 准备查询语句
$username = 'admin';  // 假设的用户名
$password = md5('123456');  // 假设的密码
$stmt->execute(array($username, $password)); // 执行查询语句

Before executing the SQL statement, you first need to prepare the query statement $sql. The question mark (?) in the query statement is a placeholder, and PDO will replace the placeholder according to the second parameter of the prepare() method. $stmt represents the preparation object of the SQL statement. By calling its execute() method, the query statement is executed and the placeholder is replaced with the actual parameter value.

3. Query data

It is also easy to query the database using PDO. PDO provides the query() method to execute query statements and save the query results in the PDOStatement object. The following is an example of using PDO to query the database:

$sql = "SELECT * FROM `users` WHERE `username`=?";
$stmt = $dbh->prepare($sql);  // 准备查询语句
$username = 'admin';  // 假设要查询的用户名
if($stmt->execute(array($username))){  // 执行查询语句
    $rows = $stmt->fetchAll();  // 获取查询结果
    foreach($rows as $row){  // 遍历查询结果
        echo $row['id'] . ' ' . $row['username'] . ' ' . $row['password'] . '<br/>';
    }
}

In the above code, the $sql variable stores the SELECT statement, which will query the user information of the user name 'admin' from the users table. The $stmt variable points to the prepared object of the query statement. When calling the execute() method to execute the query statement, we pass the actual value of the placeholder to PDO through the array. When the query is successfully executed, the fetchAll() method will be used to obtain the query results and the results will be saved in the variable $rows. We can use a foreach loop to output the results one by one.

4. Modify data

Similar to inserting data, modifying data is also one of the common operations. The following is an example of changing a user's password:

$sql = "UPDATE `users` SET `password`=? WHERE `username`=?";
$stmt = $dbh->prepare($sql);  // 准备更新语句
$username = 'admin';  // 假设要更新的用户名
$new_password = md5('654321');  // 假设要更新的密码
if($stmt->execute(array($new_password, $username))){  // 执行更新语句
    echo 'Updated successfully.'; // 更新成功
}

In the above code, the $sql variable stores the UPDATE statement, which is used to update the password of the user named 'admin' in the user table. Note that in the update statement, the password field after the SET keyword is the new password to be modified. At the same time, the WHERE keyword is used to specify the user record that needs to be updated. When PDO executes the update statement and completes it successfully, the program will output a prompt message indicating that the update is successful.

5. Delete data

Deleting data is similar to modifying data and is also one of the common operations. The following is an example of deleting user information through PDO:

$sql = "DELETE FROM `users` WHERE `username`=?";
$stmt = $dbh->prepare($sql);  // 准备删除语句
$username = 'admin';  // 假设要删除的用户名
if($stmt->execute(array($username))){  // 执行删除语句
    echo 'Deleted successfully.'; // 删除成功
}

In the above code, the $sql variable stores the DELETE statement, which is used to delete user information with the user name 'admin'. Similar to the update statement, this statement uses the WHERE keyword to specify the user records that need to be deleted. When PDO executes the delete statement and completes it successfully, the program will output a prompt message indicating that the delete was successful.

6. Summary

Using PDO for database operations requires some skills and techniques, but after mastering the basic operations of PDO, you will be able to add, delete, modify, and query the database more efficiently. This article introduces how to use PDO to perform basic operations such as addition, deletion, modification, and query. However, in actual development, more in-depth PDO operation technology needs to be applied to better solve development problems. Through continuous learning and practice, you will be able to master the advanced technology of PDO and develop better web applications.

The above is the detailed content of How to use PDO database to add, delete, modify and check. For more information, please follow other related articles on the PHP Chinese website!