| The View Catalog page of the Web store is the heart of the application. This is where users come to view the items that currently reside in the catalog and place any item that they choose in their shopping cart. Before you begin creating the application, take a look at the Web store database, which should reside within the Database folder of the project. Figure 28.1 shows the two tables that this chapter concerns itself with, the products and inventory tables. Figure 28.1. The products and inventory tables are used in this chapter.
The products table contains the product information on what the Web store sells DVDs. Figure 28.2 shows the titles and descriptions of the products the Web store sells. Figure 28.2. The Web store sells DVD titles from current movie selections.
What the products table does not currently show are the images that will reside as thumbnails within the View Catalog page. This will be covered a bit later. Also, notice that Figure 28.3 shows the inventory table and the quantities for each item. Figure 28.3. The inventory table contains all the quantities available for each product.
You may be asking yourself why there aren't any product names within the inventory table. Figure 28.4 shows the relationship between the products table and the inventory table. Remember, the relationship is created with the ProductID between the two tables; this is the reason for the ProductID within the inventory table rather than the simple ProductName. Figure 28.4. A relationship exists between the inventory table and products table based on the ProductID.
NOTE Technically the quantity field could reside within the products table. The reason for separating the products from the quantities is simplicity and scalability. If in the future you wanted to carry clothing, you could potentially have the same shirt in both blue and green. Because the shirt quantities would vary by color, a separate table to handle the quantities solves the dilemma.
Now that you've given the database a final overview, you are ready to begin extracting data from it. The next few sections cover Recordsets Dynamic Text Paging Conditionals Regions Commands Creating a Recordset By now you may be curious as to how the data within your database can be extracted into your application. Sure, you've learned a lot about SQL and are familiar with the commands to retrieve the information, but now what? SQL alone does not provide enough to be able to read from the database and write that data to the application; there's still a piece of the puzzle missing. That piece is the recordset. Recordsets act as an intermediary virtual table between the database and the application. You can write SQL commands to ask the questions of the database, also known as a query, but the information retrieved is stored within a recordset, allowing for programming logic to iterate through the recordset and ultimately present the data to the application in a structured way. Figure 28.5 illustrates this point. Figure 28.5. The application makes a call to the database which then returns data in the form of a recordset.
Remember that the questions asked of the database are made in the form of queries, and queries are a process that usually involve SQL to structure how the question will be asked. In the case of the preceding example, the SQL commands could have a join to merge the two tables into one virtual table or recordset. The recordset is then browsed through by the application logic and presented to the user within a well-structured HTML document. To demonstrate this point, you can create a simple recordset using Dreamweaver MX that will query the products table only. To create a recordset, follow the steps outlined next: -
From the + icon in either the Bindings tab or the Server Behaviors tab, select Recordset (Query). Figure 28.6 shows the drop-down menu that you will see. Figure 28.6. Select Recordset (Query) from either the Server Behaviors tab or the Bind-ings tab.
-
The Recordset dialog box appears. At this point, you can provide a name for the recordset. -
Select the connection name from the dropdown list. If the name of your connection does not appear within the drop-down menu, it typically means that you did not define a connection. If that is the case, select Define to create the connection or refer back to Chapter 17, "Web Application Preparation," for more help on creating connections. -
Pick the table within the database that you want to create a recordset for. -
At this point, you can select All or Selected and choose only certain fields within the database that you want to include within the recordset. -
You may also select optional Filters and Sorts. The results appear within Figure 28.7. Figure 28.7. Fill out all the information within the recordset dialog box.
-
Select Test. -
Figure 28.8 shows the returned recordset with all the data from the products table. Now that you know the recordset works, click OK. Figure 28.8. The test results show the recordset with populated data.
-
Select OK in the recordset dialog box. You will be returned to the authoring environment, only this time you will have a new server behavior listed for the recordset. Figure 28.9 shows the Server Behavior panel with the new recordset behavior. Figure 28.9. Creating a recordset displays the new behavior within the list.
Note that by selecting the Bindings tab and then expanding the recordset, you are able to view the field names contained within the recordset. Creating an Advanced Recordset Creating a simple recordset would serve your needs if you were merely performing a query of all or certain fields within the database. But what if you wanted to perform joins and merge two tables into one recordset? Unfortunately, the simple method would not do. Although creating advanced recordsets can become very complex, the trade-off is flexibility, scalability, and power. Rather than creating multiple recordsets in which to store each and every table, you can join two or more tables into one recordset based on a common value. To create an advanced recordset for the products and inventory tables, follow these steps: -
Now that you have a basic recordset already complete, why waste it? Double-click it within the Server Behaviors panel to open the Recordset dialog box again. -
Click the Advanced button. -
Figure 28.10 shows that the dialog box is relatively similar in design, except that you are able to enter SQL manually rather than allowing Dreamweaver MX to create it for you. Figure 28.10. The Advanced Recordset tab enables you to manually type in the SQL code.This allows for greater flexibility.
-
If you remember the lengthy code structure for creating SQL joins, begin typing. If you are like most people and like to rely on programs to do the work for you, open Access and create a new query in design view. -
Add the products and inventory tables as shown in Figure 28.11. Figure 28.11. Add the products and inventory tables to the design view.
-
Select Products.* from the products table and Inventory.* from the inventory table. Remember, the star signifies that all records will be extracted. Figure 28.12 shows the query designer. Figure 28.12. Select the fields from the tables that you want to include in your query.
-
To use the SQL code that was generated, select SQL View from the View menu. Figure 28.13 shows the SQL code that is generated. Figure 28.13. View the SQL code that is generated by selecting it from the View menu.
-
Copy the SQL code, save the query, and close Access. -
You are now free to paste the code into the SQL box in the Advanced Recordset dialog box. Figure 28.14 shows the result. Figure 28.14. Paste the SQL code you created in Access into the SQL code box of the Advanced Recordset dialog box.
-
Figure 28.15 shows that when you select Test, you get all the results from both tables joined into one recordset view. Figure 28.15. Combine the contents of two tables into one recordset by using a join.
Whether you are creating simple or advanced recordsets within Dreamweaver MX, you can be assured that the process remains relatively simple. As you have seen, even complex joins can be achieved with ease. The next sections will introduce you to methods of extracting the data from the recordset into your application. Working with Dynamic Elements Now that you have been able to extract data from your data source, your next step is to structure it within your application somehow. Dreamweaver MX's Server Behavior and Binding panels provide the capabilities you need to get started producing dynamic elements that are centralized within the database but exposed via the application. Dynamic Text The first step to creating dynamic elements is making all your text as dynamic as possible. That is, allowing all your company's valuable information to reside within the database. To start creating dynamic text, begin by creating a table that will serve as the means of organizing the data output. Place a new table inside the viewcatalog.asp page by selecting Table from the Insert menu. This table should have only 1 row and 2 columns. Insert a nested table into the cell on the right with the specifications shown in Figure 28.16. Figure 28.16. Insert a nested table. 
Merge the three cells on the right by clicking and selecting the Merge Cells command from the Table submenu, as shown in Figure 28.17. Figure 28.17. Merge the three cells on the right. 
Figure 28.18 shows the three data choices that you will place in the cells. Figure 28.18. Place static text in the three fields to caption the dynamic text.
NOTE I mentioned the addition of an image. Keep in mind that the image will be placed dynamically as well depending on which record you are currently on. This will be covered next.
NOTE Notice that there is a text caption for cost. If you remember how the database was structured, there was no field for cost. This will be added in next and will demonstrate the use of the recordset refresh.
You've probably guessed by now that new fields have to be added into the database for cost of the products and for the image that relates to the DVD title. To do this, follow the steps outlined next: -
Open the database file, which should reside in the Database folder of the application in Access. -
Double-click to open the products table. -
Switch to design view. -
Add two new fields for ImagePath and ProductCost, as shown in Figure 28.19. Figure 28.19. Add two new fields for ImagePath and ProductCost.
TIP Access has no way of physically storing binary files. Instead, a path is inserted into the field, and later the application will use that path and dynamically change an <img> to coincide with the path in the database. -
Go ahead and add the information for each field as shown in Figure 28.20. Notice that the image paths actually point to GIF images within the Thumbnails folder. Figure 28.20. Add all the relevant information for all the fields.
-
When you are finished, save the database and close Access. That wasn't so bad! Whenever you want to add more information to the database, open it up and begin adding the necessary text. Now the problem becomes the recordset. Remember that you created a recordset based on the values that were previously in the database, not the recent changes. To correct this, select the Bindings tab and click the Refresh Recordset icon on the top right, as shown in Figure 28.21. Figure 28.21. Refresh the recordset to update any changes made to the database.
With the Bindings panels still open, select the fields from within the recordset and drag them to their proper locations, as shown in Figure 28.22. Figure 28.22. Drag the fields from the recordset into the proper cells.
Save the page and run it from the browser. Notice how the first record in the database is shown within the cells. Later you will learn about paging, which allows you to cycle through each record. Dynamic Images Now that you have created dynamic text within your application, you're ready to begin adding images. The images that will be added here will not be the typical static images that you have used throughout the book. To begin adding dynamic images, follow these steps: -
Place your cursor in the cell designated for the image. -
Select Image from the Insert menu. -
Near the top of the Select Image Source dialog box, you can choose from the file system or from a data source. Select Data Source. -
Figure 28.23 shows how you are able to select the ImagePath from the recordset. Select OK. Figure 28.23. Insert an image dynamically by adding it from a data source rather than from the file system.
Figure 28.24 shows how the image icon has been inserted for you. Figure 28.24. The image icon is used as a placeholder until the page is run.
Save the file and view it in the browser. The image for the DVD should now appear as shown in Figure 28.25. Figure 28.25. The image is dynamically placed based on the path within the database.
Recordset Paging Now that you've seen just how easy it is to place dynamic content within your site, you'll probably want to begin adding paging features. Paging exposes all records within a pagination system, not unlike those of a book. For every press of a button, your user can advance to the next record or, conversely, return to a previous record. Developers gain certain benefits from pagination, including Load time Rather than the page having to process multiple records, it processes only one. Records are only loaded as users advance forward to another record. Size constraints By paging through a recordset, screen real estate is ultimately gained. The records are loaded within a certain area of the page rather than all records showing down the page. Dreamweaver MX's pagination behaviors include Move to First Record Returns the user to the first record of the recordset. Move to Previous Record Returns the user back one record. Move to Next Record Advances the user one record forward. Move to Last Record Advances the user to the last record in the recordset. Move to Specific Record Advances or returns the user to a record that is specified by the developer or by a parameter passed by the user. To begin adding pagination features to your site, follow these steps: -
Place your cursor just below the table that includes all the dynamic data. -
From the Recordset Paging submenu, select Move to Previous Record, as shown in Figure 28.26. Figure 28.26. Add the Move To Previous Record behavior from the Recordset Paging submenu.
-
The Move To Previous Record dialog box will appear similar to Figure 28.27. Make sure that it will create the appropriate link and that it is being generated for the appropriate recordset. Select OK. Figure 28.27. Confirm the settings from the Move To Previous Record dialog box.
-
Add a space and select Move to Next Record from the Recordset Paging submenu, as shown in Figure 28.26. -
Save your file and test it in the browser. You should be able to navigate from the first record to the last record and back. Figure 28.28 shows the record Air Force One that appears after The Abyss as a result of clicking Next. Figure 28.28. Selecting Next enables you to cycle through records.
Showing Specific Regions Now that you have added the capability to cycle to the end of the recordset and back, consider the following problem: Users click Next until they get to the last record and then they are abruptly stopped. They keep clicking Next but nothing happens. The problem is that the users have reached the end of the recordset and they cannot go any further, but the user doesn't know that. Dreamweaver MX provided functionality in the form of a group of Show Region behaviors that alert users that they have reached the end of the recordset. The complete list of Show Region behaviors are as follows: Show Region if Recordset Is Empty This behavior can be useful to alert a user that an empty result was returned from the database. Show Region if Recordset Is Not Empty Use this behavior when you want to alert the user that results were indeed returned from the database. Show Region if First Record If users are on the first record, you can alert them. Show Region if Not First Record As users cycle through the records, you can provide a message. When they are on the first record, a message can be displayed. Show Region if Last Record If users are on the last record, you can alert them. Show Region if Not Last Record As the user cycles through the records, you can provide a message. When they are on the last record, a message can be displayed. To add a Show Region behavior to your application, follow these steps: -
Place the text No more items to view just below the table or where you want the text to appear. -
With the text highlighted, select the server behavior Show Region if Last Record from the Show Region submenu, as shown in Figure 28.29. Figure 28.29. Add the Show Region server behavior to the text message you want displayed.
-
Select the appropriate recordset from the dialog box and select OK. -
Save the page and run it in a browser. Figure 28.30 shows the message as it will appear when you reach the end of the recordset. Figure 28.30. The message appears when you reach the end of the recordset.
Using Repeat Region Although recordset paging is the ideal model to strive for, at times you might want to display all the records in the database at once. The Repeat Region behavior enables you to create a pattern that repeats within the application. For instance, in the View Catalog Web store example, a table was created to display the content for the image, name, description, and cost. Using the Repeat Region server behavior, you are able to maintain that structure while repeating the contents for every record with the database. To create a repeatable region, follow the instructions outlined next: -
Select the table for which you want to create the repeatable region. -
Select Repeat Region from the Server Behaviors panel, as shown in Figure 28.31. Figure 28.31. Select the table that you want to create the repeatable region for.
-
The Repeat Region dialog box allows you to enter choices regarding which recordset to create the repeat region for as well as how you want to display the results. The Repeat Region dialog box is shown in Figure 28.32. Figure 28.32. Select the options for show and recordset from the Repeat Region dialog box.
-
Save the file and run it in the browser. Figure 28.33 shows all the records listed at once. You can scroll down the page to view all the DVDs that are contained within the products table. Figure 28.33. All the records are shown when you use the Repeat Region server behavior.
|
