PHP MySQL Delete Record

The DELETE statement is used to delete records from a table.

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

DELETE statement syntax

DELETE FROM table_name
WHERE some_column = some_value

The WHERE clause specifies which record or records that should be deleted.

If you omit the WHERE clause, all records will be deleted.

Example

Let’s look at the “Students” table:

id	firstname	lastname	email	reg_date
1	Nil	Doe	nil@example.com	2014-10-22 14:26:15
2	Smith	Patel	smith@example.com	2014-10-23 10:22:30
3	Julie	Simon	julie@example.com	2014-10-26 10:48:23

The following examples delete the record with id=3 in the “Students” table:

Example – MySQLi Procedural

<?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 Students WHERE id=3";

if (mysqli_query($conn, $sql)) {
  echo "Record deleted successfully";
} else {
  echo "Error deleting record: " . mysqli_error($conn);
}

mysqli_close($conn);
?>

Example – MySQLi Object-oriented

<?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 Students WHERE id=3";

if ($conn->query($sql) === TRUE) {
  echo "Record deleted successfully";
} else {
  echo "Error deleting record: " . $conn->error;
}

$conn->close();
?>

Example – PDO

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

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);

  // sql to delete a record
  $sql = "DELETE FROM Students WHERE id=3";

  // use exec() because no results are returned
  $conn->exec($sql);
  echo "Record deleted successfully";
} catch(PDOException $e) {
  echo $sql . "<br>" . $e->getMessage();
}

$conn = null;
?>

After the record is deleted, the table will look like this:

id	firstname	lastname	email	reg_date
1	Nil	Doe	nil@example.com	2014-10-22 14:26:15
2	Smith	Patel	smith@example.com	2014-10-23 10:22:30