End User Recipe: Meeting Conflict

After the user submits the meeting request, the data source is checked to see if a conflicting meeting is already scheduled. If so, the meeting_conflict page is displayed with an error message and details of the existing meeting. From this page, the user is advised to return to the meeting_request form and modify the request.

All the data-driven elements on the meeting_conflict page come from a single recordset. The recordset's filter is based on a URL parameter passed by the meeting_request page.

Step 1: Implement Meeting Conflict Design

No form is needed for the meeting_conflict page; only a series of dynamic text elements are used.

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

   In the ConferenceRoomScheduler folder, locate the folder for your server model and open the meeting_conflict 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 > ConferenceRoom > Wireframes > Meeting Conflict - Wireframe snippet into the Content editable region.

3. Add the table area to hold the dynamic text for the conflicting record details and an informative error message.

   Place your cursor in the row below the words MEETING CONFLICT and insert the Recipes > ConferenceRoom > ContentTables > Meeting Conflict - Content Table snippet [r3-5].

   
   

   Figure r3-5.

   

   
   

4. Save the file in your site.

Recipe Variation: Adding a Return to Form Button

Although it's perfectly fine to advise the user to select his or her browser's Back button to return to the previous form, some developers might want to provide a dedicated button for this functionality. As described in Recipe 1, a bit of JavaScript history.back() added to the onClick event of a form button, either directly in the tag or through the Call Javascript behavior, works quite well.

Step 2: Add Database Components

Only one data source is required for this page: a recordset containing only the conference details of the just-entered record. For ASP and ColdFusion, this recordset uses a view that combines the Conferences and Rooms tables as well as two formatted data source fields, StartDate and StartTime and one calculated one, ConferenceEnd. Here's the SQL statement used to construct the view:

 SELECT Conferences.ConferenceID, Rooms.RoomName, Rooms.RoomID, Rooms.RoomDescription, Conferences.ConferenceName, Conferences.ConferenceDescription, Conferences.ConferenceStart, Conferences.ConferenceDuration, Conferences.ConferenceBy, Format([ConferenceStart],'h:nn ampm') AS StartTime, Format([ConferenceStart],'m/dd/yyyy') AS StartDate, DateAdd('n',([ConferenceDuration]*60),[ConferenceStart]) AS ConferenceEnd FROM Conferences INNER JOIN Rooms ON Conferences.ConferenceRoom = Rooms.RoomID; 

Because Dreamweaver makes views as accessible as tables, this complex SQL statement can be added through the simple Recordset dialog for ASP and ColdFusion developers.

PHP users must take a different route because views are unsupported in MySQL.

For ASP and ColdFusion

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

2. In the simple Recordset dialog, enter an appropriate name.

   Enter Conferences in the Name field.

3. Select the desired data source connection.

   Choose Recipes from the Connection (Data Source) list.

4. Choose the needed view.

   From the Tables list, select ConferenceDisplay.

5. Leaving the Columns option set to All, set the four Filter list elements like this:

   

6. Keep the Sort option set to None and click OK to close the dialog.

For PHP

PHP users have two hurdles to overcome when defining this recordset. First, we'll have to compensate for the lack of view support in MySQL. This is accomplished fairly easily by using a custom SQL statement. Second, Dreamweaver's implementation of PHP code requires a work-around. Unfortunately, the Dreamweaver MX 2004 MySQL engine does not support complex date and time formatting, such as DATE_FORMAT(conferences.ConferenceStart,'%m/%d/%Y'), which converts the string returned by ConferenceStart field to a month/day/year format. To get around this issue, we'll insert a pseudo recordset and then replace the code that Dreamweaver writes a bit later.

To prepare for this step, right-click (Control-click) on the Recipes > ConferenceRoom > SQL > Meeting Conflict - Fake PHP SQL Statement snippet and select the Copy Snippet command.

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

2. In the advanced Recordset dialog, enter an appropriate name for the recordset.

   Enter Conferences in the Recordset field.

3. Choose the desired data source.

   Select Recipes from the Connections list.

4. Enter the proper SQL statement:

   In the SQL area, press Ctrl-V (Command-V) to paste the code copied from the snippet.

   

    SELECT conferences.ConferenceID, rooms.RoomName, rooms.RoomID, rooms.RoomDescription, conferences.ConferenceName,  conferences.ConferenceDescription, conferences.ConferenceStart, conferences.ConferenceDuration, conferences.ConferenceBy, conferences.ConferenceStart AS StartTime,  conferences.ConferenceStart AS StartDate, conferences.ConferenceDuration AS ConferenceEnd FROM conferences, rooms WHERE conferences.ConferenceRoom = rooms.RoomID AND conferences.ConferenceID = 'IDParam' 

5. In the Variables section, choose Add (+) and enter the following details in the Add Parameter dialog:

   

6. Click OK to close the dialog and insert the recordset.

7. Save your page.

This temporary recordset will enable us to complete the next step in the recipe binding the data to the page after which we will replace the temporary code with the actual code.

Step 3: Data Binding Process

Data binding for the meeting_conflict page is very straightforward. After the recordset has been defined, all that remains to complete that operation is to put seven dynamic text elements in place.

Note

PHP users need to complete one additional action after the data-binding step is done.

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

2. Place the data source fields onto the page in their respective areas:

   Drag ConferenceName to the cell next to the Meeting Name label.

   Drag ConferenceBy to the cell next to the Meeting Owner label.

   Drag ConferenceDescription to the cell next to the Meeting Description label.

   Drag StartDate to the cell next to the Meeting Date label.

   Drag StartTime to the cell next to the Start Time label.

   Drag ConferenceDuration to the cell next to the Duration label.

   Drag RoomName to the cell next to the Conference Room label.

3. Save the page when you're done.

To test this page, browse to meeting_request and enter information for a new meeting that conflicts with an existing meeting. Remember, the room as well as the date and time must conflict to display the meeting_conflict page [r3-6].

Figure r3-6.

There's one more chore for PHP users before this page is ready. With the data fields properly placed on the page, we can substitute the pseudo-code with the actual code for the Conferences recordset.

1. From the Server Behaviors panel, select the Conferences recordset.

2. In Code view, select and cut the unneeded section of the recordset:

    $query_Conferences = sprintf("SELECT conferences.ConferenceID, rooms.RoomName, rooms.RoomID, rooms.RoomDescription, conferences.ConferenceName, conferences.ConferenceDescription, conferences.ConferenceStart,  conferences.ConferenceDuration, conferences.ConferenceBy, conferences.ConferenceStart AS StartTime, conferences.ConferenceStart AS StartDate, conferences.ConferenceDuration AS ConferenceEnd FROM conferences, rooms WHERE conferences.ConferenceRoom = rooms.RoomID AND conferences.ConferenceID = '%s'", $IDParam_Conferences); 

   Although the code is quite long, if you have Automatic wrapping turned off in Preferences, it will appear as a single line.

3. Insert the replacement code:

   From the Snippets panel, insert the Recipes > ConferenceRoom > SQL > Meeting Conflict - Real PHP SQL Statement snippet.

    $query_Conferences = "SELECT conferences.ConferenceID, rooms.RoomName, rooms.RoomID, rooms.RoomDescription, conferences.ConferenceName, conferences.ConferenceDescription, conferences.ConferenceStart, TIME_FORMAT(conferences.ConferenceDuration,'%k:%i') AS ConferenceDuration, conferences.ConferenceBy, DATE_FORMAT(conferences.ConferenceStart,'%k:%i %p') AS StartTime, DATE_FORMAT(conferences.ConferenceStart,'%c/%d/%Y') AS StartDate, (INTERVAL HOUR(conferences.ConferenceDuration) HOUR + INTERVAL MINUTE(conferences.ConferenceDuration) MINUTE + conferences.ConferenceStart) AS ConferenceEnd FROM conferences, rooms WHERE conferences.ConferenceRoom = rooms.RoomID AND conferences.ConferenceID = " . $IDParam_Conferences; 

4. Save your page.

The PHP page is now ready for testing.