To delete data, use the DELETE SQL command:
DELETE FROM Table WHERE column = value
Let’s create a test base for an example. In which there will be columns id, name, age.
CREATE TABLE Users (id INTEGER AUTO_INCREMENT PRIMARY KEY, name VARCHAR(30), age INTEGER)
Now let’s create an index.php file in which we will display a list of all users with a link to delete their data.
index.php code:
<!DOCTYPE html>
<html>
<head>
<title>Delete user data</title>
<meta charset="utf-8" />
</head>
<body>
<?php
//create MySQL connection
$conn = new mysqli("localhost", "root", "password", "testdb");
//check connection errors
if($conn->connect_error){
die("Error: " . $conn->connect_error);
}
//write the SQL command in the variable
$sql = "SELECT * FROM Users";
if($result = $conn->query($sql)){//get users list with info
echo "<table><tr><th>Name</th><th>Age</th><th></th></tr>";
foreach($result as $row){
echo "<tr>";
echo "<td>" . $row["name"] . "</td>";
echo "<td>" . $row["age"] . "</td>";
echo "<td><form action='delete.php' method='post'>
<input type='hidden' name='id' value='" . $row["id"] . "' />
<input type='submit' value='Удалить'>
</form></td>";
echo "</tr>";
}
echo "</table>";
$result->free();//clean the memory occupied by the query results
} else{
echo "Ошибка: " . $conn->error;
}
$conn->close();//close database connection
?>
</body>
</html>
In the table, for each row, a form is defined that sends data in a POST request to the delete.php script. To pass
in delete.php the identifier of the object to be deleted, on the form a hidden field is defined to store the id of the object.
Now let’s create the delete.php script itself, which will delete user data:
<?php
if(isset($_POST["id"]))//check whether the value has come by the method of POST
{
$conn = new mysqli("localhost", "root", "password", "testdb");
if($conn->connect_error){
die("Error: " . $conn->connect_error);
}
$userid = $conn->real_escape_string($_POST["id"]);
//wite our SQL request
$sql = "DELETE FROM Users WHERE id = '$userid'";
if($conn->query($sql)){
echo 'User deleted';
}
else{
echo "Error: " . $conn->error;
}
$conn->close();
}
?>
Delete data in MySQL. PHP function example
May 8, 2022