|
Creating a Category Site with ASP
Content vs. Format
In this issue we will implement a catalog site that is build with Active Server pages and SQL Server. Along with the implementation there is source code and a discussion of the advantages and disadvantages of creating a catalog site that gets it's content from a database. Included are pages for displaying products, creating a menu page, category page, and running a search across a database. , Before we begin let us make a distinction between content and format. Content is the actual meat of the pages, it is the thing that draws the user to the site and gives the most value add. Format is how the content looks. Different formats allow content to look differently and give your site character.
Dynamic Sites
There are two different types of dynamic web sites. The first type has pages that are generated based on user input. The second type has pages that are generated based on database constraints. An example of the first type is Yahoo http://www.yahoo.com, the user types in a search phrase and the pages generated are based off the information in the database. In theory, the number of pages that can be viewed is infinite. Stephen Genusa's FAQ http://www.15Seconds.com/faq is an example of the second type of dynamic web site. This site uses the database to store the content that is displayed in its pages. Since no user input is involved the number of pages that can be possible view are finite. The second type of site is the one that we will be discussing in this article, since it is perfect for page generation.
Advantages
Leaving the content in the database has a lot of advantages. For instance, since the content of most web sites is changed more than the format, having the content in the database makes it easy to change. Secondly you can have a small number of pages display a large amount of content. In the example there are five pages that can display thousands of products. Having the content in the database also reduces the risk of improperly formatted pages. Since the number of pages are small and the content is stored within the pages there is less likelihood of problems with the format, compared to having a static HTML page for each product. Another advantage is that if you have to change the format, changing one page, could change the look for all products in the database.
Comparision
Though out the example we will make a comparison between a dynamic site, the example, and that same site implemented with static pages. By the end it should be obvious that the number of static pages to make a decent catalog would be huge and unmanageable.
The Approach
In this issue we will be dealing with content that is stored in the database. The examples read content from the database and display it in the pages provided. However, to make the examples simple we have not formatted the content. It is left as an exercise to the reader to format the content, by adding HTML to the Active Server pages. The examples assume that the format of the pages is within the pages themselves and not in the database.
Getting Started
Imagine a web site that runs off a database with one table and has one page. Each row in the table represents a particular product that the web site will display. The table has fields like Id, Title, Price, and Description. Here is a SQL statement that will generate the table for you:
CREATE TABLE tblProduct
(
Product_Id int IDENTITY NOT NULL PRIMARY KEY,
Product_Title varchar (100),
Product_Price money,
Product_Description text
)
The goal of the product page is to display detailed information about the individual product. It can also be considered the leaf page of the web site. The product page is dynamic and takes an Id in the request, like this:
http://www.myserver.com/scripts/leaf.asp?Product_Id=1
The requested Id is used to reference a row in the table and retrieve the Title and other information. The page then displays this information. Here is an example of the what leaf.asp might look like.
Leaf.asp Code
<%
' These Constants will have to be changed to reflect
' Your Database
Const SQLDSN = "Example"
Const SQLUser = "sa"
Const SQLPassword = ""
' Create Objects
Set Conn = Server.CreateObject("ADODB.Connection")
'Open the Connection
Conn.Open SQLDSN, SQLUser, SQLPassword
' Get Values
Product_Id = Request.QueryString("Product_Id")
' Let's Get All the Question Information Ahead of Time
sql="SELECT Product_Title, Product_Description, Product_Price " &_
"FROM tblProduct " &_
"WHERE Product_Id=" & Product_Id
Set RS = Conn.Execute(sql)
Title = RS("Product_Title")
Description = RS("Product_Description")
Price = RS("Product_Price")
RS.close
Conn.Close
%>
<HTML>
<HEAD>
<TITLE><%=Title%></TITLE>
</HEAD>
<BODY>
<H1><%=Title%></H1>
<%=Description%><P>
<%=Price%>
</BODY>
</HTML>
Comparision
The other way to accomplish the same task is to have a separate static page for each product. So if you had P products you would have to have P static pages.
The problem with the one page, one table example is that the user must know the Id number of the products in order to view them. We can fix this problem by adding another page to the example. This page displays all the products in the Table by selecting the Id and the Title from the table. Here is an example of the SQL query:
SELECT Product_Id, Product_Title FROM tblProduct
For each row returned from the query the page would create an anchor that would link to the leaf page. Something link this:
<A HREF="leaf.asp?Product_Id=<%=RS("Product_Id")%>"><%=RS("Product_Title")%></A>
Menu.asp Code
<%
' These Constants will have to be changed to reflect
' Your Database
Const SQLDSN = "Example"
Const SQLUser = "sa"
Const SQLPassword = ""
' Create Objects
Set Conn = Server.CreateObject("ADODB.Connection")
'Open the Connection
Conn.Open SQLDSN, SQLUser, SQLPassword
%>
<HEAD>
<TITLE>Menu</TITLE>
</HEAD>
<BODY>
<HTML>
<H1>Menu</H1>
<%
sql="SELECT Product_Id, Product_Title FROM tblProduct"
Set RS = Conn.Execute(sql)
Do while Not RS.Eof
%>
<A HREF="leaf.asp?Product_Id=<%=RS("Product_Id")%>"><%=RS("Product_Title")%></A>
<%
Rs.MoveNext
Loop
%>
</BODY>
</HTML>
<%
RS.close
Conn.Close
%>
Comparison
If you were going to do this without the help of the database you would need to create a static menu page by hand that links to all the other static product pages that you created by hand. Then if you need to add a new product you would have to make a new static product page and add a new link to the static menu page. With P products and 1 menu you would need to create P+1 static pages.
If you have more then 20 products then the menu page in the above example becomes to long. This is when you want to divide your products into multiple categories. In order to do this you will need to create two more tables and another page. First create a table called tblCategory that contains a Category_Id and a Title field. Second create a table called bndCategoryProduct that will bind the product identifier with the category identifier. Here are the SQL statements that will create both tables:
CREATE TABLE tblCategory
(
Category_Id int IDENTITY NOT NULL PRIMARY KEY,
Category_Title varchar (100)
)
CREATE TABLE bndCategoryProduct
(
Category_Id int,
Product_Id int
)
For each category fill in the category title in the tblCategory table. As you fill in the titles SQL Server will generate a number for you that you can use to fill in the bndCategoryProduct table. In the bndCategoryProduct table add all the product identifiers and the category identifiers for the category that the product is in. Notice here that one product can be in many categories.
Now all we have to do is create a page that displays the each category and the products that are in the categories.
The Category page looks a lot like the menu page, except that the category page must be requested with a Category Id. For example:
http://www.myserver.com/scripts/category.asp?Category_Id=1
The SQL query uses the category identifier to find the products in that category. Here is an example of the query:
SELECT tblProduct.*, tblCategory.*
FROM tblProduct, tblCategory, bndCategoryProduct
WHERE tblProduct.Product_Id = bndCategoryProduct.Product_Id AND
tblCategory.Category_Id = bndCategoryProduct.Category_Id AND
tblCategory.Category_Id = 1
Here is the ASP page that would display links to all the products that were within a particular category.
Category.asp Code
<%
' These Constants will have to be changed to reflect
' Your Database
Const SQLDSN = "Example"
Const SQLUser = "sa"
Const SQLPassword = ""
' Create Objects
Set Conn = Server.CreateObject("ADODB.Connection")
'Open the Connection
Conn.Open SQLDSN, SQLUser, SQLPassword
' Get Values
Category_Id = Request.QueryString("Category_Id")
' Get the Category Title so that we can use it for the title of this page
sql="SELECT Category_Title FROM tblCategory WHERE Category_Id=" & Category_Id
Set RS = Conn.Execute(sql)
Title = RS("Category_Title")
RS.Close
%>
<HEAD>
<TITLE><%=Title%></TITLE>
</HEAD>
<BODY>
<HTML>
<H1><%=Title%></H1>
<%
sql="SELECT tblProduct.*, tblCategory.* " &_
"FROM tblProduct, tblCategory, bndCategoryProduct " &_
"WHERE tblProduct.Product_Id = bndCategoryProduct.Product_Id AND " &_
"tblCategory.Category_Id = bndCategoryProduct.Category_Id AND " &_
"tblCategory.Category_Id = 1"
Set RS = Conn.Execute(sql)
Do while Not RS.Eof
%>
<A HREF="leaf.asp?Product_Id=<%=RS("Product_Id")%>"><%=RS("Product_Title")%></A>
<%
Rs.MoveNext
Loop
%>
</BODY>
</HTML>
<%
RS.close
Conn.Close
%>
Comparison
If you where going to do this with static pages you would need to create each category page by hand. Every time that you added a product you would have to modify the static category pages to point to the new static category page. If you had P products and C categories you would have P+C static pages.
Let's make the example more complex, by adding a link that takes the user from the product page back to the category that they came from. In order to do this we are going to need to modify the category page to pass the category identifier to the product page. The product page can then use the category identifier to get back to the correct category. Remember in order to link to a category you need to know the category identifier.
Let's rename the category page, category.asp, to category2.asp. And modify the anchor line to look like this:
<A HREF="leaf2.asp?Category_Id=<%=Category_ID%>&Product_Id=<%=RS("Product_Id")%>"><%=RS("Product_Title")%></A>
Now the category2.asp pass the category identifier to the leaf page. Notice that we have renamed leaf.asp to leaf2.asp because we need to modify the leaf page also. Change the leaf2.asp.
Leaf2.asp Code
<%
' These Constants will have to be changed to reflect
' Your Database
Const SQLDSN = "Example"
Const SQLUser = "sa"
Const SQLPassword = ""
' Create Objects
Set Conn = Server.CreateObject("ADODB.Connection")
'Open the Connection
Conn.Open SQLDSN, SQLUser, SQLPassword
' Get Values
Product_Id = Request.QueryString("Product_Id")
Category_Id = Request.QueryString("Category_Id")
' Let's Get All the Question Information Ahead of Time
sql="SELECT Product_Title, Product_Description, Product_Price " &_
"FROM tblProduct " &_
"WHERE Product_Id=" & Product_Id
Set RS = Conn.Execute(sql)
Title = RS("Product_Title")
Description = RS("Product_Description")
Price = RS("Product_Price")
RS.close
Conn.Close
%>
<HTML>
<HEAD>
<TITLE><%=Title%></TITLE>
</HEAD>
<BODY>
<H1><%=Title%></H1>
<%=Description%><P>
<%=Price%>
<P>
<A HREF="Category2.asp?Category_Id=<%=Category_Id%>">Back To Category</A>
</BODY>
</HTML>
Comparison
If you wanted to create the same web site using static pages you would have a different product page for each category that the user could possible come from. And every time that you added a product you would have to add a new product page for every category that was linking to that product and you would have to modify each category page that the product was in. In you had P products and C categories you would have no less then P product page with a maximum of P*C product pages and C category pages for a possible total of P*C C pages. Stephen Genusa's FAQ ( http://www.15Seconds.com/faq) has 400 FAQ (products) and 20 topics (categories). Because all the FAQ are in more then one topic it would have over 2000 static pages if the site were build statically.
The Big Disadvantage
Search engines are one of the biggest disadvantages to a dynamically driven web site. The problem is two fold, commercial search engines don't usually crawl dynamically generated pages, and Microsoft Index Server, doesn't index the content that comes from the database.
Yahoo
Yahoo (www.yahoo.com) doesn't traverse web pages that are generated with anything besides .htm or. html tags. This means that if you create pages with ASP or the Internet database Connector (.idc) your pages will not appear in Yahoos Index. Commercial search engine's potential to promote your side is tremendous. Waving aside this potential for more efficient content handling is a hard decision. Though Yahoo might change in the future, I currently have been waiting six months.
Microsoft Index Server
Microsoft Index Server doesn't index the content of your pages, only the pages themselves. The Index server reads the page off of the hard drive, doing this means that pages are not executed through the Active Server page ISAPI. Since the page does not execute the data is not drawn from the database so it is not available to index. What gets indexed is the format contented in the .asp page not the data from the database. Having a catalog site that contains a significant number of items means that your users will want to search across the content to quickly find what they are looking for. Though there are some workarounds to the problem, like the article published in the February 1997 issue of MIND, they are expensive from a performance perspective. There is a rumor that Index Server will be creating an OLEDB interface allowing you to index database, and hopefully sometime Exchange Server.
The Example
For this example it is relatively straightforward to create a search page that searchs across the description in the content table. However, there is plenty of room for improvement, like handling multiple words, and searching the other fields like the category title, and the product title.
Search.asp
<%
' These Constants will have to be changed to reflect
' Your Database
Const SQLDSN = "Example"
Const SQLUser = "sa"
Const SQLPassword = ""
' Turn Response Buffer Off so that result will display
' As they come up
Response.Buffer = False
%>
<HTML>
<BODY>
<%
strSearch=Request.QueryString("Search")
strSearch=LTrim(strSearch)
strSearch=RTrim(strSearch)
If Not IsNull(strSearch) AND (Len(strSearch)>0) Then
' Create Objects
Set Conn = Server.CreateObject("ADODB.Connection")
'Open the Connection
Conn.Open SQLDSN, SQLUser, SQLPassword
sql="SELECT Product_Id, Product_Title " &_
"FROM tblProduct " &_
"WHERE Product_Description LIKE '%" & strSearch & "%'"
Set RS = Conn.Execute(sql)
Do while Not RS.Eof
%>
<A HREF="leaf.asp?Product_Id=<%=RS("Product_Id")%>"><%=RS("Product_Title")%></A>
<%
Rs.MoveNext
Loop
Conn.Close
End If
%>
<FORM ACTION="Search.asp" METHOD=GET>
<INPUT TYPE=TEXT NAME="Search" VALUE="<%=strSerach%>">
<INPUT TYPE=SUBMIT VALUE="Search">
</FORM>
</BODY>
</HTML>
Let us take a quick look at improving the search example. Users usually try to type in more then one word into a search line. By typing in more then one word, the user assumes that the returned results will reflect all pages where all individual words are in the page. This narrows the scope of the search so that fewer pages are returned. This isn't the result of more then one word in the previous search page example, search.asp. In search.asp entering more than one-word returns only pages where the entire phrase is found on each page.
Let us modify search.asp to reflect what the user assumes will happen. First rename search.asp to search2.asp. The objective is to modify the search string so that all the words have only one space between them, and that there are no other spaces. Once the search string has this format, then we substitute the spaces for "and"clauses. Let's trim any additional spaces to start using these commands:
strSearch =Ltrim(strSearch)
strSearch =Rtrim(strSearch)
Next substitute a space for all instances of "and". This way users implicitly typing in "and" will have the same results as users typing in a space.
strSearch = Replace(strSearch,"and"," ")
Next remove all symbols that the search doesn't understand, but might be assumed by the user.
strSearch = Replace(strSearch,"or","")
strSearch = Replace(strSearch,"+","")
strSearch = Replace(strSearch,"-","")
strSearch = Replace(strSearch,"(","")
strSearch = Replace(strSearch,")","")
strSearch = Replace(strSearch,"not","")
strSearch = Replace(strSearch,"\"","")
Next remove all extra spaces with these commands:
strSearch = Replace(strSearch," "," ")
strSearch = Replace(strSearch," "," ")
strSearch = Replace(strSearch," "," ")
Now you should have a string that has all words separated by spaces. All you have to do is replace the spaces with the and clause, like this:
strSearch=Replace(strSearch ," ","%' AND Product_Description LIKE '%")
Search2.asp
<%
' These Constants will have to be changed to reflect
' Your Database
Const SQLDSN = "Example"
Const SQLUser = "sa"
Const SQLPassword = ""
' Turn Response Buffer Off so that result will display
' As they come up
Response.Buffer = False
%>
<HTML>
<BODY>
<%
strSearch=Request.QueryString("Search")
strSearch=LTrim(strSearch)
strSearch=RTrim(strSearch)
strSearchSave = strSearch
strSearch = Replace(strSearch,"and"," ")
strSearch = Replace(strSearch,"or","")
strSearch = Replace(strSearch,"+","")
strSearch = Replace(strSearch,"-","")
strSearch = Replace(strSearch,"(","")
strSearch = Replace(strSearch,")","")
strSearch = Replace(strSearch,"not","")
strSearch = Replace(strSearch,"""","")
strSearch = Replace(strSearch," "," ")
strSearch = Replace(strSearch," "," ")
strSearch = Replace(strSearch," "," ")
If Not IsNull(strSearch) AND (Len(strSearch)>0) Then
' Create Objects
Set Conn = Server.CreateObject("ADODB.Connection")
'Open the Connection
Conn.Open SQLDSN, SQLUser, SQLPassword
strSearch=Replace(strSearch ," ","%' AND Product_Description LIKE '%")
sql="SELECT Product_Id, Product_Title " &_
"FROM tblProduct " &_
"WHERE Product_Description LIKE '%" & strSearch & "%'"
Set RS = Conn.Execute(sql)
Do while Not RS.Eof
%>
<A HREF="leaf.asp?Product_Id=<%=RS("Product_Id")%>"><%=RS("Product_Title")%></A><BR>
<%
Rs.MoveNext
Loop
Conn.Close
End If
%>
<%=strSearch%><BR>
<FORM ACTION="Search2.asp" METHOD=GET>
<INPUT TYPE=TEXT NAME="Search" VALUE="<%=strSearchSave%>">
<INPUT TYPE=SUBMIT VALUE="Search">
</FORM>
</BODY>
</HTML>
Another disadvantage of having your whole site dynamically generated is the speed. Static HTML pages are much faster then Active Server pages. The reason is that the Active Server pages need to be executed and they query data from a database. In comparison the HTML page just needs to be read from the disk. Most people recommend that a web site only contain a minimal number of dynamic pages for that reason.
For this reason people choose to use tools that generate static HTML pages from content that is stored in a database. These tools give you the advantages of holding the content in the database and the speed advantages of static pages. A good example of this is the Microsoft Knowledge Base http://www.microsoft.com/kb. You can be sure that Microsoft did not generate each one of these pages individually with exactly the same format. On the other hand, because of the huge volume of traffic they don't display the pages dynamically. Sign Me Up Marketing makes a tool called XBuilder that you can use to create static sites from dynamic ones. You can get more information at http://www.postpointsoft.com
The Example
You can download the Active Server pages in the example:
http://15seconds.com/files/073197.zip
|
