htmldatamerge statement

htmldatamerge

HTML

OPTIONS

TEMPLATE

The htmldatamerge statement uses a template to generate HTML you want from a database.

Parameters

This statement has three parameters:

html – field or variable that the output HTML will be placed into.

options – list of options that modify how the HTML is generated from the template.

template – controls how the data is placed into the HTML output by this statement. The template consists of a section of text with HTML tags and Panorama formulas embedded in it. The formulas are embedded in between curly braces { and }. You may embed as many formulas as you want – either one big formula or lots of little ones. However, each formula must produce text as an output, so any numbers must be converted to text with the str( or pattern( functions.

Description

This statement uses a template to generate HTML you want from a database. Typical applications include lists, formatted tables, and even JavaScript (usually to initialize a JavaScript array). Here is a simple example that creates an HTML list:

htmldatamerge cgiHTML,{},
  |||<li>{str(seq())} <b>{Hotel}</b>, {pattern(Rate,"$#,.##") } ({Phone})|||

This template has four formulas embedded in it:

{str(seq())} ☞ Merges an automatic sequence number: 1, 2, 3 etc.
{Hotel} ☞ Merges the contents of the Hotel field
{pattern(Rate,"$#,.##") } ☞ Merges the Rate field, converted to text
{Phone} ☞ Merges the Phone field

The output HTML looks something like this:

1 Aspen Square, $85.00 (925-1000)
2 Best Western Aspenalt Lodge, $52.00 (927-3191)
3 Boomerang Lodge, $72.00 (925-3416)
4 Innsbruck Sports Motel, $74.00 (925-2980)
5 Limelite Lodge, $62.00 (925-3025)
6 Sands Motel, $22.00 (366-3581)
7 The Molly Gibson Lodge, $75.00 (925-2580)
8 Ullr Lodge, $48.00 (769-7146)
9 Hotel Jerome, $295.00 (920-1000)
10 International Hotel, $129.50 (555-9876)
11 Snowflake Inn, $59.00 (925-3221)

Using the browser’s View Source command we can examine the actual HTML generated by the procedure. Notice that the htmldatamerge statement was smart enough to add <ul> and </ul> tags at the beginning and end of the list. If your layout begins with <li> it will automatically add these tags. If your layout begins with <tr> it will automatically add <table> and </table> tags. (You can override these defaults and specify your own header and footer, see the next section.)

<ul>
<li>1 <b>Aspen Square</b>, $85.00 (925-1000)
<li>2 <b>Best Western Aspenalt Lodge</b>, $52.00 (927-3191)
<li>3 <b>Boomerang Lodge</b>, $72.00 (925-3416)
<li>4 <b>Innsbruck Sports Motel</b>, $74.00 (925-2980)
<li>5 <b>Limelite Lodge</b>, $62.00 (925-3025)
<li>6 <b>Sands Motel</b>, $22.00 (366-3581)
<li>7 <b>The Molly Gibson Lodge</b>, $75.00 (925-2580)
<li>8 <b>Ullr Lodge</b>, $48.00 (769-7146)
<li>9 <b>Hotel Jerome</b>, $295.00 (920-1000)
<li>10 <b>International Hotel</b>, $129.50 (555-9876)
<li>11 <b>Snowflake Inn</b>, $59.00 (925-3221)
</ul>

Here is a modified version of the previous procedure:

htmldatamerge cgiHTML,{},
  |||<li><font face="verdana" size=-2> <b>{Hotel}</b>,
    {pattern(Rate,"$#,.##")} ({Phone})<p>
    {?(Description="","",Description+"<p>")}</font>|||

This procedure uses the ?( function to merge in the description (if any). We’ve also changed the HTML tags to render the list using extra small Verdana font.

Aspen Square, $85.00 (925-1000)
The center of activities in downtown Aspen. Luxury condominiums with kitchens, fireplaces, color TV, private balconies, daily maid service, underground parking, heated pool, Jacuzzi and saunas.
Best Western Aspenalt Lodge, $52.00 (927-3191)
Riverfront rooms (on the Frying Pan River) just 16 miles from Aspen/Snowmass. Seven restaurants nearby. Deluxe Continental Breakfast.
Boomerang Lodge, $72.00 (925-3416)
Nicely landscaped grounds. Some fireplaces. Balconies or patios. Cable TV, radios, phones. Kitchens and laundry. Sauna, whirlpool, heated year round pool.
Innsbruck Sports Motel, $74.00 (925-2980)
Imaginative decor, some refrigerators. Cable TV, phones, year-round heated pool. No pets.
Limelite Lodge, $62.00 (925-3025)
Quality accommodations at reasonable rates in the heart of Aspen. Next to beautiful Wagner Park, 1 block to Wheeler Opera House, unique shopping and many fine restaruants. Heated Pools, HBO, in-room refrigerators. Guest laundry on premises.

You can customize the HTML produced by the htmldatamerge statement with the options parameter. The basic options are header= and footer=. These options allow you to define additional HTML that will automatically be placed at the beginning and end of the generated HTML. (Of course you could also do this with a formula in your procedure.) For example, if you wanted to center the entire page, you could use

header="<center>"
footer="</center>"

Here is a modified version of our sample procedure that uses the header= and footer= options to center the output:

htmldatamerge cgiHTML,
  {header="<center><hr>" footer="</center>"},
  |||<h2>{Hotel}</h2>
    {pattern(Rate,"$#,.##")}<br>{Phone}<hr>|||

And here’s the first few records generated by this procedure:

Aspen Square

$85.00
925-1000

Best Western Aspenalt Lodge

$52.00
927-3191

Boomerang Lodge

$72.00
925-3416

Innsbruck Sports Motel

$74.00
925-2980

You can also specify color= and colorpattern= options. For a complete description of these options see Table Background Colors in HTMLDataTable. To use the color options you must embed the tag <color> somewhere in your template. This tag will be replaced by the actual rendered color specified by the options. However, the <color> tag must not be in one of the embedded formulas -— it should only be in the HTML portion of the template. For example, the <color> tag can be embedded in a <td> tag to specify the background color of table cells.

htmldatamerge cgiHTML,
  |||
      color="EEEEFF"
      color="EEFFEE"
      colorpattern="12"
      header={<center><table border=0 width=450 cellpadding=4 cellspacing=4><tr><td align=center bgcolor="999999"><font size=+2 face="Helvetica" color="FFFFFF">&nbsp;<br><b>Hotel Listings</b><br>&nbsp;</font></td>}
      footer={</table></center>}|||,
  |||<tr><td bgcolor="<color>"> <font face="Verdana" size=-2> <b>{Hotel}</b>, {pattern(Rate,"$#,.##") } ({Phone})<p>
    {?(Description="","",Description+"<p>")}</font></td></tr>|||

The result is a nice, formatted table listing each hotel, with alternating background colors for each hotel:

Hotel Listings

Aspen Square, $85.00 (925-1000)

The center of activities in downtown Aspen. Luxury condominiums with kitchens, fireplaces, color TV, private balconies, daily maid service, underground parking, heated pool, Jacuzzi and saunas.

Best Western Aspenalt Lodge, $52.00 (927-3191)

Riverfront rooms (on the Frying Pan River) just 16 miles from Aspen/Snowmass. Seven restaurants nearby. Deluxe Continental Breakfast.

Boomerang Lodge, $72.00 (925-3416)

Nicely landscaped grounds. Some fireplaces. Balconies or patios. Cable TV, radios, phones. Kitchens and laundry. Sauna, whirlpool, heated year round pool.

Innsbruck Sports Motel, $74.00 (925-2980)

Imaginative decor, some refrigerators. Cable TV, phones, year-round heated pool. No pets.

Limelite Lodge, $62.00 (925-3025)

Quality accommodations at reasonable rates in the heart of Aspen. Next to beautiful Wagner Park, 1 block to Wheeler Opera House, unique shopping and many fine restaruants. Heated Pools, HBO, in-room refrigerators. Guest laundry on premises.

Sands Motel, $22.00 (366-3581)

This is a test!

The Molly Gibson Lodge, $75.00 (925-2580)

Beautiful contemporary rooms and suites, including Honeymoon and Anniversary suites. Jacuzzis, Fireplaces, kitchens, balconies and patios. Remote control TV's and VCR's. Complimentary Continental Breakfast. Mountain Views. Two swimming pools and whirlpools.

Ullr Lodge, $48.00 (769-7146)

Traditional European style lodging in Aspen. Family owned and operated. Indoor and outdoor whirlpools. Sauna. Walking distance to Music Tent and Aspen Institute.

Hotel Jerome, $295.00 (920-1000)

International Hotel, $129.50 (555-9876)

A great hotel in downtown Denver.

Snowflake Inn, $59.00 (925-3221)

In Room: Cable TV. Coffee Maker. Hair Dryer. Iron & Ironing Board. Safe. Data Line. Kitchen.

Linking a Table or List with a Detail Form

A list can include links to detail pages, just like a table. For more information on setting up this type of link see Linking Individual Table Rows to Detail Pages in HTMLDataTable. Be sure to include <link> and </link> tags in the template for displaying the list.

See Also

HTML Export -- custom exports into an HTML file.
htmlarraytable -- generates an HTML table from an arrray.
htmldatatable -- generates an HTML table from selected data in the current database.
htmlextractimages -- extracts the image URLs from an HTML page.
htmlextractlinks -- extracts the links from an HTML page.
htmlformitemnames -- returns a list of form input elements (*INPUT* tags, etc.) within an HTML page.
htmltablecell( -- extracts the data from a cell in an HTML table.
htmltablecellexists( -- returns true if an HTML table contains the specified cell, false if it doesn't.
htmltablecellraw( -- extracts the data from a cell in an HTML table (including tags, if any).
htmltableheight( -- calculates the height (number of rows) in an HTML table.
htmltablerow( -- extracts the data from a row in an HTML table.
htmltablerowraw( -- extracts the raw data (including tags, if any) from a row in an HTML table.
htmltabletoarray -- converts an HTML table into a text array.
htmltablewidth( -- calculates the width (number of columns) in an HTML table.
xtag( -- generates an HTML/XML tag.

History

Version	Status	Notes
10.0	No Change	Carried over from Panorama 6.0.