You learned in earlier chapters that planning is the most important aspect of creating any product. In this case, think of the elements you will need for your subscription mechanism:
The following sections describe each item individually.
Creating the subscribers Table
You really need only one field in the subscribers table: to hold the email address of the user. However, you should have an ID field just for consistency among your tables, and also because referencing an ID is much simpler than referencing a long email address in where clauses. So, in this case, your MySQL query would look something like
mysql> CREATE TABLE subscribers ( -> id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, -> email VARCHAR (150) UNIQUE NOT NULL -> ); Query OK, 0 rows affected (0.00 sec)
Note the use of UNIQUE in the field definition for email. This means that although id is the primary key, duplicates should not be allowed in the email field either. The email field is a unique key, and id is the primary key.
Log in to MySQL via the command line and issue this query. After creating the table, issue a DESC or DESCRIBE query to verify that the table has been created to your specifications, such as
mysql> DESC subscribers; +-------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+--------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | email | varchar(150) | NO | UNI | | | +-------+--------------+------+-----+---------+----------------+ 2 rows in set (0.00 sec)
Now that you have a table in your database, you can create the form and script that place values in there.
Creating the Subscription Form
The subscription form will actually be an all-in-one form and script called manage.php, which will handle both subscribe and unsubscribe requests. Listing 19.1 shows the code for manage.php, which uses a few user-defined functions to eliminate repetitious code and so that you start thinking about creating functions on your own. The code looks long, but a line-by-line description follows, and the majority of the code is an HTML form, so no worries!
Listing 19.1. Subscribe and Unsubscribe with manage.php
Listing 19.1 may be long, but it's not complicated. In fact, it could be longer, were it not for the user-defined functions at the top of the script. One of the reasons for creating your own functions is to be able to reuse them within your scripts. Lines 315 set up the first function, doDB(), which is simply the database connection function. If the connection cannot be made, the script will exit when this function is called; otherwise, it will make the value of $mysqli available to other parts of your script. Lines 1725 define a function called emailChecker(), which takes an input and returns an outputlike most functions do. We'll look at this one in the context of the script, as we get to it.
Line 28 starts the main logic of the script. Because this script performs several actions, we need to determine which action it is currently attempting. If the presence of $_POST is false, we know that the user has not submitted the form; therefore, we must show the form to the user.
Lines 3044 create the subscribe/unsubscribe form, using $_SERVER["PHP_SELF"] as the action (line 31), creating a text field called email for the user's email address and setting up a set of radio buttons (lines 3640) to find the desired task. At this point, the script breaks out of the if...else construct, skips down to line 114, and proceeds to print the HTML, which is stored in the variable called $display_block. The form is displayed as shown in Figure 19.1.
Figure 19.1. The subscribe/unsubscribe form.
Back inside the if...else construct, if the presence of $_POST is true, we need to do something. There are two possibilities: the subscribing and unsubscribing actions for the email address provided in the form. We determine which action to take by looking at the value of $_POST["action"] from the radio button group.
In line 46, if the presence of $_POST is true and the value of $_POST["action"] is "sub", we know the user is trying to subscribe. To subscribe, the user needs an email address, so we check for one in lines 4850. If no address is present, the user is redirected back to the form.
However, if an address is present, we call the doDB() function in line 53 to connect to the database so that we can issue queries. In line 56, we call the second of our user-defined functions: emailChecker(). This function takes an input ($_POST["email"], in this case) and processes it. If we look back to lines 2124, we see code within the emailChecker() function that issues a query in an attempt to find an id value in the subscribers table for the record containing the email address passed to the function. The function then returns the resultset, called $check_res, for use within the larger script.
By the Way
Note the definition of global variables at the beginning of both user-defined functions in Listing 19.1. These variables need to be shared with the entire script, and so are declared global.
Jump down to line 59 to see how the $check_res variable is used: The number of records referred to by the $check_res variable is counted to determine whether the email address already exists in the table. If the number of rows is less than 1, the address is not in the list, and it can be added. The record is added, and the response is stored in lines 6468, and the failure message (if the address is already in the table) is stored in line 74. At that point, the script breaks out of the if...else construct, skips down to line 114, and proceeds to print the HTML. You'll test this functionality later.
The last combination of inputs occurs if the presence of $_POST is true and the value of the $_POST["action"] variable is "unsub". In this case, the user is trying to unsubscribe. To unsubscribe, an existing email address is required, so we check for one in lines 7981. If no address is present, the user is sent back to the form.
If an address is present, we call the doDB() function in line 84 to connect to the database. Then, in line 87, we call emailChecker(), which again will return the resultset, $check_res. The number of records in the resultset is counted in line 90, to determine whether the email address already exists in the table. If the number of rows is less than 1, the address is not in the list, and it cannot be unsubscribed.
In this case, the response message is stored in lines 9596. However, if the number of rows is not less than 1, the user is unsubscribed (the record deleted) and the response is stored in lines 104108. At that point, the script breaks out of the if...else construct, skips down to line 114, and proceeds to print the HTML.
Figures 19.2 through 19.5 show the various results of the script, depending on the actions selected and the status of email addresses in the database.
Figure 19.2. Successful subscription.
Figure 19.3. Subscription failure.
Figure 19.4. Successful unsubscribe action.
Figure 19.5. Unsuccessful unsubscribe action.
Next, you'll create the form and script that sends along mail to each of your subscribers.