MySQL Delete Data

The Ultimate Guide to Deleting Data in MySQL with PHP

In this article, we'll go over the steps needed to delete data in a MySQL database using PHP. Whether you're looking to clean up old records or remove sensitive information, this guide covers the essential workflow.

Step 1: Connect to Your MySQL Database

Before you can delete any data, you'll need to connect to your MySQL database using PHP. Here's an example of how to do it:

PHP connect to mysql server

<‌?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());
}

In this example, replace localhost with the name of your server, username and password with your login credentials, and database_name with the name of your database.

Step 2: Write Your DELETE Statement

Once you've connected to the database, you can start writing your DELETE statement. This is the SQL code that will actually delete the data from your database. Here's an example of a DELETE statement:

PHP / SQL example of a DELETE statement

DELETE FROM table_name
WHERE column_name = 'value';

In this example, replace table_name with the name of your table and column_name with the name of the column you want to use to filter the data. Note that string values must be enclosed in single quotes. You'll also need to replace value with the actual data you want to match.

Step 3: Execute Your DELETE Statement with PHP

Now that you've written your DELETE statement, you can execute it using PHP. To prevent SQL injection, it's best practice to use prepared statements:

Execute Your DELETE Statement with PHP

<‌?php
// Prepare the statement
$stmt = mysqli_prepare($conn, "DELETE FROM table_name WHERE column_name = ?");
mysqli_stmt_bind_param($stmt, "s", $value);
$value = "target_value"; // Replace with the actual value

if (mysqli_stmt_execute($stmt)) {
    echo "Record deleted successfully";
} else {
    echo "Error deleting record: " . mysqli_error($conn);
}
mysqli_stmt_close($stmt);

In this example, replace table_name and column_name with your actual table and column names. The value variable is safely bound to the query.

Step 4: Close Your Connection

Finally, once you've executed your DELETE statement, you'll need to close your connection to the MySQL database. Here's an example of how to do it:

PHP close mysql connection

<‌?php

mysqli_close($conn);

And that's it! You've successfully deleted data from a MySQL database using PHP.

Conclusion

Deleting data in a MySQL database using PHP follows a clear workflow: connect, prepare the query, execute, and close the connection. While this tutorial uses procedural mysqli for simplicity, modern PHP projects typically prefer PDO or object-oriented mysqli for better flexibility and security. With these steps, you can safely manage your database records.

Practice

What are necessary to delete data from MySQL in PHP?

Establishing a connection to the MySQL serverSelecting a specific database before deletionWriting an SQL DELETE statement to remove specific dataRunning an INSERT statement to remove the dataClosing the PHP script after deletion using the END command