PHP & MySQL Tutorial PHP & MySQL References

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:

EmpIDNameCityAgeSalary
1JohnLondon253000
2MarryNew York242750
3JoParis272800
4KimAmsterdam303100
5RameshNew Delhi283000
6HuangBeijing282800

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.