Deleting records from a MySQL database in PHP using procedural style involves creating a form or link for deletion, sending the request to a PHP script, and then executing the DELETE SQL query.

Steps for Deleting Records:

1. Connect to the MySQL database.
2. Retrieve the record to be deleted (if needed for confirmation).
3. Submit a delete request (via a form or link).
4. Write the SQL DELETE query.
5. Execute the query using mysqli_query().
6. Provide feedback (success or error message).

Example: Deleting a User from the Database

Assume we have a users table with fields: id, username, email, and created_at. We will delete a user based on their id.

Step 1: Displaying Records with a "Delete" Option

You can show all users and provide a "Delete" link or button next to each user.

<?php
// Step 1: Connect to the MySQL database
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "test_db";

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

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

// Step 2: Query to select all users
$sql = "SELECT id, username, email FROM users";
$result = mysqli_query($conn, $sql);

// Check if there are records
if (mysqli_num_rows($result) > 0) {
    echo "<table border='1'>
            <tr>
                <th>ID</th>
                <th>Username</th>
                <th>Email</th>
                <th>Action</th>
            </tr>";

    // Step 3: Display records with a "Delete" link
    while ($row = mysqli_fetch_assoc($result)) {
        echo "<tr>
                <td>" . $row['id'] . "</td>
                <td>" . $row['username'] . "</td>
                <td>" . $row['email'] . "</td>
                <td><a href='delete_user.php?id=" . $row['id'] . "' onclick='return confirm(\"Are you sure you want to delete this user?\");'>Delete</a></td>
              </tr>";
    }

    echo "</table>";
} else {
    echo "No records found.";
}

// Close the connection
mysqli_close($conn);
?>

Explanation:

• We connect to the database and fetch all the users from the users table.
• For each user, we display a "Delete" link that sends the user’s id to the delete_user.php script via a GET request.
• The onclick='return confirm()' function prompts the user with a confirmation dialog before proceeding with the deletion.

Step 2: Handling the Deletion (delete_user.php)

This PHP script handles the actual deletion when the user clicks on the "Delete" link.

<?php
// Step 1: Check if an ID is provided in the URL
if (isset($_GET['id'])) {
    $id = intval($_GET['id']);  // Sanitize the ID

    // Step 2: Connect to the MySQL database
    $servername = "localhost";
    $username = "root";
    $password = "";
    $dbname = "test_db";

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

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

    // Step 3: Write the SQL DELETE query
    $sql = "DELETE FROM users WHERE id = $id";

    // Step 4: Execute the query
    if (mysqli_query($conn, $sql)) {
        echo "Record deleted successfully.";
        // Optionally, redirect back to the list of users
        // header("Location: users_list.php");
    } else {
        echo "Error deleting record: " . mysqli_error($conn);
    }

    // Step 5: Close the database connection
    mysqli_close($conn);
} else {
    echo "No ID provided.";
}
?>

Explanation:

• Check if the id is passed via the URL using the isset() function. The id is sanitized using intval() to ensure it’s a number.

• Connect to the database using mysqli_connect().

• Write the SQL DELETE query that removes the user with the specified id from the database.

  DELETE FROM users WHERE id = 1;
  

• Execute the query using mysqli_query(), and check whether the deletion was successful.

• Provide feedback: If successful, a confirmation message is displayed; otherwise, an error message is shown.

Step 3: Security Considerations

1. SQL Injection: The above code is vulnerable to SQL injection if the id parameter is not properly sanitized. To mitigate this risk, you should use prepared statements.

   Example using prepared statements:

   $sql = "DELETE FROM users WHERE id = ?";
   $stmt = mysqli_prepare($conn, $sql);
   mysqli_stmt_bind_param($stmt, "i", $id);
   mysqli_stmt_execute($stmt);
   

2. Confirmation before Deletion: Always prompt the user to confirm before deleting a record to avoid accidental deletions (as shown with the onclick='return confirm()').

3. Escaping Output: When displaying user data (like the id or username), ensure it is properly escaped using htmlspecialchars() to prevent Cross-Site Scripting (XSS) attacks.

   Example:

   echo "<td>" . htmlspecialchars($row['username']) . "</td>";
   

Optional: Handling Deletion with POST Requests

For security reasons, you might want to handle deletion requests via POST instead of GET. This avoids accidental deletions through URL manipulation.

In the table, you would change the "Delete" link to a form:

<form action="delete_user.php" method="POST" onsubmit="return confirm('Are you sure?');">
    <input type="hidden" name="id" value="<?php echo $row['id']; ?>">
    <input type="submit" value="Delete">
</form>

Then, in delete_user.php, handle the POST request:

<?php
// Step 1: Check if the form was submitted
if ($_SERVER['REQUEST_METHOD'] == 'POST') {
    $id = intval($_POST['id']);  // Sanitize the ID

    // Step 2: Connect to the database and delete the record (same as before)
    // ...
}
?>

Conclusion

To delete records in PHP MySQL procedural style:

1. Create a "Delete" option (link or button) that passes the record's id to a PHP script.
2. Handle the delete request by writing and executing an SQL DELETE query using mysqli_query().
3. Provide feedback: Inform the user if the deletion was successful or if an error occurred.
4. Security: Always sanitize input to prevent SQL injection and use confirmation dialogs to avoid accidental deletions.