Wrox Home  
Search


Excerpt from Beginning PHP, Apache, MySQL Web Development

Using the Mailing List Administrator

The first page of the mailing list application we want to take a look at is the Mailing List Administrator page. Load admin.php in your browser. As you can see in Figure 1, you can create a new mailing list, delete an existing mailing list, or send a quick message to users. Feel free to create a couple of new mailing lists. Go crazy, have fun, get wacky. Good. Let's move on.

Beginning PHP, Apache, MySQL Web Development - Figure 1
Figure 1

Click the link at the bottom of the Mailing List Administrator page, "Send a quick message to users." A new page appears where you can compose a new message and send it to either a single mailing list, or all users (see Figure 2). If you just created these pages, you don't have any users yet. You can compose a message, but it won't go to anyone. You'll need to create the user pages first, which you'll do shortly.

Beginning PHP, Apache, MySQL Web Development - Figure 2
Figure 2

How It Works

Your first file, config.php, contains just five lines:

define('SQL_HOST','yourhost');
define('SQL_USER','joeuser');
define('SQL_PASS','yourpass');
define('SQL_DB','yourdatabase');
define('ADMIN_E-MAIL', 'your@e-mailaddress.com');

These constants are created separately so that in the event that you make a change such as moving the application to another server, or changing the password, it can be done in only one location. If this data were in each file, any change would require modification of several files.

Include config.php in each file that requires access to MySQL like so:

require('config.php');

You use require() instead of include() because if the file is not loaded, you want to halt loading of the page. Another option would be to use include(), and then immediately test for the existence of one of the constants. If it does not exist, you could then redirect the user to another page, which makes for a more user-friendly experience.

As you can see here, the constants from config.php are used to make your connection:

$conn = mysql_connect(SQL_HOST, SQL_USER, SQL_PASS)
 or die('Could not connect to MySQL database. ' . mysql_error());

$sql = "CREATE DATABASE IF NOT EXISTS" . SQL_DB . ";";

$res = mysql_query($sql) or die(mysql_error());

mysql_select_db(SQL_DB,$conn);

Take notice of the CREATE DATABASE statement. There are a couple of things you should be aware of about creating databases. If you already have a database (one that you created in another chapter, or if your site is already using a database), it's better to keep your mailing list tables in that database. You don't need extra databases because if your Web site spans multiple databases, you will need to create multiple connections to those databases, and that is extra overhead that is just not necessary.

In addition, if there are any relationships between your tables from this application and tables from other apps, they need to be in the same database. For example, if you have a user table that stores your registered users for all applications, all of the applications that rely on that user table should reside in the same database.

If you do need to create a new database, the CREATE DATABASE command may still not work if your site is hosted by an Internet service provider (ISP). Some ISPs don't allow programmed creation of databases. If this is the case, you may need to go through your ISP to create the database (through PHPMyAdmin, for example) and run sql.php. Just be sure to put the proper database name in the code. See Chapter 9 for more information about creating databases.

This SQL is used to create the tables in your database:

$sql1 = <<<EOS
 CREATE TABLE IF NOT EXISTS ml_lists (
  ml_id int(11) NOT NULL auto_increment,
  listname varchar(255) NOT NULL default '',
  PRIMARY KEY (ml_id)
 ) TYPE=MyISAM;
EOS;

$sql2 = <<<EOS
 CREATE TABLE IF NOT EXISTS ml_subscriptions (
  ml_id int(11) NOT NULL default '0',
  user_id int(11) NOT NULL default '0',
  pending tinyint(1) NOT NULL default '1',
  PRIMARY KEY (ml_id,user_id)
 ) TYPE=MyISAM;
EOS;

$sql3 = <<<EOS
 CREATE TABLE IF NOT EXISTS ml_users (
  user_id int(11) NOT NULL auto_increment,
  firstname varchar(255) default '',
  lastname varchar(255) default '',
  e-mail varchar(255) NOT NULL default '',
  PRIMARY KEY (user_id)
 ) TYPE=MyISAM;
EOS;

Note there are three tables: ml_lists, ml_users, and ml_subscriptions. The ml_lists table contains two columns: the unique ID (ml_id) and the name of the mailing list (listname). The ml_users table contains four columns: the unique id (user_id), first and last name (firstname, lastname), and e-mail address (e-mail).

The ml_subscriptions table is where most of the "work" is done when it comes to mailing lists. It contains three columns: ml_id, user_id, and pending. The combination of ml_id and user_id must be unique. (You don't want to have the same user subscribed to the same mailing list more than once, right?). The pending column is used to determine whether or not a user has confirmed his or her subscription.

The following lines simply run the SQL queries to create the tables. As long as all three tables are created (or already exist), you will see "Done" echoed to the screen. Otherwise, you will see an error message.

$res = mysql_query($sql1) or die(mysql_error());
$res = mysql_query($sql2) or die(mysql_error());
$res = mysql_query($sql3) or die(mysql_error());
echo "Done.";

Next, let's take a look at admin.php:

require('config.php');

Now you should see why we put the connection values in a separate file. By doing this, all you need is a single line to include the constants, and you can use them in this page.

Let's pause here for a moment and talk about form submittal. A common practice is to post a form back to itself, and you certainly could have done that here. When your page contains data that needs to be inserted into a database, however, you need to think twice about a self-posting form. If the user were to refresh or reload the page, all of your database functions would run again, and that could be disastrous. You would end up with duplicate data, or delete records you didn't mean to delete.

Obviously, you don't want anything like that to happen, so in order to minimize the probability, you post to a separate form called admin_transact.php. This page handles all of the necessary database transactions, and then redirects back to the page from which you came. If the user reloads the page at that point, no harm will come to your database.

<form method="post" action="admin_transact.php">

You might notice that all of your buttons have the same name, "action," each with a different value. When posting the form, you will be accessing the $_POST['action'] variable to see which button was pressed, and perform the appropriate actions. This allows you to use one script for multiple transactions, rather than having to create a page with multiple forms, each posting to a different transaction page.

<input type="submit" name="action" value="Add New Mailing List" />

Now you get all of the mailing lists available and wrap them in option tags so that they will appear on your page in a drop-down select box.

<select name="ml_id">
<?php

$conn = mysql_connect(SQL_HOST, SQL_USER, SQL_PASS)
 or die('Could not connect to MySQL database. ' . mysql_error());

mysql_select_db(SQL_DB,$conn);

$sql = "SELECT * FROM ml_lists ORDER BY listname;";
$result = mysql_query($sql)
 or die('Invalid query: ' . mysql_error());

while ($row = mysql_fetch_array($result))
{
 echo "  <option value=\"" . $row['ml_id'] . "\">" . 
    $row['listname'] . "</option>\n";
}

?>
</select>

This is the link to the e-mail portion of the admin's functions, which is pretty self-explanatory:

<a href="quickmsg.php">Send a quick message to users</a>

You should be able to figure out quickmsg.php fairly easily. Most of it is HTML, and the PHP code is practically identical to the code used to build the select in admin.php. Feel free to cannibalize your own code as often as you need.

Finally, we come to the real workhorse of the Mailing List Administrator application, admin_transact.php. This page is the one to which you post your forms, and it will process that information, update the database tables, and send out e-mails as required. Let's take a look under the hood:

require('config.php');

Remember seeing the preceding line in admin.php? Having your connection data in one file and including it in each page makes your code much more efficient. Of course, you already knew that. Let's move on to create the connection to the database, and select it so that you can work with it:

$conn = mysql_connect(SQL_HOST, SQL_USER, SQL_PASS)
 or die('Could not connect to MySQL database. ' . mysql_error());

mysql_select_db(SQL_DB,$conn);

Did the user click an "action" button?

if (isset($_POST['action']))
{

Depending on which button was clicked, you're going to perform one of three actions: create a new mailing list, delete an old mailing list, or send a message to the users subscribing to a mailing list:

switch ($_POST['action'])
 {

Not only must you delete the mailing list, like this:

case 'Add New Mailing List':
   $sql = "INSERT INTO ml_lists (listname) VALUES ('" .
       $_POST['listname'] . "');";
   mysql_query($sql)
    or die('Could not add mailing list. ' . mysql_error());
   break;

  case 'Delete Mailing List':
   mysql_query("DELETE FROM ml_lists WHERE ml_id=" . $_POST['ml_id'])
    or die('Could not delete mailing list. ' . mysql_error());

if anyone was subscribed to that mailing list, you must delete those subscriptions, too:

$sql = "DELETE FROM ml_subscriptions WHERE ml_id=" .
       $_POST['ml_id'];
   mysql_query($sql)
    or die('Could not delete mailing list subscriptions. ' .
    mysql_error());
   break;

When you send a message, you want to let the user know which mailing list you are referring to. If the mailing list ID (ml_id) is "all" instead of a number, you will want to reflect that as well:

case 'Send Message':
   if ((isset($_POST['msg'])) and (isset($_POST['ml_id'])))
   {
    if (is_numeric($_POST['ml_id'])) {
$sql = "SELECT listname FROM ml_lists WHERE ml_id='" . $_POST['ml_id'] . "'"; $result = mysql_query($sql, $conn) or die(mysql_error()); $row = mysql_fetch_array($result); $listname = $row['listname']; } else { $listname = "Master"; }