PHP & MySQL - Delete Data

The MySQL DELETE statement is used to delete the existing records from a table. A WHERE clause can be used with the DELETE statement to delete the selected rows, otherwise all records will be deleted.

The syntax for using DELETE statement in MySQL is given below:

DELETE FROM table_name
WHERE conditions;

To specify condition in a query, MySQL comparison or logical operators like <, >, =, LIKE, IN, NOT, NULL etc. are used.

Along with this, to connect to the MySQL server, mysqli_connect() function can be used. After establishing the connection, mysqli_query() function can be used to perform a query on the database.

Delete Data From a MySQL Table - Object-oriented style

Consider a database containing a table called Employee with the following records:

EmpID	Name	City	Age	Salary
1	John	London	25	3000
2	Marry	New York	24	2750
3	Jo	Paris	27	2800
4	Kim	Amsterdam	30	3100
5	Ramesh	New Delhi	28	3000
6	Huang	Beijing	28	2800

The example below demonstrates how to delete records from this table where EmpID is greater than 4 (uses object-oriented style).

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDatabase";

//establishing connection 
$mysqli = new mysqli($servername, $username, $password, $dbname);

//checking connection
if ($mysqli->connect_errno) {
  echo "Failed to connect to MySQL: ". $mysqli->connect_error;
  exit();
}

//query for deleting records from the table
$sql = "DELETE FROM Employee
WHERE EmpID > 4";

//executing the query
if (!$mysqli->query($sql)) {
  echo "Error deleting records: ". $mysqli->error;
} else { 
  echo "Records deleted successfully.";
}

//closing the connection
$mysqli->close();
?>

The output of the above code will be similar to:

Records deleted successfully.

Delete Data From a MySQL Table - Procedural style

To obtain the same result using procedural style, the following script can be used.

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDatabase";

//establishing connection 
$mysqli = mysqli_connect($servername, $username, $password, $dbname);

//checking connection
if (mysqli_connect_errno()) {
  echo "Failed to connect to MySQL: ". mysqli_connect_error();
  exit();
}

//query for deleting records from the table
$sql = "DELETE FROM Employee
WHERE EmpID > 4";

//executing the query
if (!mysqli_query($mysqli, $sql)) {
  echo "Error deleting records: ". mysqli_error($mysqli);
} else { 
  echo "Records deleted successfully.";
}

//closing the connection
mysqli_close($mysqli);
?>

The output of the above code will be similar to:

Records deleted successfully.

Complete PHP MySQLi Reference

For a complete reference of all properties, methods and functions of PHP MySQLi extension, see PHP MySQLi Reference.