Brian B. Burton's Guide to WebDNA Coding

Changing Records in a Database File
"Hey, great job on letting the real estate people enter their own records! They love it! But, uh..." so the boss starts. Figuring that self preservation is a Good Thing™, you instinctively reach under your desk, and frantically search around for your Fruit of the Forge undies to quickly put on. Before you can find them "well, you know their receptionist Marge Sweetwater, well, she made some mistakes." Your eyes and brain disconnect, and your eyes dizzily circle the room, searching for salvation, a quick look at your desk, and your mind shifts back into gear, vaguely remember that it is made of wood, a fact long since covered by things yet to be completed. "Anyway," he continues "some of the mistakes are serious, and they need these corrections now! Can't you find some way to make this easier so they will stop calling me?!?" Before you can respond, he's off in a huff, leaving the words make this easier lingering in the air.

In this lesson, we are going to learn how to program a few web pages that allows someone, using just a web browser, to modify a record at a time 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

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

Editing Stored Data on a Web Page
This is definitely the most complex thing we have covered up to this point, and one of the most difficult and time consuming ongoing programming tasks in WebCatalog. 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 modification of a record in WebCatalog, we have to go through a few steps. First, we have to allow the user to select which record they want to modify. Second, we have to show that record as pre entered data on a standard HTML form. Finally, we have to take this submitted information, and replace the original record with the new information. For this example we will do this in just three pages. In real world implementations you can do this in coding from just one page, up to about 10 pages, all depending on how complicated the application and the user interface is. (side note, geez - this seems more complicated then just using the [replace] tag that's in the manual. Damn skippy! WebDNA is a programming language. Nothing's automated here, it's a do it yourself solution.)

First Page: Show the records of the database, and select the record to edit.
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 to edit it. We'll call this page1.tpl STOP - some of you may be wondering how pages should be named, or why I name these sample pages what I do. The simple answer is that it doesn't matter, the name of the page has no meaning to WebCatalog. But a word of advise - if you make the names meaningful to humans, it makes your web site stats program so much more worthwhile, and easier for the client to interpret.

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>

Wow. What a coding mess. Actually, the code above should look mostly familiar, I ripped it off of the very first search we did.What changed? Well, the page title did, and you know how important that is :-) And the search changed a little. We coded the search to find all of the records of the database. This works for this simple database, larger databases need to have more complicated searches, but let's finish the basics first. Oh, and the search is sorted by the city. Everything else looks good, in the found items, we have rearranged the city to the front, and created a hyperlink. Oh, the hyperlink takes us to page2, and it tells WebCatalog which record we want to edit. 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
Dover, 432 Beverwyck, 3, 1
Dover, 7 Lucky Lane, 5, 5.5
Huntsville, 48 Rock Ave, 2, 2
Newton, Route 3 Box 52, 3, 2.5
Parsippany, 123 West Main, 3, 2.5
Parsippany, 88 Eighth Ave, 2, 3
Parsippany, 13 Oak Street, 1, 1
Rockaway, 62 Valley View, 1, 2
Rockaway, 99 Century Blvd, 2, 2

Second Page: Show an HTML form, with the existing record data entered.
On this page, we have to allow the website user to edit the record they selected on the previous page. Let's look at the HTML form code we used for adding a record.

<body>
  <form method="post" action="addrecord.tpl">
    Address:     <input type="text" name="_Address"><br>
    City:        <input type="text" name="_City"><br> 
    Zip Code:    <input type="text" name="_Zip"><br>
    Bedrooms:    <input type="text" name="_Bedrooms"><br>
    Bathrooms:   <input type="text" name="_Baths"><br>
    Square Feet: <input type="text" name="_Sq_Ft"><br>
    <input type="submit" name="Submit" value="Submit">
  </form> 
</body>

Obviously, I left off the <html> tags and the header info, I'll leave that part as an exercise for you, dear reader, to do if you so desire. (again)

Let's reuse this code too. We have to make some changes, the biggest change we have to make is adding an embedded search to the page, to show what's currently in the record that's being edited (as passed to us from the previous page.)

<body>
[search db=listings.db&eqADDRESSdata=[_RID]]
[founditems]
<form method="post" action="page3.tpl">
    Address:     <input type="text" name="_Address" value="[Address]"><br>
    City:        <input type="text" name="_City" value="[City]"><br> 
    Zip Code:    <input type="text" name="_Zip" value="[Zip]"><br>
    Bedrooms:    <input type="text" name="_Bedrooms" value="[Bedrooms]"><br>
    Bathrooms:   <input type="text" name="_Baths" value="[Baths]"><br>
    Square Feet: <input type="text" name="_Sq_Ft" value="[Sq_Ft]"><br>
    <input type="submit" name="Submit" value="Submit">
[/founditems]
[/search]
  </form> 
</body>

Things we changed: I added a search (duh). The search searches for a record that matches the address of the address that was passed into the page. (I used _RID as the passed in variable name, this stands for RecordID, I typically have a field of the same name in the database, which stores a sequential number, to easily select any unique record in the database.) I also changed the Form action to go to Page 3. The last part of the change was filling in the values stored in to database into the form fields. This page looks like this when displayed to the user:

Address:
City:
Zip Code:
Bedrooms:
Bathrooms:
Square Feet:

Page Three: Storing the Changed Data in the Database
Almost done! This step is similar to the final step of appending new data to a database. The difference is that in an append, you are just tacking on a new record to a database, to change a record, WebCatalog has to find the old record specifically, then change the data in just that record. This is done with a [replace] context, which is kind of an append and a search smashed together (hey, you got chocolate in my peanut butter)

To change things, we have to search for the right record, and then give WebCatalog new data by reference the actual name of the fields in the database, and then tell it what data to put in those fields. To store our data, the replace would look like:

[Replace db=listings.db&eqADDRESSdata=[_address]]Address=[url][_Address][/url]
&City=[_City]&Zip=[_Zip]&Bedrooms=[_bedrooms]&Baths=[url][_baths][/url]
&Sq_Ft=[_sq_ft][/replace]

See what I mean about the replace context looking like a fusion of search and append? Yep, Also, all of the usual rules apply about field names and variable names matching actual named values. WebCatalog is not a mind reader, and will not correct typos. If WebCatalog is directed to store information in a field not in the database, it will simply discard the information. Don't forget the URL tags around data likely to contain illegal characters (the ampersand and carriage returns and line feeds)

It is also very important to note that the entire replace has to be done on one line (no line breaks for readability!) so you're going to have to get used to seeing a really long line in your text editor. When I am putting a replace tag together, I usually type it on a few lines, and as the last step concatenate it to just one line, but your method may differ, either way, the end result has to be just one line.

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. (It will give you grief if you leave off the [/replace]) 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.

page3.tpl

<html>
[Replace db=listings.db&eqADDRESSdata=[_address]]Address=[url][_Address][/url]
&City=[_City]&Zip=[_Zip]&Bedrooms=[_bedrooms]&Baths=[url][_baths][/url]
&Sq_Ft=[_sq_ft][/replace]
 <head>
   <title>Record Saved</title>
 </head>
 <body>
   Your data has been saved, and is now live on the website. Thank You! 
 </body>
</html>

That's about as basic as you could get. I often times embed a simple search in the body, showing the record pulled back out of the database as a visual confirmation that the information was indeed saved properly in the database. It aids in debugging, and users like it too.

Final Note about [replace]. 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 roll-back to. We will talk about this advanced technique a little later.

<-- 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!