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

Guess what? This exercise shows you how to delete records from a database.

Delete Syntax

Deleting a record uses similar syntax to updating a user. To delete user 231 from the users table you’d write:

DELETE FROM users
WHERE id = 231

Remember again that you must always include the WHERE line. If you do not you’d delete every record in the table!

We’ll build a few pages that allow the user to delete records from the user table.

1. Open userList.php from the delete folder.

2. In a browser go to:

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

   You’ll see a list of all the records in the users table. The last column contains links that say Delete that will bring the user to a confirmation page to ask if they are sure they want to delete the user.

3. Switch back to your code editor.

4. Around line 49, find the Delete link. Currently it links to deleteConfirm.php, but does not have a URL variable:

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

5. Add the start of the variable as shown in bold:

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

6. Next we’ll add the PHP that will echo each user’s id. Directly after the id= add the bold code:

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

7. Save the page and then in a browser go to:

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

8. Try clicking a few of the links. Notice that you are taken to deleteConfirm.php and that the id of the user you click is in the URL.

   deleteConfirm.php is simply a page that contains two links. One will take the user to the page that actually deletes the user, and the other returns the user back to the user list.

9. Switch back to your code editor.

10. Open deleteConfirm.php from the delete folder.

11. Around line 14 add the user id to the deleteUser.php link as shown below in bold:

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

    Because the user id was passed along from the previous page, it is available in the $_GET array.

12. Save the page.

13. Open deleteUser.php from the delete folder.

    Most of the PHP database code has already been written for you. All that is left to do is to write the SQL, bind the parameters, and direct the user back to the user list after the record has been deleted.

14. Around line 5 find the empty $sql variable and add the bold code:

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

    This says to delete from the users table where the id is equal to the bound parameter.

15. Around line 12 find the //bind params here comment and replace it with the bold code:

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

    This binds the id variable and tells PHP to expect an integer.

16. Around line 19 find the //go back to user list comment and underneath it add the bold code:

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

    This will include the user list page.

17. Save the page and then in a browser go to:

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

18. Click one of the Delete links and be sure to make note of which one you clicked.

19. On the confirmation page click Delete User and you’ll see that record get deleted.

20. Switch back to your code editor.

21. Close any open files. We’re done for now.