Delete Data from MySQL Table Using PDO & MySQLi
To delete records from a table we have to use DELETE statement. It is typically used in conjugation with the WHERE clause
Syntax of MySQL Delate:
DELETE FROM table_name WHERE column_name=some_value
Consider our table has the following records:
+—-+————+———–+————————–+
| id | first_name | last_name | email |
+—-+————+———–+————————–+
| 1 | Sabbir | Rahaman |sabbirrahaman@mail.com |
| 2 | John | Carter |mahinchowdhury@mail.com |
| 3 | Sadman | Faijul | sadmanfaijul@mail.com |
| 4 | Mr. | Zami | zami009@mail.com |
| 5 | John | Potter | johnpotter@mail.com |
+—-+————+———–+————————–+
MySQLi Procedural
<?php
/* Attempt MySQL server connection. Assuming you are running MySQL
server with default setting (user 'root' with no password) */
$link = mysqli_connect("localhost", "root", "", "demo");
// Check connection
if($link === false){
die("ERROR: Could not connect. " . mysqli_connect_error());
}
// Attempt delete query execution
$sql = "DELETE FROM persons WHERE first_name='John'";
if(mysqli_query($link, $sql)){
echo "Records were deleted successfully.";
} else{
echo "ERROR: Could not able to execute $sql. " . mysqli_error($link);
}
// Close connection
mysqli_close($link);
?>
MySQLi Object Oriented
<?php
/* Attempt MySQL server connection. Assuming you are running MySQL
server with default setting (user 'root' with no password) */
$mysqli = new mysqli("localhost", "root", "", "demo");
// Check connection
if($mysqli === false){
die("ERROR: Could not connect. " . $mysqli->connect_error);
}
// Attempt delete query execution
$sql = "DELETE FROM persons WHERE first_name='John'";
if($mysqli->query($sql) === true){
echo "Records were deleted successfully.";
} else{
echo "ERROR: Could not able to execute $sql. " . $mysqli->error;
}
// Close connection
$mysqli->close();
?>
PDO
<?php
/* Attempt MySQL server connection. Assuming you are running MySQL
server with default setting (user 'root' with no password) */
try{
$pdo = new PDO("mysql:host=localhost;dbname=demo", "root", "");
// Set the PDO error mode to exception
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch(PDOException $e){
die("ERROR: Could not connect. " . $e->getMessage());
}
// Attempt update query execution
try{
$sql = "DELETE FROM persons WHERE first_name='John'";
$pdo->exec($sql);
echo "Records were deleted successfully.";
} catch(PDOException $e){
die("ERROR: Could not able to execute $sql. " . $e->getMessage());
}
// Close connection
unset($pdo);
?>
In the above example the PHP code will delete the records of those persons from the persons table whose first_name equals to John. After the executing the code the table will look like this:
+—-+————+———–+————————–+
| id | first_name | last_name | email |
+—-+————+———–+————————–+
| 1 | Sabbir | Rahaman |sabbirrahaman@mail.com |
| 3 | Sadman | Faijul | sadmanfaijul@mail.com |
| 4 | Mr. | Zami | zami009@mail.com |
+—-+————+———–+————————–+