Basic PHP Tutorial

Advanced PHP Tutorial

PHP & MySQL

PHP Reference Manual

PHP MySQL DELETE

In this tutorial, you will learn how to delete records from a MySQL table using PHP.

Delete database table data

Just as you can insert records into a table, you can use SQL DELETEThe statement deletes records from the table. It is usually used with the WHERE clause to delete only those records that match specific conditions or criteria.

The basic syntax of the DELETE statement can be given as follows:

DELETE FROM table_name WHERE column_name=some_value

Let's use a DELETE statement with a WHERE clause to perform an SQL query, and then execute this query by passing it to the PHP mysqli_query() function to delete table records. See the following persons table in the demo database:

+----+------------+-----------+----------------------+
| id | first_name | last_name | email |
+----+------------+-----------+----------------------+
|　　1　| Peter | Parker | [email protected] |
|　　2　| John | Rambo | [email protected] |
|　　3　| Clark | Kent | [email protected] |
|　　4　| John | Carter | [email protected] |
|　　5　| Harry | Potter | [email protected] |
+----+------------+-----------+----------------------+

The following PHP code in the example will delete from the persons tablefirst_nameRecords of people named John

Example: Procedural approach

<?php
/*　Attempt to connect to MySQL server. Assume you are running MySQL.
Server with default settings (user 'root' without password)　*/
$link = mysqli_connect("localhost", "root", "", "demo");
　
//Check the connection
if($link === false){
　　　　die("Error: Unable to connect. " . mysqli_connect_error());
}
　
//Attempt to execute deletion
$sql = "DELETE FROM persons WHERE first_name='John'";
if(mysqli_query($link, $sql)){
　　　　echo "Record has been successfully deleted.";
} else{
　　　　echo "Error: Unable to execute $sql. " . mysqli_error($link);
}
　
//Close the connection
mysqli_close($link);
?>

Example: Object-oriented approach

<?php
/*　Attempt to connect to MySQL server. Assume you are running MySQL.
Server with default settings (user 'root' without password)　*/
$mysqli = new mysqli("localhost", "root", "", "demo");
　
//Check the connection
if($mysqli === false){
　　　　die("Error: Unable to connect. ". $mysqli->connect_error);
}
　
//Attempt to execute deletion
$sql = "DELETE FROM persons WHERE first_name='John'";
if($mysqli->query($sql) === true){
　　　　echo "Record has been successfully deleted.";
} else{
　　　　echo "Error: Unable to execute $sql. ". $mysqli->error;
}
　
//Close the connection
$mysqli->close();
?>

Example: PDO method

<?php
/*　Attempt to connect to MySQL server. Assume you are running MySQL.
Server with default settings (user 'root' without password)*/
try{
　　　　$pdo = new PDO("mysql:host=localhost;dbname=demo", "root", "");
　　　　//Set PDO error mode to exception
　　　　$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch(PDOException $e){
　　　　die("Error: Unable to connect. ". $e->getMessage());
}
　
//Attempt to update query execution
try{
　　　　$sql = "DELETE FROM persons WHERE first_name='John'";　　
　　　　$pdo->exec($sql);
　　　　echo "Record has been successfully deleted.";
} catch(PDOException $e){
　　　　die("Error: Unable to execute $sql. ". $e->getMessage());
}
　
//Close the connection
unset($pdo);
?>

After deletion,personsThe table will look like this:

+----+------------+-----------+----------------------+
| id | first_name | last_name | email |
+----+------------+-----------+----------------------+
|　　1　| Peter | Parker | [email protected] |
|　　3　| Clark | Kent | [email protected] |
|　　5　| Harry | Potter | [email protected] |
+----+------------+-----------+----------------------+

As you can see, the record has been successfully deleted from the persons table.

Warning:The WHERE clause in the DELETE statement specifies which records should be deleted. If the WHERE clause is omitted, all records will be deleted.

PHP Ctype Functions PHP MySQL UPDATE