Deleting records

See more about:

Where are we?

You know how to create an admin menu, with a delete link for each record. But what happens when the user clicks on one? Let’s see.

This lesson’s goals

In this lesson, learn:

  • On the admin menu, there’s a delete link for each record. It includes the id of the record.
  • Clicking the delete link shows a confirmation page. This page shows all of the data in the record to be deleted. It has a form with a hidden field containing the record id, and a confirmation button.
  • If the user confirms the delete, another PHP program creates and runs an SQL DELETE statement.

The DELETE statement

The SQL DELETE statement deletes records from a table. Here’s a typical example:

delete from dogs where weight < 25;

This will delete all records from the dogs table that have a weight less than 25.

Typically, you use the primary key value to delete a single record. Like this:

delete from articles where article_id = 17;

Delete a DogToys product

The administration menu looks like this:

DogToys administration menu

Figure 1. DogToys administration menu

There’s a Delete link for each record. Here’s the PHP that makes it:

while( $row = $record_set->fetch_assoc() ) {
  //Get fields for an product.
  $product_id = $row['product_id'];
  ...
  print "
  ...
        <a href='confirm-delete-product.php?id=$product_id'>Delete</a>
?>
  </tbody>
</table>

Figure 2. DogToys administration menu code

The code generates HTML like this:

<a href='confirm-delete-product.php?id=2'>Delete</a>

Figure 3. HTML generated by PHP

When the user clicks on a Delete link, we could delete the record immediately. But that’s not a good idea. It would be easy to accidentally click the Delete link, maybe when aiming for the Edit link.

So let’s ask the user to confirm the deletion. That’s why the link in Figure 3 calls the page confirm-delete-product.php. It renders like this:

Confirm deletion

Figure 4. Confirm deletion

The page shows the record that the user has chosen to delete. The user has to click the button before the record will be deleted.

Here’s code for this page.

<?php
//Connect to DB.
require $path_to_root . '/library/db-connect.php';
$db = new mysqli($host, $user_name, $password, $db);
//Fetch product data.
$product_id = $_GET['id'];
$query = "select name, image_file_name, price
    from products
    where product_id = " . $product_id;
$record_set = $db->query($query);
$row = $record_set->fetch_assoc();
//Get fields.
$name = $row['name'];
$image_file_name = $row['image_file_name'];
$price = $row['price'];
//Output confirmation.
print "
  <p>You have chosen to delete this product:</p>
  <blockquote>
    <p><img src='$path_to_root/product-images/$image_file_name'> $name</p>
    <p>$$price</p>
  </blockquote>
";
?>
<form method="post" action="delete-product.php">
  <p>Are you sure you want to do this?</p>
  <p>The action cannot be undone.</p>
  <p>
    <input type="hidden" name="id" value="<?php print $product_id; ?>">
    <button type="submit">Confirm</button>
  </p>
</form>
<p><a href="index.php">< Back</a></p>

Figure 5. confirm-delete-product.php

Lines 3 and 4 connect to the database.

Line 6 gets the id of the record from the URL.

Lines 7 to 9 creates an SQL SELECT statement with the id. Line 10 runs the query.

Line 11 gets a row from the record set (there will be only one) and puts it into the variable $row. Lines 13 to 15 get the fields from $row, putting them into variables.

The next few lines show the field values:

print "
  <p>You have chosen to delete this product:</p>
  <blockquote>
    <p><img src='$path_to_root/product-images/$image_file_name'> $name</p>
    <p>$$price</p>
  </blockquote>
";

Part of Figure 5. confirm-delete-product.php

Line 21 looks a little odd:

<p>$$price</p>

Why two dollar signs? The second dollar sign is part of the variable name. The first one is shown on the page. So if $price is 9.95, then the following shows:

<p>$9.95</p>

Let’s look at the screen shot again:

Confirm deletion

Figure 4 (again). Confirm deletion

We’ve done everything except for the button. Here’s the code:

<form method="post" action="delete-product.php">
  <p>Are you sure you want to do this?</p>
  <p>The action cannot be undone.</p>
  <p>
    <input type="hidden" name="id" value="<?php print $product_id; ?>">
    <button type="submit">Confirm</button>
  </p>
</form>

Another part of Figure 5. confirm-delete-product.php

The program that will delete the product is delete-product.php. It’s the action property of the form:

<form method="post" action="delete-product.php">

We only need to send one piece of data to the page: the id of the product to delete. How to add it to the form? With a hidden field. It’s in line 29:

<input type="hidden" name="id" value="<?php print $product_id; ?>">

If the product id is, say, 17, the HTML will be:

<input type="hidden" name="id" value="17">

A hidden field is like a regular <input> field, but it isn’t shown on the page. Its value, however, is passed to the destination page, like any other field on a form.

What happens when the user clicks the Confirm button? The id is sent to delete-product.php, which actually does the deletion.

Here’s the code for delete-product.php. I left out the error checking code.

<?php
//Delete a product.
//Input:
//  id: id number of the product. POST.
$path_to_root = '..';
$product_id = $_POST['id'];
//Connect to DB.
require $path_to_root . '/library/db-connect.php';
$db = new mysqli($host, $user_name, $password, $db);
//Delete the product.
$query = "delete from products where product_id = $product_id";
$db->query($query);
//Back to admin menu.
header('location:index.php');
exit();
?>

Figure 6. delete-product.php

Line 6 gets the id passed to the page.

Lines 8 and 9 connect to the database.

Line 11 creates the SQL statement that will delete the product:

$query = "delete from products where product_id = $product_id";

Line 12 runs the query.

Finally…

header('location:index.php');

...back to the administration menu.

Delete a DogRock article

The administration menu for DogRock looks like this:

DogRock administration menu

Figure 7. DogRock administration menu

There’s a Delete link for each record. Here’s the PHP that makes it:

while( $row = $record_set->fetch_assoc() ) {
  //Get fields for an article.
  $article_id = $row['article_id'];
  ...
  print "
  ...
        <a href='confirm-delete-article.php?id=$article_id'>Delete</a>
?>
  </tbody>
</table>

Figure 8. DogRock administration menu code

Clicking on the Delete link loads a confirmation page:

Confirm deletion

Figure 9. Confirm deletion

Here’s the code for this page.

<?php
//Connect to DB.
require $path_to_root . '/library/db-connect.php';
$db = new mysqli($host, $user_name, $password, $db);
//Fetch product data.
$product_id = $_GET['id'];
$query = "select name, image_file_name, price
    from products
    where product_id = " . $product_id;
$record_set = $db->query($query);
$row = $record_set->fetch_assoc();
//Get fields.
$name = $row['name'];
$image_file_name = $row['image_file_name'];
$price = $row['price'];
//Output confirmation.
print "
  <p>You have chosen to delete this product:</p>
  <blockquote>
    <p><img src='$path_to_root/product-images/$image_file_name'> $name</p>
    <p>$$price</p>
  </blockquote>
";
?>
<form method="post" action="delete-product.php">
  <p>Are you sure you want to do this?</p>
  <p>The action cannot be undone.</p>
  <p>
    <input type="hidden" name="id" value="<?php print $product_id; ?>">
    <button type="submit">Confirm</button>
  </p>
</form>
<p><a href="index.php">< Back</a></p>

Figure 10. confirm-delete-article.php

As before, it shows the record to be deleted, then a <form> with:

  • A hidden field with the id of the record.
  • A confirmation button.

Here’s the code for delete-article.php.

<?php
//Delete an article.
//Input:
//  id: id number of the article. POST.
$path_to_root = '..';
$article_id = $_POST['id'];
//Connect to DB.
require $path_to_root . '/library/db-connect.php';
$db = new mysqli($host, $user_name, $password, $db);
//Delete the article.
$query = "delete from articles where article_id = $article_id";
$db->query($query);
//Back to admin menu.
header('location:index.php');
exit();
?>

Figure 6. delete-article.php

This is almost identical to delete-product.php.

Exercise: Deleting jokes

Add confirm-delete-joke.php and delete-joke.php to your Jokes application. They should act like their DogToys and DogRock counterparts.

You can see my solutions for the confirmation and deletion pages. But write them yourself first!

(Log in to enter your solution to this exercise.)

Summary

  • On the admin menu, there’s a delete link for each record. It includes the id of the record.
  • Clicking the delete link shows a confirmation page. This page shows all of the data in the record to be deleted. It has a form with a hidden field containing the record id, and a confirmation button.
  • If the user confirms the delete, another PHP program creates and runs an SQL DELETE statement.

What now?

We’re almost done. Time to let users edit existing records.


How to...

Lessons

User login

Log in problems? Try here


Dogs