I often get asked by my clients to provide in their website a feature to subscribe/unsubscribe to their newsletter. In simpler/coding words, allow website users to easily add or remove their emails from a database and provide an interface for the client to retrieve that information. Just to be clear, this script don't address the email sending phase, so far my clients always had their software to do this ( or i encourage them to get one). I added a little ajax in here, but you could of course remove this part and make it work in other way.
Ok, first of all, Lets take a look at the file structure of this mini project:
Code:
Configuration and Database connection:
/config.php
Mysql Class:
/mySQL.class.php
A script to process our data:
/subscribenews.php
The jQuery javascript framework:
/js/jquery.js
A function to send/receive data to/from subscribenews.php:
/js/ajax.js
Our site html page:
/index.html
An interface to retrieve emails form Database:
/get_emails.php
To start we gonna need to set up our database. Create a database or add to your existing database the following table:
Code:
# SQL to create emails table:
CREATE TABLE `emails` (
`email` varchar(60) NOT NULL,
PRIMARY KEY (`email`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
Pretty simple, a table with only one field where we gonna hold the addresses. Once we have that done we have to set our configuration file:
config.php:
PHP Code:
<?php
// We define the password thats gonna be provided
// to the client to fetch the list of emails from the database.
// Try to use a better password than 123 ;-)
define("SITE_PASSWORD", "123");
// Next, we set the database connection info:
$szHost = "127.0.0.1"; // Database Host
$szUser = "root"; // Database User
$szPass = "root"; // Database Pass
$szDataBase = "newsletter"; // Database Name
// Now we connect to the database
$connection = mysql_pconnect($szHost, $szUser, $szPass) or trigger_error(mysql_error(),E_USER_ERROR);
mysql_select_db($szDataBase, $connection);
?>
Following is our mySQL class file. Im not going to explain OOP, or get into detail of this class. No need to say you can use your own/preferred class or other method to handle your database duties:
mySQL.class.php:
PHP Code:
<?php
require_once('config.php');
class mySQL
{
public $result;
function __construct($szSQL = NULL)
{
global $szDataBase, $connection;
mysql_select_db($szDataBase, $connection);
if($szSQL)
$this->query($szSQL);
}
public function query($szSQL)
{
$this->result = mysql_query($szSQL) or die(mysql_error());
}
public function next_record()
{
return mysql_fetch_assoc($this->result);
}
public function load_array($szKeyfield)
{
$return = array();
while( $r = $this->next_record() )
array_push($return,$r[$szKeyfield]);
return $return;
}
public function num_rows()
{
return mysql_num_rows($this->result);
}
public function escape($value)
{
if (get_magic_quotes_gpc())
{
$value = stripslashes($value);
}
if (!is_numeric($value) || $value[0] == '0')
{
$value = mysql_real_escape_string($value);
}
return $value;
}
public function get_last_error()
{
mysql_error();
}
public function free()
{
mysql_free_result($this->result);
}
}
?>
Ok, here comes the fun part (?) :-),
subscribenews.php, the file thats gonna process the data coming from the website (an email address and the action to take: add or delete ).
subscribenews.php:
PHP Code:
<?php
// We include our mySQL class
include "mySQL.class.php";
// Right after that, the first thing we do is to run a
// regular expression to see if the email we are getting
// from the form is valid.
if (!preg_match('/^\w[\w+-]*(?:\.[\w+-]+)*@[a-z0-9-]+(?:\.[a-z0-9-])*\.[a-z]{2,4}$/i',$_GET['email']))
{
echo 'INVALID E-MAIL'; // if its not valid we echo the error.
}
// Next, if it is valid, we want to check
// we have an action to take:
else if( isset($_GET['delete']) || isset($_GET['add']) )
{
// If there is an action we create a new database object
$dbh = new mySQL();
// Paranoids as we have to be, we escape the
// email address that comes from the form
$szEmail = strtolower($dbh->escape($_GET['email']));
// Ok this is pretty simple, we ask if the variable $_GET['add'] is set:
if(isset($_GET['add']))
{
// If 'add' is set then we first run a query
// to check if that email is already in our database
$szResponse = $dbh->query(sprintf("SELECT email FROM emails WHERE email = '%s'", $szEmail));
$szStatus = $dbh->next_record();
// if the result is not empty then the email is already
// on the database so we echo and error
if( $szStatus != "" )
{
echo "E-mail already exists in database";
}
else
{
// else we insert the email and echo a success message.
$dbh->query(sprintf("INSERT INTO emails (email) VALUES ('%s')", $szEmail));
echo "E-mail added successfully";
}
}
else
{
// Now if the action is 'delete' then we run a query
// to delete the email from the database
$dbh->query(sprintf("DELETE FROM emails WHERE email = '%s'",$szEmail));
// we check the affected rows and give back our status message:
if( mysql_affected_rows() != 1)
echo "E-mail does not exist in database";
else
echo "E-mail deleted successfully";
}
}
else
{
echo 'UNKNOWN ERROR'; // In case something above goes wrong
}
Ok, now that we have that done, we gonna create our website form to allow users to interact with the script, so first download
jQuery and place it under /js folder, same place in which we are going to create our next file:
ajax.js
(Also not going to get into detail about jQuery, you can use whatever framework you preffer)
js/ajax.js
Code:
// pretty simple function, we pass an url, some data
// and the target where we want the action to happen
function request_action(jurl,jdata,jtarget)
{
$(jtarget).html('Processing request...');
$.ajax(
{
type: "GET",
url: jurl,
data: jdata,
success: function(data)
{
$(jtarget).html(data);
}
});
};
So now that we have those two files we add this code to our website:
index.html:
Quote:
note: this is a very simple/incomplete example of an html file but its to show you how this script works:
|
HTML Code:
<script src="js/jquery.js" type="text/javascript"></script>
<script src="js/ajax.js" type="text/javascript"></script>
<input id="email" name="email" type="text" />
<input id="subscribe" name="subscribe" type="button" onclick="request_action('subscribenews.php','add&email='+$('#email').val(),'#target')" value="Subscribe"/>
<input id="unsubscribe" name="unsubscribe" type="button" onclick="request_action('subscribenews.php','delete&email='+$('#email').val(),'#target')" value="Unsubscribe"/>
<div id="target"></div>
In the code above, when a button is clicked we are simply calling for request_action with some parameters: the url where the action is going to happen, the parameters ( add or delete, and the email address ) and last the target were our success message is going to be displayed.
If you followed all the steps, you should have now a working script to add or remove emails from the database with some ajax flavor. So to conclude the script, last but not least, we gonna create
get_emails.php, the interface for the client to retrieve the emails. This file will consist of a little form to ask for the password and a script to display the serialized emails.
get_emails:
PHP Code:
<?php
// We include our mySQL class
include "mySQL.class.php";
// if the form has been sent, we check the password
if($_POST['password'] == SITE_PASSWORD)
{
// If the password is correct then we return
// the emails from the database in an array
$dbh = new mySQL("SELECT email FROM emails ORDER BY email");
$aEmails = $dbh->load_array('email');
// We echo a little informative title
echo "<div><strong>Copy and Paste in your email app (".count($aEmails)." emails):</strong></div><br/>";
// then we echo the serialized list of emails:
echo implode(', ', $aEmails);
}
else
{
// If the password is wrong:
if( isset($_POST['password']) )
echo "<div>WRONG PASSWORD</div>";
?>
<form action="" method="post">
<label>Password:</label><br/><input name="password" type="password" value="">
<input name="ENTER" type="submit">
</form>
<? } ?>
That will give something like this:
which client can copy/paste into their email massive/sender/whatever application.
Well guys hope this help someone out there, and please as always, comments and critics are more than welcome.
