MySQL Delete Data

Deleting Data in MySQL with PHP

The SQL DELETE statement removes one or more rows from a table. In PHP you send that statement to MySQL over an existing database connection, and — because deletes are irreversible — you should always filter them with a WHERE clause and bind any user-supplied value through a prepared statement to guard against SQL injection.

This guide covers the full workflow with the mysqli extension: connect, build the DELETE, run it safely, read how many rows were affected, and (optionally) close the connection. It also shows the equivalent in PDO, which most modern projects prefer.

The DELETE statement at a glance

DELETE FROM table_name
WHERE column_name = 'value';

• table_name — the table you want to remove rows from.
• The WHERE clause decides which rows go. String values must be wrapped in single quotes.
• Omit WHERE and you delete every row in the table. There is no undo, so treat a WHERE-less DELETE the way you would TRUNCATE.

If you are new to filtering rows, read PHP MySQL Where first — the same condition syntax applies to DELETE, UPDATE, and SELECT.

Step 1: Connect to your MySQL database

Before deleting anything you need an open connection. mysqli_connect() returns a connection handle, or false on failure:

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

// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);

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

Replace localhost with your server host, the credentials with your login, and database_name with the database that holds the table. For a deeper look at the options, see PHP MySQL Connect.

Step 2: Delete a row safely with a prepared statement

Never paste a user-supplied value straight into the SQL string — that is how SQL injection happens. Instead, put a ? placeholder in the query and bind the value to it. MySQL then treats the value strictly as data, never as SQL.

<?php
// 1. Prepare the DELETE with a placeholder
$stmt = mysqli_prepare($conn, "DELETE FROM users WHERE id = ?");

// 2. Bind the value: "i" = integer, "s" = string, "d" = double, "b" = blob
$id = 42; // e.g. the id of the record to remove
mysqli_stmt_bind_param($stmt, "i", $id);

// 3. Execute and report the result
if (mysqli_stmt_execute($stmt)) {
    $deleted = mysqli_stmt_affected_rows($stmt);
    echo "Deleted {$deleted} record(s).";
} else {
    echo "Error deleting record: " . mysqli_stmt_error($stmt);
}

mysqli_stmt_close($stmt);

Replace users and id with your own table and column. The type string passed to mysqli_stmt_bind_param() must match the bound values: use "i" for the integer id above, "s" for text.

mysqli_stmt_affected_rows() tells you how many rows were actually removed. If it returns 0, no row matched your WHERE clause — useful for telling "deleted" apart from "nothing to delete."

Step 3: Delete with PDO (the modern alternative)

PDO works the same way and is portable across database engines. Named placeholders make the intent clearer:

<?php
$pdo = new PDO("mysql:host=localhost;dbname=database_name", "username", "password");
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$stmt = $pdo->prepare("DELETE FROM users WHERE id = :id");
$stmt->execute([':id' => 42]);

echo $stmt->rowCount() . " record(s) deleted.";

rowCount() is PDO's equivalent of affected_rows. Setting ERRMODE_EXCEPTION makes PDO throw on errors instead of failing silently.

Step 4: Close the connection (optional)

PHP closes the connection automatically when the script ends, but you can release it early on long-running scripts:

<?php
mysqli_close($conn);

Common pitfalls

• Forgetting WHERE. DELETE FROM users; empties the whole table. Always double-check the clause before running it on production data.
• Building SQL with string concatenation. "DELETE FROM users WHERE name = '$name'" is injectable. Use placeholders instead.
• Assuming a row was deleted. Check affected_rows / rowCount(); a non-matching WHERE deletes nothing and still "succeeds."
• Deleting rows another table depends on. A foreign key with ON DELETE RESTRICT will block the delete; with ON DELETE CASCADE it removes the related rows too. Know your schema first.
• Soft deletes. When you may need the data back, add an is_deleted flag and UPDATE it instead — see PHP MySQL Update Data.

Conclusion

Deleting data in MySQL with PHP is a clear, four-part workflow: connect, prepare a parameterized DELETE, execute it, and read the affected-row count. The non-negotiable rules are to always scope the delete with a WHERE clause and always bind user input through a prepared statement. To remove the matched rows differently, compare with PHP MySQL Insert Data and PHP MySQL Select Data, or go deeper on safety in PHP MySQL Prepared Statements.

Practice