Displaying formatted records from database table

This simple example uses few basic WhizBase variables and functions, but it includes HTML tag STYLE to define classes wbspttbl, wbspthdr and wbsptrow which are used to format table containing records from database.

[FormFields]
wb_basename=biblio.mdb
wb_rcdset=Titles
WB_Command=Q
WB_MaxRec=10

<html>
<head>
<style>
.wbspttbl{
border:1px solid #000000;
font-family:verdana;
font-size:12px;
border-collapse:collapse;
border-spacing:0px;
}
.wbspthdr{
background-color:#CC0000;
border:1px solid #000000;
color:#C0C0C0;
}
.wbsptrow{
background-color:#FFCC00;
border:1px solid #000000;
color:#0000CC;
}
</style>
<title>Simple database example</title>
</head>
<body>
$wbdetail[t]
</body>
</html>

Now we have added some title to the report and some HTML formatting using $WBDetail[T] function and CSS styles. We also changed the number of records per page using WB_MaxRec.

With further modifications we can include Author's and Publisher's names instead of their ID numbers, and sort titles by year in descending order as shown in following example:

[FormFields]
wb_basename=biblio.mdb
WB_RcdSet=(Authors inner join titles on authors.au_id=titles.au_id) inner join publishers on publishers.pubid=titles.pubid
WB_DBFlds=Title,[Year Published],ISBN,Authors.Name as Author,publishers.Name as Publisher
WB_Command=Q
WB_MaxRec=10
WB_Order=[Year published] desc

<html>
<head>
<style>
.wbspttbl{
border:1px solid #000000;
font-family:verdana;
font-size:12px;
border-collapse:collapse;
border-spacing:0px;
}
.wbspthdr{
background-color:#CC0000;
border:1px solid #000000;
color:#C0C0C0;
}
.wbsptrow{
background-color:#FFCC00;
border:1px solid #000000;
color:#0000CC;
}
</style>
<title>Simple database example</title>
</head>
<body>
$wbdetail[t]
</body>
</html>

To enable search though this recordset we should add the search form and modify the recordset definition as shown in following example:

[FormFields]
wb_basename=biblio.mdb
WB_RcdSet=(select Title,[Year Published],ISBN,Authors.Name as Author,publishers.Name as Publisher,Authors.Name,publishers.Name from (Authors inner join titles on authors.au_id=titles.au_id) inner join publishers on publishers.pubid=titles.pubid)
WB_Command=Q
WB_MaxRec=10
WB_Order=[Year published] desc

<html>
<head>
<style>
.wbspttbl{
border:1px solid #000000;
font-family:verdana;
font-size:12px;
border-collapse:collapse;
border-spacing:0px;
}
.wbspthdr{
background-color:#CC0000;
border:1px solid #000000;
color:#C0C0C0;
}
.wbsptrow{
background-color:#FFCC00;
border:1px solid #000000;
color:#0000CC;
}
input, select, label{
width:150px;
font-family:verdana;
font-size:11px;
}
</style>
<title>Simple database example</title>
</head>
<body>
<form action="$wbe[script_name]">
<label for="year">Select year of publishing: </label>
<select name="WBF_Year published" size="1" id="year">
<option value="1996">1996</option>
<option value="1997">1997</option>
<option value="1998">1998</option>
<option value="1999">1999</option>
<option value="2000">2000</option>
<option value="2001">2001</option>
</select><br>
<label for="title">Title: </label><input type="text" size="20" id="title" name="wbf_title"><br>
<label for="author">Author: </label><input type="text" size="20" id="author" name="wbf_author"><br>
<label for="publisher">Publisher: </label><input type="text" size="20" id="publisher" name="wbf_publisher"><br>
<label for="AO">Return records that: </label>
<select name="WB_AndOr" size="1" id="AO">
<option value="AND">match all conditions</option>
<option value="OR">match any of the conditions</option>
</select><br>
<input type="submit" name="sButt" value="Search">
</form>
$wbdetail[t]
</body>
</html>

This looks much better, but in some cases there is a need for placing the database field in a specific location on your report, or even not using HTML at all. In that case we do not use $WBDetail function but some (or all) of database field functions $WBF, $WBFF, $WBFC, $WBFU and $WBRF. In the following example we will modify the code so it will return previous recordset in XML format:

[FormFields]
wb_basename=biblio.mdb
WB_RcdSet=(Authors inner join titles on authors.au_id=titles.au_id) inner join publishers on publishers.pubid=titles.pubid
WB_DBFlds=Title,[Year Published],ISBN,Authors.Name as Author,publishers.Name as Publisher
WB_Command=Q
WB_MaxRec=$all$
WB_Order=[Year published] desc
WB_ContentType=text/xml
<?xml version="1.0" encoding="UTF-8"?>
<dataroot xmlns:od="urn:schemas-microsoft-com:officedata" generated="2008-10-02T12:24:19">
<Titles>
<Title>$wbmrepl[$wbf[Title]|',&|',&]</Title>
<Year_x0020_Published>$wbf[year published]</Year_x0020_Published>
<ISBN>$wbf[isbn]</ISBN>
<Author>$wbf[Author]</Author>
<Publisher>$wbf[Publisher]</Publisher>
</Titles>
</dataroot>

We modified WB_MaxRec to special value $all$ (this will show all records from recordset in a single report page) and set the WB_ContentType variable to text/xml. Note that we also had to use $WBMREPL function to replace apostrophe and ampersand characters in field "Title".