Beginning PHP, Apache, MySQL

Chapter 13 Code - Mailing Lists

Try It Out 1

config.php - Try It Out 1

<?php

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

?>

sql.php - Try It Out 1

<?php
require('config.php');

$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);

$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;

$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.";
?>

admin.php - Try It Out 1

<?php
require('config.php');
?>

<html>
<head>
<title>Mailing List Administration</title>
</head>
<body>

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

<p>
 Add Mailing List:<br />
 <input type="text" name="listname" maxlength="255" />
 <input type="submit" name="action" value="Add New Mailing List" />
</p>

<p>
 Delete Mailing List:<br />
 <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>
 <input type="submit" name="action" value="Delete Mailing List" />
</p>

</form>

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

</body>
</html>

quickmsg.php - Try It Out 1

<?php
require('config.php');
?>

<html>
<head>
<title>Quick Message</title>
</head>
<body>

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

<p>
 Choose Mailing List:<br />
 <select name="ml_id">
  <option value="all">All</option>
<?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>
</p>

<p>Compose Message:</p>

<p>
 Subject:<br />
 <input type="text" name="subject" />
</p>

<p>
 Message:<br />
 <textarea name="msg" rows="10" cols="60"></textarea>
</p>

<p>
 <input type="submit" name="action" value="Send Message" />
</p>

</form>

<p>
 <a href="admin.php">Back to mailing list administration</a>
</p>

</body>
</html>

admin_transact.php - Try It Out 1

<?php
require('config.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);

if (isset($_POST['action']))
{
 switch ($_POST['action'])
 {
  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':
   $sql = "DELETE FROM ml_lists WHERE ml_id=" . $_POST['ml_id'];
   mysql_query($sql)
    or die('Could not delete mailing list. ' . mysql_error());
	$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;

  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";
    }

    $sql = "SELECT DISTINCT usr.e-mail, usr.firstname, usr.user_id ".
        "FROM ml_users usr " .
        "INNER JOIN ml_subscriptions mls " .
        "ON usr.user_id = mls.user_id " .
        "WHERE mls.pending=0";
    if ($_POST['ml_id'] != 'all')
    {
     $sql .= " AND mls.ml_id=" . $_POST['ml_id'];
    }

    $result = mysql_query($sql) or die('Could not get list of
       e-mail addresses. ' . mysql_error());

    $headers = "From: " . ADMIN_E-MAIL . "\r\n";
    while ($row = mysql_fetch_array($result))
    {
    if (is_numeric($_POST['ml_id'])) {
     $ft = " You are receiving this message as a member of the ";
     $ft .= $listname . "\n mailing list. If you have received";
     $ft .= "this e-mail in error, or would like to\n remove your";
     $ft .= "name from this mailing list, please visit the";
     $ft .= "following URL:\n";
     $ft .= " http://" . $_SERVER['HTTP_HOST'] .
        dirname($_SERVER['PHP_SELF']) . "/remove.php?u=" .
        $row['user_id'] . "&ml=" . $_POST['ml_id'];
    } else {
     $ft = " You are receiving this e-mail because you subscribed";
     $ft .= " to one or more\n mailing lists. Visit the following";
     $ft .= " URL to change your subscriptions:\n";
     $ft .= " http://" . $_SERVER['HTTP_HOST'] .
        dirname($_SERVER['PHP_SELF']) . "/user.php?u=" .
        $row['user_id'];
    }
     $msg = stripslashes($_POST['msg']) . "\n\n";
     $msg .= "--------------\n";
     $msg .= $ft;

     mail($row['e-mail'],
        stripslashes($_POST['subject']),
        $msg,$headers) or die('Could not send e-mail.');
    }
   }
   break;
 }
}

header('Location: admin.php');

?>

Try It Out 2

user.php - Try It Out 2

<?php
require('config.php');
?>

<html>
<head>
<title>Mailing List Signup</title>
</head>
<body>

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

<p>
 Sign up for Mailing List:<br />
</p>

<?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);

if (isset($_GET['u']))
{
 $uid = $_GET['u'];
 $sql = "SELECT * FROM ml_users WHERE user_id = '$uid';";
 $result = mysql_query($sql)
  or die('Invalid query: ' . mysql_error());
 if (mysql_num_rows($result)) {
  $row = mysql_fetch_array($result);
  $e-mail = $row['e-mail'];
 } else {
  $e-mail = "";
 }
}
?>

<p>
 E-mail Address:<br />
 <input type="text" name="e-mail" size="40" value="<?php echo
    $e-mail;?>"/>
</p>

<p>
 If you aren't currently a member, please provide
 your name:<br /><br />
 First Name:<br />
 <input type="text" name="firstname" /><br />
 Last Name:<br />
 <input type="text" name="lastname" /><br />
</p>

<p>
 Select the mailing lists you want to receive:<br />
 <select name="ml_id">

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

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

?>
 </select>
</p>

<p>
 <input type="submit" name="action"
    value="Subscribe" />
</p>

</form>

</body>
</html>

user_transact.php - Try It Out 2

<?php
require('config.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);

if (isset($_REQUEST['action']))
{
 $headers = "From: " . ADMIN_E-MAIL . "\r\n";
 switch ($_REQUEST['action'])
 {
  case 'Remove':
   $sql = "SELECT user_id FROM ml_users " .
       "WHERE e-mail='" . $_POST['e-mail'] . "';";
   $result = mysql_query($sql,$conn);

   if (mysql_num_rows($result))
   {
    $row = mysql_fetch_array($result);
    $user_id = $row['user_id'];
    $url = "http://" . $_SERVER['HTTP_HOST'] .
       dirname($_SERVER['PHP_SELF']) .
       "/remove.php?u=" . $user_id .
       "&ml=" . $_POST['ml_id'];
    header("Location: $url");
    exit();
   }
   $redirect = 'user.php';
   break;
  case 'Subscribe':
   $sql = "SELECT user_id FROM ml_users " .
       "WHERE e-mail='" . $_POST['e-mail'] . "';";
   $result = mysql_query($sql,$conn);

   if (!mysql_num_rows($result))
   {
    $sql = "INSERT INTO ml_users " .
        "(firstname,lastname,e-mail) ".
        "VALUES ('" . $_POST['firstname'] . "'," .
        "'" . $_POST['lastname'] . "'," .
        "'" . $_POST['e-mail'] . "');";
    $result = mysql_query($sql, $conn);
    $user_id = mysql_insert_id($conn);
   }
   else
   {
    $row = mysql_fetch_array($result);
    $user_id = $row['user_id'];
   }

   $sql = "INSERT INTO ml_subscriptions (user_id,ml_id) " .
       "VALUES ('" . $user_id . "','" . $_POST['ml_id'] . "')";
   mysql_query($sql,$conn);

   $sql = "SELECT listname FROM ml_lists " .
       "WHERE ml_id=" . $_POST['ml_id'];
   $result = mysql_query($sql,$conn);
   $row = mysql_fetch_array($result);
   $listname = $row['listname'];

   $url = "http://" . $_SERVER['HTTP_HOST'] .
      dirname($_SERVER['PHP_SELF']) .
      "/user_transact.php?u=" . $user_id .
      "&ml=" . $_POST['ml_id'] . "&action=confirm";

   $subject = 'Mailing list confirmation';
   $body = "Hello " . $_POST['firstname'] . "\n" .
      "Our records indicate that you have subscribed to the " .
      $listname . " mailing list.\n\n" .
      "If you did not subscribe, please accept our apologies. " .
      "You will not be subscribed if you do not visit the " .
      "confirmation URL.\n\n" .
      "If you subscribed, please confirm this by visiting the " .
      "following URL:\n" . $url;

   mail($_POST['e-mail'],$subject,$body,$headers);

   $redirect = "thanks.php?u=" . $user_id . "&ml=" .
      $_POST['ml_id'] . "&t=s";
   break;


  case 'confirm':

   if (isset($_GET['u']) & isset($_GET['ml']))
   {
    $sql = "UPDATE ml_subscriptions SET pending=0 " .
        "WHERE user_id=" . $_GET['u'] .
        " AND ml_id=" . $_GET['ml'];
    mysql_query($sql, $conn);

    $sql = "SELECT listname FROM ml_lists " .
        "WHERE ml_id=" . $_GET['ml'];
    $result = mysql_query($sql,$conn);

    $row = mysql_fetch_array($result);
    $listname = $row['listname'];

    $sql = "SELECT * FROM ml_users " .
        "WHERE user_id='" . $_GET['u'] . "';";
    $result = mysql_query($sql,$conn);
    $row = mysql_fetch_array($result);
    $firstname = $row['firstname'];
    $e-mail = $row['e-mail'];

    $url = "http://" . $_SERVER['HTTP_HOST'] .
        dirname($_SERVER['PHP_SELF']) .
        "/remove.php?u=" . $_GET['u'] .
        "&ml=" . $_GET['ml'];

    // Send out confirmed e-mail
    $subject = 'Mailing List Subscription Confirmed';
    $body = "Hello " . $firstname . ",\n" .
       "Thank you for subscribing to the " .
       $listname . " mailing list. Welcome!\n\n" .
       "If you did not subscribe, please accept our apologies.\n".
       "You can remove this subscription immediately by ".
       "visiting the following URL:\n" . $url;

    mail($e-mail,$subject,$body,$headers);

    $redirect = "thanks.php?u=" . $_GET['u'] . "&ml=" .
       $_GET['ml'] . "&t=s";
   } else {
    $redirect = 'user.php';
   }
   break;

  default:
   $redirect = 'user.php';
 }
}

header('Location: ' . $redirect);

?>

thanks.php - Try It Out 2

<?php
require('config.php');
?>

<html>
<head>
<title>Thank You</title>
</head>
<body>

<?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);

if (isset($_GET['u']))
{
 $uid = $_GET['u'];
 $sql = "SELECT * FROM ml_users WHERE user_id = '$uid';";
 $result = mysql_query($sql)
  or die('Invalid query: ' . mysql_error());
 if (mysql_num_rows($result)) {
  $row = mysql_fetch_array($result);
  $msg = "<h2>Thank You, " . $row['firstname'] . "</h2><br /><br />";
  $e-mail = $row['e-mail'];
 } else {
  die("No match for user id " . $uid);
 }
}

if (isset($_GET['ml']))
{
 $ml_id = $_GET['ml'];
 $sql = "SELECT * FROM ml_lists WHERE ml_id = '" . $ml_id . "';";
 $result = mysql_query($sql)
  or die('Invalid query: ' . mysql_error());
 if (mysql_num_rows($result)) {
  $row = mysql_fetch_array($result);
  $msg .= "Thank you for subscribing to the <i>" .
     $row['listname'] . "</i> mailing list.<br />";
 } else {
  die ("Could not find Mailing List $ml_id");
 }
} else {
 die ("Mailing List id missing.");
}

if (!isset($_GET['t'])) die("Missing Type");
switch ($_GET['t'])
{
 case 'c':
  $msg .= "A confirmation request has been sent " .
      "to <b>$e-mail</b>.<br /><br />";
  break;
 case 's':
  $msg .= "A subscription notification has been " .
      "sent to you at <b>$e-mail</b>.<br /><br />";
}
$msg .= "<a href='user.php?u=$uid'>" .
    "Return to Mailing List Signup page</a>";
echo $msg;
?>

</body>
</html>

remove.php - Try It Out 2

<?php
require('config.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);

if ((isset($_GET['u'])) and (isset($_GET['ml'])))
{
 $sql = "DELETE FROM ml_subscriptions " .
     "WHERE user_id=" . $_GET['u'] .
     " AND ml_id=" . $_GET['ml'];
 $result = mysql_query($sql,$conn);
} else {
 die("Incorrect parameters passed for deletion");
}

if ($result) {
 $msg = "<h2>Removal Successful</h2>";
} else {
 $msg = "<h2>Removal Failed</h2>";
}

$ml_id = $_GET['ml'];
$sql = "SELECT * FROM ml_lists WHERE ml_id = '" . $ml_id . "';";
$result = mysql_query($sql)
 or die('Invalid query: ' . mysql_error());
if (mysql_num_rows($result)) {
 $row = mysql_fetch_array($result);
 $msg .= "You have been removed from the <i>" .
     $row['listname'] . "</i> mailing list.<br />";
} else {
 $msg .= "Sorry, could not find Mailing List id#{$ml_id}");
}

$msg .= "<a href='user.php?u=" . $_GET['u'] .
    "'>Return to Mailing List Signup page</a>";
echo $msg;
?>

Beginning PHP, Apache, MySQL Web Development

Chapter 13 Code - Mailing Lists

Try It Out 1

Try It Out 2