Home

A First Look

Page 7 of 22 - Chapter 14

We have done enough to be able to browse to the web site. Currently that's as far as we can go, but you can confirm that the dynamic creation of the list of categories in the home page and in the select tag are working.

Producing a Dynamic Product List

Our next task is to dynamically create a page displaying goods contained within a particular category of music as shown above. We also need to limit how many items are displayed per page; if our stock contains more than can be displayed, then we need a facility to go to the next page and back to the previous one. What we don't want is the customer being presented with a page containing a hundred plus long list. They'll get fed up of scrolling down long before they actually get to the end. Also a long page will be slow in downloading.

First, let's create the ListStockByCategory stored procedure following the same steps as we did for ListCategories. It takes two input parameters, CatId and StartItemId. To prevent the length of a page becoming unmanageable for the user we only return the first 16 rows with the first item having an ItemId greater then @StartItemId. We match item details and artist details using an inner join of the Stock table and the Artist table.

CREATE PROCEDURE [ListStockByCategory]

(@CatId int, @StartItemId int = -1)

SELECT TOP 16 S.ItemId, ArtistName, Title, Price, QtyInStock

FROM (Stock S JOIN Artist A ON S.ArtistId = A.ArtistId)

WHERE S.CatId = @CatId AND S.ItemId >= @StartItemId

ORDER BY ItemId

After you have completed the code click OK and close the dialog box. As before you'll need to give MMCustomer Execute permissions for this stored procedure.

Next we create the page itself.

The number of items per page is determined by the lMaxPerPage variable we define in the server-side global include file. Its value has been set to 15 but you may wish to vary this, though if you do so you'll also need to alter the ListStockByCategory stored procedure which returns 16 records.

The ListStockByCategory stored procedure returns the next 16 records starting with the StartItemId we pass it. We display the first 15 of them and use the ItemId of the 16^th as the StartItemId for our next 15. If we have a full page then next and previous links are included which reload this page, but with the StartItemId passed in the link's URL determining whether it's the next or previous 15 items that are displayed. A StartItemId of –1 retrieves the very first page of stock.

Its time to take a look at the code. Having included our ServerSideGlobalDef.inc file, we set the page to expire as soon as it has been written to the client's browser. The list of items we produce also includes whether they are in stock. By stopping the page from being cached we can ensure the user sees up-to-date stock availability information every time they browse the stock.

Next we retrieve the data passed in the URL by using the Request.QueryString collection. We can then use this information to create a new recordset and populate it using our ListStockByCategory stored procedure.

<% Response.Expires = -1; %>
<HTML>
<%
   // Retrive the data we included in the link
   var scatTitle = unescape(Request.QueryString("Description"));
   var lCatId = Request.QueryString("CatId");
   var lstartItemId = Request.QueryString("StartItemId");


   var sRowBGColor = "oldlace";
   var savailable;
   var lRecordCount = 0;
   var loRS;
   loRS = Server.CreateObject("ADODB.Recordset");
   loRS.Open("Exec ListStockByCategory " + lCatId + _
             "," + lstartItemId, sdbConnString);
%>

Next we create our table tag and start looping through the rows in the recordset. The background color for each row is alternated so it's obvious where a product's row starts and ends, even if it has wrapped on to a second line. A variable contains the color value and is simply alternated between ivory and oldlace HTML colors.

Having dealt with the row color we need to deal with the stock availability by creating a string containing either "In Stock" or "Unavailable" which we will display in a table cell and the surlData variable to give the href value of the Add to Basket hyperlink. If stock is available then its value will point to the additem page with the item details added to the end of the URL. If no stock is available, or cookies are not enabled, then the value will be a JavaScript line which alerts the user that they can't proceed because either no stock exists or cookies are disabled.

<BODY>
<H1 ALIGN="CENTER">
   <FONT FACE="Comic Sans MS" color="Navy"><%=scatTitle%></FONT>
</H1>
<DIV align="center">
<TABLE>
<%
   while (!(loRS.Eof))
   {
      // Alternate the row's background colour
      sRowBGColor = (sRowBGColor == "ivory"? "oldlace" : "ivory");
      lRecordCount++;

      // Create the Item availability string
      if (loRS("QtyInStock") > 0)
      {
         savailable = "In Stock";
      }
      else
      {
         savailable = "<FONT color='#808080'>Unavailable</FONT>";
      }

      // Determine what ACTION clicking a product's add item link
      // does. If stock we want to add the item, if no stock or if
      // cookies disabled then inform user
      if (savailable == "In Stock")
      {
         surlData = "AddToBasket.asp?ID" + escape(loRS("ItemId") + _
                    "&1&" + loRS("ArtistName") + "&" + loRS("Title") + _
                    "&" + loRS("Price") + "Ł&");
      }
      else
      {
         surlData = "javascript:{alert('Sorry this item is currently _
                                          out of stock')}";
      }

%>

The creation of the individual item row occurs next with cells populated with our recordset row's values. Note the Add to Basket hyperlink has an onClick event handler defined which returns the value of the cookiesEnabled variable in the parent window MusicMad.php page. If it's false, indicating that cookies are disabled, any attempts to add to the basket will be cancelled.

<TR bgcolor="<%= sRowBGColor %>">
   <TD WIDTH="125">
      <FONT FACE="Comic Sans MS" SIZE="-1"><%=loRS("ArtistName")%></FONT>
   </TD>
   <TD WIDTH="250">
      <FONT FACE="Comic Sans MS" SIZE="-1"><%=loRS("Title")%></FONT>
   </TD>
   <TD WIDTH="45">
      <FONT FACE="Comic Sans MS" SIZE="-1">Ł<%=loRS("Price")%></FONT>
   </TD>
   <TD WIDTH="85">
      <FONT FACE="Comic Sans MS" SIZE="-1"><%=savailable%></FONT>
   </TD>
   <TD WIDTH="90">
      <FONT FACE="Comic Sans MS" SIZE="-1">
      <A HREF="<%=surlData%>" onClick="return parent.cookiesEnabled" >
         Add to Basket
      </A>
      </FONT>
   </TD>
</TR>

Having moved to the next record, we check to see if we have come to the last record. If so, we can add a previous and next page hyperlink if appropriate. If the lstartItemId was –1 then this was the first page requested so there is no need to put a previous page link.

If we have gone past the maximum per page allowed then we don't display the next record, but, instead, display a next page link with a startItemId of that last record (so we know that if the user clicks the link the page will contain at least one record), then we break out of the while loop.

<%
      loRS.MoveNext();

      // Has the end of the page been reached
      if (lRecordCount == lMaxPerPage || loRS.Eof)
      {
%>
<TR>
<%
         // If this is not the first page then add previous link
         if (lstartItemId != -1)
         {
%>
            <TD COLSPAN=4 ALIGN="RIGHT">
               <A HREF="javascript:history.back();">Previous Page</A>
            </TD>
<%
         }
         // If last item then add link to next page
         if (!loRS.Eof)
         {
%>
            <TD COLSPAN=5 ALIGN="RIGHT">
               <A HREF="Browse.asp?CatId=<%=lCatId %>& _
                                   Description=<%=escape(scatTitle)%>& _
                                   StartItemId=<%=loRS("ItemId")%>">
                  Next Page

               </A>
            </TD>
<%
         }
%>
</TR>
<%
         break;
      }
   }
   loRS = null;
%>
</TABLE>
</DIV>
</BODY>
</HTML>

Save this page with your other files as browse.asp.

Testing Times

You should now be able to browse to the home page and view a list of the products available.

The Menu Bar

(1) (2) (3) (4) (5) (6) (7) (8) (9) (10) (11) (12) (13) (14) (15) (16) (17) (18) (19) (20) (21) (22)

The MusicMad Shopping Basket (Part 1)

Sponsors

Home