How to delete data from mysql database in php

1. #Back up the database before deleting

Before deleting, it is best to back up the database. Common strategies for working with PostgreSQL and MySQL databases. Can be rewritten as: Common PostgreSQL and MySQL database processing strategies. 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. Using the DELETE statement, you can delete specific rows or use wildcards to delete rows in batches.

<?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 can cause your application to Data leakage. 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[&#39;id&#39;];

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

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

//执行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

When you need to delete data in multiple tables, transaction management is very important. In PHP, transaction-based deletion can be achieved by using MySQLi or PDO extensions.

<?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. When encountering an error, you can use the rollback() function to undo the previous deletion operation.

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!