Brian B. Burton's Guide to WebDNA Coding

Deleting Records in a Database File
"Hi, this is Marge Sweetwater, over at the real estate company. Recently some of our properties sold, and I'm using the website, and uhh, I can't figure out how I can just get rid of these old listing, anyways, if you could give me a call back and tell me how ya do this, that'd be great." You smack your forehead while listening to this voicemail. How did you overlook something as simple as deleting a record?

In this lesson, we are going to learn how to delete a record in a WebCatalog hosted database. For the purposes of this guide, I will be illustrating the database structure with spaces to improve legibility, your databases when done properly will probably not look like this if viewed in a text editor.

A sample of our database file listings.db, used in this lesson.

Address          City        Zip     Bedrooms   Baths  Sq_Ft
123 West Main    Parsippany  07054   3          2.5    2000
13 Oak Street    Parsippany  07054   1          1      950
48 Rock Ave      Huntsville  08943   2          2      1700

Deleting Records in a Database Using a Web Page
So far we've covered adding, searching, and modifying data records in a database. The last step would be deleting a record. In WebCatalog, deleting a record is kind of the search from hell. Not because it's hard to code, no, but instead because it's coded very similar to a search, except that the records that are found, are deleted. On the spot. Any and all that match. No Undo.

Before we go on, it is essential that you understand database searches. If not, please go back, and reread the section on searches (and maybe try your own hand at coding one or two) before starting this.

To perform a deletion of a record in WebCatalog, we have to go through two steps. First, we have to allow the user to select which record(s) they want to delete. Second, we have to delete the matching record(s). For this example we will do this as a modification to the record editor we created in the editing a record section. In real world implementations you can do this in coding in one or two pages, all depending on how complicated the application and the user interface are.

First Step: Show the records of the database, and select the record to delete.
This page is simply going to list the records in the database to allow the user to click on any one record, to go to the next page which deletes it. Let's just reuse page1.tpl for the data editor, shall we? Let's review the code:

Page1.tpl

<HTML>
   <head><title>Choose your record to edit...</title></head>
   <body>
Please click on the item below to edit it.<BR>
[search db=listings.db&neCITYdata=[blank]&CITYsort=1]
City, Address, Bedrooms, Bathrooms <BR><BR>
[founditems]
[City], <A href="page2.tpl?_RID=[url][address][/url]">[Address]</A>, [Bedrooms], [Baths]<BR>
[/founditems]
[/search]
   </body>
</HTML>

Hmm, we want to add a link to remove a record that is no longer wanted. Let's work on the founditems loop:

[founditems]
  [City], 
  <A href="page2.tpl?_RID=[url][address][/url]">[Address]</A>,
  [Bedrooms], 
  [Baths]<BR> 
[/founditems]

We need to add a link to go to the page that will delete the record. If we keep it simple, it will look like:

[founditems]
  [City], 
  <A href="page2.tpl?_RID=[url][address][/url]">[Address]</A>,
  [Bedrooms], 
  [Baths]
  &nbsp;&nbsp;<A href="killit.tpl?_RID=[url][address][/url]">(remove)</A><BR> 
[/founditems]

So what's the deal, it looks exactly the same as the link to edit a page, only the name of the page changed! This is because we haven't told WebCatalog what to do on either subsequent page, that what the coding on those pages does. The truth of the matter is most of the time, when you get good at this, you will have several functions going to the same page, but with a hidden value, or a vague value passed through telling the next page what functionality it should have. We'll cover this in the One-Hit Wonders section.

Notice the [url] tags around the address. This encodes the space character in the address, so the link will work. (Links don't work if they have spaces in them) Here's what the page looks like on a browser:

Please click on the item below to edit it.
City, Address, Bedrooms, Bathrooms

Dover, 836 Main St, 4, 3  (remove)
Dover, 432 Beverwyck, 3, 1  (remove)
Dover, 7 Lucky Lane, 5, 5.5  (remove)
Huntsville, 48 Rock Ave, 2, 2  (remove)
Newton, Route 3 Box 52, 3, 2.5  (remove)
Parsippany, 123 West Main, 3, 2.5  (remove)
Parsippany, 88 Eighth Ave, 2, 3  (remove)
Parsippany, 13 Oak Street, 1, 1  (remove)
Rockaway, 62 Valley View, 1, 2  (remove)
Rockaway, 99 Century Blvd, 2, 2  (remove)

Step two: Deleting the record(s) from the the Database
This step is similar to an embedded search of a database. The difference is that in a delete, anything that's found, is killed. Dead.

Still want to go through with this, huh? Ok, it's your data... to do this, we have to to search for the right record(s), at which point WebCatalog will delete the record(s) that were found. The delete is as simple as:

[delete db=listings.db&eqADDRESSdata=[_address]]

Notice the similarity to a search. There is no closing tag. When WebCatalog hits this line, its finds and deletes the records. Nothing else is required.

Ok, you type the above line into a webpage, and run it, what will you see on the HTML page you get back?
NOTHING.
WebCatalog gives no feedback whatsoever that this worked. You were expecting some sort of an "are you sure" dialog box to pop up? Not here. Because this is more of a programming language then a consumer database, it is up to you to decide what you want to response to be. Let's put together a simple page, and you can see what I mean.

killit.tpl

<html>
[delete db=listings.db&eqADDRESSdata=[_address]]
<head>
   <title>Record Saved</title>
 </head>
 <body>
   This listing has been deleted from the website. Thank You! 
 </body>
</html>

That's about as basic as you could get. Some of you may want an "Are you Sure?" type of middle page. If so, then the steps would be:

allow the user to select the record to be removed( as shown above) and carry the record variable in the link.
show "are you sure" on the next page, with a link back for no, and a link to the third page caring the record variable again.
on the third page, delete the record, and show a message to that effect.

I'll leave it as an exercise to you, loyal reader, to code this if you like. A final note about [delete]. It's permanent. There is no undo. If this will become an issue, then you should make a backup of the database that you can rollback to. We will talk about this advanced technique a little later. (How permanent is permanent?)

<-- Previous Index Next -->

This whole page, and everything on it ©2001 Brian B. Burton. This page and the text contained herein may not be reproduced on any other site, or via any other means. Really, how hard is it just to link to this site, so people get to see the latest updates? http://www.burtons.com/webdna/ Thank You.

Site design by Clint Davis. Thank You Clint!