DELETE query | Web apps

We know how to let users view records, change records, and create records. Just one operation left: delete.

Requirements

Let's start by working out what we want users to see. We made a view page like this:

View

Let's add a delete link:

Delete link

Here's the HTML to make the links:

print
"<p class='operations-container'>
<a href='add-edit-comedian-form.php?comedian_id={$comedianId}'>Edit</a>
<a href='confirm-delete-comedian.php?comedian_id={$comedianId}'>Delete</a>
<a href='add-edit-comedian-form.php'>Add new</a>
</p>\n";

Let's think about this. When the user hits Delete, what should happen?

Reflect

When the user hits Delete, what should happen?

If you were logged in as a student, the lesson would pause here, and you'd be asked to type in a response. If you want to try that out, ask for an account on this site.

Adela

Don't just delete. Make sure the user didn't click it by accident.

Right! Something like this:

Confirm delete

If the user clicks delete:

Confirmed

This gives us a new principle:

Principle

Confirm destructive actions

People make mistakes. If there's a destructive action, like deleting a record, then confirm it with users.

One approach is the delete conformation page, as in the famous goats app. There's a delete link on each edit page.

Delete link

Click it, and the app asks you to confirm. It shows you the thing you would delete, too, so you can check it's the right one.

Confirm

It's easy to get into the habit of confirming everything, though. Another approach is to ask the user to type something about the thing to be deleted. That's what Github does. When you delete a repo, it asks you type the name of the repo as confirmation.

First step

Here's the delete link on the view page:

<a href='confirm-delete-comedian.php?comedian_id={$comedianId}'>Delete</a>

This link passes the comedian id. Here's confirm-delete-comedian.php:

<?php
// Connect to the database.
require_once 'library/useful-stuff.php';
$errorMessage = '';
$comedianId = getParamFromGet('comedian_id');
$errorMessage = checkComedianId($comedianId);
if ($errorMessage == '') {
$comedianEntity = getComedianWithId($comedianId);
if (is_null($comedianEntity)) {
$errorMessage = "Sorry, there was a problem loading comedian with id $comedianId.";
}
else {
$comedianName = $comedianEntity['name'];
$comedianComments = $comedianEntity['comments'];
}
}
?><!doctype html>
<html lang="en">
<head>
<?php
$pageTitle = 'Confirm delete';
require_once 'library/page-components/head.php';
?>
</head>
<body>
<div class="container">
<?php
require_once 'library/page-components/top.php'
?>
<h1>Comedian</h1>
<?php
if ($errorMessage != '') {
print "<p class='error-message'>$errorMessage</p>";
}
else {
?>
<p>Are you sure you want to delete this comedian?</p>
<p>Id: <?php print $comedianId; ?></p>
<p>Name: <?php print $comedianName; ?></p>
<p>Comments: <?php print $comedianComments; ?></p>
<p class='operations-container'>
<a href='delete-comedian.php?comedian_id=<?php print $comedianId; ?>'>Delete</a>
<a href='view-comedian.php?comedian_id=<?php print $comedianId; ?>'>Forghedaboudit</a>
</p>
<?php
}
require_once 'library/page-components/footer.php';
?>
</body>
</html>

It's mostly the same as the view page. The operation links are different:

<p class='operations-container'>
<a href='delete-comedian.php?comedian_id=<?php print $comedianId; ?>'>Delete</a>
<a href='view-comedian.php?comedian_id=<?php print $comedianId; ?>'>Forghedaboudit</a>
</p>

The first one goes through with the delete. The Forghedaboudit link jumps back to the view page.

Doing the delete

We could run the DELETE and jump to another page, but instead, we'll let the user know what happened.

After delete

Here's the PHP that deletes the record, and makes the display.

<?php
// Connect to the database.
require_once 'library/useful-stuff.php';
$errorMessage = '';
$comedianId = getParamFromGet('comedian_id');
$errorMessage = checkComedianId($comedianId);
if ($errorMessage == '') {
$comedianEntity = getComedianWithId($comedianId);
if (is_null($comedianEntity)) {
$errorMessage = "Sorry, there was a problem loading comedian with id $comedianId.";
}
else {
// Put the field values into variables.
$comedianName = $comedianEntity['name'];
$comedianComments = $comedianEntity['comments'];
// Delete.
$sql = "DELETE FROM comedians WHERE comedian_id = :id;";
/** @var PDO $dbConnection */
$stmnt = $dbConnection->prepare($sql);
$stmnt->execute([':id' => $comedianId]);
}
}
?><!doctype html>
<html lang="en">
<head>
<?php
$pageTitle = 'Deleted';
require_once 'library/page-components/head.php';
?>
</head>
<body>
<div class="container">
<?php
require_once 'library/page-components/top.php'
?>
<h1>Comedian deleted</h1>
<?php
if ($errorMessage != '') {
print "<p class='error-message'>$errorMessage</p>";
}
else {
?>
<p>This comedian was deleted:</p>
<p>Id: <?php print $comedianId; ?></p>
<p>Name: <?php print $comedianName; ?></p>
<p>Comments: <?php print $comedianComments; ?></p>
<?php
}
require_once 'library/page-components/footer.php';
?>
</body>
</html>

The only new thing is the SQL DELETE statement.

$sql = "DELETE FROM comedians WHERE comedian_id = :id;";
$stmnt = $dbConnection->prepare($sql);
$stmnt->execute([':id' => $comedianId]);

The WHERE clause identifies the record to delete.

Up next

Let's make a report listing the comedians, with an operation link for each.