Delete Data From a MySQL Table
The SQL DELETE statement is used to delete records from a table:
text
DELETE FROM table_name
WHERE some_column = some_value
NOTE: The
WHEREclause specifies which record(s) that should be deleted. If you omit theWHEREclause, all records will be deleted!
To learn more about SQL, please visit the 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
html
<?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
html
<?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
html
<?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 |