php uses pdo to add and delete

Detailed explanation of modification operation

PHP is an extremely popular scripting language in the network field. In PHP, we often need to interact with the database to complete operations such as data storage, query and update. . For data operations, currently the most commonly used methods include mysqli and PDO. In this article, we will focus on explaining the usage of PDO (PHP Data Objects) and how to use PDO to add, delete, modify and query data.

1. The basic concept of PDO

PDO is a lightweight, consistent object-oriented data access layer processing method introduced in PHP 5.1, which provides A unified API for accessing various relational databases. Using PDO to connect to the database can provide certain database driver objects and be compatible with many different types of databases to the greatest extent. When you need to change the database, you only need to change the database driver, without modifying other database operation interfaces.

Before using PDO for database operations, we need to perform database connection operations first. PDO provides a simpler way to connect to the database:

$dbh = new PDO('mysql:host=localhost;dbname=test', $user, $pass);

Through the above code, we can Create a PDO object named $dbh that is connected to a MySQL database named test. $user and $pass are the username and password of the database, and mysql:host=localhost; represents the host address connecting to the database.

2. Using PDO for data query operations

When using PDO for data query operations, we first need to create a query statement through the PDO object and save the result in a in variables. PDO supports precompiled query statements, which can significantly improve query efficiency and avoid SQL injection attacks.

2.1 Query operation

In PDO, we first need to prepare the SQL query statement and execute it:

$sql = "SELECT * FROM users WHERE username=:username";
$stmt = $dbh->prepare($sql);
$stmt->bindParam(':username', $username);
$stmt->execute();

In the above code, we bind the parameter: username to the variable The value of $username is bound to the query statement, thus avoiding SQL injection vulnerabilities.

2.2 Obtain query results

After we successfully execute a query statement, we need to obtain the execution results. PDO provides the following ways to obtain query results:

• fetch(): Get query results row by row, returning one row of data each time;
• fetchAll(): Get the entire result set, return a two-dimensional array containing all result rows;
• fetchColumn(): Get the value of the specified column in the result set;
• rowCount(): Return the total number of rows in the query result.

The following is a sample code to obtain query results:

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

In the above sample code, we traverse the query result set through a while loop, returning one row of data each time, and echo The statement outputs the values ​​corresponding to the username and email columns in each row of data.

2.3 Binding parameters

When using PDO to perform precompiled queries, we need to bind the dynamic parameters in the query statement to variables to avoid SQL injection vulnerabilities. PDO supports the following methods for parameter binding:

• bindParam(): Bind variables to the parameters of a prepared statement;
• bindValue(): Bind values Set to the parameters of the prepared statement;
• execute(): Execute the PDOStatement object.

The following is a sample code for parameter binding using the bindParam() method:

$stmt = $dbh->prepare("SELECT * FROM users WHERE username=:username AND email=:email");
$stmt->bindParam(':username', $username);
$stmt->bindParam(':email', $email);
$stmt->execute();

In the above sample code, we use the bindParam() method to bind $username and $email A variable is bound to the corresponding parameter in the query statement, and the query operation is executed through the execute() method. In this way, we can avoid SQL injection vulnerabilities when querying.

3. Use PDO to add, delete, and modify data

When using PDO to add, delete, and modify data, we need to create a PDO object Corresponding operation statement and save the result in a variable. When executing statements through PDO objects, PDO will automatically escape the input data and precompile it before execution, thus avoiding SQL injection vulnerabilities.

3.1 Data addition operation

In PDO, we create a preprocessed SQL statement through the prepare() method, and use the execute() method to pass parameters to the statement. The following is a sample code for adding data using PDO:

// 准备SQL语句并绑定参数
$sql = "INSERT INTO `users` (`username`, `email`) VALUES (?, ?)";
$stmt = $dbh->prepare($sql);
$stmt->bindParam(1, $username);
$stmt->bindParam(2, $email);

// 执行SQL语句，插入新的用户记录
$username = 'test_user';
$email = 'test_user@example.com';
$stmt->execute();

In the above code, we first create a preprocessed SQL statement through the prepare() method, and use the bindParam() method to set the variable $username and $email are bound to the two question mark placeholders in the statement, corresponding to the two ? in VALUES (?, ?) respectively? . Finally, the SQL statement is executed through the execute() method to complete a data adding operation.

3.2 Data deletion operation

When using PDO to delete data, we need to create a preprocessed SQL statement through the prepare() method, and use the bindParam() method to delete the data bound to this statement. The following is a sample code for using PDO for data deletion:

// 准备SQL语句并绑定参数
$sql = "DELETE FROM `users` WHERE `username` = ?";
$stmt = $dbh->prepare($sql);
$stmt->bindParam(1, $username);

// 执行SQL语句，删除指定用户名的用户记录
$username = 'test_user';
$stmt->execute();

In the above sample code, we first created a preprocessed SQL statement through the prepare() method to enable it to perform data deletion operations, and passed The bindParam() method binds the variable $username to the question mark placeholder in the statement, corresponding to one of WHERE username = ?? . Finally, the SQL statement is executed through the execute() method, and a data deletion operation is completed.

3.3 Data modification operation

使用PDO进行数据修改时，我们需要通过prepare()方法创建一个预处理的SQL语句，并使用bindParam()方法将需要修改的数据绑定到该语句中。下面是使用PDO进行数据修改的示例代码：

// 准备SQL语句并绑定参数
$sql = "UPDATE `users` SET `email` = ? WHERE `username` = ?";
$stmt = $dbh->prepare($sql);
$stmt->bindParam(1, $email);
$stmt->bindParam(2, $username);

// 执行SQL语句，更新指定用户名的用户记录
$username = 'test_user';
$email = 'new_test_user@example.com';
$stmt->execute();

在上述示例代码中，我们使用prepare()方法创建了一个预处理的SQL语句，并使用bindParam()方法将需要修改的参数绑定到该语句中。其中，使用了SET语句将用户记录中的email字段进行修改，并使用了WHERE语句将修改操作限制在了指定的用户上。在bindParam()方法中，我们通过1和2两个参数分别对应了email和username，从而完成了参数绑定的操作。

4. 总结

通过本文的学习，我们了解了如何使用PDO进行MySQL数据库的连接、查询、数据添加、删除和修改等操作。在使用PDO进行操作时，我们需要使用prepare()方法创建一个预处理的SQL语句，并使用bindParam()方法对该语句中的动态参数进行绑定。这样，在使用execute()方法执行语句时，我们就可以避免SQL注入漏洞的发生，使数据操作更加安全可靠。

The above is the detailed content of php uses pdo to add and delete. For more information, please follow other related articles on the PHP Chinese website!