Forms and databases on the Web

By Jon: First published in Online Currents 2004 – 19(9) 24-26

It is relatively easy to create a form using HTML, but not so easy to process the information which the form collects from the user. Web designers planning to set up a form for user input must make decisions in advance; they may also find their choices severely limited by decisions about hosting and design software which have been made in the past. This article examines the options for processing form information from web pages, including the use of on-line databases to generate forms and produce reports. The options are presented in order from simplest to most complex.

---

1. JavaScript and VBScript

These are simple programming languages which can be interpreted and carried out by Web browsers. VBScript (for Visual Basic) tends to be used in a Windows hosting environment and JavaScript under Unix, although both will work under both systems.

JavaScript and VBScript are interpreted languages. Their actions are confined to controlling the activities of the browser itself: they have no access to the user’s computer outside the browser program. A JavaScript routine might, for instance, respond to information submitted by a user by changing the colour of the page background, by comparing two numbers in form fields to see if they match, or by doing simple calculations on a form. It could also be used to open new windows, to redirect users to other web pages, or to change images in a ‘slide show’.

JavaScript and VBScript also can be used to create, save and retrieve ‘cookies’ – small text files which are stored on the user’s own computer. Apart from this they have no information storage capability. Once a user has left the page there is no way to recall or preserve the information they have submitted, making JavaScript and VBScript by themselves unsuitable for long-term information collection and retrieval purposes.

2. FrontPage extensions for form processing

Microsoft FrontPage has a simple and user-friendly system for validating, storing and transmitting user information from forms. Unfortunately this does not use standard HTML or JavaScript but relies on Microsoft FrontPage extensions being installed on the hosting site. Because this requires a payment to Microsoft it is unlikely to be available on a free hosting system. Most paid hosts provide FrontPage extensions, but this may change over time if FrontPage continues to fall behind Macromedia Dreamweaver in popularity.

The FrontPage form handling system allows the designer to have the form results sent to a specified email address and/or appended to the bottom of a page on the website. (This page can be stored in a private directory, making it inaccessible to other users.) The information can be validated to check that fields have been filled in and that values are within acceptable ranges; if not, the user is returned to the form and prompted to complete it properly. The designer can also specify the format of the information to be saved (text or HTML) and incorporate several ‘hidden fields’ including the date and time. For designers and hosts who are happy to work with FrontPage and who are expecting a relatively small amount of data input, this is an ideal solution.

3. CGI (Common Gateway Interface) Scripting

This predates the arrival of website authoring programs like FrontPage and Dreamweaver, having been in use since the very beginnings of the Web. CGI is not itself a programming language but a protocol specifying what format the information from a form takes when it is submitted by the user. Once that format is known the information can be passed to a program which can display, store and analyse it. These programs are usually written in Perl, a relatively simple open-source interpreted language. Perl programs are stored in files on the Web, usually in a special cgi-bin directory on the host site. Because the host’s permission is required to run CGI scripts, free hosting services may not offer this, or may limit use to a few predefined programs.

Perl scripts are plain text files with an extension .pl. They can be created through Notepad or any other text authoring system. They can be uploaded to your website along with other files in the web directory, but in order for them to run their attributes must be changed on-line to make them executable. Other languages which can be used instead of Perl include C++, Unix shell languages, and Visual Basic.

Perl programming is not difficult to learn, and there are many textbooks available and tutorials on the Web. However, most web designers will prefer to hire a skilled programmer and/or make use of the many pre-written Perl scripts available through the Web. Matt’s Script Archive, for instance (http://www.scriptarchive.com), offers free generalised Perl/CGI scripts for site searching, sending form results via email, displaying images and sending and receiving cookies.

4. Hosting decisions: PHP, ASP or ColdFusion?

All the above technologies will work (or can be made to work) on any hosting platform. More sophisticated database management tools, however, depend on the operating system running on the host. The two most popular systems are PHP (a recursive acronym for ‘PHP: Hypertext Preprocessor’), which runs only on Unix/Linux systems and ASP (Active Server Pages), which runs only on Windows NT/2000 systems. (A newer variant is known as ASP.NET). Although some hosting companies offer a choice between the two systems, others will only have one, and it may not even be clear at the outset which they offer.

A useful comparison of Unix and Windows hosting plans can be found athttp://www.challengehost.com, where the differences in software are listed as follows:

Service	Under Unix hosting	Under Windows hosting
Webmail	Squirrelmail	Imail
Programming languages	JavaScript, Perl	VBScript, ASP.Net
Dynamic page handling	PHP and JSP (Java Server Pages)	ASP and ColdFusion
Database formats	MySQL	Microsoft Access

Windows hosting tends to be slightly more expensive, due to the costs of using proprietary rather than open-source software. There is a tendency for FrontPage-using web designers to opt for Windows hosting and Dreamweaver users to opt for Unix-based, although both programs will work with either kind of host.

A third option – ColdFusion hosting – is supported by a much smaller range of hosts, but may be ‘out-sourced’; a site can be hosted by one company but its ColdFusion functions can be sent off through the web to a specialised ColdFusion server like the one at Macromedia. This entails extra costs, however, and it is hard to see what advantage it offers over using the system on the main site host server.

5.   FrontPage XP and Microsoft Access

Web designers wishing to get a Microsoft Access database on to the web with a minimum of fuss should opt for a Windows hosting system and use FrontPage (XP or later) as their authoring package. A new or existing Access database can be incorporated into a FrontPage website through the Database Interface Wizard. This allows the user either to adopt an existing Access file or to create a very basic new one through following a simple set of instructions. Connections with the file are then made using both JavaScript and VBScript on ASP pages.

Unfortunately very little of the functionality of an Access database makes its way through to the Web, and Access features like data validation and drop-down lists for selecting entries all have to be laboriously re-created in ASP. (The same is true when using an Access database in Dreamweaver: see below.)

Up to three ASP pages are created by the Database Wizard: a results page where simple searches can be carried out, a submission form for new records, and an editing page where the current contents of the database can be modified or removed. If desired this third page can be password-protected to prevent unauthorised changes. The pages are given an .ASP extension and placed in a special folder named after the database, while the database itself is stored in a folder called ‘fpdb’. These folders can then be uploaded to and used from any ASP-enabled host.

FrontPage also incorporates a Database Results Wizard, which can be used to insert tables resulting from predefined searches. The searches are defined through SQL (Structured Query Language). (Complex SQL queries can be developed graphically in Microsoft Access and then cut-and-pasted into FrontPage.) The Form Properties dialog box in FrontPage also allows designers to link forms of their own design with a database.

Although this is a quick and easy way of collecting and presenting information, FrontPage XP does not provide any way of searching a database for a subset of records other than by direct programming in ASP, limiting its usefulness in this area.

6.   PHP and MySQL

PHP is another open-source system, and probably the most widely used one on the Web after CGI. It was officially launched in 1998 and is still under extensive development. It can be used for immediate responses like JavaScript or VBScript, but also for form input processing like CGI and Perl. PHP is also extensively used as a front end to the MySQL database system. Several functions in PHP relate directly to SQL database management.

PHP as a scripting language functions like JavaScript and VBScript; that is, the PHP instructions are included on the webpage along with standard HTML. PHP code is identified by opening and closing brackets that incorporate question marks: i.e.   For PHP pages to function they must be given a .PHP extension, and the host must support PHP; again, this usually limits functionality to paid hosts. As a more powerful and generalisable system, PHP is more complex and user-unfriendly than either JavaScript or Perl.

Setting up a MySQL database requires the user to create it on-line, usually through a page or sequence of pages on the host’s website control panel. Populating the database can then be done through creating on-line forms or (for those on a nostalgia trip) by using Telnet to run a series of command-line routines in order to import records from a text file. This system is not for beginners! People wanting to experiment with PHP and MySQL can find a slow but free hosting service that supports PHP at http://members.lycos.co.uk.

7.   Dreamweaver database connections

Dreamweaver’s database connection system is less user-friendly but far more powerful than the Database Wizard in FrontPage. It relies on the user establishing two databases – one on-line and one on their local system – and giving them the same Data Source Name (DSN). This DSN is used on the local system to set up the forms and links which connect to the database; then when all this is copied to the Web the same DSN provides access to the on-line data. An on-line DSN can be set up through your website host’s Control Panel. The database can be in any one of a number of formats including MySQL (on a Unix host), Microsoft Access (on a Windows host) or a plain comma-delimited text file on any hosting system. Be careful when synchronising your local with the on-line site; you don’t want a database file which has been modified on the web to be written over by an older local version, or vice versa.

A database-enabled site is set up in Dreamweaver in the normal way, with the addition of a Testing Server which identifies the type of database access to use. Dreamweaver offers ASP with JavaScript or VBScript, ASP.NET, PHP with MySQL and ColdFusion. It is necessary to plan ahead, as once this decision is made, changing it will be a major project.

I set up an ASP system with VBScript, linked to an Access database containing four tables. Once the basic links were in place, the following tasks became possible. Each required one or more .ASP pages to be created and linked to the relevant tables in the database.

• Listing all records in any given order
• Listing a subset of fields and records based on a fixed criterion
• Listing a subset of fields and records based on a criterion entered by the user
• Going from a summary list of all records to a detailed display for any one record
• Modifying any or all of the fields for any given record
• Deleting a record
• Adding new records