| In this exercise, we are going to restructure the Beelze-Bubba site by putting much of the information into a database and using that database to power much of the site's functionality. Currently, Beelze-Bubba publishes news items about the company and its products via its web site. These news items change frequently and also appear in several places throughout the site, leading to many manual updates. In addition, the web site contains information about the company's products. Currently, each category of product has its own page. Under this structure, the number of pages that need to be manually updated and maintained will continue to grow as the product range expands. Beelze-Bubba also has monthly product specials; this information appears in several places on the site, leading to many manual updates. To address these issues, we will use a database to store and track all of Beelze-Bubba's news items and product information. We will then use this information to create dynamic templates that retrieve the latest information from the Beelze-Bubba database. We will also create a News box and a Specials box that we can reuse multiple times throughout the site. By doing this, we will gain the benefit of having fewer pages to change while also cutting down on the time it takes to update content. First let's take a look at the database structure. Table 3.7 outlines the tables and fields used in the Beelze-Bubba database. You can download the BBdata.mdb database from the www.LearnColdFusionMX.com web site. Table 3.7. BBdata.mdb Database Structure| Table | Field Names | Data Type |
|---|
| NewsItems | NewsID | AutoNumber, numeric | | | NewsDate | Date/time (date that the news item was published) | | | NewsHeadline | Text | | | NewsBody | Memo, text | | | Publish | Boolean (indicates whether the stored news item should be published) | | Products | ProductNo | Text (stores unique product identification numbers such as ch001) | | | Name | Text (product name) | | | Description | Memo, text | | | Category | Text (product category) | | | Price | Currency | | | ImageName | Text (stores the file name of the product image) | | | OnSpecial | Boolean (indicates whether this product is currently on special) | | | SpecialPrice | Currency (indicates the price of the item when it is on special) | | | Notes | Memo, text | | Users | UserID | AutoNumber, numeric (a unique ID number for each user) | | | UserName | Text (the user's login name for the administration section of the site) | | | Password | Text (the user's login password for the administration section of the site) | | | Roles | Text (the level of administration the user can perform on the site) |
Create a Database Connection We need to start by connecting to the database. -
In your C:\CFMX10Steps\NewSite directory you will find a folder called _data that contains the BBdata.mdb file. -
Following the procedures outlined previously in this step, use ColdFusion Administrator to create a data source connection to our BBdata.mdb file called "BBdata". (If necessary, refer back to the "Setting Up a Data Source with ColdFusion Administrator" section earlier in this step.)
Create a Reusable News Box Next we want to create a reusable snippet of code that pulls the latest news item from the database. We can then feature this News box several places throughout the site by using the <CFINCLUDE> tag. -
With your text editor, open the tbl_NewsBox.htm page from the Examples\Step03 folder. -
Add the following query to the top of the page: <CFQUERY NAME="qLatestArticle" DATASOURCE="BBdata" MAXROWS=1> SELECT NewsHeadline, NewsBody, NewsDate, Publish FROM NewsItems WHERE Publish <> 0 ORDER BY NewsDate DESC </CFQUERY> By using ORDER BY NewsDate DESC, we will put the most recent article at the top of the resulting recordset. Then, by using the MAXROWS = 1 attribute of the <CFQUERY> tag, we will limit the results to one article (which will be the most recent). NOTE Note the WHERE Publish <> 0 clause. The Publish field is a Boolean field that determines whether the news item should be published or not. All databases store negative (or no) values as zero and positive (or yes) values as a nonzero value. This nonzero value is usually 1; however, some databases use 1. To be sure that our query will work as intended, we specify that the Publish value is NOT equal to zero (or no). If it is not equal to no, it must be yes. Make sense? -
In the second row of the inner table, use <CFOUTPUT> to display the NewsHeading field from our query. We will use the CLASS attribute of the <TD> tag to utilize formatting information from our style sheet. -
In the third row of the inner table, use <CFOUTPUT> to display the NewsBody field from our query. We will use the ColdFusion Left() function. The Left() function limits the number of characters displayed in a text string, starting from the left of the string. It takes two arguments: the name of the text string (in this case, NewsBody) and the number of characters you would like to display (in this case, the first 50). This will prevent large news items from creating a large, unsightly news box. -
In the fourth row of the inner table, we will put a link to the News.cfm page so that users can read the entire article. -
Your code should resemble the code shown in Listing 3.3. Listing 3.3 NewsBox.cfm [View full width] <!--- File: NewsBox.cfm Description: Box with latest news article teaser Author: Created: ---> <!--- Run query to get latest new item ORDER BY date DESC to list the latest news article first. Use MAXROWS=1 to get the first item on the list. ---> <CFQUERY NAME="qLatestArticle" DATASOURCE="BBdata" MAXROWS=1> SELECT NewsHeadline, NewsBody, NewsDate, Publish FROM NewsItems WHERE Publish <> 0 ORDER BY NewsDate DESC </CFQUERY> <!--- outside table for colored border ---> <TABLE BORDER="0" CELLPADDING="3" CELLSPACING="0" WIDTH="95%" ALIGN="center"  BGCOLOR="#FF6600"> <TR> <TD> <!--- inner table for content ---> <TABLE WIDTH="100%" BORDER="0" CELLPADDING="4" CELLSPACING="0" BGCOLOR="#FFFFFF"> <TR BGCOLOR="#003399"> <TD BGCOLOR="#CC0000" ALIGN="center"> <FONT COLOR="#FFCC00"><B>News</B></FONT> </TD> </TR> <TR> <TD > <CFOUTPUT QUERY="qLatestArticle"> #NewsHeadline# </CFOUTPUT> </TD> </TD> </TR> <TR> <TD> <CFOUTPUT QUERY="qLatestArticle"> #Left(NewsBody,50)# ... </CFOUTPUT> </TD> </TR> <TR> <TD ALIGN="right"> <CFOUTPUT> <A HREF="http://CGI.Server_Name#/NewSite/News/News.cfm"> <FONT STYLE="font-family: Verdana, sans-serif; font-weight: bold; font-size: 10px;"> more>> </FONT> </A> </CFOUTPUT> </TD> </TR> </TABLE> </TD> </TR> </TABLE> -
Save this file as NewsBox.cfm into our NewSite\ _includes folder. -
Now use <CFINCLUDE> to add NewsBox.cfm to the right column of our Index.cfm home page.
Create a Product Specials Box Next we will do something similar with our current product special. -
With your text editor, open the tbl_ProdcutSpecial.htm page from the Examples\Step03 folder. -
Add the following query to the top of the page: <CFQUERY NAME="qOnSpecial" DATASOURCE="BBdata" MAXROWS=1> SELECT ProductNo, Name, ImageFile, SpecialPrice, OnSpecial FROM Products WHERE OnSpecial <> 0 </CFQUERY> There should only be one product on special at a time, but just to be sure, we will use the MAXROWS = 1 attribute of the <CFQUERY> tag to limit the results to one special item. NOTE Note the WHERE OnSpecial <> 0 clause. The OnSpecial field is a Boolean field that determines whether the new item is on special or not. All databases store negative (or no) values as zero and positive (or yes) values as a nonzero value. This nonzero value is usually 1; however, some databases will use 1. To be sure our query will work as intended, we specify that the OnSpecial value is NOT equal to zero (or no). If it is not equal to no, it must be yes. -
In the second row of the inner table, use <CFOUTPUT> to display the Name field from our query, formatted to match the rest of the site using the CLASS attribute. -
In the third row of the inner table, use <CFOUTPUT> to display the ImageFile field from our query. Make sure you use the #CGI.Server_Name# variable and specify an absolute path to the image (as we did for the images in our Header.cfm include). There are two subfolders in the Images directory: one called products_sml for small images and one called products_lrg for larger images. We will use the small image for our On Special box. The code for the <IMG> tag is as follows: <IMG SRC = "http://CGI.Server_Name#/NewSite/Images/Products_sml/ImageFile#" ALT="#Name#" BORDER="0"> -
In the fourth row of the inner table, we will put the SpecialPrice information. We will use a ColdFusion function called DollarFormat() to make sure it is displayed as currency. The DollarFormat() function takes only one argument, the number you want displayed as currency. -
In the last row of the inner table, we will put the word more>>. In the Step 4, "Controlling Program Flow," we will create a link to a ProductDetail.cfm template. -
Your code should resemble the code shown in Listing 3.4. Listing 3.4 OnSpecial.cfm [View full width] <!--- File: OnSpecial.cfm Description: Box with latest product special teaser Author: Created: ---> <!--- Run query to get latest special ---> <CFQUERY NAME="qOnSpecial" DATASOURCE="BBdata" MAXROWS=1> SELECT ProductNo, Name, ImageFile, SpecialPrice, OnSpecial FROM Products WHERE OnSpecial <> 0 </CFQUERY> <!--- outside table for colored border ---> <TABLE BORDER="0" CELLPADDING="3" CELLSPACING="0" WIDTH="95%" ALIGN="center" BGCOLOR="#FF6600"> <TR> <TD> <!--- inner table for content ---> <TABLE WIDTH="100%" BORDER="0" CELLPADDING="4" CELLSPACING="0" BGCOLOR="#FFFFFF"> <TR BGCOLOR="#003399"> <TD BGCOLOR="#CC0000" ALIGN="center"> <FONT COLOR="#FFCC00"> <B>On Special</B> </FONT> </TD> </TR> <TR> <TD ALIGN="center"> <CFOUTPUT QUERY="qOnSpecial"> #Name# </CFOUTPUT> </TD> </TR> <TR> <TD ALIGN="center"> <CFOUTPUT QUERY="qOnSpecial"> <IMG SRC = "http://CGI.Server_Name#/NewSite/Images/Products_sml/ImageFile#" ALT="#Name#" BORDER="0"> </CFOUTPUT> </TD> </TR> <TR> <TD ALIGN="center"> <CFOUTPUT QUERY="qOnSpecial"> Special Price:<B>#DollarFormat(SpecialPrice)#</B> </CFOUTPUT> </TD> </TR> <TR> <TD ALIGN="right"> <CFOUTPUT QUERY="qOnSpecial"> more>> </CFOUTPUT> </TD> </TR> </TABLE> </TD> </TR> </TABLE> -
Save this file as OnSpecial.cfm into our NewSite\ _includes folder. -
Now use <CFINCLUDE> to add the OnSpecial.cfm to the right column of our Index.cfm home page. The home page should now look similar to Figure 3.12. Figure 3.12. Beelze-Bubba home page With feature boxes. 
Create a Single Product Category Page We are going to use the database to display our product information while also cutting down on our maintenance. In our old, premakeover site, there was a separate page for each product category. It will become very maintenance intensive to update all these pages as the number of products and categories grows. Back in Step 2, "Using Variables," we added a URL parameter to the category links on our LeftNav.cfm file. We will use this URL parameter to create a single ProductList.cfm page that pulls database information for the product category chosen by the user. -
Make a copy of the Products.cfm page, name it ProductList.cfm, and save it into the NewSite\Products folder. -
Make some room at the top of the template under the file information comments. Create a <CFQUERY> to pull ProductNo, Name, Description, Category, Price, and ImageFile information from the Products table based on the URL variable passed from the category links in the LeftNav.cfm navigation menu. Your query should look similar to the following: <CFQUERY NAME="qProducts" DATASOURCE="BBdata"> SELECT ProductNo, Name, Description, Category, Price, ImageFile FROM Products WHERE Category = '#URL.Category#' </CFQUERY> -
Delete all of the content in the center main body text cell except for the <CFINCLUDE> at the bottom of the cell that calls our Footer.cfm code. -
At the top of the cell, use <CFOUTPUT> and the #URL.Category# variable to output a category header at the top of the main body text cell. To match the rest of the site, surround the variable with HTML <P> tags and use the CLASS=header attribute to use style sheet formatting. Your code for this line should be similar to the following: <CFOUTPUT><P >#URL.Category#</P></CFOUTPUT> -
For our product listings, we are going to use a nested table. You can find the code for this table in a file called tbl_ProductList.htm in your Examples\Step03 folder. Copy the code for the table into your ProductList.cfm template just beneath the category header you set up in the preceding step. -
In the first cell of the product list nested table, we will create a link to insert a small image of our product. The small product images are located in our NewSite\Images directory in a subfolder called products_sml. The name of the image we want to use is the value of the ImageFile field from our query. The code to call this image is as follows: <IMG src="/books/4/16/1/html/2/../images/products_sml/ImageFile#" ALT="#Name#" WIDTH="100" HEIGHT="100" BORDER="0" > We will also put the Name field from the query into the ALT attribute of the <IMG> tag to enable users to view the product name when they hover their mouse pointers over the image. -
In the second cell, we will put product information, including the product Name, an abbreviated Description using the Left() function, and the Price (again, using the DollarFormat() function). We will use inline styles to match the formatting to the rest of the site. Your code should look similar to the following: <TD WIDTH="150" VALIGN="top"> <!--- product information ---> <FONT COLOR="##990033"><B>#Name#</B><BR></FONT> #Left(Description, 60)#...<BR> <FONT FACE="Verdana, sans-serif"> <B>#DollarFormat(Price)#</B> </FONT> </TD> Don't forget to escape the # symbol for any color codes that fall between <CFOUTPUT> tags. -
The third cell contains graphics that will be linked to other pages in future exercises. These include a Details button that will eventually pop up a window with more product detail and an Add to Cart button that will place the product into our online shopping cart. -
The code in the main body text should look similar to the following: [View full width] <!--- main body text ---> <TD WIDTH="385" VALIGN="top" BGCOLOR="#FFFFFF"> <!--- category header ---> <CFOUTPUT><P >#URL.Category#</P></CFOUTPUT> <!--- database query information ---> <HR> <CFOUTPUT QUERY="qProducts"> <!--- nested product listing table ---> <TABLE CELLSPACING="0" CELLPADDING="2" ALIGN="center"> <TR> <TD WIDTH="100"> <!--- small product image ---> <IMG src="/books/4/16/1/html/2/../images/products_sml/ImageFile#" ALT="#Name#" WIDTH="100" HEIGHT="100" BORDER="0" > </TD> <TD WIDTH="150" VALIGN="top"> <!--- product information ---> <FONT STYLE="color: ##990033; font-weight: bold;">#Name#</FONT><BR> #Left(Description, 60)#...<BR> <FONT STYLE="font-family: Verdana, sans-serif; font-weight: bold; font-size: 10px;">#DollarFormat(Price)#</FONT> </TD> <TD WIDTH="100" ALIGN="center" VALIGN="middle"> <!--- detail and shopping cart buttons ---> <IMG src="/books/4/16/1/html/2/../images/details.gif" ALT="more product detail" WIDTH="75" HEIGHT="25" BORDER="0" VSPACE="10"> <IMG src="/books/4/16/1/html/2/../images/add_cart.gif" ALT="add this item to your shopping cart" WIDTH="75" HEIGHT="25" BORDER="0"> </TD> </TR> </TABLE> <HR> </CFOUTPUT> <P> </P> <!--- include text-based nav bar ---> <CFINCLUDE TEMPLATE="../_includes/Footer.cfm"> </TD>
-
Save the your changes and browse to the main Products.cfm file. Follow one of the category links. Your page should look similar to Figure 3.13. Figure 3.13. The product category display. 
Pull News Items from the Database Finally, we want to be able to pull all news items from the database and display them on the News.cfm page. Attempt to use what you have learned so far to come up with the solution yourself. To make this happen, you will need to do several things: Create a SQL statement to request all news items, sorted in descending order by date Create a <CFQUERY> block to pass that SQL statement to the database and retrieve the resulting recordset Create a <CFOUTPUT> block to display the news items on the News.cfm page
If you get stuck, you can find the code in your CompletedFiles\MakeoverExercises\Step03 folder. Go get 'em! |
