Editors Note: This article originally appeared in three parts with the fourth and final installation yet to be written. Please keep this in mind as you read this article. We will publish the final piece of the article when it becomes available.

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.

In the previous article [http://www.bytesworth.com/learn/php00001.php] 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:

<.script language="javascript" type="text/javascript">
function validate() { var res; res=true; if(document.toSub.email.value.length==0 || document.toSub.email.value==null) { alert("You cannot subscribe without submitting an email address."; res=false; document.toSub.email.focus(); } return res; }
</script>

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.

<?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 . ".rnrn"; $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 article 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 in http://www.bytesworth.com/learn/php00002.asp. 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, go to http://www.bytesworth.com/learn/html00009.asp 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.

No comments yet. Be the first one to comment.