Managing a Simple Mailing List

This hour provides the first of several hands-on, small projects designed to pull together your PHP and MySQL knowledge. In this hour, you'll learn the methods for creating a managed distribution list, which can be used to send out newsletters or anything else that you want to send, to a list of email addresses in a database.

The mailing mechanism you'll use in this hour is not meant to be a replacement for mailing list software, which is specifically designed for bulk messages. The type of system you'll build in this lesson should be used for only small lists of fewer than a few hundred email addresses.

In this hour, you will learn how to

• Create a subscribe/unsubscribe form and script
• Create a front end for sending your message
• Create the script that sends your message
  
  
  
  

  Developing the Subscription Mechanism

  You learned in earlier lessons 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:
• A table to hold email addresses
• A way for users to add or remove their email addresses
• A form and script for sending the message

The following sections will 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 a lot 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.

This relationship is represented in the table information as MUL (or "multiple") in the Key field:

mysql> describe subscribers;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int(11)      |      | PRI | NULL    | auto_increment |
| email | varchar(150) | YES  | MUL | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
2 rows in set (0.00 sec) 

Now that you have a table, 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 18.1 shows the code for manage.php, which uses a few user-defined functions to eliminate repetitious code.

Listing 18.1 Subscribe and Unsubscribe with manage.php

  1: <?php
  2: //set up a couple of functions
  3: function doDB() {
  4:     global $conn;
  5:    //connect to server and select database; you may need it
  6:    $conn = mysql_connect("localhost", "joeuser", "somepass")
  7:         or die(mysql_error());
  8:    mysql_select_db("testDB",$conn) or die(mysql_error());
  9: }
 10: 
 11: function emailChecker($email) {
 12:     global $conn, $check_result;
 13:    //check that email is not already in list
 14:    $check = "select id from subscribers where email = '$email'";
 15:    $check_result = mysql_query($check,$conn) or die(mysql_error());
 16: }
 17: 
 18: //determine if they need to see the form or not
 19: if ($_POST[op] != "ds") {
 20:    //they do, so create form block
 21:    $display_block = "
 22:    <form method=POST action=\"$_SERVER[PHP_SELF]\">
 23: 
 24:    <p><strong>Your E-Mail Address:</strong><br>
 25:    <input type=text name=\"email\" size=40 maxlength=150>
 26: 
 27:    <p><strong>Action:</strong><br>
 28:    <input type=radio name=\"action\" value=\"sub\" checked> subscribe
 29:    <input type=radio name=\"action\" value=\"unsub\"> unsubscribe
 30: 
 31:    <input type=\"hidden\" name=\"op\" value=\"ds\">
 32: 
 33:    <p><input type=submit name=\"submit\" value=\"Submit Form\"></p>
 34:    </form>";
 35: 
 36: } else if (($_POST[op] == "ds") && ($_POST[action] == "sub")) {
 37:    //trying to subscribe; validate email address
 38:    if ($_POST[email] == "") {
 39:        header("Location: manage.php");
 40:        exit;
 41:    }
 42:    //connect to database
 43:    doDB();
 44:    //check that email is in list
 45:    emailChecker($_POST[email]);
 46: 
 47:    //get number of results and do action
 48:    if (mysql_num_rows($check_result) < 1) {
 49:        //add record
 50:        $sql = "insert into subscribers values('', '$_POST[email]')";
 51:        $result = mysql_query($sql,$conn) or die(mysql_error());
 52:        $display_block = "<P>Thanks for signing up!</P>";
 53:    } else {
 54:        //print failure message
 55:        $display_block = "<P>You're already subscribed!</P>";
 56:    }
 57: } else if (($_POST[op] == "ds") && ($_POST[action] == "unsub")) {
 58:    //trying to unsubscribe; validate email address
 59:    if ($_POST[email] == "") {
 60:    header("Location: manage.php");
 61:        exit;
 62:    }
 63:    //connect to database
 64:    doDB();
 65:    //check that email is in list
 66:    emailChecker($_POST[email]);
 67: 
 68:    //get number of results and do action
 69:    if (mysql_num_rows($check_result) < 1) {
 70:        //print failure message
 71:        $display_block = "<P>Couldn't find your address!</P>
 72:        <P>No action was taken.</P>";
 73:    } else {
 74:        //unsubscribe the address
 75:        $id = mysql_result($check_result, 0, "id");
 76:        $sql = "delete from subscribers where id = '$id'";
 77:        $result = mysql_query($sql,$conn) or die(mysql_error());
 78:        $display_block = "<P>You're unsubscribed!</p>";
 79:    }
 80: }
 81: ?>
 82: <html>
 83: <head>
 84: <title>Subscribe/Unsubscribe</title>
 85: </head>
 86: <body>
 87: <h1>Subscribe/Unsubscribe</h1>
 88: <?php echo "$display_block"; ?>
 89: </body>
 90: </html>