Dynamic Catalogue Generation

E-commerce web sites have extensive catalogues of thousands of items whose details are changed very often. Maintaining static HTML catalogues is infeasible. Items are usually displayed dynamically. Each page of output contains just a few items that are sorted in some order. We can also generate our catalogue on the fly using a very simple ASP script.

Open the catalogue.asp file. This file contains just html; we simply renamed it in the previous exercise.

Scroll down to just below the line that contains the headings of the catalogue table-

  
  <TR>
        <TH>Product</TH> <TH >Description</TH><TH>Price</TH>
</TR>

Remove all the table rows except for the first one - the one that contains the 'Red Roses' entry.

Copy the following code right before the row:

  <%set conn=Server.CreateObject("ADODB.Connection")
  conn.Mode = 3
  conn.open "florist","",""
  set rs = Server.CreateObject("ADODB.recordset")
  rs.Open "select * from items", conn
  flowernum = 0
 
  while not rs.eof
    itemname = rs("itemname")
    itemdescription = rs("itemdescription")
    itemcost = rs("itemcost")
    itemimage = rs("itemimage")
        itemshort = mid(itemimage,1,len(itemimage)-4)
%>

The above code opens a connection to the database and retrieves all the items. This query may be easily modified to sort items based on cost or name and return the first N items in the sorted list - an enhancement that will allow page by page sorted output. We will just display without sorting. A 'while' loop executes the code between the while statement and a corresponding 'wend' statement over and over again as long as the condition right next to the while statement (here, 'not rs.eof' - until the returned record set's end is reached). Within the while statement, we assign relevant database field values to variables. The variable 'itemshort' uses the mid function to extract the name of the item image without the file extension (.png). This returns 'redrose' , 'lily' and so on - the values we have assigned to the name attribute within the <img> tags. The flowernum variable holds the number of the item being loaded strting from 0. It will be used to set the input parameter for the showLarge() Javascript function that opens a window with a large picture of the clicked-on item

Copy the following code right after the single table row for Red Rose:

  <%flowernum = flowernum + 1 %>
<%rs.MoveNext %>
<%wend %>

<%rs.close %>
<%conn.Close %>
<%set rs = nothing %>
<%set conn = nothing %>

The rs.movenext statement simply moves the row pointer to the next row in the database. Thus, the rs("itemname") will contain "Lilies" after the first rs.movenext and "Yellow Roses" after the second rs.movenext. Right after the rs.movenext statement is the wend statement that terminates the while loop. Thus, rs.movenext is executed as many times as there are rows in the database table 'items' - until rs.eof is reached. This is how we 'iterate' through all the catalogue entries in the database. Finally, we close the record set and connection.

This is all very well but so far, our code simply displays the Red Rose row over and over again. In case you have not already guessed, we will use the response.write shorthand to display the database output. Simple replace "Red Rose" with <%= itemname%>, $99.99 with $< = itemcost%> and so on until the row looks like this:

  <TR><TD>
<table>
  <tr><td align=center>
  <a href=# onClick="showLarge(<%= flowernum%>)">
  <img src="images/<%= itemimage%>"
  name="<%= itemshort %>"
  alt="<%= itemname %>" width=200 height=140></a></td></tr>
  <tr><td align=center>
  <a href=# onClick="showLarge(<%= flowernum%>)"><%= itemname %></a></td></tr>
  </table>
</TD>
<TD><%= itemdescription %></TD>
<TD><%= itemcost%></TD>
</TR>

We have now endowed our web site with basic functionality. We will learn to enhance the site by adding a shopping cart type application that will allow users to place orders and by implementing digital signature security login in the next chapter.