III. CFMail for Newsletters By J. Eric Wilkison Web Development Manager - Winterlink Inc. ericw@winterlink.net

============== I. Comments:

We have been getting geared up for our new online Journal. If you have suggestions for the Online Journal.

If you have any suggestions for articles please send them to us. If you would like to write for cftipsplus.com send us an email to:

admin@cftipsplus.com

IF YOU WANT TO BE AN AUTHOR SEND IN YOUR COLDFUSION TIPS.

Remember this is a great way to get your name know in the ColdFusion Community.

=============================================================== If you have any suggestions please email me at cftips@nsnd.com. ===============================================================

=============================================================== II. ColdFusion in Context: Filtering and Selecting Records By R. Martin Ladner martin.ladner@knology.net =============================================================== Most users want to enter just the beginning of a key and let the application bring up matches, but the syntax for the "like" function - the method used in most examples - is not portable across database engines. We'll look at a portable method for this kind of search.

When generating the resulting list, it would be nice to let the user pick the desired item in a single step without having to click on the item and then find the select button. We'll explore a single-click method.

Finally, when many options are available, it's useful to be able to show which items have already been selected and let the user unselect some and select others as desired.

Get Items Beginning with a Search String as mentioned earlier, the implementation of SQL's "like" function is not portable across database engines. Oracle's "like" function for partial matches uses a percent sign (where key like 'abc%'); Microsoft Access' "like" function for partial matches uses an asterisk (where key like 'abc*').

However, if you want to find keys whose beginning matches a user-supplied string, you can do it without using the "like" function. The following example works just fine as long as you don't have any keys with more than five consecutive "Z"s in them. (You could screen for this on entry or just use more "Z"s.)

<!--- Get Vendors beginning with the search string ---> <cfif isDefined("URL.Vendor") and ("#URL.Vendor#" is not 0)> <cfset attributes.Vendor="#URL.Vendor#"> <cfelse> <cfset attributes.Vendor=""> </cfif> <cfquery name="listVendor" datasource="myDB"> select * from Vendor where Vendor >= '#attributes.Vendor#' and Vendor <= '#attributes.Vendor#zzzzz' </cfquery>

Do One-step Selection from a List Radio buttons would seem to be the natural method to employ when the user has to pick one option from a list, but if the list fills a screen or two, it's awkward to maneuver the mouse cursor to the small radio button and then scroll to the top or bottom of the logical page to find and then click the submit button.

However, suppose you made every selection into a submit button instead of a radio button. Then the user could just click the submit button whose label is the desired key to pick the item with one click instead of two.

<!--- Pick a Vendor from the resulting list ---> <table border="1"> <cfform name="listVendor" action="frmVendor.cfm" method="post"> <tr> <td>PICK</td> <td>VENDOR</td><td>Email</td></tr>

<td><input type="submit" name="VendorID" value=0></td> <td> (none of these)</td> <td> - </td></tr>

<cfoutput query="listVendor"> <tr><td><input type="submit" name="VendorID" value="#round(VendorID)#"></td><td>#Vendor#</td><td>#Email#</td></tr> </cfoutput> </cfform> </table>

If this looks too crude, you could put the Vendor name into the submit button instead of the VendorID (assuming the Vendor name is unique).

The previous scripts could go in the same file; I named mine frmListVendor.cfm. They would feed one of two queries; the choice of query depends on whether the user picked a useful value. If a Vendor is selected, the query brings back the rest of the row. If a Vendor is not selected, the query brings back an empty row instead.

<cfif isDefined("URL.VendorID") and ("#URL.VendorID#" is not 0)> <cfset attributes.VendorID="#URL.VendorID#"> <cfelse> <cfif isDefined ("form.VendorID") and ("#form.VendorID#" is not 0)> <cfset attributes.VendorID="#form.VendorID#"> <cfelse> <cfset attributes.VendorID=0> </cfif> </cfif> <cfif attributes.VendorID is not 0> <cfquery name="qryGetVendor" datasource="myDB"> select * from Vendor where VendorID = #attributes.VendorID# </cfquery> <cfelse> <cfquery name="qryGetVendor" datasource="myDB"> select * from VendorAdd </cfquery> </cfif>

Make a Pick List Showing Current Selections A query that shows all possible options is not the same as a query showing currently selected options, but to let the user unselect some options and select others as desired, both types of queries need to be used together somehow in the same pick list. One way to do this is to query for selected items (product lines in this case), store the results in a list structure, and check the list while displaying each item in the larger query that shows possible items.

Here's an example of building a list structure from a query. "ListAppend" essentially pastes new values to an existing string and places a comma between the values. In this case, the values are LineIDs already associated with this Vendor.

<cfquery name="getLineVendor" datasource="myDB"> select * from LineVendor where VendorID = #attributes.VendorID# </cfquery>

<cfset newLineIDList=''> <cfloop query="getLineVendor"> <cfset newLineIDList=ListAppend(#newLineIDList#, #LineID#)> </cfloop>

Here's the query of possible product lines; the group of lines already associated with the Vendor is a subset of this list. The user will pick from the larger list knowing which lines are already selected (associated with the Vendor) and can select and de-select lines at will.

<cfquery name="getLine" datasource="myDB"> select * from Line </cfquery>

Here's where the query of all Lines and the query of the Lines associated with this Vendor come together. The following code puts a checkbox next to every Line and checks the ones already associated with this Vendor.

It's important to use "listFindNoCase" instead of "contains" or "listContains"; because; the "contains" functions will match pieces of keys. If you use "contains" functions, then if 18 is selected (for example), 1 and 8 will also become checked and will be associated with the Vendor user submits the form; that's not what you want. Use "listFindNoCase" so only complete matches will count.

Finally, the Exit button uses JavaScript to break out of the endless loop presented by this form and the page it feeds. It will error out if you don't have an index.cfm in this directory.

<cfform name="frmVendor" action="chgVendor.cfm" method="post"> <table> <tr><td>Vendor:</td> <td> <cfinput type="text" name="Vendor" value="#qryGetVendor.Vendor#"> </td> </tr> <tr><td>E-mail:</td> <td> <cfinput type="text" name="Email" value="#qryGetVendor.Email#"> </td> </tr>

<cfoutput query="getLine"> <cfif listFindNoCase(#newLineIDList#, #LineID#)> <cfset qual="CHECKED"> <cfelse> <cfset qual=""> </cfif> <tr><td> <input type="checkbox" name="newLineIDList" value="#round(LineID)#" #qual#></td>

<td>--#Line#--</td> </cfoutput> <cfoutput> <input type="hidden" name="VendorID" value="#attributes.VendorID#"> <tr><td></td> <td> <input type="button" name="dothis" value="Exit" onClick="document.location='index.cfm'; return false;">

<input type="reset" name="" value="Reset">

<input type="submit" name="formaction" value="Submit"></td> <td></td></tr> </cfoutput> </table> </cfform>

All of the scripts presented so far (except for the first two which went into frmListVendor.cfm) can fit into a single file; I called mine frmVendor.cfm. It gives you a list of product line IDs, some of which are associated with the Vendor, and passes along the VendorID to be used. The user can check and uncheck a few options and feed the result to the following set of queries.

These queries update or insert the Vendor, delete all product lines for this Vendor, and then add back all selected product lines for the Vendor. I put them all in a single file: chgVendor.cfm. This file calls the form that called it - frmVendor.cfm - and the cycle continues until the user presses the Exit button.

<cfif isDefined("form.VendorID") and ("#form.VendorID#" is not 0)> <!--- Update ---> <cfquery name="chgVendor" datasource="myDB"> update Vendor set <!--- (leave VendorID alone) ---> Vendor = '#trim(form.Vendor)#', Email = '#trim(form.Email)#' where VendorID = #form.VendorID# </cfquery> <cfset ID1="#form.VendorID#"> <cfelse>

<!--- Make next VendorID and add vendor with it --->

<cfquery name="getMax1" datasource="myDB"> select max(VendorID) as Max1 from Vendor </cfquery> <cfif len(getMax1.Max1)> <cfset ID1=getMax1.Max1+1> <cfelse> <cfset ID1=1> </cfif> <cfquery name="addVendor" datasource="myDB"> insert into Vendor (VendorID, Vendor, Email) values (<cfoutput>#ID1#, '#form.Vendor#', '#form.Email#'</cfoutput>) </cfquery> </cfif>

<cfif isDefined("form.newLineIDList")> <!--- Delete all Product Lines for this Vendor --->

<cfquery name="delLineVendor" datasource="myDB"> delete * from LineVendor where VendorID = #ID1# </cfquery> <!--- Insert Selected Lines for this Vendor --->

<cfloop index="ID2" list="#form.newLineIDList#"> <cfquery name="addLineVendor" datasource="myDB"> insert into LineVendor (LineID, VendorID) values (<cfoutput>#ID2#, #ID1#</cfoutput>) </cfquery> </cfloop> </cfif>

<cfset ref="frmVendor.cfm?VendorID=#ID1#"> <cflocation URL="#ref#">

Summary Although it took a good bit of supporting code to round out the demonstration, the focus of this session was to show that one can search in a portable fashion for keys that begin with a given search string, build a one-click list to pick the desired item (or none at all), and readily show in a list of values which ones are currently selected.

The techniques for doing these tasks ranged from using inequalities in SQL to using multiple submit buttons in a form to packing a query into a list for later ready reference.

=Marty= martin.ladner@knology.net

=============================================================== III. CFMail for Newsletters By J. Eric Wilkison Web Development Manager - Winterlink Inc. ericw@winterlink.net

===============================================================

E-mail has become the killer application of the web and Cold Fusion has given us the power to build dynamic content and push that content to anyone. This article will show you how to build a simple script using CFMail to send a newsletter to thousands of people at once.

Collect the Addresses ----------------------------------------------- The first step in implementing a newsletter for your site is to build a form to collect e-mail addresses. The simplest solution is just a text box that the user enters his or her e-mail address in and submits. This address should then be stored in your back end database. I'm not going to go into the code here because it's just a simple form and database insert. You can also collect other data at the same time such as their name, address, phone numbers etc. Or you can let them select what newsletters they would like to receive. Lets just say you need to collect a list of e-mail address in a database. You can figure out how to best do that for your site.

Building the Message -----------------------------------------------

Now for the fun part, sending the newsletter. First do a database query to pull all the e-mail addresses for the recipients:

<cfquery name="MailList" datasource="MyDatabase"> SELECT email FROM MailList </cfquery>

Once you have the query with all the recipients use CFMail to send the newsletter:

<cfmail from="newsletter@X.com" to="#email#" subject="December Newsletter from X.com" query="MailList" Server="127.0.0.1" > All the content for the newsletter would go here </cfmail>

This code will send E-Mail to every recipient that was found in the MailList table of MyDatabase. The 'from' field will be set to "newsletter@X.com" and the 'subject' of the message will be set to "December Newsletter from X.com". The server setting will need to be changed to a valid SMTP mail server you have access to relay messages with, or if your Cold Fusion server is configure properly you can omit this setting all together.

One thing to note is the lack on indentation for the body content of the cfmail tag. Most Cold Fusion programmers use indentation to make their code easier to read. If you were to indent the content then the messaged sent out would have every new line stat with an indentation. This can quickly make for a very ugly message.

Building a Dynamic Message ----------------------------------------------------

The above example will send the exact same message to everyone in the database. But, adding a little personalization to the newsletter is easy:

<cfquery name="MailList" datasource="MyDatabase"> SELECT email, firstName, lastName FROM MailList WHERE MonthlyNews IS 1 </cfquery>

<cfmail from="newsletter@X.com" to="#email#" subject="X.com Newsletter for #firstName# #lastName#" query="MailList" Server="127.0.0.1" > Hello #firstName#,

All the content for the newsletter would go here </cfmail>

In this example the message generated will have the users full name in the subject and have the user name in the message body. Also by adding the WHERE statement in the SQL query you can send your newsletter to a subset of the recipients based on the newsletters they may have signed up for.

Some Things to Think About -----------------------------------------------

When building a mass mailer like this you should be aware of a few things. First, make sure your Internet connection can handle the added bandwidth utilization of sending these messages. If you were to send a message that was 4k in size (about the length of this article) and send it to 5000 e-mail addresses you would be generating about 20 Megabytes of messages that would need to be sent. That much mail can flood a slow Internet connection very easily.

Make very sure that the messages that you send out are to people who want them. Spam mail is something that everyone hates but has to live with unfortunately. The last thing you should be doing to contributing to the problem. Always make sure the e-mail addresses in your list are from people who have signed up for your newsletter.

Most importantly make sure the users you are sending the newsletters to have an easy way to remove themselves from the list. Every message you send should contain clear easy to follow instructions on how this can be done. Usually the best way is to have the user send a message to a specific mail account with "Remove" or "Unsubscribe" in the subject. My next article will be on how to build a cold fusion script to automatically check a pop mail account for unsubscribe messages and remove them from the list.

Eric Wilkison ericw@winterlink.net ==============

============== Publisher and Creator: Nathan Stanford, admin@cftipsplus.com C.F. Concepts, Inc.

Web Developers: Dain Anderson, dain_anderson@yahoo.com Hal Helms, hal.helms@TeamAllaire.com Ian Smith, ians@inker.com Kevin Schmidt, schmidt@pwb.com Scott Knaub, scott@web-mgt.com Marty Ladner, martin.ladner@knology.net Eric Wilkison ericw@winterlink.net

============== Macromedia and ColdFusion are U.S. registered trademarks.

============== Copyright (c) 2000 - 2001 C.F. Concepts, Inc. CFTIPSPLUS.COM and NSND.COM Permission is granted to circulate this publication via MANUAL forwarding by email to friends provided that the text is forwarded in its entirety and no fee is charged. ===============================================================

============== To unsubscribe: unsubscribe-cftips@nsnd.com

To subscribe: subscribe-cftips@nsnd.com ==============

==^================================================================ EASY UNSUBSCRIBE click here: http://topica.com/u/?aVxjC7.aVG5kO Or send an email To: cftips-unsubscribe@topica.com This email was sent to: cftipsplus@tallylist.com

T O P I C A -- Learn More. Surf Less. Newsletters, Tips and Discussions on Topics You Choose. http://www.topica.com/partner/tag01 ==^================================================================