Publishing Newsletter Using PHP & MySQL – 2
In the previous article 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.
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” />
alert(“You cannot subscribe without submitting an email address.”;
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 http://www.bytesworth.com/learn/php00001.asp, 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.
$db=mysql_connect(“localhost”, $user_name, $pwd) or die(“I cannot connect to the database because ” .
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.
// 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:
// 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\r\n”;
$headers.=”ReplyTo: Your email\r\n”;
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.