End User Recipe: Meeting Results

After the user has entered the search criteria on the Search Existing Meetings page, selecting the submit button executes the Meeting Results page. If no results are found, the user is redirected back to the search page, and a no-results message is displayed; otherwise, all the results are listed on the the results page. Each of the result entries includes a link to a detail page and, to handle a large number of results being returned, recordset navigation controls are also included.

As noted on the Search Existing Meetings page, the Meeting Results page handles the actual parsing of the submitted search terms as well as the no-results redirection. In addition, this page also makes sure that a valid date was entered. Consequently, there is a fair amount of hand-coding involved, all of which is included in the Recipes snippets.

Step 1: Implement Search Results Design

The results page is basically a table with a repeating region applied with room for recordset navigation controls.

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

    graphics/book.gif In the ConferenceRoomScheduler folder, locate the folder for your server model and open the meeting_results page found there.

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

    graphics/book.gif From the Snippets panel, drag the Recipes > ConferenceRoom > Wireframes > Meeting Results - Wireframe snippet into the Content editable region.

  3. Add the table area with four columns to display the meeting name, conference room, start time, and duration [r3-16].


    Figure r3-16.

    graphics/05fig16.jpg


    graphics/book.gif Place your cursor in the row below the words MEETING RESULTS and insert the Recipes > ConferenceRoom > ContentTables > Meeting Results - Content Table snippet.

  4. Save the page before continuing.

Step 2: Add Database Components

The Meeting Results page uses the same search functions found in Recipe 2, "Employee Lookup." If you've already built that application, you'll recognize the SQL statement's dynamically generated WHERE clause used within this page's recordset. In addition, ASP users will also need to include a statement that initializes the variable, WhereParam.

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

  2. Switch to advanced view, if necessary, and enter an appropriate name for the recordset.

    graphics/book.gif Enter Conferences in the Name field.

  3. Choose the connection for the data source.

    graphics/book.gif Select Recipes from the Connections list.

  4. In the SQL area, enter the following code:

     
     SELECT * FROM ConferenceDisplay WhereParam ORDER BY ConferenceName 
     
  5. From the Variable area, choose Add (+) to add a new variable.

  6. In the Name column, enter WhereParam.

  7. In the Default Value column, enter WHERE ConferenceID <> 0.

  8. In the Run-time Value column, enter WhereParam.

  9. Click OK to close the dialog.

The WhereParam variable is a server-side variable with a default value that returns all records in the Conferences table. When any search criteria are submitted, the WhereParam variable will be built dynamically. To work properly, WhereParam, like all variables in ASP, must be initialized. The following step is necessary for ASP developers only.

  1. In Code view, position the cursor near the top of the page on a new line right after the <!--include> tag.

  2. Enter the following code:

    graphics/book.gif From the Snippets panel, open the Recipes > ConferenceRoom > Custom Code folder for your server model and insert the WhereParam Init snippet.

    graphics/vb.gif

     <% WhereParam = "" %> 

    graphics/js.gif

     <% var WhereParam = ""; %> 

For ColdFusion

The ColdFusion recordset is initially declared without a WHERE clause to enable easy placement of data source fields during the data binding phase. After all the dynamic elements are in place, we'll add code to generate the WHERE clause on the fly.

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

  2. In simple view of the Recordset dialog, enter an appropriate name for the recordset.

    graphics/book.gif Enter Conferences in the Name field.

  3. Select the desired data source.

    graphics/book.gif Choose Recipes from the Data Source list.

  4. Enter the user name and password for the data source, if needed.

  5. Select the table or view to use.

    graphics/book.gif Choose ConferenceDisplay from the Table menu.

  6. Leave the Columns, Filter, and Sort options at their default settings and click OK to close the dialog.

For PHP

graphics/book.gif

Prepare for this step by using the Copy Snippet command to copy the Recipes > ConferenceRoom > SQL > Search Results - PHP SQL Statement snippet.


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

  2. Switch to advanced view, if necessary, and enter an appropriate name for the recordset.

    graphics/book.gif Enter Conferences in the Name field.

  3. Choose the connection for the data source.

    graphics/book.gif Select Recipes from the Connections list.

  4. In the SQL area, enter the following code:

    graphics/book.gif Paste the copied snippet in the SQL area by pressing Ctrl-V (Command-V).

     

    [View full width]

    SELECT ConferenceID, ConferenceRoom, ConferenceName, ConferenceDescription, UNIX_TIMESTAMP graphics/ccc.gif(ConferenceStart) AS ConferenceStart, CONCAT(HOUR(ConferenceDuration),':', IF(MINUTE graphics/ccc.gif(ConferenceDuration)<10,CONCAT('0',MINUTE (ConferenceDuration)),MINUTE graphics/ccc.gif(ConferenceDuration))) AS ConferenceDuration, ConferenceBy, RoomID, RoomName, RoomDescription FROM conferences, rooms WHERE ConferenceRoom = RoomID WhereParam
     
  5. From the Variable area, choose Add (+) to add a new variable.

  6. In the Name column, enter WhereParam.

  7. In the Default Value column, enter AND ConferenceID != 0.

  8. In the Run-time Value column, enter $WhereParam and click OK to close the dialog.

  9. Save your page.

Step 3: Data Binding Process

Only four text elements need to be bound to the data source for the Meeting Results page, and all can be dragged directly from the Bindings panel. After the dynamic text elements are in position, we can create a link to a details page.

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

  2. Switch to Design mode and place the needed data source fields into position on the page:

    graphics/book.gif Drag ConferenceName to the row under the Meeting Name column.

    Drag RoomName to the row under the Conference Room column.

    ASP and ColdFusion users: Drag ConferenceStart to the row under the Start Time column.

    graphics/php.gif In PHP, this column requires formatting, so rather than perform this step, click inside this column and then in the Snippets panel, go to Recipes > ConferenceRoom > CustomCode_PHP and insert the Search Results - Display Formatted ConferenceStart snippet.

    graphics/book.gif Drag ConferenceDuration to the row under the Duration column.

  3. PHP users should enter code specifically to format ConferenceStart properly by inserting the following code in the row under the Start Time column:

    graphics/book.gif From the Snippets panel, insert the Recipes > ConferenceRoom > CustomCode_PHP > Formatted ConferenceStart Column snippet.

    graphics/php.gif

     <?php echo date("n/d/Y",$row_Conferences['ConferenceStart']); ?> 

  4. Save your page.

Now we're ready to add a link to a details page. In this case, we'll add it to the name of the meeting.

  1. Select the dynamic text you want to use as a link to a detail page.

    graphics/book.gif Choose the Conferences.ConferenceName dynamic text element.

  2. From the Property inspector, enter the path to the details page.

    graphics/book.gif Choose the Browse for File icon and select the meetings_detail page.

  3. Add a parameter to the URL passing the meeting ID.

    graphics/book.gif From the Select File dialog, click the Parameters button and, in the Parameters dialog, enter ID under Value. Then, place the cursor in the Value field, select the lightning icon to open the Dynamic Data dialog, and choose ConferenceID. Click OK three times to close all the open dialogs.

When complete, each of the columns should have dynamic text associated with them, and the Meeting name should be linked to the meeting details page [r3-17].


Figure r3-17.

graphics/05fig17.jpg


Step 4: Add Repeat Region

Enter into Live Data view at this stage, and you'll see just one entry. We'll use the Dreamweaver Repeat Region server behavior to display a number of entries.

  1. Select any of the dynamic text elements on the page.

  2. From the Tag Selector, select the <tr> tag to the left of the current selection.

  3. From the Server Behaviors panel, choose Add (+) and select Repeat Region.

  4. In the Repeat Region dialog, make sure the Conferences recordset is displayed.

  5. Choose the Show option and enter 10 in the numeric field.

  6. Click OK to close the dialog.

Now when you enter into Live Data view (in truth, you didn't really need to exit from it to insert the Repeat Region) you should see the first ten records. Next we'll add recordset navigation to handle larger recordsets [r3-18].


Figure r3-18.

graphics/05fig18.gif


Step 5: Add Application Objects

Dreamweaver includes two standard application objects Recordset Navigation Bar and Recordset Navigation Status that are used to navigate through records. Let's add the navigation bar first.

  1. Place your cursor in the Meeting Results page where you want the navigation links to appear.

    graphics/book.gif Place the cursor in the row above the Start Time label. The <td> cell is styled to align text in the center, so the cursor will not appear directly above the label. If you are unsure of your placement, press F6 to enter Expanded Table mode; press F6 again to return to Standard mode.

  2. Choose the Recordset Navigation Bar object from the Recordset Paging menu button on the Insert bar's Application category.

    Alternatively, you could select Insert > Application Objects > Recordset Paging > Recordset Navigation Bar.

  3. In the Recordset Navigation Bar dialog, select the Conferences recordset.

  4. Leave the Display Using option set to Text and click OK.

The other Dreamweaver application object, Recordset Navigation Status, provides feedback about the number of total records and which records are currently being displayed.

  1. Place your cursor in the Meeting Results page where you want the navigation status to be displayed.

    graphics/book.gif Place the cursor in the row above the Meeting Name cell.

  2. Choose the Recordset Navigation Status object from the Display Record Count menu of the Application category of the Insert bar.

    Alternatively, you could select Insert > Application Objects > Display Record Count > Recordset Navigation Status.

  3. In the Recordset Navigation Status dialog, select the Conferences recordset and click OK.

Preview the page in a browser to get the full effect of the recordset navigation controls [r3-19].


Figure r3-19.

graphics/05fig19.jpg


Step 6: Add Server-Side Date Validation

Although the date itself was entered on the Search Existing Meeting page, the validation is being handled on this page. Why? Rather than have the search page submit to itself and then, if the date was judged proper, redirect to the results page, all the server-side functions are centralized on one page. If an error is found, the user is redirected to the search page and an error message already encoded on the search page is displayed.

Note

A custom function is needed for those working with ASP-JavaScript and PHP, in addition to another code snippet used for all server models.


  1. In Code view, place the cursor at the top of the page.

    ASP users should position the cursor after the code block in which the WhereParam variable is initialized. PHP users place the cursor after the <?php require_once statement.

  2. ASP-JS and PHP users only: Insert the following code:

    graphics/book.gif From the Snippets panel, open the Recipes > ConferenceRoom > Custom Code folder for your server model and insert the IsADate Function snippet.

    graphics/js.gif

    [View full width]

     <% function isADate(DateString)  {   var DateRegex = /^(\d{1,2})\/(\d{1,2})\/(\d{4})$/   var theMatch = DateString.match(DateRegex);   var validDate = false;   if (theMatch)  {     var DateObj = new Date(DateString);   if ((DateObj.getMonth()+1 == parseInt(theMatch[1])) &&  graphics/ccc.gif(DateObj.getDate() == parseInt(theMatch[2])) && (DateObj graphics/ccc.gif.getFullYear() == parseInt(theMatch[3])))       validDate = true;   }   return validDate; } %> 

    graphics/php.gif

     <?php function isADate($DateString) {   $validDate = false;   if (ereg("^[0-9]{1,2}\/[0-9]{1,2}\/[0-9]{4}$",$DateString)) {     $submittedDate = explode("/",$DateString);     $month = $submittedDate[0];     $day = $submittedDate[1];     $year = $submittedDate[2];     $validDate = (checkdate($month,$day,$year))?true:false;   }   return $validDate; } ?> 

  3. Users of all server models should insert the following code:

    graphics/book.gif From the Snippets panel, open the Recipes > ConferenceRoom > Custom Code folder for your server model and insert the Meeting Request - Date Validation snippet.

    graphics/vb.gif

    [View full width]

     <% if (cStr(Request.Form("MeetingDate")) <> "")  then   if not (isDate(Request.Form("MeetingDate"))) then Response graphics/ccc.gif.Redirect("existingmeeting_request.asp?0" width="14" height="9" align="left" src="/books/2/711/1/html/2/files/ccc.gif" alt="graphics/ccc.gif">("MM_recordId") & "&badDate=true") end if%> 

    graphics/js.gif

    [View full width]

     <% if (String(Request.Form("MeetingDate"))!="undefined" && graphics/ccc.gif !isADate(String(Request.Form("MeetingDate")))) Response.Redirect("existingmeeting_request.asp?0" width="14" height="9" align="left" src="/books/2/711/1/html/2/files/ccc.gif" alt="graphics/ccc.gif">("MM_recordId")+"&badDate=true"); %> 

    graphics/cf.gif

    [View full width]

     <cfif IsDefined("FORM.MeetingDate")>   <cfif not IsDate(FORM.MeetingDate)>     <cflocation url=" existingmeeting_request.cfm?ID=#FORM graphics/ccc.gif.ConferenceID#&badDate=true">   </cfif> </cfif> 

    graphics/php.gif

    [View full width]

     <?php if ((isset($_POST['MeetingDate'])) && (!isADate graphics/ccc.gif($_POST['MeetingDate']))) {   $url = "existingmeeting_request.php?badDate=true";   header("Location: $url"); } ?> 

You can test this portion of the application by opening Search Existing Meeting in a browser and entering an improper date value. If all goes well, you won't even see the Meeting Results page.

Step 7: Hand Code Search Functionality

The final step to complete on this page is its raison d'etre the search functions. There are three phases to getting the search fully operational.

A custom function, AddFieldToSearch(), that builds the WHERE clause one search criteria field at a time is inserted. This step is not necessary for ColdFusion.

To compensate for the manner in which Dreamweaver deals with single quote characters in its ASP and PHP SQL statements, two quote handler functions are added along with calling code from within the SQL statement. Again, this is not necessary in ColdFusion.

For ASP and PHP

To make sure our helper function, AddFieldToSearch(), is available, we'll add it first.

  1. In Code view, place your cursor after the Date Validation code added in the last step.

  2. Insert the following code:

    graphics/book.gif From the Snippets panel, open the Recipes > ConferenceRoom > Custom Code folder for your server model and insert the Add Field To Search snippet.

    graphics/vb.gif

    [View full width]

     <% AddFieldToSearch(CurrentWhere,ColumnName,ValString graphics/ccc.gif,Comparison,Separator,OpenEncap,CloseEncap)    if (ValString <> "") then       if (CurrentWhere = "") then          CurrentWhere = "WHERE "       else          CurrentWhere = CurrentWhere & " " & Separator & " "       end if       CurrentWhere = CurrentWhere & ColumnName & " " & graphics/ccc.gif Comparison & " " & OpenEncap & Replace (ValString, "'", graphics/ccc.gif "''") & CloseEncap    end if    AddFieldToSearch = CurrentWhere end function %> 

    graphics/js.gif

    [View full width]

     <% function AddFieldToSearch(CurrentWhere,ColumnName,ValString graphics/ccc.gif,Comparison,Separator,OpenEncap,CloseEncap) {    if (ValString!="") {       if (CurrentWhere == "") {          CurrentWhere = "WHERE ";       }       else {          CurrentWhere += " " + Separator + " ";       }       CurrentWhere += ColumnName + " " + Comparison + " " + graphics/ccc.gif OpenEncap + ValString.replace(/'/g, "''") + CloseEncap;    }    return CurrentWhere; } %> 

    graphics/php.gif

    [View full width]

     <?php function AddFieldToSearch($CurrentWhere,$ColumnName graphics/ccc.gif,$ValString,$Comparison,$Separator,$OpenEncap,$CloseEncap)  {   if ($ValString!="")  {     if ($CurrentWhere == "")  {       $CurrentWhere = "AND ";     } else  {       $CurrentWhere.= " ".$Separator." ";     }   $CurrentWhere.= $ColumnName." ".$Comparison." ".$OpenEncap graphics/ccc.gif.$ValString.$CloseEncap;   }   return $CurrentWhere; } ?> 

The next code block calls the just-inserted function to build the WhereParam variable. Essentially, the code looks at every form field on the search page and adds its values, if any, to WhereParam so that the variable will eventually be used to filter the recordset.

  1. In Code view, place the cursor in the proper position for your server model after the AddFieldToSearchFunction() just inserted for ASP and PHP and after the Date Validation code added in the previous step for ColdFusion.

  2. Insert the following code:

    graphics/book.gif From the Snippets panel, open the Recipes > ConferenceRoom > Custom Code folder for your server model and insert the Find Meeting Search Filter snippet.

    graphics/vb.gif

    [View full width]

     <% if (cStr(Request.QueryString("FindMeeting"))<>"") then WhereParam = AddFieldToSearch(WhereParam,"ConferenceName" graphics/ccc.gif,cStr(Request.QueryString("MeetingName")),"LIKE","AND","'","%'") WhereParam = AddFieldToSearch(WhereParam,"ConferenceBy",cStr graphics/ccc.gif(Request.QueryString("MeetingOwner")),"LIKE","AND","'","%'") WhereParam = AddFieldToSearch(WhereParam,"StartDate",cStr graphics/ccc.gif(Request.QueryString("MeetingDate")),"=","AND","#","#") WhereParam = AddFieldToSearch(WhereParam,"RoomID",cStr graphics/ccc.gif(Request.QueryString("ConferenceRoom")),"=","AND","","") end if %> 

    graphics/js.gif

    [View full width]

     <% if (String(Request.QueryString("FindMeeting"))!="undefined")  {   WhereParam = AddFieldToSearch(WhereParam,"ConferenceName" graphics/ccc.gif,String(Request.QueryString("MeetingName")),"LIKE","AND","'" graphics/ccc.gif,"%'");   WhereParam = AddFieldToSearch(WhereParam,"ConferenceBy" graphics/ccc.gif,String(Request.QueryString("MeetingOwner")),"LIKE","AND","'" graphics/ccc.gif,"%'");   WhereParam = AddFieldToSearch(WhereParam,"StartDate" graphics/ccc.gif,String(Request.QueryString("MeetingDate")),"=","AND","#","#");   WhereParam = AddFieldToSearch(WhereParam,"RoomID",String graphics/ccc.gif(Request.QueryString("ConferenceRoom")),"=","AND","",""); } %> 

    graphics/php.gif

    [View full width]

     <?php if(isset($_GET['FindMeeting'])) {   $WhereParam = AddFieldToSearch($WhereParam graphics/ccc.gif,"ConferenceName", $_GET['MeetingName'],"LIKE","AND","'","%'");   $WhereParam = AddFieldToSearch($WhereParam,"ConferenceBy", graphics/ccc.gif $_GET['MeetingOwner'],"LIKE","AND","'","%'");   $WhereParam = AddFieldToSearch($WhereParam,"DATE_FORMAT graphics/ccc.gif(ConferenceStart,'%c/%d/%Y')",$_GET['MeetingDate'], "=","AND" graphics/ccc.gif,"'","'");   $WhereParam = AddFieldToSearch($WhereParam,"RoomID", graphics/ccc.gif $_GET['ConferenceRoom'],"=","AND","'","%'"); } ?> 

Next, ASP users will need to insert code to handle Dreamweaver's method of working with single quotes. To do this, we'll need to add two functions, RemoveQuotes() and ReturnQuotes(), as well as two calls to invoke the functions. The calls to the function will take place before and after the SQL statement for the Conferences recordset is actually defined. This step is not needed for PHP.

  1. Locate the code block containing the Conferences recordset by selecting it from the Server Behaviors panel and switching to Code view. Position the cursor just above that code block and below the code block in which the WhereParam variable is initialized.

  2. Insert this code:

    graphics/book.gif From the Snippets panel, open the Recipes > ConferenceRoom > Custom Code folder for your server model and insert the Quote Handler Function snippet.

    graphics/vb.gif

     <% function RemoveQuotes(theString)   RemoveQuotes = Replace(theString, "'", "|!|") end function function ReturnQuotes(theString)   ReturnQuotes = Replace(theString, "|!|", "'") end function %> 

    graphics/js.gif

     <% function RemoveQuotes(theString)  {   return theString.replace(/'/g, "|!|"); } function ReturnQuotes(theString)  {   return theString.replace(/\|!\|/g, "'"); } %> 

  3. To insert the first function call, place your cursor below the quote handler functions just inserted and make a new line.

  4. Insert the following code:

    graphics/book.gif From the Snippets panel, open the Recipes > ConferenceRooms > Custom Code folder for your server model and insert the WhereParam - Remove Quotes snippet.

    graphics/vb.gif

     <% Conferences__WhereParam = RemoveQuotes(Conferences__WhereParam) %> 

    graphics/js.gif

     <% Conferences__WhereParam = RemoveQuotes(Conferences__WhereParam); %> 

  5. To insert the second function call, place your cursor within the Dreamweaver coded SQL function, just after line that starts Conferences.Source = "SELECT... and add a new line.

  6. On the new line, insert the following code:

    graphics/book.gif From the Snippets panel, open the Recipes > ConferenceRoom > Custom Code folder for your server model and insert the Replace Quotes Function snippet.

    graphics/vb.gif

     Employees.Source = ReturnQuotes(Employees.Source) 

    graphics/js.gif

     Employees.Source = ReturnQuotes(Employees.Source); 

The advantage to this subroutine function is that the Dreamweaver visual interface still recognizes and protects the code for the hand-editing recordset definition.

For ColdFusion

ColdFusion doesn't need an external function to incrementally build up the WhereParam variable that can be handled right from within the <cfquery> tag.

Warning

Although the enhanced code does give a fair degree of power over what Dreamweaver can provide natively, there is a minor downside. After the code is inserted, you cannot examine or change the recordset through the Dreamweaver dialog doing so causes Dreamweaver to display an error and disrupts the functionality of the Bindings panel. Any additional modifications to the recordset must be made in Code view.


  1. In Code view, position the cursor within the code defining the Conferences recordset, before the closing </cfquery> tag.

    You can highlight the recordset in the code by choosing it from the Server Behaviors panel.

  2. Insert this code:

    graphics/book.gif From the Snippets panel, open the SQL folder under Recipes > ConferenceRoom and insert the Conferences RS - CFML SQL Where Statement snippet.

    graphics/cf.gif

     <cfif isDefined("URL.FindMeeting")> WHERE 1 = 1 <cfif URL.MeetingName NEQ "">    AND ConferenceName LIKE '#URL.MeetingName#%' </cfif> <cfif URL.MeetingOwner NEQ "">    AND ConferenceBy LIKE '#URL.MeetingOwner#%' </cfif> <cfif URL.StartDate NEQ "">    AND StartDate = ###URL.MeetingDate### </cfif> <cfif URL.ConferenceRoom NEQ "">    AND ConferenceRoom = #URL.ConferenceRoom# </cfif> </cfif> 

You may notice that this custom code uses an interesting SQL clause that starts with WHERE 1=1. As this condition will always be true, this clause permits us to establish a series of if statements combined with AND clauses to create search criteria under a range of circumstances. With the WHERE 1=1 clause in place, the SQL statement will execute without error regardless of the fields employed by the user.

Step 8: Handle No Matching Results

The final step for this page is needed to handle situations where no matches to the search criteria are found. A small snippet of code looks to see if the Conferences recordset is empty and, if it is, redirects the application to the Search Existing Meetings page with an error code attached.

  1. In Code view, place the cursor after the Conferences recordset.

  2. Insert the following code:

    graphics/book.gif From the Snippets panel, open the Recipes > ConferenceRooms > Custom Code folder for your server model and insert the No Results Redirect snippet.

    graphics/vb.gif

     <% if (Conferences.BOF AND Conferences.EOF) Then  Response.Redirect("existingmeetings_request.asp?noResults=true") %> 

    graphics/js.gif

    [View full width]

     <% if (Conferences.BOF && Conferences.EOF) Response.Redirect("existingmeetings_request graphics/ccc.gif.asp?noResults=true"); %> 

    graphics/cf.gif

     <cfif Conferences.RecordCount EQ 0>   <cflocation url="existingmeetings_request.cfm?noResults=true"> </cfif> 

    graphics/php.gif

     <?php if ($totalRows_Conferences == 0) {   $url = "existingmeetings_request.asp?noResults=true";   header("Location: $url");   exit(); } ?> 

  3. Be sure to save your page.

    The search capabilities of the Conference Room Scheduler are now fully operational. Test your pages by trying a variety of search parameters ones that you know should return results and ones where you would not expect to see any matches [r3-20].


    Figure r3-20.

    graphics/05fig20.jpg




Macromedia Dreamweaver MX 2004 Web Application Recipes
Macromedia Dreamweaver MX 2004 Web Application Recipes
ISBN: 0735713200
EAN: 2147483647
Year: 2003
Pages: 131

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net