Note: A panel,
foreach or ?COLUMN must be contained with its appropriate workwith or it may not be
correctly associated with the database and will function unpredictably. Outline:
Return to Table of Contents
Flexibility is the key to the GoldPaint Shopping Systems appeal. Through the use of databases for products, shipping, taxes and other variable information, the system can be easily maintained without constantly updating web pages. GoldPaint databases can be simple ASCII flat files or fully integrated extracts from your corporate database. This section will center on using the special product database, but the concepts apply to any database information you decide to include in your shopping system. Because of the special uses of the shipping and tax tables, those sections are discussed separately. See the Shipping and Taxes Sections for more information.
Database files designed for GoldPaint use can be stored in a number of locations. The default location is the shop directory. As the shop or mall owner you can define, through the Administration Screens, the location of your database files. In addition, you can define a location of a database within a page using the setgate tag. To limit potential access, storing the data outside the web directory tree is always more secure.
The standard GoldPaint database format is the *.tdb format. The special shipping and tax tables are all created using this format. The product database can be created in this format or in other formats discussed in the Creating a Database section. Each of these file types has a special name and fields which ensure that they work correctly within GoldPaint.
Once your database is created, accessing the data required a structured process which ensure that you return the data you intend to the appropriate location in your shop. GoldPaint provides methods to present data directly to the customer or to allow them to complete search requests against your databases. By having both methods, you can provide your customer with additional flexibility.
The remainder of this section discusses each of the concepts associated with database creation and data retrieval in detail. In addition, use the glossary for detailed examples of each tag described in this section.
The standard file extension for Tame database files is .tdb. Tame assumes that any ASCII file ending with the extension .tdb is structured with the following characteristics:
The first row starts with semicolon (;) which is followed by a list of fields separated by spaces. If a field name contains an embedded space, it must be enclosed with quotation marks.
Each row must end with the appropriate record terminator for the particular platform: CR LF for NT and LF for UNIX.
Data columns are separated by one or more spaces. If a column entry contains embedded spaces, that data is enclosed in square brackets [ ].
The first column must contain a unique value for each row.
A special case of .tdb is the product.tdb file. The product database may be a tdb structured file, or a table selected from a tdb structured file with wgrep. In addition, to the general requirements for a .tdb file, the product.tdb file requires that the first three columns be item, price and descrip in that order. As many user-defined fields as you wish can be added after these fields.
GoldPaint includes keyword field names that support special functions in the system. The following keywords can be used in the product database:
item: The first column in the product database which must be a unique indicator of product.
price: The second column in the product database. This should be a numeric value without symbols such as $.
descrip: The third column in the product database. When providing a description use square brackets [ ] or quotation marks " " to surround the information if there are embedded white spaces.
qty: A preset quantity that is purchased when the buy button is selected. Golf balls by packages of three for example.
weight: The weight of the product which can be used when shipping charges are calculated.
file: The file path and name for a download purchase.
expires: The date on which an offer expires. The expires field is in seconds, for instance, 24 hours would be 86400 seconds:
24 hrs = 24hr * 60min/hr * 60sec/min = 86400 seconds.
In addition there are three other commands that assist you in controlling customer purchases. These are include_items, no_remove and buyplus, and each is discussed in the next section.
This is an example of a basic product.tdb file:
;item price descrip weight keywords
example 2.90 "This is an example product" 1.0 "stuff"
thing 3.90 "This is a thing" 1.5 ""
widget 4.90 "This is a widget" 2.5 ""
In addition to *.tdb files in which the fields are separated by white space, tab delimited files can also be used. The requirements for a definition line and the column headings are the same as a *.tdb. Tab delimited files are saved as *.tab within the GoldPaint System.
In addition to static files, GoldPaint can create virtual tables by performing searches on *.tdb and *.tab files. Once defined, these files can be used the same way as static files.
With the addition of the GateBoy software, GoldPaint can query directly against SQL databases. This option, while discussed briefly here, is covered in detail in the advanced data access section.
Your database files should be stored in a secure directory wthat is accessible by the GoldPaint Shopping System. The default location is the shop directory under the webroot as defined in the Administration Screens. This directory is acceptable as long is write access is restricted. You can define your own default location through the administration features or by using the setgate feature described in the Define the Source of the Data section.
In the previous section a number of product database keywords were discussed. In this section we will review three column keywords that provide additional purchase options to your customer. The use of these column keywords can create flexibility within your shopping system.
Multiple products with one click
Additional items can be included with a product by specifying the item codes of the products using the include_items column. The value in this column is a white space delimited list of additional item codes to include from the same database. For example, when the customer is buying a package with multiple components this column is used. Consider this product database:
| ; item | price | description | include_items |
| shoe | 22.00 | Oxford deluxe | laces proofing |
| laces | .00 | Shoe Laces | |
| proofing | 1.00 | Waterproofing |
When the shoe is purchased, the laces and the waterproofing are automatically put into the shopping cart. Often these may be items may be free to the customer, but the items must be reported for inventory purposes.
Preventing items from removal from cart
When multiple components are purchased with one buy button, it is frequently necessary to prevent one of the essential components from being deleted by the customer. This is achieved by including a no_remove column in the product database and placing a value of "y" in that column.
| ; item | price | descrip | include_items | no_remove |
| s100 | 5.00 | oxford shoes | s100a s200b | |
| s100a | 0 | laces | y | |
| s100b | 0 | shoe trees | y | |
| s200 | 5.00 | tennis shoes | s200a | |
| s200a | 0 | laces | y |
The laces and shoe trees are included free of charge to the customer using the include_items. The shoes can be removed from the cart but the laces can not be removed by themselves. This is useful for component purchases such as computers.
Buy One Item, Get one Free
The buyplus column allows an item to be placed in the cart that will be replaced by a subsequent item put into the cart by the customer. This feature is used to implement promotions like 'Buy One Get One Free'. The buyplus column can contain one of the following:
replace: The item will be replaced by the next item that contains a price in the buyplus column and its price will become that buyplus price.
price: The price to charge when the item is replacing a buyplus replace item.
null: (No data in the column) Means that feature is not active for this item.
To implement a "Buy one Get on Free" policy, setup the product database as follows:
; item price description include_items buyplus no_remove s100 5.00 Lori Bear 1-free 0.00 y s200 5.00 Bob Bear 1-free 0.00 y s300 5.00 Sam Bear 1-free 0.00 y s400 5.00 Scottie Bear 1-free 0.00 y 1-free 0.00 Get next one Free replace
Once your database is create, you want to access the data or provide the ability for your customers to complete queries against the database. In order for GoldPaint to provide the correct information, the data access tags must have appropriate information available when they are executed. Therefore, it is important that the tags be presented in the correct order on the page. The order is:
Frequently steps 1 - 4 are placed in the custom.tag file or in an included tag file.
Note: A panel,
foreach or ?COLUMN must be contained with its appropriate workwith or it may not be
correctly associated with the database and will function unpredictably.
Example:
The following tags might be placed in a file named data_info.tag and that file placed in the shop directory.
<tame data_access>
<tame setgate: WGREP ~shop>
<tame define_wgrep: product speak.tdb FIRSTCOL 1 LASTCOL 1 WORDS {&item}>
<tame define_wgrep: products speak.tdb FIRSTCOL 1 WORDS {&query}>
<tame define_wgrep: all_product speak.tdb HITS 10 >
<tame define_wgrep: categories speak.tdb FIRSTCOL 4 LASTCOL 4 HITS 25 WORDS {&category}>
<tame define_wgrep: page_products speak.tdb FIRSTCOL FIRSTCOL 4 LASTCOL 4 HITS 100 TAG awf WORDS {&thispage}>Now each page in the shop that needed to access data from a database would have <tame include: data_info.tag> as the first tag on the page. These tags could also be placed in custom.tag for the shop, but then every page of the shop would have the extra burden of the data_info housekeeping.
Displaying information from the product database on an HTML page is easy. The system is set to the product.tdb, so steps 1 - 6 in the data access section are assumed as defaults. Consequently, you are initially setup to extract data from the product database. The database information is organized into tables, rows, and columns. Each table has a name, each row is uniquely identified by the value in the first column, and each column has a name. To display information from the product database use this syntax:
Note: There are no spaces between the question mark and the item or on either side of the comma.
Consider the following product database:
| ;item | price | descrip | wholesale-price |
| widget | 10.95 | A great widget | 5.95 |
| gizmo | 100.00 | Expensive gizmo | 40.00 |
To display information about the widget in this database the following syntax is used:
{?widget,descrip}
Item: {?widget,item}
Retail Price: {?widget,price}
Wholesale Price: {?widget,wholesale-price}
As more databases are integratedinto your site, the location and name of tables to use on your pages must be further defined. The following process is used to access data in tables.
The process above is designed to access data in tables with specific formats. See Creating a Database for details on tables. The tables with which Tame can work come from three different sources.
The setgate tag defines the type of file that will be accessed, the path to the file and the file's name. When subsequent data access tags are used after the setgate they are automatically associated with the file defined here. You can define multiple setgate tags in various places on different pages; however only one setgate tag is active at any given time. If you only have a product database, you would have one setgate and it would be placed in custom.tag for the shop. The syntax for setgate is:
<tame>
setgate: ACCESS-TYPE DIRECTORY/DATA-SOURCE
</tame>
There are four access types for use with the setgate tag:
| Type | Use With: |
| FILE | .tdb and .tab (tab delimited) ASCII flat files |
| WGREP | tables retrieved by the wgrep search engine |
| ODBC | SQL retrieval of tables coming from a ODBC data source on Windows95 or the NT |
| DAO | for SQL tables retrieved through GateBoy using the MS Jet engine |
The directory/data-source for the FILE and WGREP access types is the path to the directory containing the ASCII flat files. In the case of ODBC and DAO types, the directory/data-source specifies the data source name as entered in the ODBC manager in the NT or Windows95 control panel.
How you define a table depends on the type of file and the database system you are using. We will discuss the define_tdb, define_wgrep and define_sql methods of assigning a table name.
The define_tdb assigns a table name to a .tdb structured file. The system retrieves the entire file as the table for access when it is defined with define_tdb. There is no selection as with define_wgrep or define_sql. Every row in the entire file becomes available for data access. The syntax for the define_tdb is:
where TABLE is the name to be used for future reference by other data access tags and the PATH-FILE is the name that the database file is know by on the system. The file must reside in the directory given by the setgate tag.
The define_tdb is excellent for small databases, but as the database grows it is useful to define a table of information to be retrieved from a larger database file. The define_wgrep defines a particular search within a larger database. The syntax for define_wgrep is:
<tame> define_wgrep: TABLE PATH-FILE TYPE T FIRSTLINE # FIRSTCOL # LASTCOL # HITS # WORDS W </tame>
where TABLE is the name to be used for future reference by other data access tags and the PATH-FILE is the name that the database file is know by on the system. The file must reside in the directory given by the setgate tag.
The type specifies how the search is to be performed. The default is to complete a case insensitive search for ALL arguments in one row, assuming that the file begins with a field definition row which will not be searched.
Next, the line and column filters indicate the location to start and end the search. Be aware that the default is to begin the search with the second column and search to the end of each record. The combination of FIRSTCOL and LASTCOL allows you to specify a range of columns to be searched. When designing the database, consider columns that might need to be searched and group them together.
The maximum number of records that match the arguments to return to the page is indicated by the hits. Normally only up to 1000 hits can be returned. To allow returning more than 1000 hits you must specify the number of hits as a negative number.
The list of arguments for which to search is indicated by the words parameter. WORDS must be the last parameter given on the option line. If this parameter is left out, then the search will start returning on the first record. Leave off the words and use the "e" type to return every record in the database up to the number of requested hits.
Detailed descriptions of each keyword and parameter are provided in the glossary and here are two basic examples.
<tame> define_wgrep: my-shoes shoes.tdb TYPE af FIRSTCOL 3 LASTCOL 5 WORDS "oxford brown"</tame>
This example will search the shoes.tdb for rows containing the words "oxford" and the word "brown" between column 3 and column 5 and will name the the resultant table my-shoes.
<tame> define_wgrep: my-shoes shoes.tdb FIRSTCOL 3 HITS 3 WORDS {&query}</tame>
Will search the shoes.tdb from column 3 to the last column for the words in the variable &query which was input by the consumer on a form. It will return only 3 hits at a time.
The define_sql tag is used to retrieve a table from a SQL database server. The syntax for define_sql is:
where TABLE is the name to be used for future reference by other data access tags. It should not be confused with the tables in the SQL database from which the data is retrieved. This is the name that the retrieved data will be known as on the tame pages. The SQL-QUERY is the exact SQL statement which will retrieve the desired data table from the database. Here is a basic example.
<tame> define_sql: new-products SELECT code, price, description FROM products where status = 'new'</tame>
More details are available in the glossary.
Once a table has been defined with the define_tdb, define_sql or define_wgrep tag, you can further define a file as your default product database. This is not required if you use product.tdb and locate it in your shop directory because these are the default setting for the system. Designating the product database using the setpdb tag, will set the &pdb variable. This specifies in which table or file the shopping system will look to find the items designated by the buy buttons. For example:
<tame> setpdb: ~//shop/speak.tdb</tame>
will designate speak.tdb as the product database from this point forward on this page. This tag overrides the file designated in through Administration in the config.tag file.
Once your tables are defined you are ready to use the data in your tame pages. To use a table on your page, you must first tell the system with which table to work. The workwith tag accomplishes this using the following syntax:
<tame workwith: TABLE> ... HTML-AREA ... <tame /workwith>
All tags that work with database need a TABLE context in order to function correctly. The data tags will operate on the selected table until a /workwith is encountered. In addition, workwith areas may be nested to create more complex presentations. Here is an example of workwith statements nested together to present information to a customer.
<tame workwith: states>
{?Washington,population} comes from the states table
<tame workwith: cities>
{?Seattle,population} comes from the cities table
<tame /workwith>
{?Washington,capital} back to the states table
<tame /workwith>
Once a workwith area is defined you can make direct requests for a single piece of information using {?row,column}, retrieve multiple results using a query and present information using foreach statement.
If you want to present specific information you can use the direct data request. This is accomplished with the following format:
{?table,row,column} or {?row,column} or {?column}
The amount of information you provide within the tag determines what additional context information is required to present the information correctly. Once a table name has been defined, you can directly present a specific piece of information using {?table,row,column}. The row is the literal value in the first column of the record you wish to retrieve and the column is the name of the column from which data is to be displayed. Once "states" and "cities" are defined as the tables the following request will function:
The capitol of Washington is {?states,Washington,capitol} and the population of Seattle is {?cities,Seattle,population}.
Note: Because the table name is contained within the tag, a workwith section is not required.
Once the context of a table has been define with a workwith statement the table slot may be omitted and only the row and column need to be specified. In this example the "states" table is defined and omitted from the tags within the workwith:
<tame workwith: states>
Capitol: {?Washington,capitol}
Population: {?Washington,population}
<tame /workwith>
Similarly, once the context of a particular row is established with a foreach tag, the
row context can be omitted.
The syntax for foreach is:
<tame> foreach:: HTML-AREA </tame> (Note the double colons.)
This tag will generate a HTML-area for each row of the table designated
in a workwith. Within a foreach, it is only necessary to call out a
particular column {?column} to show data from the row.
For example:
<tame workwith: states>
<tame>
foreach::
Capitol: {?capitol}
Population: {?population}
Rainfall: {?rainfall}
</tame>
<tame /workwith>
In conclusion, requests for information require a table, row and column context. Whether you use the full context request {?table,row,column} or the reduced forms of the tag with workwith and foreach tags, be sure that your requests are adequately defined. Always tests your requests to ensure that the logic used returns the results that you expect.
There are two primary ways to present your products to potential customers. The first is to present your entire product line on the catalog pages at your site and allow customers to browse through your items. This is effective for smaller product offering. For larger product offering, it is more effective to provide a query mechanism for the customer to use. Through this query the customer is presented with the products that are of importance to him. The rest of this section will discuss presenting an entire product database, and the query section will discuss customer requests.
Presenting an entire database does have value to your customers. The shopping system for example presents an entire database when shipping methods are presented. In this example we will assume that the product database includes an exclusive and unique selection of products. As the shop owner you want to present each product with an image and a buy button.
<tame form>
<tame setgate: FILE ~shop>
<tame define_wgrep: data product.tdb TYPE afe FIRSTCOL 1 LASTCOL 6>
<tame workwith: data>
<table width=75% cellpadding=3 border="2">
<tr>
<td><h3>Item</h3></td>
<td><h3>Price</h3></td>
<td><h3>Description</h3></td>
<td><h3>Image</h3></td>
<td><h3>Click to Buy</h3></td>
<td><h3>Quantity</h3></td>
</tr>
<tame foreach::
<tr><td>{?item}</td>
<td>{?price}</td>
<td>{?descrip}</td>
<td>{?image}</td>
<td><tame ^buy: {?item}></td>
<td><tame ^buyinputqty: {?item}></td>
</tr>
>
<tame /workwith>
</table>
</form>
The customer is presented with a table showing each of the products with a description, picture, price and a buy button.
Small databases can easily be presented in this manner, whether it is for a product listing or supporting information such as shipping. For customer queries of larger database, the next section provides a detailed overview.
A search page is any page that displays information out of a WGREP or SQL table, based upon some criteria. One way to establish criteria is to ask the consumer what he wants. This is most easily done by displaying the query panel discussed below. Another way is to pass the page a fixed constant, for example a department code, allowing the consumer to page through all products containing this code. This is most easily done by displaying the subquery panel discussed below.
Once criteria are defined they are used by a define_sql or the define_wgrep statement to search the database. The information is then put on the page inside a workwith tag for that table. Most often, each record of the table is displayed in a template designated by the foreach tag. Inside the template is HTML code surrounding information that is displayed out of the table with {?fieldname} tags. See foreach and workwith.
To place a panel on the consumer's screen use a query panel (query.pan). This panel will asks for search keywords, provide a button with which to submit the search and provide a button to page through the results. To give the panel a table context within which to function, the query.pan must be within a workwith area. The tag
<tame>
show_panel: query.pan BORDER 5
</tame>
might present the following panel:
Below this panel would be the first 20 items that met the query criteria. The format for presentation is defined using workwith, foreach and HTML formatting functions. You can present the data as a list, a table or any other presentation that fits the type of information you are providing.
When the data for the query is passed from a prior page or provided by the system, the subquery panel presents the buttons necessary to page through the hits returned by the database query. The difference between a subquery and a query is that a data input area is not provided on a subquery. The syntax for a subquery panel, which must be within the workwith statement, is:
<tame>
show_panel: subquery.pan OPTION-LIST
</tame>
The options are:
Note: The priorface button is never available with wgrep database access. This is an SQL only button. For wgrep, use the submit button to start the search over at the beginning.
<tame>
show_panel: subquery.pan subquery.pan NEWFACE
"Start Over"
</tame>
might present the following panel:
Here is the tame code to present a page with a query panel:
<tame>setgate: FILE ~shop</tame>
<tame>define_wgrep: horses horse.tdb TYPE af FIRSTCOL 1 WORDS
{&query}</tame>
<tame> workwith: horses </tame>
<tame>show_panel: query.pan SUBMITFACE [FIND HORSES]</tame><br>
<b>Page down for search results</b><p>
<table border=1><TR ALIGN=CENTER>
<TH>More information?</TH>
<TH>Horse's Name</TH>
<TH>Title Won</TH>
</TR>
<tame>foreach::
<TR ALIGN=CENTER>
<TD><tame hidden: {?item}><tame pass: ?item; link: ponyid.tam
{*pointer*}></TD>
<TD>{?ponyname}</TD>
<TD><tame &item = ?item>{?product.tdb,&item,title}</TD>
</tame>
</TR>
</TABLE><tame /form><tame /tamepage>
The default search for a query panel is {&query} that was used in the wgrep search in this example. If you use another variable, it must be defined as an option in the query tag.
For additional presentation flexibility the when_hits and when_not_hits tags can be used. These tags allow the designer to presented different information based on whether the query returns hits. For example, tables can be presented with information when hits are returned and an appropriate message when hits are not found. Here is the tame code for such a page.
<tame tamepage>
{data_access}
<tame form>
{setgate: FILE ~shop}
{define_wgrep: access computer.tdb FIRSTCOL 1 WORDS [XYZ Computer]}
{workwith: access}
{when_hits:
<b>The computer you have selected is presented below with its associated
accessories.</b>
<table border=1>
<TR ALIGN=CENTER>
<TH>{?item}</TH>
</TR>
{foreach::
<TR>
<TD>{?accessory}</TD>
}
</TR>
</TABLE>
}
{when_not_hits: <b>The computer you have selected does not include any additional
accessories.</b>}
<tame /form>
<tame /tamepage>
When the query returns hits, this type of page is presented:
When no hits are found this type of page would appear:
For syntax see when_hits and when_not_hits in the glossary.
There are times when you will obtain information from the customer that is useful on another page within your site. To move this data to the new location as the page is presented, use the pass tag. This tag modifies linking and dialog linking commands such as ^link and ^goto. The information is passed to and remembered on the destination page. This is accomplished by placing the passed information in a hidden area of the shopping cart. In addition, any database context, such as the workwith area containing the tag, will be passed to the destination page. The passed data is available on the destination page as &VARIABLE. If the tag contains ?FIELDNAME variables, it must be inside a workwith area.The pass modifier is used as follows:
<tame>
pass: &VARIABLE-NAME
?FIELD-NAME . . .; ^LINK/^GOTO: DOCUMENT-LOCATION HTML-ARGUMENT-AREA
</tame>
Note the use and position of the semicolon.
Note: The variables are not enclosed in curly braces { }. Both the name of the variable and the value of the variable will be passed to and remembered on the destination page.
There are two other ways to move data between pages: transfer and carry. Pass is used when you are passing parameters that are part of the input to a query so that a return to the page will show the same results. All other transfers of variables should use transfer or carry. To simply move a variable to the next page without any query context, use the transfer modifier. The carry modifier moves the variable along from page to page as a hidden variable. For more details see the carry tag.
In this example, the pass modifier is used to move information associated with a query to a new page. This would be the first page presented to the customer:
<tame data_access>
<tame setgate: WGREP ~shop>
<tame define_wgrep: holiday product.tdb FIRSTCOL 4 LASTCOL 4 WORDS Christmas>
<tame setpdb: holiday>
<tame workwith: holiday>
<b>Click on item code to purchase, or {*pointer*} for more details</b>
<tame>foreach::
<b><tame ^buy: {?item} {?item}></b> {?descrip} ${?price}
<tame pass: ?item; link: prod.tam {*pointer*}>
</tame>
<tame /workwith>
When the customer selects the pointer, the ?item is passed to the new page, prod.tam. The prod.tam page would be written as follows:
<tame data_access>
<tame workwith: product>
<b>{?item}</b> {?descrip} <tame ^buy: {?item}> ${?price}
<p>{?pagetext}
<tame /workwith>
Because the pass modifier was used, the data is passed to the new page and remembered. Therefore, the customer can return to the first page and his results will be retained.
Using databases adds flexibility to your system. By following the steps defined in the Overview sections and using the detailed sections for support and examples, you can create and use databases to sell your products at your web site.
These tags and processes will support most database needs. If you have a SQL database or find that your needs are not supported through these tags, check out the Advanced Database Access section for more tags. The advanced section uses application level syntax and is designed for advanced users.
created: 4/8/97 Katherine C. Davis
edited: 4/8/97 Katherine C. Davis