Managing multiple subscriptions in PHP

As companies and organizations become increasingly web-savvy, they seek to strengthen their customer outreach efforts. This desire often manifests itself as e-mail–based newsletters. As noted in an earlier Adobe Developer Center article, Building a subscribe/unsubscribe app in PHP with Dreamweaver CS3, you can create all the code you need in Dreamweaver and PHP to handle individual subscriptions. In this article, you'll learn how to extend those capabilities so your client's visitors can manage multiple newsletter subscriptions. As a bonus, I'll show you how to incorporate an error message into a standard Dreamweaver login page.

• Login.php: The login page verifies the e-mail address and password of anyone who desires to change their subscription settings. Once authenticated, the user is taken to the manage_sub.php page. If the log-on fails, an error message is displayed on the current page.
• Manage_sub.php: The manage_sub page is the workhorse of this application. Here, all possible subscriptions are listed with accompanying check boxes. If a user is subscribed to one item, its check box is selected. Users can toggle their subscriptions on or off at will; once they've made their adjustments, a click of the Submit button updates their records and takes them to the thank you page.
• Thank_you.php: A simple page that thanks visitors for changing their subscriptions.
• Logout.php: This page contains code to log out the current user and return them to the login page.

Before you begin building the application, take a moment to examine the database table you'll be using and also make sure the database connection is properly set up in Dreamweaver.

Note: It's a good idea to have your dynamic PHP site already set up at this point and have unpacked the sample files into the local root folder.

Understanding the database schema

The database that accompanies this article, subscriptions_multiple, consists of a single table called subs. The subs table has six data columns: ID, SubEmail, SubPassword, Sub1, Sub2, and Sub3 (see Figure 1). The ID column is the primary key and, as such, 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; this field serves double duty as the user name. The remaining three columns—Sub1, Sub2, and Sub 3—are all Boolean fields, designed to note whether or not a user is currently subscribed to that particular newsletter; a 1 indicates subscribed whereas a 0 means unsubscribed.

The SQL file for the subscriptions-multiple database is included in the sample files download. You can recreate it on your test server through any number of utilities, including phpMyAdmin, MySQL Control Center, or MySQL Administrator.

Making the database connection

After 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. When the MySQL Connection dialog box appears (see Figure 2), enter a name for your new connection in the Connection name field (for example, connSubMultiple).

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. Here, the example database is named subscriptions_multiple.
7. Click Test to verify the connection and then OK if the connection is successful.

With your PHP dynamic site, MySQL database, and Dreamweaver connection all established, you're ready to build the first page of the application.

The login page is straightforward, but I've added a new twist to keep it interesting. The page consists of a form with two text fields: one for the user name and the other for password (see Figure 3).

Setting up the server-side code for this page is equally direct and requires a single server behavior. The interesting part is how failed log-ons are handled: rather than send the user to another page—from which he or she would have to click the Back button—you'll display the error message on the same page for a quicker and smoother response.

1. Choose File > Open. Navigate to the folder with the uncompressed sample files and select login.php. Click OK.
2. From the Server Behaviors panel, click Add (+) and choose User Authentication > Log In User.

   In the sample file, the two fields are named appropriately: Email and Password. The Password field, additionally, is set to mask the entered values.

3. When the Log In User dialog box opens (see Figure 4), leave the selected form in place and make sure the Username field is set to email and the Password field to password.
   • In the Validate using connection list, choose connSubMultiple.
   • From the Table list, choose subs.
   • From the Username column list, choose SubEmail and from Password column list, SubPassword.
   • In the If login succeeds, go to field, enter manage_subs.php.
   • In the If login fails, go to field, enter login.php?failed=true.
   • Set the Restrict access based on option to Username and password and click OK.

4. Choose File > Save to store your changes.

When activated by clicking the Log In button, the Log In User server behavior verifies that the supplied e-mail address and password match values found in the subs table. If a match is found, the subscription management page, cleverly called manage_subs.php, is displayed; if not, the login page is reshown and the Email and Password fields are cleared. Although some folks might think this is enough of a hint to site visitors that they need to try again, it's better to be as clear as possible when it comes to error messages. In the next step, you'll insert an error message that displays on the login page.

Add code for an error message

The basic Dreamweaver server behaviors—such as insert, update, and delete—all depend on the form being submitted to the same page in which the server-side code is inserted. The Log In server behavior works the same way. You can use this knowledge to create a same-page error message.

1. Place your cursor anywhere in the form and, from the Tag Selector, choose <form>.

   Although you could put the error message anywhere on the page, placing it below the form works well.

2. Press the right arrow key once.

   By selecting the form and then moving to one side of it, you're sure to insert your <p> tag in the right place.

3. Switch to Code view and insert the following:

This code is a ternary (or conditional) statement. Translated into English, it reads, "If there is a URL parameter called 'failed', insert the error message; otherwise, don't insert anything." You'll recall that in the Log In dialog box, you entered the current page with the URL parameter string failed=true as the page to display should the log-on not be successful, like this: login.php?failed=true. The CSS class .error will display the message in a noticeable red.

4. Return to Design view and save your page.

Now for the main event: the subscription management page.

The subscription management page displays the current subscriptions for whoever has just logged in. To accomplish this you'll need to set up a recordset that is restricted (or filtered) to the logged-in user. Dreamweaver's Log In server behavior stores the value of the username column—in this example, the e-mail address—in the session variable MM_Username. In the following steps, you'll filter the recordset by that same session variable.

After you have the recordset defined, you'll need to bind the dynamic date to the various form field elements; this will display the subscriptions currently stored in the database. Then, you'll bring the Update Record server behavior in Dreamweaver into play to store any changes to the subscriptions.

Creating the Recordset

Dreamweaver makes it easy to filter data based on a session variable; there's no need to write any SQL code at all.

1. Choose File > Open and, in the sample files folder, select manage_subs.php. Click OK.

   The manage_subs page (shown in Figure 5) consists of a simple form that focuses on the available subscriptions. There is room for the user's e-mail address at the top of the form, which will be displayed as dynamic text to confirm that the proper subscriptions are shown. Check boxes enable the visitor to quickly turn subscriptions on or off. There is also a hidden field, named ID, which will contain the ID of the selected record—a necessary step for the Update Record server behavior.

2. From the Bindings panel, choose Add (+) and select Recordset.
3. When the Recordset dialog box appears (see Figure 6), make sure you're in Simple mode and enter rsSubscription in the Name text box.

4. From the Connection list, choose connSubMultiple.
5. From the Tables list, choose subs.
6. Leave the Columns and Sort options set to their default settings.
7. Set the Filter option to the following:

The Filter setting looks at the value stored in the MM_Username session variable and matches it to the same value stored in the SubEmail database field to get a single record.

8. Click OK.
9. Save your page.

Binding record data

Now that the recordset is defined, set the dynamic values for the various form fields and areas. In this situation, you'll drag and drop two entries, one for the the Email field and another for the hidden ID field, and assign the other values for the check boxes through Dreamweaver dialog boxes.

1. From the Bindings panel, expand the Recordset entry.
2. Drag the dynamic data to their associated form elements:
   • Drag SubEmail to the cell adajent to the Email label.
   • Drag ID to the hidden form element.

   If your Property inspector is open, when you bind the database field ID to the hidden element, you see the PHP code be inserted into the Value field (see Figure 7).

3. Select the first check box next to Apartment Rentals. In the Property inspector, click Dynamic.
4. When the Dynamic CheckBox dialog box opens (see Figure 8), verify that the CheckBox list is set to Sub1 in form1 and click the Check if lightning bolt. In the Dynamic Data dialog box, expand the Recordset (rsSubscription) entry and choose Sub1; click OK. Back in the Dynamic CheckBox dialog box, in the Equal to field, enter 1 and click OK.

You'll recall that the Sub1 data field (as well as those for Sub2 and Sub3) were Booleans for which 0 meant not subscribed and 1 meant subscribed. In this step, you're setting up the check boxes to display the proper state: checked if the value is 1 (subscribed) and unchecked if the value is anything else (0, or unsubscribed).

5. Repeat Steps 3 and 4 for the next two check boxes. For the Condos and Co-ops check box, choose Sub2 in the Dynamic Data dialog box, and for the Homes on the Market check box, choose Sub3.
6. Save your page.

Adding the Update Record server behavior

The final major step of the manage_subs.php page is to insert the Update Record server behavior.

1. From the Server Behaviors panel, choose Add (+) and choose Update Record from the list.
2. When the Update Record server behavior opens (see Figure 9), make sure form1 is chosen in the Submit values from list.

3. From the Connection list, choose connSubMultiple.
4. From the Update table list, choose subs.

   Dreamweaver automatically assigns each to the proper data column because of the identically named form fields.

5. In the After inserting, go to text box, enter thank_you.php and click OK.
6. Save and close your page.

The bulk of the work is now done; there's just a little finishing up to do.

When Dreamweaver logs in a user, it creates a session variable that makes it possible for anyone using the machine at the current time to access restricted pages. A logout page is important because it eliminates that session variable and thus prevents any unauthorized access. Dreamweaver makes this easy to accomplish.

1. Choose File > Open and, in the sample files folder, select logout.php. Click OK.

   Although this page has a Log Out title and a bit of confirming text, in reality it will never be seen by the user. The server behavior executes when the page loads and immediately redirects the user to another page.

2. From the Server Behaviors panel, choose Add (+) and choose User Authentication > Log Out User (see Figure 10).

3. In the Log Out User dialog box, choose Log out when: Page Loads.

   Since the user has already clicked Log out once to get to this page, you'll want to grant his or her wish immediately.

4. In the When done, go to text box, enter login.php and click OK.
5. Save and close your page.

The final task is to protect your various pages from unauthorized access.

Let's add a little protection to your completed pages. The standard Dreamweaver Log In server behavior includes an option to restrict access based on username and password. To activate that protection, you'll need to apply Dreamweaver's Restrict Access to Page server behavior to the desired pages, starting with reg.php.

1. Choose File > Open and, in the sample files folder, select manage_sub.php. Click OK.
2. From the Server Behaviors panel, choose Add (+) and select User Authentication > Restrict Access to Page.
3. When the Restrict Access to Page dialog box appears (see Figure 11), set the Restrict based on option to Username and password.

Dreamweaver does offer a more expansive authentication protocol that also looks at the user's access level. For the purposes of this tutorial, the Username and password option is good enough.

4. In the If access denied, go to text box, enter login.php.
5. Save and close your page.

   With this page protected, you're ready to move on to the other pages in the application.

6. Repeat Steps 1–5 with the logout.php and thank_you.php pages.

Good work! Your subscription management system is now complete. To test your application, open login.php on your testing server and log in with the e-mail jlowery@idest.com and password jaylow. On the subscription management page, modify the current settings and submit the change. The next time you log in, your changes will be on screen.

Where to go from here

This article covers the development of a subscription management system. To make this usable from the administrative side, you'd need to create the ability to add and remove subscriptions to the subs table. Another enhancement would be to add e-mail confirmations to your changes; you can find detailed instructions to accomplish this in the companion Adobe Developer Connection article, Building a subscribe/unsubscribe app in PHP with Dreamweaver CS3.