Publishing Newsletter Using PHP & MySQL

by Amrit Hallan
Thursday, 18th August 2005

Are you tired of using third-party tools to send out routine notifications and emails to your clients? Or, do you want to send your newsletters with individual names of people in the beginning. This series of articles helps you achieve this.

In the first article, you'll learn to create an online subscription form that stores names and emails of people who want to receive your newsletter.

If you are hosting your website on a server that supports PHP and MySQL, through your control panel, you can access an area where you can create either a new database if it doesn't exist, or create a new database. Most servers have the phpMyAdmin interface that lets you create and maintain database without having to learn MySQL commands. Even if you can't make sense of the interface, try to locate a section that allows you to run SQL commands. Run the following command to create the new database:

CREATE DATABASE database_name

In place of "database_name", use the name of your choice. In this case, I'll use "newslet" so that we have:

CREATE DATABASE newslet

A single database can have many tables. Tables are logical structures that contain the rows and columns that store the data. Once our database is created, use the following command to create the table "subscribers":

CREATE TABLE subscribers (
s_name TEXT NOT NULL,
s_email VARCHAR(255) UNIQUE NOT NULL)

This command creates the table with fields "s_name" (name of the subscriber) and "s_email" (email of the subscriber). We make the email field unique so that we don't accept the same email again and again.

So our database is crisp ready to be used. In my next article, I'll tell you how to create an online form that accepts name and email, perform a bit of validation, and then store the details into the database.

On the previous page we learnt how to create a database and then a "subscribers" table where we'll store all the names and emails of our subscribers who'll receive the newsletter.

In this article we'll make a form that accepts name and email. There'll be a JavaScript to check if valid email has been entered. Let me point out that the validation here is just going to be for illustration purpose. Someday we'll discuss a complicated script to check valid email addresses.

We'll add another field to the "subscribers" table, named "active". This field will have either 0 or 1. We want to make our newsletter list "double opt-in". When we save the record for the first time, we set active=0. After saving the record, an email will be generated that will contain a Thank You message as well a confirmation link. The confirmation link will first check for the existence of the email address, and if found, will set active=1. Sounds confusing? By the time we're through with the article, everything will be crystal clear.

In this article, we'll read till the generation of the email message. In the next article, we'll see how that email is validated.

So let us first alter the table to accommodate our "active" field.

ALTER TABLE subscribers ADD COLUMN active TINYINT NOT NULL

Now we have the requisite table definition to begin.

First we make the form that accepts the name and the email:

<form name="toSub" method="post" action="subs.php" onSubmit="return validate(this);">
<p>Name: <input type="text" name="name" size="15" /><br />
Email: <input type="text" name="email" size="15" /><br />
<input type="submit" name="s1" value="Subscribe" />
</form>

The validation JavaScript follows:

And now the PHP file -- subs.php -- that saves the submitted information.

First we need to setup the database connection. As you can recall from the first page, we created the database called newslet. I'll be putting extra linespaces between the code just to make it clear, you don't need to do that while writing your own applications.

<?php

$user_name="your_user_name";
$pwd="your_password";

$db=mysql_connect("localhost", $user_name, $pwd) or die("I cannot connect to the database because " . mysql_error());

?>

The code above lets you connect to your database server. There is nothing to worry about the ominous sounding "die" function -- it just generates an error if the MySQL command fails to carry out amicably. And now the connection to the database and the subsequent query to add the record.

<?php
mysql_select_db("newslet", $db);

if(strlen($name)==0)
{
$name="Friend";
}
// In case name was not entered.
$query="insert into subscribers (email, name, active) values ('" . $email . "', '" . $name . "', 0)";

//Notice the single quotes that enclose
// email and name in the above
// SQL query.

$result=mysql_query($query) or die(mysql_error());
?>

Here mysql_error() throws up an error if there are duplicate entries.

According to the error status, we execute the following code:

<?php
if(!mysql_error())
// No error
{
   $tbody="Dear " . $name . ".\r\n\r\n";
   $tbody.="Please click on http://www.yoursite.com/vemail.php?vmail=" . $email . ".";

   $headers="From: \"Your name\" <Your email>";
   $subject="Please verify!";

   mail($email, $subject, $tbody, $headers);
}
?>

The above code sends the email to the subscriber for verification. In the next article, you'll see how this email actually gets verified and the verified data is then eventually stored to send the newsletter.

By the end of the previous page we sent a verification email to whomever submits the subscription form. The email should contain the following message:

"Please click on http://www.yoursite.com/vemail.php?vmail=name@somesite.com."

If the person clicks on the link, the subsequent procedure verifies the email. So now let us write vemail.php.

<?php

$user_name="your_user_name";
$pwd="your_password";

$db=mysql_connect("localhost", $user_name, $pwd) or die("I cannot connect to the database because " . mysql_error());

mysql_select_db("newslet", $db);
?>

The above code has already been explained on the second page of this tutorial. If the person actually submitted the email, this email should be there in the database with the field active set to 0. The following commands set active to 1 if the email "name@somesite.com" exists in the table "subscribers".

<?php
$email=$_GET[vmail];
?>

This command fetches the "GET" variable from the URL. If you are used to fiddling with forms you must be femiliar with using method="post" and method="get". If not, read the User Feedback HTML Form tutorial for a proper enlightenment on the subject. We move forward.

<?php
$query="update subscribers set active=1 where email='" . $email . "'";
?>

See that you enclose email in single quotes.

<?php
$result=mysql_query($query);
?>

Whenever an SQL query is executed (the Talibanis just executed 8 Pakistani soldiers, so I find this word very violent), it affects one or more rows. If the email exists and if its respective active field is 0, then at least one row should be affected.

<?php
if(mysql_affected_rows()>0)
{
   echo "<h1>Congratulations!</h1>";
   echo "<p>Your email has been verified.</p>";
}
else
{
   echo "<p>Sorry! You have either already verified your email, or you haven't submitted your details for verification. Please go to the form and submitted your details.</p>";
}
?>

This ends the verification. In the fourth (perhaps the final of this series) article, we'll see how the database is used to finally send the newsletter.

The time has arrived to launch your publishing career at last. The subscribers have subscribed and your emails have been validated. Now all you have to do is, write the newsletter, copy/pase it in the box and click the submit button.

We can publish text newsletter as well as HTML newsletter. We'll see how. First the form:

<form method="post" action="send_newsletter.php">
Subject:<br>
<input type="text" name="sub" size="40"><br>
Body<br>
<textarea cols="80" rows="20" name="bdy" wrap="hard"></textarea><br>
HTML: <input type="checkbox" name="html" value="0"><br>
<input type="submit" name="s1" value="Submit">
</form>

The form has three fields, namely, Subject, Body and a checkbox to decide whether you want to send a plain newsletter or an HTML newsletter. This form uses the file "send_newsletter.php" to send the newsletter. Here's send_newsletter.php:

<?php
$html=$_POST[html];

$user_name="your user name";
$pwd="your password";

$db=mysql_connect("localhost", $user_name, $pwd") or die("I cannot connect to the database because " . mysql_error());
mysql_select_db("newslet", $db);
$query="select name, email from subscribers where active=1";
$result=mysql_query($query);

$subject=$_POST[sub];
$tb=stripslashes($_POST[bdy]);

if($html==NULL)
{
   $headers="From: \"Newsletter Name\" <newsletter@yoursite.com>";
}
else
{
   $headers="From: \"newsletter Name\" <newsletter@yoursite.com>\n";
   $headers.="MIME-Version: 1.0\n";
   $headers.="Content-type: text/html; charset=iso-8859-1";
}
?>

Most of the initial code must be clear by now. If not, please refer to the links mentioned at the beginning of the article. We use the function stripslashes() to take care of all the characters that PHP finds unpalatable (such as double-quotes and single-quotes). The if condition checks whether you have selected the HTML checkbox. If yes, then the necessary encoding is added to the variable $headers.

<?php
while($row=mysql_fetch_row($result))
{
   $tbody="Dear " . $row[0] . ".\r\n\r\n";
   $tbody.=$tb;
   mail($row[1], $subject, $tbody, $headers);
}
echo "<h1>Done!</h1>";
?>

The while loop uses mysql_fetch_row() function to extract data from the array $result. Then for each record, an email is generated and sent to individual recipients.

This finishes the "Publishing Newsletter Using PhP & MySQL" series. I hope I was lucid enough to enable you to maintain your own newsletter. This was just a framework. I suggest you keep tweaking the code according to your requirement and taste. You are always welcome to write to me using the form given below.

Amrit Hallan is a freelance web designer. For all web site development and web promotion needs, you can get in touch with him at amrit@bytesworth.com . For further details, visit http://www.bytesworth.com You can subscribe to his newsletter [BYTESWORTH REACHOUT] on Web Designing Tips & Tricks by sending a blank email at bytesworth-subscribe@topica.com.