Perlshop 4 - PSDBI Documentation

Contents:

Overview

The ability to keep all of your product information in a central database is a tremendous advantage. The web pages draw their information directly from this database, eliminating the need for day to day alterations of the web page catalog files. The only time you'll need to alter your html code now is if you are making cosmetic changes to your web site, or are adding wholly new pages to your store.

The Perlshop 4 Database Interface (PSDBI) provides the linkage between your central database and your shopping cart software.

Basic Database Concepts, and an Example

One use for a table in a database could be to represent the inventory of a given business. For example, a typical item for sale in a typical store might have the following attributes:

• A part number used for inventory control
• A price
• A type
• A description
• A color
• A weight, in ounces, used for shipping calculations

Let's say we have a store named Wally's Widget World. Wally sells home made widgets of various kinds, and uses a database table to keep track of his inventory:

Inventory Table
Part Number	Price	Type	Description	Color	Weight
1001	12.00	Basic	Wally's Famous Basic Widget	Red	1.0
1002	12.00	Basic	Wally's Famous Basic Widget	Blue	1.0
1003	12.00	Basic	Wally's Famous Basic Widget	Green	1.0
2001	15.00	Complex	Wally's Amazing Complex Widget	Blue	1.5
2002	15.00	Complex	Wally's Amazing Complex Widget	Green	1.5
3001	16.00	Super	Wally's Astonishing Super Widget	Racing Black	2.2

Wally has his web site catalog broken up into three web pages, with one page for Basic Widgets, a second page for Complex Widgets, and a third page for his Super Widget. With only six items in stock, and only three pages of html to worry about, Wally could simply alter his html files each time he wants to change a price or add a color.

The thing is, Wally is a lazy man. Instead of doing the work himself, Wally wants to have his Basic Widgets web page talk with his database. Wally wants that web page to ask the database "Hey, Database, give me all the data you've got on all your Basic Widgets". The web page will then display all of the data that Wally has designed it to display, using the information resulting from the database query. All of Wally's web pages will work this way.

Now, if Wally changes a price, or a description, or even adds a new Basic Widget to his inventory, he never has to change the html file. Thanks to the Perlshop Database Interface, all of the work is done automatically by software.

Technical Stuff : Structured Query Language (SQL)

In order to design Perlshop catalog web pages that use the PSDBI package, you need to understand the basic SQL command that is used to query a database. This command is called a select statement, and is very simple to use.

The Select Statement is used to retrieve information from a database table. Some example queries might be:

• Give me all rows that have a description containing the word "Basic".
• Give me all rows that have a color value of "Blue".
• Give me all rows where the price is less than $20.
• Give me the row for part number 3001.

A basic select statement has the following syntax:

select [columns] from [table name] where [conditions] 

When you execute an SQL query, you get back all of the rows in the table that match your request.

This example query will retrieve all columns from all rows of the table named 'inventory':

select * from inventory

This example query will retrieve all columns from the inventory table, from all rows that have a color value of 'Blue':

select * from inventory where color='Blue'

This example query will retrieve the part number and price from the inventory table, from all rows the have a price less than $20:

select part_number,price from inventory where price < 20

This last example query will retrive the part number, description, and price from the inventory table, selecting only those rows that have a red color and a price under $15:

select part_number,description,price from inventory
where (color='Red') and (price < 15)

Using SQL in a Perlshop Catalog Page

select * from inventory where type='Basic'

But how does Wally put this query inside his Basic Widgets web page? This is where the Perlshop <PSDBI> tag comes into play.

The <PSDBI> Query Tag

The <PSDBI> query tag works as follows:

<psdbi command=query>
select * from inventory where type='Basic'
</psdbi>

This entire command is placed inside the <head> element of the web page, resulting in something like this:

<html>
<head>
<title>Wally's Basic Widgets</title>

<psdbi command=query>
select * from inventory where type='Basic'
</psdbi>

</head>

<body>
...
...
...

The <PSDBI> Result Tag

The <PSDBI> result tag is placed inside the body of the web page. It is placed wherever the results of the database query are to be displayed. The result tag itself works as follows:

<psdbi command=result>
Some HTML goes here
</psdbi>

The HTML within the result tag is reproduced one time for each table row returned by the <PSDBI> query tag. The column values returned by the query are inserted into the HTML at specifically marked locations.

For example, let's use the query from the example above:

select * from inventory where type='Basic'

To do this, Wally uses the following <PSDBI> result tag (let's leave the Perlshop specific tags out of this example):

<psdbi command=result>
<tr>
<td>!description!</td>
<td>!color!</td>
<td>!price!</td>
<td><input type=text value=0 size=4></td>
</tr>
</psdbi>

The HTML content of the result tag contains substitution variables that are marked out with surrounding '!' symbols. Each substitution variable is named after a column from the table being queried. When the web page is loaded by Perlshop, the PSDBI package extracts the PSDBI result tag HTML, executes the associated SQL query, and replaces the PSDBI substitution variables with the actual values from the database. This process is repeated one time for each item returned by the SQL query.

Advanced Topics

Using Multiple Database Queries on the Same Page

It may be sometimes useful to create a PSDBI catalog page that contains multiple database queries. This can be useful in situations where the database contains multiple tables, or where the format of the page requires that each item from a given database table be handled separately.

In order to keep each of the queries in a given page distinct, each PSDBI query command is given a unique identifier. This identifier is then used in each PSDBI result tag intended to work with that query. The 'qid' attribute of the PSDBI tag is used for this purpose.

For example, let us have a catalog page in which all of the 'Basic' type items are handled differently from the 'Complex' type items:

<psdbi command=query qid=basic>
select * from inventory where type='Basic'
</psdbi>


<psdbi command=query qid=complex>
select * from inventory where type='Complex'
</psdbi>


<psdbi command=result qid=basic>
HTML displaying the Basic type items goes here
The inventory code for this item is !basic.code!.
</psdbi>


<psdbi command=result qid=complex>
HTML displaying the Complex type items goes here
The description for this item is !complex.description!.
</psdbi>

Note that in these examples, the substitution variable names are composed of both the query name and the database column name (!queryname.columnname!).

Nested Database Queries

In complex situations, it can be useful for the result of one query to be used as the input for a second query. To show how this can be useful, let us use an example where the database is made up of two tables. In this example, every item type in the inventory has a unique part number. Each unique part number is available in a variety of colors. (For Perlshop, the part number would be the ITEM_ID tag value, and the color would be the ITEM_OPTION1 tag value.)

The first table, named 'product', contains information about each product, including part number, description, price, etc., but contains no color information. The second table, named 'colors', contains nothing more than the colors available for each part number.

Using two tables in the fashion is complex, but removes the need for the duplicated information that would be needed if only a single table were used.

Example:

<psdbi command=query qid=product>
select * from inventory where type='Basic'
</psdbi>


<psdbi command=result qid=product>
Product information for !product.description!.


<psdbi1 command=query qid=colors>
select * from color_table where code=!product.code!
</psdbi1>


Available colors are:
<psdbi1 command=result qid=colors>
!colors.color!
</psdbi1>


</psdbi>

Note that the tags used for the 'colors' query are named psdbi1, and not psdbi. This is necessary to ensure proper order of evaluation of the query and result tags.

There is no restriction on the depth of psdbiX tag nesting.
Psdbix tag nesting levels must use consecutive numbers.

The <PSDBI> Execute Tag

The Execute tag works as is shown in this example:

<psdbi command=query qid=product>
select * from inventory where type='Basic'
</psdbi>


<psdbi command=result qid=product>

Our !product.description! product does amazing things!<br>


<psdbi1 command=execute>
{
	my $output;

	if (!product.stock! == 0)
	{
		$output = 'SOLD OUT';
	}

	else
	{
		$output = 'Price : $!product.price!';
	}
}
</psdbi1>


</psdbi>

The Perl code contained within each PSDBI Execute tag is treated as an independant Perl 'eval' block. When the PSDBI catalog page file is loaded, the Execute tag an contents are replaced by the return value of the Perl 'eval' statement.

A Real Live Working Wally's Widget Page

• View the actual Basic Widget Page from Wally's web site.
• See the HTML source code to Wally's Basic Widget Page.

Adding PSDBI Catalog Pages to a Perlshop Web Site

Using the PSDBI Action

<a href="!MYURL!?action=psdbi&thispage=wally.html&ORDER_ID=!ORDERID!">

Any web page loaded in this fashion will automatically be processed through the psdbi software.

Direct Loading of PSDBI Pages

Using this method, PSDBI catalog pages are loaded using the same commands used for any catalog page, except that the psdbi subdirectory must be included in the file name:

<a href="!MYURL!?action=thispage&thispage=psdbi%2Fwally.html&ORDER_ID=!ORDERID!">

For this example, most people will expect the file name referenced by the thispage attribute to be 'psdbi/wally.html'. This would be incorrect, since the slash character is not allowed in a URL string. Instead, the slash must be encoded as the hexidecimal value %2F.

Database Catalog Page Templates

Catalog Page Template Overview

<html>

<head>
<title>!template.title!</title>

<link rel=stylesheet type="text/css" 
	href="http://www.waveridersystems.com/!template.style!.css">

</head>

<body>
<h1 align="center">!template.title!</h1>

<p>
!template.header!

<psdbi command=query qid=product>
select * from wally_products where type='!template.type!'
</psdbi>

<p>
Product description:<br>

<psdbi command=result qid=product>
!product.description! : !product.color!<br>
</psdbi>

<p>
!template.footer!

</body>

</html>

Using Database Driven Templates

The recommended design for a catalog page template table is as follows:

Template Definition Table
Column Name	Column Data Type	Column Description
Name	Text	The name of the template
File	Text	The name of the html template file
Style	Text	The name of the style sheet to use for this template
Title	Text	Title text for the web page
Header	Text	Header text for the web page
Footer	Text	Footer text for the web page
.....	Any	As many other columns as fit the needs of your business

Database driven template pages are loaded using the TEMPLATE action. The following example demonstrates how this command would be used to load a template page named 'basic':

<a href="!MYURL!?action=template&thispage=basic&ORDER_ID=!ORDERID!">

Any web page loaded in this fashion will automatically be processed through the psdbi software.

A Template Driven Wally's Widget Web Site

• See the HTML source code to Wally's product template file.
• Wally's Basic Widget's page.
• Wally's Complex Widget's page.
• Wally's Super Widget's page.

Recommended Database Inventory Table Design

For customers just starting out with an inventory control database, the following table design is recommeneded for Perlshop 4:

Inventory Table
Column Name	Column Data Type	Column Description	Perlshop Input Tag Equivalent
Code	Text	Inventory control code.	ITEM_ID
Name	Text	Product name.	ITEM_NAME
Summary	Text	A short description of this item. Optional.
Description	Text	A full description of this item. Optional.
Option1	Text	Site dependent. Optional.	ITEM_OPTION1
Option2	Text	Site dependent. Optional.	ITEM_OPTION2
Option3	Text	Site dependent. Optional.	ITEM_OPTION3
Price	Number	Item price.	ITEM_PRICE
Weight	Number	Item weight, in local units. Optional.	ITEM_WEIGHT
Thumbnail	Text	Name of thumbnail image file. Optional.
Image	Text	Name of big image file. Optional.
Stock	Number	Number of this item in stock. Optional.
Vendor	Number	Item vendor ID code. Optional.

The following SQL table creation command can be used to create this table inside your database:

create table inventory ( code varchar(20), name varchar(40), summary varchar(40), description varchar(255), option1 varchar(20), option2 varchar(20), option3 varchar(20), price float(10,2), weight float(10,2), thumbnail varchar(20), image varchar(20), stock int, vendor int );