PHP MySQL Delete Data

PHP MySQL Delete Data Tutorial

In PHP MySQL Delete Data is used to remove records from a MySQL table. In PHP, you can execute DELETE queries using mysqli or PDO.

Warning: Without a WHERE clause, DELETE will remove all rows in the table.

Using `mysqli` (Procedural)

<?php
$conn = mysqli_connect("localhost", "root", "", "test_db");

if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}

// Delete specific record
$sql = "DELETE FROM users WHERE id=3";

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

mysqli_close($conn);
?>

<?php

$conn = mysqli_connect("localhost", "root", "", "test_db");

if (!$conn) {

die("Connection failed: " . mysqli_connect_error());

}

// Delete specific record

$sql = "DELETE FROM users WHERE id=3";

if (mysqli_query($conn, $sql)) {

echo "Record deleted successfully";

} else {

echo "Error deleting record: " . mysqli_error($conn);

}

mysqli_close($conn);

Deletes the record where id = 3.

Using `mysqli` Object-Oriented

<?php
$conn = new mysqli("localhost", "root", "", "test_db");

if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}

// Delete record
$sql = "DELETE FROM users WHERE name='Ravi'";

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

$conn->close();
?>

<?php

$conn = new mysqli("localhost", "root", "", "test_db");

if ($conn->connect_error) {

die("Connection failed: " . $conn->connect_error);

}

// Delete record

$sql = "DELETE FROM users WHERE name='Ravi'";

if ($conn->query($sql) === TRUE) {

echo "Record deleted successfully";

} else {

echo "Error deleting record: " . $conn->error;

}

$conn->close();

Object-oriented style uses $conn->query().

Using PDO

<?php
try {
$conn = new PDO("mysql:host=localhost;dbname=test_db", "root", "");
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

// Delete record using prepared statement
$stmt = $conn->prepare("DELETE FROM users WHERE age < :age");
$age = 28;
$stmt->bindParam(':age', $age);
$stmt->execute();

echo "Record(s) deleted successfully";

} catch(PDOException $e) {
echo "Error: " . $e->getMessage();
}

$conn = null; // Close connection
?>

<?php

try {

$conn = new PDO("mysql:host=localhost;dbname=test_db", "root", "");

$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

// Delete record using prepared statement

$stmt = $conn->prepare("DELETE FROM users WHERE age < :age");

$age = 28;

$stmt->bindParam(':age', $age);

$stmt->execute();

echo "Record(s) deleted successfully";

} catch(PDOException $e) {

echo "Error: " . $e->getMessage();

}

$conn = null; // Close connection

Using prepared statements ensures security against SQL injection.

Delete All Records

Always be careful! Use WHERE to avoid removing all records unintentionally.

Check How Many Rows Were Deleting

This helps confirm how many records were removing.

Difference Between DELETE and TRUNCATE

DELETE removes records row by row and supports WHERE condition
TRUNCATE removes all records instantly and resets auto increment
TRUNCATE cannot be rolled back

Use DELETE when conditions are needing.

Key Points

DELETE removes one or more records from a table.
Always include a WHERE clause unless you intend to delete all records.
Use prepared statements for dynamic or user-supplied data.
mysqli_query() or $conn->query() executes the DELETE query.
For PDO, use prepare() and execute() for safety.

PHP MySQL Delete Data