Building a Newsletter App in PHP with Dreamweaver CS3
E-mail newsletters are a very popular method of maintaining an open channel of communication with your site visitors. They have the great benefit of keeping a site’s current user base up-to-date on the latest news and offerings from the organization. Moreover, they have the potential for turning site visitors into site customers or donators.
In this article, you’ll learn how to create a complete subscribe/unsubscribe application in PHP/MySQL with a standard Dreamweaver CS3 server and just one additional block of code. To use this application, a user signs up for a subscription on a site and receives a confirmation e-mail. The e-mail contains a link to unsubscribe, personalized for the user. In all, you’ll need to create five pages to achieve this functionality: * Subscribe.php: This page is comprised of a basic form that gathers an e-mail address and inserts a new record into the MySQL database. After the record is inserted the page is redirected to confirm_subscribe.php. * Confirm_subscribe.php: While the visitor sees a bit of text confirming his or her subscription on this page, a PHP function contained within e-mails the user a secondary confirmation, which contains a link to unsubscribe via the confirm_unsubscribe.php page. * Error_subscribe.php: This is an error page that displays if the e-mail attempt was not successful; it also contains a link to the subscribe.php page so another attempt can be made. * Confirm_unsubscribe.php: This page is displayed when the visitor requests to unsubscribe. It displays the user’s e-mail address and a form button to confirm the user’s wish to unsubscribe; when clicked the user’s record is deleted from the database and the visitor is redirected to the unsubscribe_complete.php page. * Unsubscribe_complete.php: The final page in the application acknowledges that the user has unsubscribed.

Preliminary set-up
Before you dive into the page development, let me say a few words about the preliminary set-up.
This PHP application requires a simple MySQL database and a Dreamweaver connection, in addition to a dynamic PHP site set-up on your testing server.
To define a dynamic site follow these steps: 1. Open your Dreamweaver Site Definition dialog box (Sites > Manage Sites > Edit) and select the Testing Server category. 2. Choose PHP MySQL from the Server Model menu and set up your access. If you’re working with your own computer as a development system choose Local/Network from the Access list and select the path to the folder on your testing server. 3. Finally, set the URL to the proper address.

Here is a folder containing lesson files that you can download and follow along with.
The Subscription database consists of a single table, called subs, which in turn has three data columns: ID, SubEmail, and SubSubscriptions (see Figure 1 below).
The ID column is the primary key and uses an integer format that is automatically incremented. The SubEmail column has a text format and is intended to hold the subscribers e-mail addresses. The final column, SubSubscriptions is a Boolean field, intended to note whether a user is currently subscribed or not; a 1 indicates subscribed whereas a 0 means unsubscribed. Although this field is not used in this article in this way—unsubscribed records are deleted—it is included as a possible next step.
One enhancement of the described technique is to keep the user’s e-mail address on file, in case your site offers other e-mail opportunities than just the newsletter, and toggle the SubSubscriptions field as needed.

The SQL file for the Subscriptions database is included in the sample files that accompany this article . You can recreate it on your test server through any number of utilities, including phpMyAdmin, MySQL Control Center or MySQL Administrator.
When you’ve established your database, it’s time to create a connection to it in Dreamweaver. To do so, follow these steps: 1. Choose Window > Databases. 2. Click Add (+) and choose MySQL Connection from the menu. 3. In the MySQL Connection dialog box (see Figure 2 below), enter the name of your new connection in the Connection name field (for example, connSubscribe). 4. Enter an IP address or MySQL server name in the MySQL server field. If you’re working with a local development system, enter localhost. 5. Insert your user name and password in the appropriate fields. 6. Click Select to display the available databases; choose the desired one from the list. 7. Click Test to verify the connection; when the connection is successful, click OK. With your PHP dynamic site, MySQL database, and Dreamweaver connection established, you’re ready to build the first page of the application.
Signing up for a subscription
The subscription page should be simple and clear. You want to keep any barriers to signing up as low as possible. The following example page has a single form field to gather the e-mail address and a submit button, clearly labeled Subscribe Me! (see Figure below). The text field is named txtEmail by selecting the page element and entering the name in the provided field on the far left of the Property inspector. The form action property is left blank, so the Dreamweaver Insert Record behavior can add its own code. One other form field is inserted into the form: a hidden field labeled SubSubscription with a value of 1. This hidden field will be used by the Insert Record server behavior to populate the identically named field in the database and indicate a subscription has been authorized. Remember that for this Boolean database column, 1 means subscribed.
Note: It’s always a good idea to validate your form fields. You can use the Dreamweaver Validate Form behavior or insert the Spry Validation Text Field if you prefer.
To apply the Insert Record server behavior, follow these steps: 1. From the Server Behaviors panel, choose Add (+) and, from the list, select Insert Record. 2. When the Insert Record dialog box opens, make sure that the current form is selected in the Submit values from list. In my example, I kept the default name, form1. 3. Choose your connection from the Connection list. Here, my connection is connSubscribe. 4. Select the table you want to insert your new record into from the Insert table list. My table is called subs. 5. In the Columns area, you only need to specify a form element for one of the database columns (see Figure 4 below): * Leave the first entry—’ID’ Is an Unused Primary Key—at its default settings. * Select the SubEmail entry and from the Value list, choose FORM.txtEmail and make sure Submit as is set to Text. * Keep the third entry (SubSubscribed) at its default settings. Because you named the hidden form element the same as the database column, Dreamweaver automatically made the assignment for you. 6. In the After inserting, go to field, enter confirm_subscribe.php. 7. Click OK and save your page. Confirming the subscription
The subscription confirmation page is well-named as it carries out its self-named function in two ways. The text on the page lets your site visitors know that they have successfully signed up. It’s a great idea to personalize this acknowledgement by repeating their registered e-mail address. A simple Dreamweaver recordset and text binding is needed to accomplish this goal.
You can also use this space to let folks know that a second confirmation has been sent via e-mail with a link for unsubscribing. The text is simple to do in Dreamweaver; the e-mail function takes a bit of handcoding. The e-mail will take advantage of the previously inserted recordset.
Let’s start by adding the recordset to the page by following these steps:
Step 1: Open or create your confirm_subscribe.php page. The basic text on the page should be simple and straightforward as shown in Figure 5 below. Step 2: From the Bindings panel, click Add (+) and choose Recordset from the list.
Step 3: When the Recordset dialog box opens (see Figure 6 below), make the following adjustments: * Ensure that you’re in Simple mode and enter an appropriate name for the recordset in the Name field (for example, rsSubscriber). * Choose your data source connection from the Connection list; mine is called connSubscribe. * Select the table from the Table list, like subs. * Leave all the columns selected and the filter at its default setting. * From the Sort lists, choose ID Descending. Do not click OK just yet.

At this stage (see Figure 6 above), the recordset will return all the records and list the last one entered first; to verify this click Test. However, you don’t want all the records, you only want one. You can restrict the recordset to the number of records returned with a simple SQL phrase. For this type of customization, you’ll need to enter the Recordset Advanced mode.
Step 4: Click Advanced. You can see how your selections in the Simple mode are translated into SQL in the Advanced mode’s SQL section.
Step 5: Place your cursor at the end of the SQL code, after ORDER BY ID DESC and enter LIMIT 1 (see Figure 7 below).

Now, the recordset returns just the single record, which because the data is sorted by the automatically incremented ID field in descending order, is the one just inserted.
Step 6: Click OK to close the Recordset dialog box. Now that you have some dynamic data coming into the page, you can personalize it.
Step 7: In Design view, place your cursor in an appropriate spot to display the subscriber’s e-mail address, like right after the phrase “…e-mail address”. Add any necessary punctuation, such as a comma or parentheses. From the Bindings panel, expand the recordset entry, select SubEmail and click Insert (see Figure 8 below).
Step 8: Save your page.
Now that you have a recordset available, you’re ready to insert the e-mail code. One of the great benefits of PHP is that it has an easy-to-use e-mail method built in. The mail() function looks like this: mail(address, subject, message[, header[,parameters]])
The first three arguments (e-mail address, subject, and message) are mandatory. The header strings, which typically contains a from address and can also set the MIME type or list CC: and BCC: addresses, is optional. The parameter argument is also optional and is most frequently used for sending additional arguments to configure the output e-mail.
To make the actual function call readable, I like to set up a series of variables with the values assigned, like this:
$id = $row_rsSubscriber['ID'];
$to = $row_rsSubscriber['SubEmail'];
$subject = “Subscription confirmation”;
The first two variables, $id and $to, pull data from the just created recordset that will be incorporated into the e-mail. The third variable, $subject, simply sets the subject line of the message.
Some variables require a lengthy string. I like to use the PHP concatenation operator—the period—to build up the content for such variables, like this:

$body=”<html><body>”. “<h2>Thank you for subscribing to our newsletter ! </h2>”. “<p>To Unsubscribe,Click <a href=\http://www.xtrinsicsoft.com/confirm_unsubscribe.php?ID=.$id.”\”>here</a>.</p>
“</body></html>”;
=======
When the variable is fully declared, be sure to conclude with a semi-colon. Although you can send your message in plain text, I thought I’d show you how to send HTML e-mail in this technique. For HTML e-mail, you’ll need to add the needed HTML tags to achieve the proper formatting. It’s important to note the backslashes in the third code line above; they are used to escape the quotation marks within the “a” tag.
HTML e-mail also requires setting the e-mail header properly. In addition to defining the From address, you’ll also need to establish the MIME and content type, like this:
$headers = “From: Subscription Manager \r\n” .
“MIME-Version: 1.0\r\n” .
“Content-type: text/html; charset=UTF-8″;
The final portion of the code executes the mail() function, but does it in such a way that it redirects the browser to an error page if a problem is encountered. Here’s the code: if (!mail($to, $subject, $body, $headers)) { header( ‘Location: http://www.relativerealty.com/error_subscribe.php’ ) ;
The exclamation mark before the mail() function can be read as NOT, so if the function cannot be executed, the redirecting header() function comes into play.
Let’s put it all together now in the following steps.
Step 1: In the Server Behaviors panel, select the Recordset entry.
Step 2: Switch to Code view and press the left arrow to move your cursor outside the recordset code block. Press Enter (Windows) or Return (Mac) to create a new line.
Step 3: Enter the following code:
<?php
$id = $row_rsSubscriber['ID'];
$to = $row_rsSubscriber['SubEmail'];
$subject = "Subscription confirmation";
$body = "” .
“
Thank you for subscribing to our newsletter!
” .
“
To unsubscribe, click here.
” .
“”;
$headers = “From: Subscription Manager \r\n” .
“MIME-Version: 1.0\r\n” .
“Content-type: text/html; charset=UTF-8″; if (!mail($to, $subject, $body, $headers)) { header( ‘Location: http://[YOUR SERVER & PATH HERE]/error_subscribe.php’ ) ;
}
?>
Make sure to substitute the placeholder phrases [YOUR SERVER & PATH HERE] with your own server information and the from address (someone@mycompany.com) with your own from address.