PHP MySQLi DELETE - Deleting Data from the Database

Deleting Data with the DELETE Statement

The DELETE statement is used to delete existing records from a table. The WHERE clause is of crucial importance here, as it specifies which record should be deleted. If you forget it, all records in the table will be deleted!

EXAMPLE

<?php
$server = "localhost";
$username = "root";
$password = "";
$database = "mysqli_database";

// Establishing a connection
$connection = mysqli_connect($server, $username, $password, $database);

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

// 1. Creating the table (if it does not exist yet)
$table = "CREATE TABLE IF NOT EXISTS mysqli_table (
    table_id INT(11) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    fruit VARCHAR(255) NOT NULL,
    vegetable VARCHAR(150) NOT NULL,
    date TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)";
mysqli_query($connection, $table);

// 2. Inserting 4 test records
$insert_sql = "INSERT INTO mysqli_table (fruit, vegetable) VALUES ('Apple', 'Carrot');";
$insert_sql .= "INSERT INTO mysqli_table (fruit, vegetable) VALUES ('Banana', 'Cabbage');";
$insert_sql .= "INSERT INTO mysqli_table (fruit, vegetable) VALUES ('Orange', 'Turnip');";
$insert_sql .= "INSERT INTO mysqli_table (fruit, vegetable) VALUES ('Plum', 'Chicory')";
mysqli_multi_query($connection, $insert_sql);

// Wait for multi_query to finish insertions
while (mysqli_next_result($connection)) { if (!mysqli_more_results($connection)) break; }

// 3. Deleting a record (Delete the row where table_id is 2)
$delete_sql = "DELETE FROM mysqli_table WHERE table_id = 2";
mysqli_query($connection, $delete_sql);

// 4. Reading remaining data for output
$sql = "SELECT table_id, fruit, vegetable, date FROM mysqli_table";
$result = mysqli_query($connection, $sql);

mysqli_close($connection);
?>

RESULT

The result will be displayed here...

Always use WHERE to limit the deletion to specific rows.

Security: Always use prepared statements (prepare) to prevent SQL injection.