End User Recipe: Mailing List Results Page

After the user selects his criteria on the search page, a list of resulting names appears on the Mailing List page. The Mailing List page allows the user to confirm the results and, if everything is as expected, forward the list to the Send Mail page for emailing. Alternatively, the user can return to the Search Users page to modify the search.

Note

This application does not include special handling in the event the search criteria returns no results. You might consider using error handling as described in earlier recipes, such as Recipe 2, "Employee Lookup."

The page is fairly straightforward and consists primarily of a table to hold the recordset data. A Repeat Region server behavior is used to display all the returned records. The recordset is constructed from a somewhat complex SQL statement based on the various session variables constructed in the Search Users page.

Step 1: Implement Mailing List Design

Here's a straightforward page in which a table-based layout for holding repeating rows of data is ideal:

1.	Create a page for the Mailing List recipe, either from a template or from the File > New dialog. In the MailMerge folder, locate the folder for your server model and open the mailing_list page there.
2.	In the content area of your page, create a header for the page and an area to hold both the returned search results. From the Snippets panel, drag the Recipes > MailMerge > Wireframes > Mailing List - Wireframe snippet into the Content editable region.
3.	Add labels and table cells to hold the mailing list information (name, email address, and access group) and links to the pages for sending email and searching again. Place your cursor in the row below the words MAILING LIST and insert the Recipes > MailMerge > ContentTables > Mailing List - Content Table snippet [r7-3]. r7-3.
4.	Save the page.

Step 2: Add Database Components

You've seen how the search criteria are placed into session variables for easy access in the Search Users page. Now we're ready to use those session variables to build a recordset of search results.

The SQL statement used in the recordset combines two tablesUsers and AccessGroupsto accommodate searches either by individual or group. The session variables are incorporated into the SQL statement in different ways, depending on the server model. ASP and PHP users must establish SQL variables for each of the session variables involved; ColdFusion users can access the session variables directly. However, in all versions, the SQL statement is constructed so that it filters results based on information entered in either of the search forms or both.

For ASP

Note

To accommodate the different dialogs for the various server models, these steps are presented for specific servers here and when necessary throughout this recipe.

	If you're following our recipe, before you begin this step, copy the SQL code from the provided snippet by first placing your cursor in any text string in Design view. Right-click (Ctrl-click) Recipes > MailMerge > SQL > Search Results ASP and select Copy Snippet from the context menu.

1.	From the Bindings panel, choose Add (+) and select Recordset (Query) from the list.
2.	In the Recordset dialog, choose Advanced.
3.	Enter an appropriate name for the recordset. Enter SearchResults in the Name field.
4.	Select the data source. Choose Recipes from the Connections list.
5.	In the SQL area, enter the following code: [View full width] SELECT * FROM Users Inner Join AccessGroups ON Users.UserAccess = AccessGroups.AccessGroupID WHERE UserFirstName LIKE 'FirstParam%' AND UserLastName LIKE 'LastParam%' AND UserEmail LIKE 'EmailParam%' OR UserAccess = AccessParam
6.	Paste the copied code in the SQL area by pressing Ctrl-V (Command-V).

Now we'll add four separate variables, one for each of the session variables.

1.	In the Variables section, choose Add (+) to create a new variable.
2.	Enter the name for the variable. In the Name column, enter FirstParam.
3.	Enter a default value for the variable. In the Default Value column, enter %. The percent sign acts as a wildcard, allowing matches with any value if no other value is available. Wildcards are used for three of the four parameters in this recordset to match the three form fields.
4.	Enter a run-time value for the variable. In the Run-time Value column, enter Session("Search_First"). In the Variables section, choose Add (+) to create a new variable.
5.	Enter the name for the variable. In the Name column, enter LastParam.
6.	Enter a default value for the variable. In the Default Value column, enter %.
7.	Enter a run-time value for the variable. In the Run-time Value column, enter Session("Search_Last"). In the Variables section, choose Add (+) to create a new variable.
8.	Enter the name for the variable. In the Name column, enter EmailParam.
9.	Enter a default value for the variable. In the Default Value column, enter %.
10.	Enter a run-time value for the variable. In the Run-time Value column, enter Session("Search_Email"). In the Variables section, choose Add (+) to create a new variable.
11.	Enter the name for the variable. In the Name column, enter AccessParam.
12.	Enter a default value for the variable. In the Default Value column, enter 0.
13.	Enter a run-time value for the variable. In the Run-time Value column, enter Session("Search_Access").
14.	Click OK to close the dialog and insert the recordset.

For ColdFusion

	If you're following our recipe, before you begin this step, copy the SQL code from the provided snippet by first placing your cursor in any text string in Design view. Right-click (Ctrl-click) Recipes > MailMerge > SQL > Search Results CFML and select Copy Snippet from the context menu.

1.	From the Bindings panel, choose Add (+) and select Recordset (Query).
2.	Switch to the advanced view of the dialog and enter an appropriate name for the recordset. Enter SearchResults in the Name field.
3.	Choose your data source. Select Recipes from the Data Source list.
4.	Enter a username and password if necessary.
5.	In the SQL area, enter the following code: [View full width] SELECT * FROM Users Inner Join AccessGroups ON Users.UserAccess = AccessGroups.AccessGroupID WHERE UserFirstName LIKE '#Session.Search_First#%' AND UserLastName LIKE '#Session .Search_Last#%' AND UserEmail LIKE '#Session.Search_Email#%' OR UserAccess = #Session .Search_Access# Paste the copied code into the SQL area by pressing Ctrl-V (Command-V). ColdFusion requires that `<cfparam>` statements be created for each of the parameters used in this recordset, even though all but one can have an empty default value.
6.	In the parameter area, choose Add (+) to add a new parameter; when the Parameter dialog box opens, choose Session.Search_First from the list, leave the Default field blank, and click OK.
7.	In the parameter area, choose Add (+) to add a new parameter; when the Parameter dialog box opens, choose Session.Search_Last from the list, leave the Default field blank, and click OK.
8.	In the parameter area, choose Add (+) to add a new parameter; when the Parameter dialog box opens, choose Session.Search_Email from the list, leave the Default field blank, and click OK.
9.	In the parameter area, choose Add (+) to add a new parameter; when the Parameter dialog box opens, choose Session.Search_First from the list, enter 0 in the Default field, and click OK.
10.	Verify your SQL in the Recordset dialog and click OK to close that dialog.

For PHP

	If you're following our recipe, before you begin this step, copy the SQL code from the provided snippet by first placing your cursor in any text string in Design view. Right-click (Ctrl-click) Recipes > MailMerge > SQL > Search Results PHP and select Copy Snippet from the context menu.

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

In the Recordset dialog, choose Advanced.

Enter an appropriate name for the recordset.

Enter SearchResults in the Name field.

Select the data source.

Choose Recipes from the Connections list.

In the SQL area, enter the following code:

[View full width]
SELECT * FROM users Inner Join accessgroups ON users .UserAccess = accessgroups.AccessGroupID WHERE (UserFirstName LIKE 'FirstParam%' AND  UserLastName LIKE 'LastParam%' AND UserEmail LIKE  'EmailParam%') OR (UserAccess = AccessParam AND  AccessParam<>0)

Paste the copied code in the SQL area by pressing Ctrl-V (Command-V).

Now we'll add four separate variables, one for each of the session variables.

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

`Name:`	`FirstParam`
`Default Value:`	`%`
`Run-time Value:`	`$_SESSION['Search_First']`

The percent sign acts as a wildcard, allowing matches with any value if no other value is available. Wildcards are used for all the parameters in this recordset that correspond to the text fields in the search form.

Click OK to close the Add Parameter dialog, and then choose Add (+) and enter the following details for the second variable:

`Name:`	`LastParam`
`Default Value:`	`%`
`Run-time Value:`	`$_SESSION['Search_Last']`

Click OK to close the Add Parameter dialog, and then choose Add (+) and enter the following details for the third variable:

`Name:`	`EmailParam`
`Default Value:`	`%`
`Run-time Value:`	`$_SESSION['Search_Email']`

Click OK to close the Add Parameter dialog, and then choose Add (+) and enter the following details for the last variable:

`Name:`	`AccessParam`
`Default Value:`	`0`
`Run-time Value:`	`$_SESSION['Search_Access']`

Click OK once to close the Add Parameter dialog, and then again to close Recordset dialog.

To set up the use of session variables properly, you'll need to add a line of code to the top of the page.

In Code view, place your cursor after the connection include statement and insert this code:

<?php session_start(); ?>

Be sure to save your page.

Step 3: Data Binding Process

With our recordset built, we're ready to bind the data fields to specific areas of the page. In this page, four data fields are used, representing the email recipient's first name, last name, email address, and access group.

From the Bindings panel, expand the SearchResults recordset.

Place the data source fields onto the page:

	Drag the UserFirstName field under the Name column, and then press Ctrl-Shiftspace (Command-Shift-space) to insert a nonbreaking space after the dynamic data.
	Drag the UserLastName field after the UserFirstName field and add a nonbreaking space (Ctrl-Shift-space or Command-Shift-space) between the two dynamic text elements.
	Drag the UserEmail field under the Email Address column.
	Drag the AccessGroupName field under the Access Group column.

Save your page [r7-4].

r7-4.

Step 4: Add Links

After the user has checked the search results, he has two possible options: accept the results or perform the search again. The Mailing List page provides links for both possibilities. If the results are accepted, the user selects the link to the Send Mail page to process the email. If the user decides to run the search again, a link to the Search Users page is available.

The link to the Send Mail page requires a bit of special attention. Because we want the link to be available independent of the current page, we need to pass a parameter that indicates that the recordset culled from the search results should be used. Without the parameter, the Send Mail page will open and allow individual email addresses to be entered by hand.

Let's create the link to the Send Mail page first:

1.	Select the text or image you want to link to the page that actually sends the email. Select the Send Mail to Group text.
2.	In the Property inspector, select the folder icon next to the Link field to open the Select File dialog.
3.	In the Select File dialog, select Parameters to begin the process of attaching a name/value pair to the URL.
4.	In the Parameters dialog, choose Add (+) and enter the name of the variable you want to pass. Enter UseRS in the Name column.
5.	Insert the value you want to attach to the link. Enter 1 in the Value column.
6.	Click OK to close the Parameters dialog.
7.	Select the file you want to link to. Select send_mail.asp. Select send_mail.asp. Select send_mail.cfm. Select send_mail.php.
8.	Click OK to close the Select File dialog.

Now let's create a simple link to the Search Users page. This link effectively replicates the browser's Back button action, but including it indicates the second of the two possible actions the user can choose.

1.	Select the text or image you want to serve as a link to the search page. Select the Search Again text.
2.	In the Property inspector, click the folder icon to open the Select File dialog.
3.	Choose the search page for your server model. Select search_users.asp. Select search_users.asp. Select search_users.cfm. Select search_users.php.

Step 5: Add Repeat Region

Our final step in the Mailing List recipe is to wrap a Repeat Region server behavior around the embedded data fields.

1.	Select any of the dynamic data fields inserted into the mailing list content area.
2.	From the Tag Selector, choose the table row tag. Select the <tr> tag from the Tag Selector.
3.	From the Server Behaviors panel, choose Add (+) and select Repeat Region.
4.	In the Repeat Region dialog, choose the recordset. Choose SearchResults from the Recordset list.
5.	Set the Show option to display the number of records you would like. Choose Show All Records.
6.	Click OK when you're done and save your page.