Building Dynamic Web Sites:Part IV

By Chris Wenham

Summary: Learn how to create a web catalog by importing your information from a database or spreadsheet program.

Last month I showed you how to create a simple "poor man's database" that was used to generate a catalog web site, one that could be used in an e-commerce site or "Web storefront." The database wasn't really a very practical one, as its "format" was very ad-hoc and consisted mostly of embedding commands for the HTML preprocessor into the data itself. Not very elegant, and not very editable. If you had a catalog with hundreds of items, the database file could get extremely unwieldy in an ordinary text editor. What we really need is the ability to import from a real database.

Luckily this is easier than it may seem. Most database programs for OS/2 can export to a comma or tab delimited format and those are a piece of cake to work with. In fact, PPWizard has an #import function that does just this.

#import
This function is one of the most complex and powerful in PPWizard's set. By default, the command will read a comma delimited, tab delimited and fixed-column-width file and format the contents it finds into a table - the most obvious structure for what is essentially data in tabular form. For example, say you had a comma delimited file called staff.csv that looked a bit like this: Chris Wenham,Editor-In-Chief,chris@os2ezine.com Chris Wright,Senior Editor,wright@dtcweb.com And imported it into a web page with this line: Then it would generate the code for a table that looks like this: Name 	       Position 	       Email Chris Wenham 	Editor-In-Chief       chris@os2ezine.com Chris Wright 	Senior Editor 	      wright@dtcweb.com It's quite simple to see how PPWizard mapped the names of the fields with the records in the staff.csv by taking them in the order in which they were named on the #import line. As it turns out, you have the power to rearrange the order of these columns as you please, but we won't be covering that this week.
 * 1) import staff.csv CMA '' "Name" "Position" "Email"

Looking at the #import line itself, you see that after the filename of the file to import there's a keyword "CMA." This tells PPWizard that it's importing a CoMmA separated file. Other keywords are "TAB" for tab delimited files (good if some of your records have commas in their data) and "FIX" for fixed width files. Check the PPWizard manual, because additional format support may have been added by the time you read this. There's also the option to specify the delimiter yourself, so if you used semicolons instead of commas, PPWizard will know what to look for.

If you put a minus sign after the file type keyword, such as "CMA-" or "TAB-" then PPWizard will ignore the first line it finds in the file - assuming it's probably where the database had inserted the names of the fields rather than the first actuall record.

It's easy to change the way the table looks, because PPWizard stores its defaults in variables you can override with the #define command. For example, say we put these two lines before the #import statement:  {$Column2} \ {$Column3}  (The backslashes "\" tell the preprocessor that the #define statement is continued on the next line)
 * 1) define IMPORT_TABLE_ATTRIBS BORDER=1 CELLSPACING=2
 * 2) define IMPORT_RECORD {$Column1} \

PPWizard would then generate a table that looks like this: Name 	       Position 	        Email Chris Wenham 	Editor-In-Chief 	chris@os2ezine.com Chris Wright 	Senior Editor 	       wright@dtcweb.com Not only was the border and cell spacing changed, but the e-mail addresses are now properly hyperlinked. We just told PPWizard to format records differently than the default settings specify. There are several other defaults that can be overridden, ones that can switch off the formation of tables altogether. It's these that we're going to use when creating a better version of the "Catalog Maker" from last month.

Making #import act like a conversion utility
We could settle for making our web catalog take the form of a boring table, but we're not. Remember that makeshift "poor man's database" format from last month? It's actually pretty good for making PPWizard create all the files for us, but it really only should have been an intermediate format, a temporary work file that gets thrown away. What we need to do is convert the easily manageable and exportable CSV format into our "poor man's database" format and keep the same templates from last month.

To do this we're going to make the preprocessor eat its own lunch, in effect. First we make it create a poor man's database, then we make it use it. Take a look at the following code, stored in a file called convert.it, and you'll see that we're tricking PPWizard into writing "#define" statements instead of finished HTML code. We'll then make it read back those #define statements that it just generated, using them to build our catalog pages. 
 * Lets create and start writing to a new file
 * 1) output database.it AsIs


 * Clear all the defauts used to define a table's HTML code
 * 1) define IMPORT_BEFORE
 * 2) define IMPORT_AFTER
 * 3) define IMPORT_HEADER

<?hash>define+ Name {$Column2}<?NewLine> \ <?hash>define+ Materials {$Column3}<?NewLine> \ <?hash>define+ Description {$Column4}<?NewLine> \ <?hash>define+ Picture {$Column5}<?NewLine> \ <?hash>define+ Price {$Column6}<?NewLine> \ <?hash>include item.it<?NewLine>
 * Create "#define" statements for each record
 * 1) define IMPORT_RECORD <?hash>define+ File {$Column1}<?NewLine> \


 * Import the tab delimited file with the database contents
 * 1) import catalog.csv CMA '' "File" "Name" "Mat" "Desc" "Pic" "Price"

 It assumes there's a file called catalog.csv, looking a bit like the "staff.csv" of earlier, but which contains our catalog of goods instead. Notice the "<?hash>" and "<?NewLine>" tags. These are built-in to the preprocessor and are substituted with the hash character ("#") and a carriage return respectfully. They had to be referenced this way so that PPWizard wouldn't try to interpret the line before we're ready.
 * Close the file
 * 1) output

The IMPORT_BEFORE variable used to contain the "" line that started the HTML code for a table. But we cleared it so it won't try to create a table where we don't want one. We also cleared a few others, then redefined the IMPORT_RECORD variable so that instead of putting the data from the imported file into table cells, it's placed after a "#define+" line instead.

(By the way, after last month's column, Dennis Bareis - author of PPWizard - pointed out that if you use "#define+" instead of plain "#define", you won't get any warnings when the variables are redefined a moment later by the next database record. He also pointed out a couple of other good tips, which you'll see later)

When you run the command "PPWizard convert.it" on the command line, a file called database.it is generated. It's contents will look a bit like this:


 * 1) define+ File item1
 * 2) define+ Name Deluxe Ball Point Pen
 * 3) define+ Materials Oak, Paduk, Bubigna, Cherry
 * 4) define+ Description A fine writing implement.
 * 5) define+ Picture ballpen.gif
 * 6) define+ Price $79.95
 * 7) include item.it


 * 1) define+ File item2
 * 2) define+ Name Elegant Fountain Pen
 * 3) define+ Materials Oak, Chak-De-Koke, Paduk, Bloodwood
 * 4) define+ Description A magnificent compliment to your desk.
 * 5) define+ Picture fountpen.gif
 * 6) define+ Price $99.95
 * 7) include item.it

(Don't worry if there are spaces before some of the #define+ lines, this won't hurt the next step)

You should recognize this format from last month (with a few changes as per Dennis's suggestion; the #output and lines got moved into item.it). But this time, it wasn't you who created it directly, it was created by PPWizard.

Taking the contents page from last month we make a slight change to the HTML code, making it look like this:


 * Make PPWizard convert from a CSV database to a "#define" file
 * 1) include convert.it



 <HEAD> <TITLE>Frank's World Of Handcrafted Goods</TITLE> </HEAD> <BODY>

<H1>Frank's World Of Handcrafted Goods</H1>

<P>Choose from our wide range of quality handcrafted goods!

<ul> </ul>
 * Make PPWizard now include the file that it generated earlier
 * 1) include database.it

Please make checks and money orders payable to: Frank's World Of Handcrafted Goods. Mail your orders to: Frank, 187 Hancrafted Way, Woodtown, NY, 12345 or phone (555)555-2624 E-mail frank@frankcrafts.com

</BODY> </HTML> </PRE>

At the top is the #include line that starts PPWizard working on converting the CSV file you exported from your database into an intermediate, throwaway format that drives the production of all the separate catalog pages. In between the Unordered List tags ( <ul> ) is the line to read back the file PPWizard had just finished creating earlier. In that file is the data and the commands needed to create the remaining catalog pages. To give you an idea of what all this mumbo jumbo produces, here is an example web site created using the source code from this month's column.

The data for it was stored in a Lotus Approach database, then exported into a Comma Separated Value file. By typing "PPWizard contents.it" on the command line (something you can create a desktop icon for, to do the job even faster), this exported file was sucked in, mulched, and spat out as a series of finished web pages ready to go up. Since the original data was stored in Approach, it was easy to create many more records than last month's example. It was also easier to sort and edit these records.

This example is also not typically how databases are leveraged onto the web. Most companies with large and relational databases will make use of an SQL server (Standard Query Language). But so many principles are still shared. Once the data is read, it's usually positioned into the HTML code using a means similar to ours: a special tag is embedded into the web page code and the web server substitutes it with the real data a second before it's served to the browser. Serving data through SQL is something I may be tackling in the future, just as soon as I figure it out myself.

In Part V of Building Dynamic Web Sites In OS/2 I'll be showing you how to make conditional statements, how to modify text on the fly, and even how to execute REXX code in the middle of a web page.