Administrator Recipe: Viewing User Statistics

The final page of the User Login application is an administrative function for tracking user registration. With this page, you'll be able to see how many total registered users there are as well as how many registered in the last day, the last week, and the last month.

One special feature of this application is that it makes use of an Access view in a process referred to as querying the query, in which a recordset is derived from a rather complex SQL statement within the data source.

Step 1: Implement User Statistics Design

As usual, we'll build the page structure first.

1. Create a basic dynamic page, either by hand or from a template.

   In the UserLogin folder, locate the folder for your server model and open the userstatistics page found there.

2. Add a table to the content region of your page to contain the interface elements for the application.

   From the Snippets panel, drag the Recipes > UserLogin > Wireframes > User Statistics - Wireframe snippet into the Content editable region.

3. Within the table, insert a content table with the necessary text and a form button. No form is necessary for this application.

   Place your cursor in the row below the words USER STATISTICS in the second cell and insert the Recipes > UserLogin > ContentTables > User Statistics - Content Table snippet [r1-25].

   
   

   Figure r1-25.

   

   
   

Step 2: Add Database Components

Although we're actually adding a very powerful bit of functionality to our application page, Dreamweaver allows us to insert it as a standard recordset. Here's the SQL behind the Access view we'll be using:

 SELECT (SELECT Count([UserID]) FROM Users WHERE ((([Users].[UserRegDate])>Now()-1))) AS RegToday, (SELECT Count([UserID]) FROM Users WHERE ((([Users].[UserRegDate])>Now()-7))) AS RegThisWeek, (SELECT Count([UserID]) FROM Users WHERE ((([Users].[UserRegDate])>Now()-30))) AS RegThisMonth, Count([UserID]) AS UserCount FROM Users; 

Despite the complexity of the view, the implementation of this functionality in Dreamweaver is quite straightforward for ASP and ColdFusion. Because PHP does not support views, the recordset needs to be added directly in the Advanced Recordset dialog.

For ASP and ColdFusion

1. From the Bindings panel, select Add (+) and choose Recordset.

2. Make sure you are in Simple mode.

3. In the Name field, enter the label for your recordset.

   Enter UserStats as the recordset name.

4. From the Connections list, choose the desired data source connection.

   Select Recipes from the Connections list.

5. Choose the appropriate table containing the users information.

   Select UserRegStats from the Tables list.

6. Choose the All option under Columns.

   No filtering or sorting is necessary; it's all handled in the SQL statement.

7. Click OK to confirm your choices and close the dialog.

For PHP

1. From the Bindings panel, choose Add (+) and select Recordset (Query).

2. Switch to the advanced view of the Recordset dialog, if necessary.

3. Enter an appropriate name for the recordset.

   Enter UserStats in the Recordset field.

4. Select your data source.

   Choose Recipes from the Connections list.

5. Enter the following code in the SQL area:

   

    SELECT SUM(UserRegDate > NOW() - INTERVAL 24 HOUR) AS RegToday, SUM(UserRegDate > NOW() - INTERVAL 7 DAY) AS RegThisWeek, SUM(UserRegDate > NOW() - INTERVAL 30 DAY) AS RegThisMonth, COUNT(UserID) AS UserCount FROM Users 

6. Save your page.

Step 3: Display User Statistics

All that's left is to show the results of the recordset. This is simply a matter of dragging dynamic elements into place.

1. From the Bindings panel, expand the UserStats recordset.

2. Drag the following dynamic elements onto the page:

   • Drag the UserCount field in the cell next to the label Total Users.

   • Drag the RegToday field in the cell next to the label Last 24 Hour Activity.

   • Drag the RegThisWeek field in the cell next to the label Last 7 Days Activity.

   • Drag the RegThisMonth field in the cell next to the label Last 30 Days Activity.

   When completed, the page will resemble the one in Figure r1-26.

   
   

   Figure r1-26.

   

   
   

3. Save the page as userstatistics using the appropriate file extension for your platform.

Your user login application is now complete. Be sure to test all the various pages as both a user and an administrator.