| |
| |
In this topic, you will create the page that lists the ads in a selected category. The ads will be retrieved from the ads_ads table by a recordset A recordset is the result of executing an SQL query. It is composed of multiple rows, each row having multiple columns. The columns presented in the query result depend on the column list declared in the query (they can belong to different tables). The number of rows and their order depend on the query conditions (WHERE, GROUP BY, HAVING, ORDER). The recordset acts as a source of dynamic data in web applications. that is filtered after the category ID. This ID is passed as an URL parameter called id_cat, from the tree navigation menu, when the user clicks on a category.
To create this page, follow the next steps:
Open the list_ads page in Dreamweaver.
Next, you need to create a recordset that retrieves all the ads, their associated categories and locations, their type and the users who posted them. You need all this information in order to display all the details for a classified ad. You also need to filter this recordset by an URL parameter that is passed from the navigation menu, which you will create later. When visitors click on a category from the menu, they need to see only those ads that fall under that category. To create the recordset, go to the Bindings tab in the Application panel, click the + button, and select Recordset from the displayed menu. In the displayed Recordset window, click the QuB3 button, to go to the QuB Visual Recordset interface.
Enter rsAds in the recordset name field, select the database connection you have created (connAds) and click the New Query button, to create the query that will extract the information you want.
In the displayed interface, enter a name for the SQL query you will create, such as "rsAds".
After clicking OK, the MX Query Builder web interface will open, where you will be able to visually generate the recordset.
In the Tables panel, select the tables that will be included in the query: ads_ads, location_loc, user_usr, type_typ.
The corresponding tables will be displayed in the Database Diagram, where you can arrange them in any way you like.
Next, link the tables, by dragging each foreign key onto the primary key it references. For instance, drag the idtyp_ads column from the ads_ads table over the id_typ column in the type_typ table:
You also need to define relations between idusr_ads and id_usr, between idloc_ads and id_loc. The database diagram should look like this:
Next, select the columns you need for your recordset: title_ads, content_ads, name_loc, name_typ, email_usr, idcat_ads, date_ads.
The idcat_ads needs to be equal to the URL parameter received from the navigation menu, when users click on a category. To define this condition, click the button next to the Condition text field, corresponding to the idcat_ads column in the Query Management Panel:
Configured the displayed dialog box as in the following image (the column must be equal to the URL parameter id_cad):
The last thing you need to do before saving the query is to order the classified ads by their date, in descending order, such that the latest ones are always displayed first.
Your query is now complete. You can view the query results or the generated SQL code in the SQL/Results Preview panel:
Click the Save button from the Query List panel, to save your settings.
To close the MX Query Builder web interface and return to Dreamweaver, select Close from the QuB menu.
Back in the MX Query Builder Visual Recordset interface, click the Refresh button to load the generated SQL code. Your recordset is ready. Click OK.
Now that you have all data available in the Bindings tab, it is time to put it up for display. Or in other words, you need to actually show the ads in your page. The most important things regarding an ad are its title, content, type, location and contact e-mail address. So these will be the fields to be displayed. Drag and drop each of them from the rsAds recordset in the Bindings tab onto the page.
The layout is yours to choose, although for this tutorial's purpose, a simple one is enough:
To prevent seeing the separator bars, or even errors when no category is selected or when there are no ads in the current category, the entire region shown above will have to be enclosed in a conditional region A conditional region is a page section that is displayed or hidden depending on the evaluation of a condition. For instance, certain information can be displayed only when the user is logged in to the website., allowing it to be visible only if the rsAds recordset contains data. For this you will use a standard Dreamweaver server behavior A server behavior is a reusable component for server-side development. They add blocks of code to your pages for accomplishing specific tasks. Dreamweaver comes with several default server behaviors and the InterAKT extensions add many more to this list., called Show If Recordset Is Not Empty. It is accessible from Server Behaviors -> Show Region.
The dialog box that opens contains only a drop-down menu, allowing you to select the recordset to check for data.
Now the text will display only if the recordset contains data, but it will only display one record. To make it display all retrieved ads, you must apply a Repeated region. This command is accessible from the Application tab of the Insert panel.
Configure the dialog box that opens to use the rsAds recordset, and to display all records.
Once you click the OK button, the repeated region server behavior will apply on the selected region, which will be marked by a second translator (the first one was put in by the conditional region):
Now if you preview the page in the browser, providing it a correct category ID for which there are ads in the database A database refers to data organized and stored on a computer that can be searched and retrieved by a computer program. Most industrial-strength and many smaller database applications can be addressed using SQL (Structured Query Language)., you will see a list of the ads, instead of only one.
This page will be included together with a tree-like menu of categories and a login nugget on the site's first page. In the next topic you will create the tree menu containing links to the specific categories.