How to delete data from mysql database in php

PHP and MySQL are the most commonly used database programming languages ​​and play a very important role in the development of web applications. As information technology advances, data management becomes even more important. The operation of PHP and MySQL databases requires great caution, and deleting data is a very important task involving financial security.

In this article, we will introduce how to delete data using MySQL database in PHP to ensure best practices and security when handling data.

1. Back up the database before deleting

Before deleting, it is best to back up the database. This is a common strategy for working with PostgreSQL and MySQL databases. Backups can help you retrieve your data if you accidentally delete it. You can use phpMyAdmin or command line tools to complete the backup. Use the mysqldump command on the command line to back up the database.

2. Use DELETE statement

Use DELETE statement to delete data in the table. The DELETE statement allows you to delete specific rows, or you can use wildcards to delete rows in bulk.

<?php
//连接到数据库
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";

//创建连接对象
$conn = new mysqli($servername, $username, $password, $dbname);

//检查连接
if ($conn->connect_error) {
    die("连接失败: " . $conn->connect_error);
}

//编写删除数据的SQL语句
$sql = "DELETE FROM myTable WHERE id=1";

//执行SQL语句
if ($conn->query($sql) === TRUE) {
    echo "数据删除成功";
} else {
    echo "删除失败: " . $conn->error;
}

//关闭连接
$conn->close();
?>

In the above code, we use the DELETE statement to delete the row with ID 1 from the myTable table. If the row is successfully deleted, the program will output "Data Deletion Successful"; if it is not deleted, the program will output "Deletion Failed" and the error message returned by MySQL.

3. Preventing SQL Injection Attacks

SQL injection attack is a type of network attack that may cause your application data to be leaked. To avoid SQL injection attacks, we can use PHP’s mysql_real_escape_string() function to escape user-entered data.

<?php
//连接到数据库
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";

//创建连接对象
$conn = new mysqli($servername, $username, $password, $dbname);

//检查连接
if ($conn->connect_error) {
    die("连接失败: " . $conn->connect_error);
}

//获取用户输入的数据
$id = $_POST['id'];

//使用mysql_real_escape_string()函数转义用户输入的数据
$id = mysql_real_escape_string($id);

//编写删除数据的SQL语句
$sql = "DELETE FROM myTable WHERE id='$id'";

//执行SQL语句
if ($conn->query($sql) === TRUE) {
    echo "数据删除成功";
} else {
    echo "删除失败: " . $conn->error;
}

//关闭连接
$conn->close();
?>

In the above code, we use the mysql_real_escape_string() function to escape user-entered data to prevent SQL injection attacks.

4. Use transaction management to delete operations

Transaction management is very important when you need to delete data in multiple tables. In PHP, transaction-based deletion can be achieved using MySQLi or the PDO extension.

<?php
//连接到数据库
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";

//创建连接对象
$conn = new mysqli($servername, $username, $password, $dbname);

//检查连接
if ($conn->connect_error) {
    die("连接失败: " . $conn->connect_error);
}

//开启事务
$conn->begin_transaction();

//编写删除数据的SQL语句
$sql1 = "DELETE FROM myTable WHERE id=1;";
$sql2 = "DELETE FROM myTable1 WHERE id=2;";

//删除数据
if ($conn->query($sql1) === TRUE && $conn->query($sql2) === TRUE) {
    //提交事务
    $conn->commit();
    echo "所有数据删除成功";
} else {
    echo "删除失败: " . $conn->error;
    //回滚事务，撤销删除操作
    $conn->rollback();
}

//关闭连接
$conn->close();
?>

In the above code, we use the begin_transaction() function to start the transaction, and use the commit() function to submit the result at the end. If deletion encounters an error, you can use the rollback() function to undo the previous deletion operation.

Summary

In PHP and MySQL, deleting data is an extremely important and sensitive task. By backing up, using DELETE statements, preventing SQL injection attacks, and using transactions to manage delete operations, we can ensure best practices and security when handling and managing data.

The above is the detailed content of How to delete data from mysql database in php. For more information, please follow other related articles on the PHP Chinese website!