Newsletter Email Subscription with PHP and MySQL

newsletter subscription is a commonly used functionality in the website or blog. An email subscription provides an option for the visitor to receive updates via email. The email subscription feature is the most important part of email marketing. Most of the website provides an email subscription option that allows the site visitors to get updates daily or weekly newsletter from the website.

In newsletter subscription, the updates from the website are sent to the subscriber’s email. The email subscription functionality can be easily implemented with PHP. In this tutorial, we will show you how to implement the email subscription functionality in the web application using PHP and MySQL.

In the example email subscription script, the following functionality will be implemented.

  • Create a subscription form with HTML.
  • Add subscriber info to the database with PHP and MySQL.
  • Send the verification link via email.
  • Verify the subscriber with email.

Before getting started to build email subscription functionality with PHP, take a look at the file structure.

email_subscription_with_php/
├── config.php
├── index.html
├── subscription.php
├── Subscriber.class.php
├── js/
│   └── jquery.min.js
└── css/
    └── style.css

Create Database Table

To store the subscriber info, a table is required in the database. The following SQL creates a subscribers table in the MySQL database.

CREATE TABLE `subscribers` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
 `email` varchar(200) COLLATE utf8_unicode_ci NOT NULL,
 `verify_code` varchar(150) COLLATE utf8_unicode_ci DEFAULT NULL,
 `is_verified` tinyint(1) NOT NULL DEFAULT '0',
 `created` datetime NOT NULL,
 `modified` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
 `status` tinyint(1) NOT NULL DEFAULT '1',
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; 

Subscriber Class

The Subscriber class help to connect with the database and handles the database related operations using PHP and MySQL.

  • __construct() – Connect with the MySQL database.
  • getRows() – Fetch the records from the database.
  • insert() – Insert record in the database.
  • update() – Update record in the database.
  • delete() – Delete records from the database.
<?php 
/*
 * Subscriber Class
 * This class is used for database related (connect, fetch, insert, update, and delete) operations
 * @author    semicolonworld.com
 * @url        http://www.semicolonworld.com
 * @license    http://www.semicolonworld.com/license
 */

class Subscriber {
    private 
$dbHost     DB_HOST;
    private 
$dbUsername DB_USERNAME;
    private 
$dbPassword DB_PASSWORD;
    private 
$dbName     DB_NAME;
    private 
$userTbl    'subscribers';
    
    function 
__construct(){
        if(!isset(
$this->db)){
            
// Connect to the database
            
$conn = new mysqli($this->dbHost$this->dbUsername$this->dbPassword$this->dbName);
            if(
$conn->connect_error){
                die(
"Failed to connect with MySQL: " $conn->connect_error);
            }else{
                
$this->db $conn;
            }
        }
    }
    
    
/*
     * Returns rows from the database based on the conditions
     * @param string name of the table
     * @param array select, where, order_by, limit and return_type conditions
     */
    
public function getRows($conditions = array()){
        
$sql 'SELECT ';
        
$sql .= array_key_exists("select",$conditions)?$conditions['select']:'*';
        
$sql .= ' FROM '.$this->userTbl;
        if(
array_key_exists("where",$conditions)){
            
$sql .= ' WHERE ';
            
$i 0;
            foreach(
$conditions['where'] as $key => $value){
                
$pre = ($i 0)?' AND ':'';
                
$sql .= $pre.$key." = '".$value."'";
                
$i++;
            }
        }
        
        if(
array_key_exists("order_by",$conditions)){
            
$sql .= ' ORDER BY '.$conditions['order_by']; 
        }else{
            
$sql .= ' ORDER BY id DESC '
        }
        
        if(
array_key_exists("start",$conditions) && array_key_exists("limit",$conditions)){
            
$sql .= ' LIMIT '.$conditions['start'].','.$conditions['limit']; 
        }elseif(!
array_key_exists("start",$conditions) && array_key_exists("limit",$conditions)){
            
$sql .= ' LIMIT '.$conditions['limit']; 
        }
        
        
$result $this->db->query($sql);
        
        if(
array_key_exists("return_type",$conditions) && $conditions['return_type'] != 'all'){
            switch(
$conditions['return_type']){
                case 
'count':
                    
$data $result->num_rows;
                    break;
                case 
'single':
                    
$data $result->fetch_assoc();
                    break;
                default:
                    
$data '';
            }
        }else{
            if(
$result->num_rows 0){
                while(
$row $result->fetch_assoc()){
                    
$data[] = $row;
                }
            }
        }
        return !empty(
$data)?$data:false;
    }
    
    
/*
     * Insert data into the database
     * @param string name of the table
     * @param array the data for inserting into the table
     */
    
public function insert($data){
        if(!empty(
$data) && is_array($data)){
            
$columns '';
            
$values  '';
            
$i 0;
            if(!
array_key_exists('created',$data)){
                
$data['created'] = date("Y-m-d H:i:s");
            }
            if(!
array_key_exists('modified',$data)){
                
$data['modified'] = date("Y-m-d H:i:s");
            }
            foreach(
$data as $key=>$val){
                
$pre = ($i 0)?', ':'';
                
$columns .= $pre.$key;
                
$values  .= $pre."'".$this->db->real_escape_string($val)."'";
                
$i++;
            }
            
$query "INSERT INTO ".$this->userTbl." (".$columns.") VALUES (".$values.")";
            
$insert $this->db->query($query);
            return 
$insert?$this->db->insert_id:false;
        }else{
            return 
false;
        }
    }
    
    
/*
     * Update data into the database
     * @param string name of the table
     * @param array the data for updating into the table
     * @param array where condition on updating data
     */
    
public function update($data$conditions){
        if(!empty(
$data) && is_array($data)){
            
$colvalSet '';
            
$whereSql '';
            
$i 0;
            if(!
array_key_exists('modified',$data)){
                
$data['modified'] = date("Y-m-d H:i:s");
            }
            foreach(
$data as $key=>$val){
                
$pre = ($i 0)?', ':'';
                
$colvalSet .= $pre.$key."='".$this->db->real_escape_string($val)."'";
                
$i++;
            }
            if(!empty(
$conditions)&& is_array($conditions)){
                
$whereSql .= ' WHERE ';
                
$i 0;
                foreach(
$conditions as $key => $value){
                    
$pre = ($i 0)?' AND ':'';
                    
$whereSql .= $pre.$key." = '".$value."'";
                    
$i++;
                }
            }
            
$query "UPDATE ".$this->userTbl." SET ".$colvalSet.$whereSql;
            
$update $this->db->query($query);
            return 
$update?$this->db->affected_rows:false;
        }else{
            return 
false;
        }
    }
    
    
/*
     * Delete data from the database
     * @param string name of the table
     * @param array where condition on deleting data
     */
    
public function delete($conditions){
        
$whereSql '';
        if(!empty(
$conditions)&& is_array($conditions)){
            
$whereSql .= ' WHERE ';
            
$i 0;
            foreach(
$conditions as $key => $value){
                
$pre = ($i 0)?' AND ':'';
                
$whereSql .= $pre.$key." = '".$value."'";
                
$i++;
            }
        }
        
$query "DELETE FROM ".$this->userTbl.$whereSql;
        
$delete $this->db->query($query);
        return 
$delete?true:false;
    }
}

Site Settings and Database Configuration (config.php)

The basic site settings and database configuration variables are defined in this file.

  • $siteName – Name of the site
  • $siteEmail – Sender email
  • DB_HOST – Database host
  • DB_USERNAME – Database username
  • DB_PASSWORD – Database password
  • DB_NAME – Database name
<?php 
/*
 * Basic Site Settings and Database Configuration
 */

// Site Settings
$siteName 'Demo Site';
$siteEmail '[email protected]';

$siteURL = ($_SERVER["HTTPS"] == "on")?'https://':'http://';
$siteURL $siteURL.$_SERVER["SERVER_NAME"].dirname($_SERVER['REQUEST_URI']).'/';

// Database configuration
define('DB_HOST''MySQL_Database_Host'); 
define('DB_USERNAME''MySQL_Database_Username'); 
define('DB_PASSWORD''MySQL_Database_Password'); 
define('DB_NAME''MySQL_Database_Name'); 

Email Subscription Form

Create an HTML form to receive input (name and email) from the visitor.

<!-- Display form submission status -->
<div class="status"></div>

<!-- Subscription form -->
<form action="#" id="subsFrm" method="post">
    <input type="text" id="name" placeholder="Full Name" required="">
    <input type="email" id="email" placeholder="E-mail" required="">
    <input type="button" id="subscribeBtn" value="Subscribe Now">
</form>

Subscription Form Submission

The subscription form will be submitted to the server-side script without page refresh.

The Ajax is used to post the form data, so include the jQuery library first.

<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.4.1/jquery.min.js"></script>

On submission, the form data is submitted using jQuery and Ajax.

  • The name and email inputs are validated with Regex using jQuery.
  • The form data is posted to the server-side script (subscription.php) via $.ajax() method.
  • Based on the response, the status is shown to the user.
<script>
$(document).ready(function(){
    $('#subscribeBtn').on('click', function(){
        // Remove previous status message
        $('.status').html('');
		
        // Email and name regex
        var regEmail = /^[A-Z0-9._%+-][email protected]([A-Z0-9-]+\.)+[A-Z]{2,4}$/i;
        var regName = /^[a-zA-Z]+ [a-zA-Z]+$/;
		
        // Get input values
        var name = $('#name').val();
        var email = $('#email').val();
		
        // Validate input fields
        if(name.trim() == '' ){
            alert('Please enter your name.');
            $('#name').focus();
            return false;
        }else if (!regName.test(name)){
            alert('Please enter a valid name (first & last name).');
            $('#name').focus();
            return false;
        }else if(email.trim() == '' ){
            alert('Please enter your email.');
            $('#email').focus();
            return false;
        }else if(email.trim() != '' && !regEmail.test(email)){
            alert('Please enter a valid email.');
            $('#email').focus();
            return false;
        }else{
            // Post subscription form via Ajax
            $.ajax({
                type:'POST',
                url:'subscription.php',
                dataType: "json",
                data:{subscribe:1,name:name,email:email},
                beforeSend: function () {
                    $('#subscribeBtn').attr("disabled", "disabled");
                    $('.content-frm').css('opacity', '.5');
                },
                success:function(data){
                    if(data.status == 'ok'){
                        $('#subsFrm')[0].reset();
                        $('.status').html('<p class="success">'+data.msg+'</p>');
                    }else{
                        $('.status').html('<p class="error">'+data.msg+'</p>');
                    }
                    $('#subscribeBtn').removeAttr("disabled");
                    $('.content-frm').css('opacity', '');
                }
            });
        }
    });
});
</script>

Insert Subscriber Info in Database (subscription.php)

If email subscription request (subscribe) is received:

  • Retrieve form data using $_POST method in PHP
  • Server-side validation for the form input fields with PHP.
  • Check whether the given email already exists using the getRows() function of the Subscriber class.
  • Insert subscriber info in the database using the insert() function of the Subscriber class.
  • Send verification email using PHP.
  • Return response in JSON encoded format.
<?php 
// Include config file
require_once 'config.php';

// Include Subscriber class
require_once 'Subscriber.class.php';
$subscriber = new Subscriber();

if(isset(
$_POST['subscribe'])){
    
$errorMsg '';
    
// Default response
    
$response = array(
        
'status' => 'err',
        
'msg' => 'Something went wrong, please try after some time.'
    
);
    
    
// Input fields validation
    
if(empty($_POST['name'])){
        
$pre = !empty($msg)?'<br/>':'';
        
$errorMsg .= $pre.'Please enter your full name.';
    }
    if(empty(
$_POST['email']) && !filter_var($_POST['email'], FILTER_VALIDATE_EMAIL)){
        
$pre = !empty($msg)?'<br/>':'';
        
$errorMsg .= $pre.'Please enter a valid email.';
    }
    
    
// If validation successful
    
if(empty($errorMsg)){
        
$name $_POST['name'];
        
$email $_POST['email'];
        
$verify_code md5(uniqid(mt_rand()));
        
        
// Check whether the given email already exists
        
$con = array(
            
'where' => array(
                
'email' => $email
            
),
            
'return_type' => 'count'
        
);
        
$prevRow $subscriber->getRows($con);
        
        if(
$prevRow 0){
            
$response['msg'] = 'Your email already exists in our subscribers list.';
        }else{
            
// Insert subscriber info
            
$data = array(
                
'name' => $name,
                
'email' => $email,
                
'verify_code' => $verify_code
            
);
            
$insert $subscriber->insert($data);
            
            if(
$insert){
                
// Verification email configuration
                
$verifyLink $siteURL.'subscription.php?email_verify='.$verify_code;
                
$subject 'Confirm Subscription';
    
                
$message '<h1 style="font-size:22px;margin:18px 0 0;padding:0;text-align:left;color:#3c7bb6">Email Confirmation</h1>
                <p style="color:#616471;text-align:left;padding-top:15px;padding-right:40px;padding-bottom:30px;padding-left:40px;font-size:15px">Thank you for signing up with '
.$siteName.'! Please confirm your email address by clicking the link below.</p>
                <p style="text-align:center;">
                    <a href="'
.$verifyLink.'" style="border-radius:.25em;background-color:#4582e8;font-weight:400;min-width:180px;font-size:16px;line-height:100%;padding-top:18px;padding-right:30px;padding-bottom:18px;padding-left:30px;color:#fffffftext-decoration:none">Confirm Email</a>
                </p>
                <br><p><strong>'
.$siteName.' Team</strong></p>';
                
                
$headers "MIME-Version: 1.0" "\r\n"
                
$headers .= "Content-type:text/html;charset=UTF-8" "\r\n"
                
$headers .= "From: $siteName"." <".$siteEmail.">";
                
                
// Send verification email
                
$mail mail($email$subject$message$headers);
                
                if(
$mail){
                    
$response = array(
                        
'status' => 'ok',
                        
'msg' => 'A verification link has been sent to your email address, please check your email and verify.'
                    
);
                }
            }
        }
    }else{
        
$response['msg'] = $errorMsg;
    }
    
    
// Return response
    
echo json_encode($response);
}
?>

Email Verification (subscription.php)

If the email verification request (email_verify) is received:

  • Verify the subscriber by comparing the verification code.
  • Update verification status in the database update() function of the Subscriber class.
  • Display the email verification status.
<?php 
// Include config file
require_once 'config.php';

// Include Subscriber class
require_once 'Subscriber.class.php';
$subscriber = new Subscriber();

if(!empty(
$_GET['email_verify'])){
    
$verify_code $_GET['email_verify'];
    
$con = array(
        
'where' => array(
            
'verify_code' => $verify_code
        
),
        
'return_type' => 'count'
    
);
    
$rowNum $subscriber->getRows($con);
    if(
$rowNum 0){
        
$data = array(
            
'is_verified' => 1
        
);
        
$con = array(
            
'verify_code' => $verify_code
        
);
        
$update $subscriber->update($data$con);
        if(
$update){
            
$statusMsg '<p class="success">Your email address has been verified successfully.</p>';
        }else{
            
$statusMsg '<p class="error">Some problem occurred on verifying your email, please try again.</p>';
        }
    }else{
        
$statusMsg '<p class="error">You have clicked on the wrong link, please check your email and try again.</p>';
    }
?> <!DOCTYPE html> <html lang="en"> <head> <title>Email Verification by semicolonworld</title> <meta name="viewport" content="width=device-width, initial-scale=1"> <meta charset="utf-8"> <!-- web-fonts --> <link href="//fonts.googleapis.com/css?family=Raleway:100,100i,200,200i,300,300i,400,400i,500,500i,600,600i,700,700i,800,800i,900,900i&amp;subset=latin-ext" rel="stylesheet"> <!-- Stylesheet file --> <link rel="stylesheet" type="text/css" href="css/style.css" /> </head> <body class="subs"> <div class="container"> <div class="subscribe box-sizing"> <div class="sloc-wrap box-sizing"> <div class="sloc-content"> <div class="sloc-text"> <div class="sloc-header"><?php echo $statusMsg?></div> </div> <a href="<?php echo $siteURL?>" class="cwlink">Go to Site</a> </div> </div> </div> </div> </body> </html> <?php
}
?>

Related Articles

Comments 0