SQL: Delete: Free PHP & MySQL Tutorial

Topics Covered in This PHP & MySQL Tutorial:

The DELETE Statement, Deleting Rows from a Database, Passing ID Variables in a URL

Exercise Overview

This exercise demonstrates how to safely delete records from a MySQL database using PHP. You'll learn to implement a multi-step deletion process that includes user confirmation and proper security measures—essential skills for any web application that manages user data.

Delete Syntax

Deleting database records follows a straightforward SQL pattern, but requires careful attention to security. To delete user 231 from the users table, you would execute:

DELETE FROM users
WHERE id = 231

The WHERE clause is absolutely critical here. Without it, you would delete every record in the table—a catastrophic error that has destroyed countless databases in production environments. Always double-check your WHERE conditions before executing DELETE statements.

In this exercise, we'll construct a secure, user-friendly deletion workflow consisting of three interconnected pages that guide users through the deletion process.

1. Open userList.php from the delete folder.

2. Navigate to the file in your browser:

   • Mac: localhost:8888/phpclass/delete/userList.php
   • Windows: localhost/phpclass/delete/userList.php

   You'll see a comprehensive list of all records in the users table. Notice the last column contains Delete links that will redirect users to a confirmation page—this two-step process prevents accidental deletions and follows modern UX best practices.

3. Return to your code editor to examine the underlying structure.

4. Locate the Delete link around line 49. Currently it points to deleteConfirm.php without passing any identifying information:

   <td><a href="deleteconfirm.php">Delete</a></td>

5. Add the URL parameter structure as shown in bold:

   <td><a href="deleteconfirm.php?id=">Delete</a></td>

6. Now integrate the PHP code that dynamically inserts each user's unique ID. Add the bold code directly after id=:

   <td><a href="deleteconfirm.php?id=<?php echo $id; ?>">Delete</a></td>

7. Save your changes and test the updated functionality in your browser:

   • Mac: localhost:8888/phpclass/delete/userList.php
   • Windows: localhost/phpclass/delete/userList.php

8. Click several different Delete links and observe the behavior. You'll be directed to deleteConfirm.php with the specific user ID visible in the URL query string—this demonstrates how data flows between pages in web applications.

   The deleteConfirm.php page serves as a crucial safety checkpoint, presenting users with clear options: proceed with deletion or return safely to the user list.

9. Switch back to your code editor to continue the implementation.

10. Open deleteConfirm.php from the delete folder.

11. Around line 14, modify the deleteUser.php link to pass along the user ID as shown in bold:

    <p><a href="deleteuser.php?id=<?php echo $_GET['id']; ?>">Delete User</a></p>

    This code leverages PHP's superglobal $_GET array to retrieve the ID parameter from the previous page, maintaining data continuity throughout the deletion workflow.

12. Save your changes and prepare for the final implementation step.

13. Open deleteUser.php from the delete folder.

    The database connection and prepared statement framework are already established. Your task is to implement the core SQL deletion logic, bind the parameters securely, and handle the post-deletion user experience.

14. Locate the empty $SQL variable around line 5 and add the deletion query:

    $SQL = "DELETE FROM users
            WHERE
            id = ?
            ";

    This parameterized query uses a placeholder (?) instead of directly inserting the ID value, protecting against SQL injection attacks—a fundamental security practice in modern web development.

15. Find the //bind params here comment around line 12 and replace it with the parameter binding code:

    $stmt->bind_param('i', $_GET['id']);

    The 'i' parameter specifies that PHP should expect an integer value, adding an additional layer of type safety to your database operations.

16. Locate the //go back to user list comment around line 19 and add the redirect functionality:

    //go back to user list
    require_once('userList.php');

    This approach seamlessly returns users to the updated list, where they can immediately verify that the deletion was successful.

17. Save your completed implementation and test the full deletion workflow:

    • Mac: localhost:8888/phpclass/delete/userList.php
    • Windows: localhost/phpclass/delete/userList.php

18. Select a specific Delete link and take note of which user record you're targeting for deletion.

19. On the confirmation page, click Delete User and observe as the system removes the record and displays the updated user list, confirming the successful deletion.

20. Return to your code editor and close any open files—you've successfully implemented a complete database deletion system.