PHP MySQL Delete Data
Delete Data From a MySQL Table
The SQL DELETE statement is used to delete records from a table:
DELETE FROM table_name
WHERE some_column = some_value
NOTE: The WHERE clause
specifies which record(s) that should be deleted. If you omit the WHERE
clause, all records will be deleted!
To learn more about SQL, please visit our SQL tutorial.
Delete Data With MySQLi
Look at the "MyGuests" table:
| id | firstname | lastname | reg_date | |
|---|---|---|---|---|
| 1 | John | Doe | [email protected] | 2024-10-22 14:26:15 |
| 2 | Mary | Moe | [email protected] | 2024-10-23 10:22:30 |
| 3 | Julie | Dooley | [email protected] | 2024-10-26 10:48:23 |
The following examples delete the record with id=3 in the "MyGuests" table:
Example - MySQLi Object-oriented
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
// SQL to delete a record
$sql = "DELETE FROM MyGuests WHERE id=3";
if ($conn->query($sql) === TRUE) {
echo "Record deleted successfully";
} else {
echo "Error deleting record: " . $conn->error;
}
$conn->close();
?>
Example - MySQLi Procedural
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";
// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}
//
SQL to delete a record
$sql = "DELETE FROM MyGuests WHERE id=3";
if (mysqli_query($conn, $sql)) {
echo "Record deleted successfully";
} else {
echo "Error deleting record: " . mysqli_error($conn);
}
mysqli_close($conn);
?>
Delete Data With PDO
The following examples delete the record with id=3 in the "MyGuests" table:
Example - PDO
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";
try {
$conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
// set the PDO error mode to exception
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch(PDOException $e){
die("Could not connect. " .
$e->getMessage());
}
try {
// SQL to delete a record
$sql = "DELETE FROM MyGuests WHERE id=3";
$conn->exec($sql);
echo "Record deleted successfully";
} catch(PDOException $e) {
echo
"Error deleting record: " .$sql . "<br>" . $e->getMessage();
}
$conn = null;
?>
After the record is deleted, the table will look like this:
| id | firstname | lastname | reg_date | |
|---|---|---|---|---|
| 1 | John | Doe | [email protected] | 2024-10-22 14:26:15 |
| 2 | Mary | Moe | [email protected] | 2024-10-23 10:22:30 |
