Build a Newsletter System With PHP and MySQL

Today, we are going to be building a newsletter system using PHP with a MySQL database. The tutorial will cover building a system that allows for multiple newsletter lists and the sending of messages to specific lists.

We are going to build a pretty complex newsletter system, so let's get started! You are going to need two resources for the project:

The Silk Icon set library will be used to add some visual flair to the application.
The Symfony PHP Mailer library will be used to send emails.

Create the Application Skeleton

Firstly, let's have a look at the overall project directory structure.

Go ahead and create a main project directory in the first place. Next, create a folder named admin within your project folder. Moving further, within the admin folder, include two sub-directories named media and swift. Additionally, create a new folder named images inside the media directory. It is recommended to position the Swift lib folder within the previously created swift folder. Moreover, make sure to copy the set of six silk icons that we will be using.

bullet_green.png
bullet_red.png
delete.png
email_go.png
find.png
page_edit.png

For the rest of the tutorial, we'll be mostly developing CRUD functionalities (create, read, update, and delete).

Application Configuration

To easily manage application configuration throughout the PHP code, let's create a global configuration file, which defines constants that can be used throughout the application. We'll name it admin/config.php.

<?php  
session_start(); 
require_once 'database.php';

$mini = false;
$nonav = false;

error_reporting(0);

define('DB_SERVER', 'localhost');
define('DB_USER', 'root');
define('DB_PASSWORD', ''); 
define('DB_NAME', 'tutsplus_ns_demo');
define('FROM_EMAIL', 'no_reply@ohyeahemail.com');
define('FROM_NAME', 'oh yeah email!');

$db = new Database(DB_SERVER, DB_USER, DB_PASSWORD, DB_NAME);

As you can see, we've defined constants related to the database settings and email configuration. Make sure you change the above settings as per your environment. So that's it for creating the global configuration file. We've also included the database class file, which we'll implement in a moment.

The Database Class

In this section, we'll implement the Database class, which helps us to perform different types of database operations that we are going to use in our newsletter application.

Let's have a look at the admin/database.php file.

<?php
class Database {
    private $connection;

    public function __construct($db_server, $db_user, $db_password, $db_name) 
    {
        $this->connection = new mysqli($db_server, $db_user, $db_password, $db_name);

        if ($this->connection->connect_error) {
            die('There was a problem connecting to the database.');
        }
    }

    public function validateUser($username, $pw) 
    {
        if ($this->checkUsernameAndPassword($username, $pw)) {
            return true;
        } else {
            $_SESSION['error'] = "Login error.";
            return false;
        }
    }

    public function loggedIn() 
    {
        if ($_SESSION['authorized'] == true) {
            return true;
        } else {
            return false;
        }
    }

    public function loginRequired() 
    {
        if ($this->loggedIn()) {
            return true;
        } else {
            return false;
        }
    }

    public function query($sql) 
    {
        $stmt = $this->connection->prepare($sql);
        $stmt->execute();
        $meta = $stmt->result_metadata();

        $parameters = [];
        while ($field = $meta->fetch_field()) {
            $parameters[] = &$row[$field->name];
        }

        $results = [];
        call_user_func_array([$stmt, 'bind_result'], $parameters);

        while ($stmt->fetch()) {
            $x = [];
            foreach ($row as $key => $val) {
                $x[$key] = $val;
            }
            $results[] = $x;
        }

        $stmt->close();
        return $results;
    }

    public function countQuery($query) 
    {
        if ($stmt = $this->connection->prepare($query)) {
            $stmt->execute();
            $stmt->bind_result($result);
            $stmt->fetch();
            $stmt->close();
        }
    }

    public function insertQuery($data, $table)
    {
        $columns = implode(', ', array_keys($data));
        $placeholders = implode(', ', array_fill(0, count($data), '?'));

        $query = "INSERT INTO $table ($columns) VALUES ($placeholders)";
        
        if ($stmt = $this->connection->prepare($query)) {
            $stmt->bind_param(str_repeat('s', count($data)), ...array_values($data));
            $stmt->execute();
            $stmt->close();
        }
    }

    public function updateQuery($data, $id, $table)
    {
        $setStatements = implode('=?, ', array_keys($data)) . '=?';
        $query = "UPDATE $table SET $setStatements WHERE id=?";

        $types = str_repeat('s', count($data)) . 'i';
        $values = array_merge(array_values($data), [$id]);

        if ($stmt = $this->connection->prepare($query)) {
            $stmt->bind_param($types, ...$values);
            $stmt->execute();
            $stmt->close();
        }
    }

    public function deleteQuery($table, $id)
    {
        $query = "DELETE FROM ? WHERE id=? LIMIT 1";
    
        if ($stmt = $this->connection->prepare($query)) {
            $stmt->bind_param('si', $table, $id);
            $stmt->execute();
            $stmt->close();
        }
    }
    
    public function checkUsernameAndPassword($u, $pw) 
    {
        $query = "SELECT * FROM users WHERE username = ? AND password = ? LIMIT 1";
        if ($stmt = $this->connection->prepare($query)) {
            $p = md5($pw);

            $stmt->bind_param('ss', $u, $p);
            $stmt->execute();
            $stmt->bind_result($id, $username, $pw);
            if ($stmt->fetch()) {
                $_SESSION['authorized'] = true;
                $_SESSION['username'] = $username;
                return true;
            } else {
                return false;
            }

            $stmt->close();
        }
    }

    public function __destruct() 
    {
        $this->connection->close();
    }

    public function errorMessages()
    {
	    $message = '';

	    if($_SESSION['success'] != '') {
	        $message = '<span class="success" id="message">'.$_SESSION['success'].'</span>';
	        $_SESSION['success'] = ''; 
	    }

	    if($_SESSION['error'] != '') {
	        $message = '<span class="error" id="message">'.$_SESSION['error'].'</span>';
	        $_SESSION['error'] = '';
	    }

	    return $message; 
	}
}

Let's quickly go through the different methods of this class.

In the __construct method, we are creating a mysqli connection object. It takes four parameters—$db_server, $db_user, $db_password, and $db_name—and establishes a database connection using the provided credentials. If the connection fails, an error message is displayed.

The validateUser method handles user authentication. It takes a username and password as parameters and calls the checkUsernameAndPassword method to verify the credentials. If the credentials are valid, it returns true; otherwise, it returns false.

The loggedIn method checks if a user is logged in by checking the $_SESSION['authorized'] variable. It returns true if the user is logged in and false otherwise.

Next, the query method executes the SQL query provided in the first argument. It prepares the query and executes it. Then, it retrieves the result metadata, binds the result to variables, fetches the results, and returns an array containing the fetched data. The insertQuery, updateQuery, and deleteQuery methods allow insert, update, and delete operations in the database. The countQuery method executes an SQL query, which returns a single result, such as a count.

The checkUsernameAndPassword method is used to check the username and password against the database. It prepares a query to fetch the user with the provided username and password. It also sets the appropriate session variables if the credentials are valid.

Application Layout

In this section, we'll discuss how to set up our main layout file, which will be used as the container for the rest of the pages that we'll implement.

Go ahead and create the admin/layout.php file as shown in the following snippet.

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "https://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd"> 

<html xmlns="http://www.w3.org/1999/xhtml" > 
    <head> 
        <meta http-equiv="Content-Type" content="text/html; charset=utf-8" /> 
        <title><?php echo $title; ?> » My Newsletter App</title> 

        <!-- Stylesheets --> 
        <!-- <link rel="stylesheet" href="media/style.css" type="text/css" media="all" /> --> 
    </head> 

    <body<?php if ($mini == true) { ?> class="mini"<?php } ?>> 
        <div id="header"> 
            <h1><a href="index.php">My Newsletter App</a></h1> 
        </div> 

        <?php if ($nonav == false) { ?> 
            <div id="nav"> 
                <a href="messages.php"<?php if($tab == 'mess') {?>class="current"<?php } ?>>messages</a> 
                <a href="subscribers.php"<?php if($tab == 'sub') {?>class="current"<?php } ?>>subscribers</a> 
                <a href="newsletters.php"<?php if($tab == 'nl') {?>class="current"<?php } ?>>newsletters</a> 
                <a href="templates.php"<?php if($tab == 'temp') {?>class="current"<?php } ?>>templates</a> 

                <span class="right"> 
                    <a href="logout.php">log out</a> 
                </span> 
            </div> 
        <?php } ?> 

        <div id="container"> 
            <h3><?php echo $title;?></h3> 
            <?php echo $content; ?> 
        </div> 
    </body> 
</html>

It's a fairly straightforward XHTML layout file, which contains sections like head, body, etc. We've also implemented a navigation, which allows users to navigate between different sections of our application.

Next, go ahead and create the admin/index.php file with the following contents.

<?php 
require_once 'config.php';

$title = "Home!";
$content =  '<h3>current stats</h3>Our home page!';
include 'layout.php'; 
?>

In the above file, we've included the config file, initialized the title variable, and then initialized the content variable. Finally, we've included the layout file. It should look like this.

Now, let's add some style. Add the following to the admin/media/style.css file.

1	/* reset */
2	html,body,div,span,applet,object,iframe,h1,h2,h3,h4,h5,h6,p,blockquote,pre,a,abbr,acronym,address,big,cite,code,del,dfn,em,font,img,ins,kbd,q,s,samp,strike,strong,sub,sup,tt,var,b,u,i,center,dl,dt,dd,ol,ul,li,fieldset,form,label,legend,table,caption,tbody,tfoot,thead,tr,th,td{margin:0;padding:0;border:0;outline:0;font-size:100%;vertical-align:baseline;background:transparent}body{line-height:1}ol,ul{list-style:none}blockquote,q{quotes:none}blockquote:before,blockquote:after,q:before,q:after{content:'';content:none}:focus{outline:0}ins{text-decoration:none}del{text-decoration:line-through}table{border-collapse:collapse;border-spacing:0}
3	/* typography */
4	body{font:13px/1.5 Helvetica,Arial,'Liberation Sans',FreeSans,sans-serif}a:focus{outline:1px dotted invert}hr{border:0 #ccc solid;border-top-width:1px;clear:both;height:0}h1{font-size:25px}h2{font-size:23px}h3{font-size:21px}h4{font-size:19px}h5{font-size:17px}h6{font-size:15px}ol{list-style:decimal}ul{list-style:square}li{margin-left:30px}p,dl,hr,h1,h2,h3,h4,h5,h6,ol,ul,pre,table,address,fieldset{margin:10px 0;}

Let's add more stuff.

1	#header {width:85%; margin:0 auto;}
2	#header h1 a {text-decoration:none; color:#000;}
3	#container {width:85%; background: #111; margin:5px auto; color:#fff; padding:10px;}

Now, it should look like this.

Next, let's style our tabs.

/* tabs */ 
#nav {margin:0 auto 2px;padding:10px;width:85%; background:#444;} 
#nav a { padding:12px 10px; margin:0; color:#fff; text-decoration:none; text-align:center;} 
#nav a.current, #nav a:hover {background:#111;} 
#nav span.right {float:right;}

While we are working on this file, go ahead and add the following styles for our mini-layout, form inputs, tables, large links, and our error and success messages.

body.mini #header {width:30%; text-align:center;} 
body.mini #container {width:30%;} 

/* form */ 
form input.text {width:95%; font-size:16px;} 
form textarea {width:95%; height:100%;} 

/* table */ 
table {width:98%; text-align:right; border:rgb(128,128,128); font-size:12px; margin:5px 10px; color:#000;background:#fff;} 
table th {background-color: rgb(229, 229, 229); border:1px solid rgb(187, 187, 187); padding:3px 6px; font-weight:normal; color:#000;} 
table tr td {border: 1px solid rgb(221, 221, 221); padding:3px 6px;} 
table tr:hover {background-color: rgb(240, 240, 240);color:#000;} 

/* a */ 
a.large {padding: 5px; color:#000; background:#eee; text-decoration:none; margin:5px;} 
a.large.right {float:right;} 
a.large:hover, a.large.current {background:#444; color:#fff;} 

/* messages */ 
#message {margin: 5px 10px; padding: 4px; display:block;text-align:center;} 
#message.error {background:#FFEBE8;border: 1px solid #CC0000;color:#CC0000;} 
#message.success {border:solid 1px #349534; background:#C9FFCA;color:#008000;}

Authentication

In this section, we're going to implement the authentication system for our application.

Go ahead and create the admin/login.php file with the following contents.

<?php 
require_once 'config.php'; 

if($db->loggedIn()) {
    header('Location: index.php');
    exit;
}

$title = "login"; 
$nonav = true; 
$mini = true; 

if($_POST && (!empty($_POST['username']) ) && (!empty($_POST['password']))) { 
    $response = $db->validateUser($_POST['username'], $_POST['password']);
    
    if (!$response) {
        header("Location: login.php");
        exit;
    }
} 

$error = $_SESSION['error'];
$content = '';
$content .= $error;
$content .= ' 
<form action="login.php" method="post"> 
    <p> 
        <label for="username">username:</label><br /> 
        <input type="text" name="username" class="text" /> 
    </p> 
    <p> 
        <label for="password">password:</label><br /> 
        <input type="password" name="password" class="text" /> 
    </p> 
    <p> 
        <input type="submit" value="login" /> 
    </p> 
</form>'; 

include 'layout.php';
?>

To begin with, we've included the configuration file. Next, we verify if the user is already logged in, and if so, we redirect the user to the home page. Afterwards, we define the title and layout options.

Next, we check if it's a POST request and if it includes both a username and password. In that case, we call the validateUser method of the Database class. Subsequently, we've assigned the session errors to the $error variable. Then, we proceed to set up our form and display any errors.

Let's also create the admin/logout.php file as well.

<?php 
require_once 'config.php'; 

$_SESSION = array();
session_destroy(); 
header('Location: login.php'); 
?>

In the above file, we'll destroy the current session and redirect the user to the login.php file.

Let's have a quick look at the users table, which we'll need in our database for the user management system.

CREATE TABLE `users` ( 
   `id` int(10) AUTO_INCREMENT, 
   `username` varchar(50), 
   `password` varchar(32), 
   PRIMARY KEY (`id`) 
) ENGINE=MyISAM DEFAULT CHARSET utf8; 


INSERT INTO `users` (`id`, `username`, `password`) VALUES  ('1', 'admin', '5ebe2294ecd0e0f08eab7690d2a6ee69');

Newsletters

In our application, users should be allowed to add as many newsletters as they want. In that case, we need to create the newsletters table as shown in the following snippet.

CREATE TABLE `newsletters` ( 
   `id` int(10) AUTO_INCREMENT, 
   `name` varchar(50), 
   `description` varchar(255), 
   `visible` varchar(10), 
   PRIMARY KEY (`id`) 
) ENGINE=MyISAM DEFAULT CHARSET utf8;

Now that we have our newsletters table in place, we are going to implement different pages for the newsletter management.

Newsletters Listing Page

Go ahead and create the admin/newsletters.php file, as shown in the following snippet.

<?php 
require_once 'config.php'; 

$response = $db->loginRequired();
if (!$response) {
    header('Location: login.php');
    exit;
}
$title = "Newsletters"; 
$newsletters = $db->query("SELECT * FROM newsletters ORDER BY id ASC");

$tab = 'nl'; 
$table = ""; 

foreach($newsletters as $row) {
    $dlink = '<a href="newsletters_delete.php?id='.$row['id'].'" onclick="return confirm(\'Are you sure you want to delete this newsletter?\');" title="delete"><img src="media/images/delete.png" alt="delete"/></a>';
    $elink = '<a href="newsletters_edit.php?id='.$row['id'].'" title="edit" ><img src="media/images/page_edit.png" alt="edit"/></a>';
    if($row['visible'] == "1") {
        $visible = '<img src="media/images/bullet_green.png" />';} else {$visible = '<img src="media/images/bullet_red.png" />';
    }
    $table .= "<tr><td>".$row['id']."</td><td>".$row['name']."</td><td>".$row['description']."</td><td>$visible</td><td>".$dlink." ".$elink."</td></tr>\n";
} 

$message = $db->errorMessages();
$content = '';
$content .= '<a href="newsletters_new.php" class="large">Add New Newsletter »</a> ';
$content .= $message;
$content .= '<table> 
    <tr> 
        <th></th> 
        <th>name</th> 
        <th>description</th> 
        <th>visible</th> 
        <th></th> 
    </tr>';
$content .= $table;
$content .= '</table>';

include 'layout.php'; 
?>

Firstly, it checks if the user is logged in by calling the loginRequired method of the Database class. If the user is not logged in, it redirects the user to the login.php page.

Next, it retrieves the list of newsletters from the database using the query method of the Database class. It selects all records from the newsletters table and orders them by the id column in ascending order.

Finally, we iterate over the newsletter records and build an HTML table list, and include the layout.php file to display the newsletter listing.

With a few records already in the newsletters table, it should look like this:

Go ahead and create the admin/newsletters_new.php file with the following contents.

<?php 
require_once 'config.php';

$response = $db->loginRequired();
if (!$response) {
    header('Location: login.php');
    exit;
}

$tab = 'nl';

if(isset($_POST['submitted'])) {
    $data = array('name'=>$_POST['name'],'description'=>$_POST['description']);
    $db->insertQuery($data, 'newsletters');
    $_SESSION['success'] = "Added newsletter.";
    header('Location: newsletters.php');
    exit;
}

$title = "Add New Newsletter";
$content = '<form action="newsletters_new.php" method="POST">
    <p> 
        <label for="name">Name:</label><br /> 
        <input type="text" name="name" class="text" />  
    </p> 
    <p> 
        <label for="description">Description:</label> 
        <input type="text" name="description" class="text" />  
    </p> 
    <p> 
        <input type="submit" value="Add Newsletter" /> 
        <input type="hidden" value="1" name="submitted" />  
    </p> 
</form>';

include 'layout.php'; 
?>

When a user visits the above page, it displays the "add newsletter" form, which allows the user to enter newsletter details and submit the form.

When the form is submitted, it calls the insertQuery method of the Database class, passing the values of $_POST['name'] and $_POST['description'] to insert a new record into the newsletters table in the database. Finally, it redirects the user to the newsletters.php page.

The form should look like this.

Let's create the admin/newsletters_edit.php file, which allows users to edit existing newsletters.

<?php 
require_once 'config.php'; 

$response = $db->loginRequired();
if (!$response) {
    header('Location: login.php');
    exit;
}

$tab = 'nl';

if(isset($_POST['submitted'])) { 
    if(isset($_POST['visible'])) {$visible = 1;}else{$visible = 0;}
    $data = array('name'=>$_POST['name'],'description'=>$_POST['description'], 'visible'=>$visible);
    $db->updateQuery($data, $_POST['id'], 'newsletters');
    $_SESSION['success'] = "Updated newsletter.";
    header('Location: newsletters.php');
    exit;
} 

$title = "Edit newsletter";
$id = (int) $_GET['id'];

$results = $db->query("SELECT * FROM newsletters WHERE id='".$id."' LIMIT 1");
$name = $results[0]['name'];
$description = $results[0]['description'];
$visible = ($results[0]['visible'] == "1") ? 'checked="checked"' : '';

$content = '<form action="newsletters_edit.php" method="POST">
    <p> 
        <label for="name">Name:</label><br /> 
        <input type="text" name="name" class="text" value="'.$name.'" />  
    </p> 

    <p> 
        <label for="description">Description:</label> 
        <input type="text" name="description" class="text" value="'.$description.'" />  
    </p> 

    <p> 
        <label for="visible">Visible:</label> 
        <input type="checkbox" name="visible" value="true" '.$visible.'/> 
    </p> 

    <p> 
        <input type="submit" value="Edit Newsletter" /> 
        <input type="hidden" value="1" name="submitted" />  
        <input type="hidden" value="'.$id.'" name="id" /> 
    </p> 
</form>'; 

include 'layout.php'; 
?>

Overall, it's responsible for displaying a form to edit an existing newsletter. When it's submitted, it updates the corresponding record in the database and redirects the user to the appropriate page based on the outcome.

The edit form looks like this.

Go ahead and create the admin/newsletters_delete.php file, which allows users to delete an existing newsletter.

<?php 
require_once 'config.php'; 

$response = $db->loginRequired();
if (!$response) {
    header('Location: login.php');
    exit;
}

$id = (int) $_GET['id'];  
$stmt = $db->deleteQuery($id);
$_SESSION['success'] = "Newsletter deleted."; 

header('Location: newsletters.php');
?>

Templates

Our application also supports newsletter template management. So we need to build the CRUD in the same way we just built it for the newsletters entity. Since it's pretty similar, I'll quickly go through it by providing the code.

Let's create the templates table as shown in the following snippet.

CREATE TABLE `templates` ( 
   `id` int(10) AUTO_INCREMENT, 
   `name` varchar(50), 
   `columns` tinyint(5), 
   `body` text, 
   PRIMARY KEY (`id`) 
) ENGINE=MyISAM DEFAULT CHARSET utf8;

Templates Listing Page

Go ahead and create the admin/templates.php file as shown in the following snippet.

<?php 
require_once 'config.php'; 

$response = $db->loginRequired();
if (!$response) {
    header('Location: login.php');
    exit;
}

$title = "Templates"; 
$tab = 'temp'; 
$templates = $db->query("SELECT * FROM templates ORDER BY id ASC");

$table = ""; 
foreach($templates as $row) { 
    $plink = '<a href="" onClick="window.open(\'templates_preview.php?id='.$row['id'].'\',width=800,height=600)" title="preview"><img src="media/images/find.png" alt="preview"/></a>'; 
    $dlink = '<a href="templates_delete.php?id='.$row['id'].'" onclick="return confirm(\'Are you sure you want to delete this template?\');" title="delete"><img src="media/images/delete.png" alt="delete"/></a>'; 
    $elink = '<a href="templates_edit.php?id='.$row['id'].'" title="edit"><img src="media/images/page_edit.png" alt="edit"/></a>'; 
    $table .= "<tr><td>".$row['id']."</td><td>".$row['name']."</td><td>".$row['columns']."</td><td>".$plink." ".$dlink." ".$elink."</td></tr>\n"; 
} 

$message = $db->errorMessages();

$content = '';
$content .= $message;
$content .= '<a href="templates_new.php" class="large">Add New Template »</a> 
<table> 
    <tr> 
        <th></th> 
        <th>name</th> 
        <th>columns</th> 
        <th></th> 
    </tr>'; 
$content .= $table;
$content .= '</table>';
include 'layout.php'; ?>

It looks like this.

Add a Template

Go ahead and create the admin/templates_new.php file with the following contents.

<?php 
require_once 'config.php';

$response = $db->loginRequired();
if (!$response) {
    header('Location: login.php');
    exit;
}

$tab = 'nl';

if(isset($_POST['submitted'])) {
    $data = array('name'=>$_POST['name'],'columns'=>$_POST['columns'],'body'=>$_POST['body']);
    $db->insertQuery($data, 'templates');
    $_SESSION['success'] = "Added template.";
    header('Location: templates.php');
    exit;
}

$title = "Add New Template";
$content = '<form action="templates_new.php" method="POST">
    <p> 
        <label for="name">Name:</label><br /> 
        <input type="text" name="name" class="text" />  
    </p> 

    <p> 
        <label for="columns">Columns</label> 
        <select name="columns"> 
            <option value="1">Single Column Layout</option> 
            <option value="2">Two Column Layout</option> 
        </select> 
    </p> 

    <p> 
        <label for="description">Body: (raw html)</label><br /> 
        Use %content% for a single column layout, %leftcol% and %rightcol% for a two column layout.<br /> 
        <textarea name="body" rows="35"></textarea>  
    </p> 

    <p> 
        <input type="submit" value="Add Template" /> 
        <input type="hidden" value="1" name="submitted" />  
    </p> 
</form>';

include 'layout.php'; 
?>

Let's have a look at the output.

Edit a Template

Let's create the admin/templates_edit.php file, which allows users to edit existing templates.

<?php 
require_once 'config.php'; 

$response = $db->loginRequired();
if (!$response) {
    header('Location: login.php');
    exit;
}

$tab = 'nl';

if(isset($_POST['submitted'])) { 
    $data = array('name'=>$_POST['name'],'columns'=>$_POST['columns'],'body'=>$_POST['body']);
    $db->updateQuery($data, $_POST['id'], 'templates');
    $_SESSION['success'] = "Updated template.";
    header('Location: templates.php');
    exit;
} 

$title = "Edit template";
$id = (int) $_GET['id'];

$results = $db->query("SELECT * FROM templates WHERE id='".$id."' LIMIT 1");

$name = $results[0]['name']; 
$templatedata = stripslashes(htmlspecialchars($results[0]['body']));

$content = '<form action="templates_edit.php" method="POST">  
    <p> 
        <label for="name">Name:</label><br /> 
        <input type="text" name="name" class="text" value="'.$name.'"/>  
    </p> 

    <p> 
        <label for="columns">Columns</label> 
        <select name="columns"> 
            <option value="1">Single Column Layout</option> 
            <option value="2">Two Column Layout</option> 
        </select> 
    </p> 

    <p> 
        <label for="body">Body: (raw html)</label><br /> 
        Use %content% for a single column layout, %leftcol% and %rightcol% for a two column layout.<br /> 
        <textarea name="body" rows="35">'.$templatedata.'</textarea>  
    </p> 

    <p> 
        <input type="submit" value="Edit Template" /> 
        <input type="hidden" value="1" name="submitted" />  
        <input type="hidden" value="'.$id.'" name="id" /> 
    </p> 
</form>'; 

include 'layout.php'; 
?>

The form in the edit mode should look like this.

Delete a Template

Go ahead and create the admin/templates_delete.php file, which allows users to delete existing templates.

<?php 
require_once 'config.php'; 

$response = $db->loginRequired();
if (!$response) {
    header('Location: login.php');
    exit;
}

$id = (int) $_GET['id'];
$stmt = $db->deleteQuery('templates', $id);
$_SESSION['success'] = "Template deleted."; 

header('Location: templates.php');
?>

Now, we are going to work on an extra page, which is not part of the CRUD spectrum; we are going to create a preview page. Go ahead and create the admin/templates_preview.php file with the following contents.

<?php 
require_once 'config.php'; 

$response = $db->loginRequired();
if (!$response) {
    header('Location: login.php');
    exit;
}

$id = (int) $_GET['id']; 

$data = $db->query("SELECT body FROM templates WHERE id={$id} LIMIT 1");
$template = $data[0]['body']; 
?> 
<?php echo $template; ?> 
<center><button type="button" onclick="self.close();">close window</button></center>

And an example preview looks like this:

So that's it for the template management.

Subscribers and Subscriptions

In this section, we'll work on subscriber management. Again, it's pretty similar to the sections that we've already discussed, so I'll quickly go through it by providing the code.

Let's create the subscribers table as shown in the following snippet.

CREATE TABLE `subscribers` ( 
    `id` tinyint(10) AUTO_INCREMENT, 
    `name` varchar(50), 
    `email` varchar(50), 
    PRIMARY KEY (`id`) 
) ENGINE=MyISAM DEFAULT CHARSET utf8;

Similarly, let's create the subscriptions table.

CREATE TABLE `subscriptions` ( 
    `id` tinyint(10) AUTO_INCREMENT, 
    `subscriber_id` tinyint(10), 
    `newsletter_id` tinyint(10), 
    PRIMARY KEY (`id`) 
) ENGINE=MyISAM DEFAULT CHARSET utf8;

So that creates a table for our many-to-many relationship with our newsletters. A subscriber can subscribe to multiple newsletters, and each newsletter can have many subscribers.

Subscriber Listing Page

Go ahead and create the admin/subscribers.php file as shown in the following snippet.

<?php 
require_once 'config.php'; 

$response = $db->loginRequired();
if (!$response) {
    header('Location: login.php');
    exit;
}

$title = "Subscribers"; 
$tab = 'sub'; 
$messages = $db->query("SELECT * FROM subscribers ORDER BY id ASC");

$table = ""; 
foreach($messages as $row) { 
	$dlink = '<a href="subscribers_delete.php?id='.$row['id'].'" onclick="return confirm(\'Are you sure you want to delete this subscriber?\');" title="delete"><img src="media/images/delete.png" alt="delete"/></a>'; 
	$elink = '<a href="subscribers_edit.php?id='.$row['id'].'" title="edit"><img src="media/images/page_edit.png" alt="edit"/></a>'; 
	$table .= '<tr><td>'.$row['id'].'</td><td>'.$row['name'].'</td><td>'.$row['email'].'</td><td>'.$dlink.' '.$elink.'</td></tr>'; 
}

$message = $db->errorMessages();

$content = '';
$content .= $message;
$content .= '<table> 
    <tr> 
        <th></th> 
		<th>name</th> 
		<th>email</th> 
		<th></th> 
    </tr>'; 
$content .= $table;
$content .= '</table>';
include 'layout.php'; ?>

We have basically the same listing pages as before, except this time we will be finding our subscribers. It should look like this:

Edit Subscribers

Let's create the admin/subscribers_edit.php file.

<?php 
require_once 'config.php'; 

$response = $db->loginRequired();
if (!$response) {
    header('Location: login.php');
    exit;
}

$tab = 'sub'; 

if(isset($_POST['submitted'])) {  
    $id = (int) $_POST['id']; 
    $data = array('name'=>$_POST['name'],'email'=>$_POST['email']);
    $db->updateQuery($data, $id, 'subscribers');

    foreach($_POST['newsletter'] as $n) {

        if($n['exists'] != '1' && $n['subscribe'] == "true") { // If we want to subscribe but the record doesnt exist
            $nlid = $n['nlid']; 
            $data = array('subscriber_id'=>$id,'newsletter_id'=>$_POST['newsletter_id'],'body'=>$nlid);
            $db->insertQuery($data, 'subscriptions');
        } elseif ($n['exists'] == '1' && $n['subscribe'] != "true") {// Else if we had an exits but we want to unsubscribe
            $subid = $n['subid'];
            $db->deleteQuery('subscriptions', $subid);
        } 
    } 

    $_SESSION['success'] = "Edited subscriber.";  

    header('Location: subscribers.php');
} 

$title = "Edit Newsletter"; 

$id = (int) $_GET['id']; 

$subscriber = $db->query("SELECT * FROM subscribers WHERE id='".$id."'");
$name = $subscriber[0]['name']; 
$email = $subscriber[0]['email']; 

$newsletters = $db->query("SELECT * FROM newsletters");
$subs = $db->query("SELECT * FROM subscriptions WHERE subscriber_id='".$id."'");
$subscriptions = '';

foreach($newsletters as $nl) {
    $s = false; 
    $subid = ''; 

    foreach($subs as $sub) {
        if($sub['newsletter_id'] == $nl['id']) {$s = true; $subid = $sub['id'];} 
    } 

    $checked = ($s == true) ? 'checked="checked"' : ''; 

    $subscriptions .= '
<input type="checkbox" name="newsletter['.$nl["id"].'][subscribe]" value="true" '.$checked.'/>
<label for="newsletter['.$nl["id"].']">'.$nl['name'].'</label>
<input type="hidden" name="newsletter['.$nl["id"].'][exists]" value="'.$s.'" />
<input type="hidden" name="newsletter['.$nl["id"].'][nlid]" value="'.$nl['id'].'" />
<input type="hidden" name="newsletter['.$nl["id"].'][subid]" value="'.$subid.'" /><br />
'; 
} 

$content = '<form action="subscribers_edit.php" method="POST">  
    <p> 
        <label for="name">Name:</label><br /> 
        <input type="text" name="name" class="text" value="'.$name.'" />  
    </p> 

    <p> 
        <label for="email">Email</label><br /> 
        <input type="text" name="email" class="text" value="'.$email.'" />  
    </p> 

    <p> 
        <strong>Newsletters:</strong><br /> 
        $subscriptions 
    </p> 

    <p> 
        <input type="submit" value="Edit Subscriber" /> 
        <input type="hidden" value="1" name="submitted" />  
        <input type="hidden" value="'.$id.'" name="id" /> 
    </p> 
</form>';

include 'layout.php'; ?>

This page is quite different, so I will explain each part.

When a user loads this page for the first time, we open it in edit mode. The form displays the subscriber details and the subscriptions that are attached to the subscriber.

When the form is submitted, we update the subscriber details in the subscribers table. Next, we'll either create or delete the subscription record in the subscriptions table based on the checkbox selection, which either subscribes or unsubscribes the user from the selected newsletter.

The page should look like this.

Delete a Subscriber

Go ahead and create the admin/subscribers_delete.php file.

<?php 
require_once 'config.php'; 

$response = $db->loginRequired();
if (!$response) {
    header('Location: login.php');
    exit;
}

$id = (int) $_GET['id'];
$stmt = $db->deleteQuery('subscribers', $id);
$_SESSION['success'] = "Subscriber deleted."; 

header('Location: subscribers.php');
?>

So that's it for subscriber management.

Messages

This section is going to be a bit lengthy, as it'll contain a lot of pages.

Firstly, let's create the messages table with this SQL.

CREATE TABLE `messages` ( 
   `id` tinyint(10) AUTO_INCREMENT, 
   `subject` varchar(255), 
   `leftcol` text, 
   `rightcol` text, 
   `template_id` tinyint(10), 
   PRIMARY KEY (`id`) 
) ENGINE=MyISAM DEFAULT CHARSET utf8;

List Messages

Go ahead and create the admin/messages.php file as shown in the following snippet.

<?php 
require_once 'config.php'; 

$response = $db->loginRequired();
if (!$response) {
    header('Location: login.php');
    exit;
}

$title = "Messages"; 
$tab = 'mes'; 
$messages = $db->query("SELECT * FROM messages ORDER BY id ASC");

$table = ""; 
foreach($messages as $row) { 
    $slink = '<a href="messages_send.php?id='.$row['id'].'" title="send message"><img src="media/images/email_go.png" alt="send message"/></a>'; 
    $plink = '<a href="messages_preview.php?id='.$row['id'].'" target="_new" title="preview"><img src="media/images/find.png" alt="preview"/></a>'; 
    $dlink = '<a href="messages_delete.php?id='.$row['id'].'" onclick="return confirm(\'Are you sure you want to delete this message?\');" title="delete"><img src="media/images/delete.png" alt="delete"/></a>'; 
    $elink = '<a href="messages_edit.php?id='.$row['id'].'" title="edit"><img src="media/images/page_edit.png" alt="edit"/></a>'; 
    $table .= '<tr><td>'.$row['id'].'</td><td>'.$row['subject'].'</td><td><a href="" onClick="window.open(\'templates_preview.php?id='.$row['template_id'].'\',width=800,height=600)" title="preview"><img src="media/images/find.png" alt="preview"/></a></td><td>'.$slink.' '.$plink.' '.$dlink.' '.$elink.'</td></tr>'; 
}

$message = $db->errorMessages();

$content = '<a href="messages_new.php" class="large">New Message »</a>';
$content .= $message;
$content .= '<table> 
    <tr> 
        <th></th> 
        <th>subject</th> 
        <th>template</th> 
        <th></th> 
    </tr>'; 
$content .= $table;
$content .= '</table>';
include 'layout.php'; ?>

It's a listing page with a few additional links for different operations. Your page should look like this:

Now, we are going to start working on our new pages.

On the first page, we'll collect the subject and template-related information, and then on the next page, we'll collect the actual message.

Create the admin/messages_new.php file with the following contents.

<?php 
require_once 'config.php'; 

$response = $db->loginRequired();
if (!$response) {
    header('Location: login.php');
    exit;
}

$tab = 'mess'; 

if(isset($_POST['subject'])) {
    $data = array('subject'=>$_POST['subject'],'template_id'=>$_POST['template_id']);
    $db->insertQuery($data, 'messages');

    $i = $db->query("SELECT id FROM messages WHERE subject='".$_POST['subject']."' AND template_id=".$_POST['template']." ");
    $id = $i[0]['id'];

    $_SESSION['success'] = "Added template.";
    header('Location: messages_new_step2.php?id=' + $id);
} 

$title = "New Message";
$templates = $db->query("SELECT id,name,columns FROM templates");
$tselect = '<select name="template">';
foreach($templates as $row) { 
    $tselect .= '<option value="'.$row['id'].'">'.$row['name'].'</option>'; 
} 
$tselect .= "</select>"; 

$content = '<form action="messages_new.php" method="POST">
    <p>
        <label for="subject">Subject:</label><br />
        <input type="text" name="subject" class="text" />
    </p>

    <p> 
        <label for="template">Template:</label>
        $tselect
    </p> 

    <p> 
        <button onclick="">Continue »</button> 
    </p> 
</form>'; 

include 'layout.php'; 
?>

This page is very similar to the other new pages, but there is a minor addition. Right after we create a new message entry, we find the most recent insert id.

The page looks like this:

Next, we redirect the user to step two. Let's create the admin/messages_new_step2.php file with the following contents.

<?php
require_once 'config.php';

$response = $db->loginRequired();
if (!$response) {
    header('Location: login.php');
    exit;
}

$title = "New Message - Step 2";
$tab = 'mess';
$id = (int) $_GET['id'];

$mess = $db->query("SELECT * FROM messages WHERE id= $id"); 
$message = $mess[0]; 
$subject = $message['subject']; 

$templates = $db->query("SELECT id,name,columns FROM templates");

$tselect = '<select name="template">'; 
foreach($templates as $row) { 
    if($message['template_id'] == $row['id']) {
        $selected = ' selected="selected"';

        if($row['columns'] == "1") {
            $textareas = '<p><label for="body">Body: (raw html)</label><br /><textarea name="body" rows="35"></textarea></p>'; 
        } else { 
            $textareas = '<p><label for="leftcol">Left column: (raw html)</label><br /><textarea name="leftcol" rows="35"></textarea></p>
<p><label for="rightcol">Right column: (raw html)</label><br /><textarea name="rightcol" rows="35"></textarea></p>'; 
        } 
    } else {
        $selected = '';
    }

    $tselect .= '<option value="'.$row['id'].'"'.$selected.'>'.$row['name'].'</option>';
}
$tselect .= '</select>'; 

// Check for a POST
if(isset($_POST['submitted'])) {
    $template = $db->query("SELECT columns FROM templates WHERE id=".$message['template_id']);

    if($template[0]['columns'] == "1") {
        $body = mysql_real_escape_string($_POST['body']); 
        $data = array('subject'=>$_POST['subject'],'leftcol' => $body);
        $db->updateQuery($data, $id, 'messages');
    } else { 
        $leftcol = mysql_real_escape_string($_POST['leftcol']);
        $rightcol = htmlentities($_POST['rightcol']);
        $data = array('subject'=>$_POST['subject'],'leftcol' => $leftcol, 'rightcol'=>$rightcol);
        $db->updateQuery($data, $id, 'messages');
    } 

    header('Location: messages_new_step3.php?id='.$id); 
} 


$content = '<form action="messages_new_step2.php?id='.$id.'" method="POST">
    <p>
        <label for="subject">Subject:</label><br /> 
        <input type="text" name="subject" class="text" value="'.$subject.'"/>  
    </p> 

    <p> 
        <label for="template">Template:</label> 
        $tselect 
    </p> 

    '.$textareas.'

    <p> 
        <input type="submit" value="Continue »" /> 
        <input type="hidden" value="1" name="submitted" /> 
    </p> 
</form>';

include 'layout.php'; ?>

Firstly, we find the message we are working with, and then we find all the templates and construct a dropdown menu. Next, we have our POST block, which creates the link and then checks to see if we are working with one or two columns and creates the appropriate SQL query. In the end, your form should look like this:

Next, let's create the admin/messages_new_step3.php file with the following contents.

<?php 
require_once 'config.php'; 

$response = $db->loginRequired();
if (!$response) {
    header('Location: login.php');
    exit;
}

$title = "New Message - Step 3";
$id = (int) $_GET['id'];
$tab = 'mess'; 


$mess = $db->query("SELECT * FROM messages WHERE id=$id");
$message = $mess[0];
$subject = $message['subject'];

$content = '<a href="messages_preview.php?id=$id" class="large" target="_new">preview »</a><br />
<p>Do you want to <a href="messages.php" class="large">return to messages</a> or <a href="messages_send.php?id=$id" class="large">send the message</a>?</p>';

include 'layout.php'; 
?>

It's a very simple page, and it's the end of creating a message. It offers us a few links. The first one is the link to preview the message. The next link takes us back to the home page. Finally, the third one takes us to the page to send the message.

The page looks like this:

Next, we are going to continue with the edit page. It's pretty similar to the messages_new_step2.php file.

Go ahead and create the admin/messages_edit.php file as shown below.

<?php 
require_once 'config.php'; 

$response = $db->loginRequired();
if (!$response) {
    header('Location: login.php');
    exit;
}

$title = "Edit Message";
$id = (int) $_GET['id'];
$tab = 'mess';

$mess = $db->query("SELECT * FROM messages WHERE id=$id");
$message = $mess[0];
$subject = $message['subject'];

$templates = $db->query("SELECT id,name,columns FROM templates");

$tselect = '<select name="template">';
foreach ($templates as $row) {
    if($message['template_id'] == $row['id']) {
        $selected = ' selected="selected"';
    } else {
        $selected = '';
    }

    $tselect .= '<option value="'.$row['id'].'"'.$selected.'>'.$row['name'].'</option>';
} 
$tselect .= '</select>'; 

$mid = $message['template_id'];
$template = $db->query("SELECT id,name,columns FROM templates WHERE id=$mid");

if ($template[0]['columns'] == "1") {
    $textareas = '<p><label for="body">Body: (raw html)</label><br /><textarea name="body" rows="35">'.$message['leftcol'].'</textarea></p>';
} else {
    $textareas = '<p><label for="leftcol">Left column: (raw html)</label><br /><textarea name="leftcol" rows="35">'.$message['leftcol'].'</textarea></p>
<p><label for="rightcol">Right column: (raw html)</label><br /><textarea name="rightcol" rows="35">'.$message['rightcol'].'</textarea></p>';
}

// Check for a POST
if (isset($_POST['submitted'])) {
    if ($template[0]['columns'] == "1") {
        $body = mysql_real_escape_string($_POST['body']);
        $data = array('subject'=>$_POST['subject'],'leftcol'=>$body);
        $db->updateQuery($data, $id, 'messages');
    } else {
        $leftcol = mysql_real_escape_string($_POST['leftcol']);
        $rightcol = htmlentities($_POST['rightcol']);

        $data = array('subject'=>$_POST['subject'],'leftcol'=>$body,'rightcol'=>$rightcol);
        $db->updateQuery($data, $id, 'messages');
    }

    $_SESSION['success'] = "Edited message.";
    header('Location: messages.php');
} 

$content = '<form action="messages_edit.php?id='.$id.'" method="POST">
    <p> 
        <label for="subject">Subject:</label><br /> 
        <input type="text" name="subject" class="text" value="'.$subject.'"/>  
    </p> 

    <p> 
        <label for="template">Template:</label> 
        '.$tselect.'
    </p> 
    '.$textareas.'
    <p> 
        <input type="submit" value="Save »" /> 
        <input type="hidden" value="1" name="submitted" /> 
    </p> 
</form>'; 

include 'layout.php'; 
?>

It looks identical to our step two, but the textarea is populated with the content in edit mode.

Next, let's create the admin/messages_delete.php file.

<?php 
require_once 'config.php'; 

$response = $db->loginRequired();
if (!$response) {
    header('Location: login.php');
    exit;
}

$id = (int) $_GET['id'];

$db->deleteQuery("messages", $id);
$_SESSION['success'] = "Message deleted."; 

header('Location: messages.php'); 
?>

Finally, let's have a look at the preview page.

<?php 

require_once 'config.php'; 

$response = $db->loginRequired();
if (!$response) {
    header('Location: login.php');
    exit;
}

$id = (int) $_GET['id'];

$mess = $db->query("SELECT * FROM messages WHERE id=$id");
$message = $mess[0];
$subject = $message['subject'];
$tid = $message['template_id']; 

$data = $db->query("SELECT body,columns FROM templates WHERE id=$tid LIMIT 1");
$template = $data[0]['body']; 

if($data[0]['columns'] == "1") { 
    $leftcol = $message['leftcol']; 
    $body = str_replace('%content%', $leftcol, $template); 
} else { 
    $leftcol = $message['leftcol']; 
    $rightcol = $message['rightcol']; 
    $b = str_replace('%leftcol%', $leftcol, $template); 
    $body = str_replace('%rightcol%', $rightcol, $b); 
} 
?> 

<?php echo $body; ?> 

<button type="button" onclick="self.close();">close window</button>

Basically, we're just fetching a message and template from the database and preparing the necessary variable for preview.

The Front-End

In this section, we'll build front-end pages. We need to create files for the front-end section in the root folder.

Subscribe to Newsletters

Let's create the index.php page with the following contents.

<?php 

require_once 'admin/config.php'; 

$newsletters = $db->query("SELECT * FROM newsletters WHERE visible=1");

$subscriptions = ''; 

foreach($newsletters as $nl) {
    $subscriptions .= '
<input type="checkbox" name="newsletter['.$nl["id"].'][subscribe]" value="true" '.$checked.'/>
<label for="newsletter['.$nl["id"].']">'.$nl['name'].'</label>
<input type="hidden" name="newsletter['.$nl["id"].'][nlid]" value="'.$nl['id'].'" /><br />
'.$nl["description"].'<br />
'; 
} 
?> 

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd"> 
<html xmlns="http://www.w3.org/1999/xhtml" > 
    <head> 
        <meta http-equiv="Content-Type" content="text/html; charset=utf-8" /> 
        <title>my newsletters</title> 
        <!-- Stylesheets --> 
        <link rel="stylesheet" href="style.css" type="text/css" media="all" /> 
    </head> 

    <body> 
        <div id="header"> 
            <h1>my newsletters</h1> 
        </div> 

        <div id="container"> 
            <h3>Subscribe to our newsletters!</h3> 
            <form action="subscribe.php" method="POST"> 
                <p> 
                    <label for="name">Name:</label><br /> 
                    <input type='text' name='name' class="text" />  
                </p> 

                <p> 
                    <label for="email">Email</label><br /> 
                    <input type="text" name="email" class="text" />  
                </p> 

                <p> 
                    <strong>Newsletters:</strong><br /> 
                    <?php echo $subscriptions; ?> 
                </p> 

                <p> 
                    <input type='submit' value='Subscribe »' /> 
                    <input type='hidden' value='1' name='submitted' />  
                </p> 
            </form> 
        </div> 
    </body> 
</html>

We've fetched all the public newsletters from the database and prepared a list, so that a user can subscribe to it.

It looks like this:

Let's have a look at the style.css file.

1	#header, #container {width:65%;margin:0 auto; padding:0.7%;}
2	#container {background:#ccc;}
3	form input.text {width:95%; font-size:16px;}
4	#message.success {border:solid 1px #349534; background:#C9FFCA;color:#008000;}

Let's have a look at the subscribe.php file, which is called when the user submits the form on the newsletter subscribe page.

<?php
require_once 'admin/config.php';

if(isset($_POST['submitted'])) {  
    $name = $_POST['name'];
    $email = $_POST['email'];

    $data = array('name'=>$name,'email'=>$email);
    $db->insertQuery($data, 'subscribers');

    $sql = "SELECT id FROM subscribers WHERE name='$name' AND email='$email' LIMIT 1";
    $subscriber = $db->query($sql);
    $id = $subscriber[0]['id'];

    foreach($_POST['newsletter'] as $n) {
        if($n['subscribe'] == "true") {
            $nlid = $n['nlid'];
            $data = array('subscriber_id'=>$id, 'newsletter_id'=>$nlid);
            $db->insertQuery($data, 'subscriptions');
        }
    }
} else {
    header('Location: index.php');
}
?>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd"> 
<html xmlns="http://www.w3.org/1999/xhtml" > 
    <head> 
        <meta http-equiv="Content-Type" content="text/html; charset=utf-8" /> 

        <title>my newsletters</title> 
        <!-- Stylesheets --> 
        <link rel="stylesheet" href="style.css" type="text/css" media="all" /> 
    </head> 

    <body> 
        <div id="header"> 
            <h1>my newsletters</h1> 
        </div> 

        <div id="container"> 
            <h3>Thank you for subscribing!</h3> 
        </div> 
    </body> 
</html>

When the form is submitted, we insert the user in the subscribers table in the first place. Next, for every selected newsletter, we create an entry in the subscriptions table.

Next, let's create the preferences.php page.

<?php 
require_once 'admin/config.php'; 

if(isset($_POST['submitted'])) {
    $id = (int) $_POST['id']; 
    $data = array('name'=>$_POST['name'],'email'=>$_POST['email']);
    $db->updateQuery($data, $id, 'subscribers');

    foreach ($_POST['newsletter'] as $n) {
        if($n['exists'] != '1' && $n['subscribe'] == "true") { // If we want to subscribe but the record doesnt exist
            $nlid = $n['nlid']; 

            $data = array('subscriber_id'=>$id,'newsletter_id'=>$nlid);
            $db->insertQuery($data, 'templates');
        } elseif ($n['exists'] == '1' && $n['subscribe'] != "true") {// Else if we had an exits but we want to unsubscribe
            $subid = $n['subid'];
            $db->insertQuery('subscriptions', $subid);
        }
    }

    $_SESSION['success'] = "Preferences saved.";  
} 

if(isset($_GET['email'])) {
    $email = $_GET['email'];
    $display = 'form';
} else {
    $display = 'find';
}

$subscriber = $db->query("SELECT * FROM subscribers WHERE email='$email'");
if($subscriber || $display == 'find') {
    $id = $subscriber[0]['id'];
    $name = $subscriber[0]['name'];
    $email = $subscriber[0]['email'];
} else {
    header('Location: index.php');
}

$newsletters = $db->query("SELECT * FROM newsletters WHERE visible=1");
$subs = $db->query("SELECT * FROM subscriptions WHERE subscriber_id='".$id."'");
$subscriptions = '';

foreach($newsletters as $nl) {
    $s = false; 
    $subid = ''; 
    foreach($subs as $sub) { 
        if($sub['newsletter_id'] == $nl['id']) {$s = true; $subid = $sub['id'];} 
    } 

    $checked = ($s == true) ? 'checked="checked"' : ''; 

    $subscriptions .= '
<input type="checkbox" name="newsletter['.$nl["id"].'][subscribe]" value="true" '.$checked.'/>
<label for="newsletter['.$nl["id"].']">'.$nl['name'].'</label>
<input type="hidden" name="newsletter['.$nl["id"].'][exists]" value="'.$s.'" />
<input type="hidden" name="newsletter['.$nl["id"].'][nlid]" value="'.$nl['id'].'" />
<input type="hidden" name="newsletter['.$nl["id"].'][subid]" value="'.$subid.'" /><br />'; 

}  

$message = $db->errorMessages();
?>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd"> 

<html xmlns="http://www.w3.org/1999/xhtml" > 
    <head> 
        <meta http-equiv="Content-Type" content="text/html; charset=utf-8" /> 

        <title>my newsletters - my preferences</title> 
        <!-- Stylesheets --> 
        <link rel="stylesheet" href="style.css" type="text/css" media="all" /> 
    </head> 

    <body> 
        <div id="header"> 
            <h1>my newsletters</h1> 
        </div> 

        <div id="container"> 
            <h3>my preferences</h3> 
            <?php if($display == 'form') {?> 
                <form action="preferences.php" method="POST"> 
                    <p> 
                        <label for="name">Name:</label><br /> 
                        <input type='text' name='name' class="text" value="<?php echo $name; ?>"/>  
                    </p> 

                    <p> 
                        <label for="email">Email</label><br /> 
                        <input type="text" name="email" class="text" value="<?php echo $email; ?>"/>  
                    </p> 

                    <p> 
                        <strong>Newsletters:</strong><br /> 
                        <?php echo $subscriptions; ?> 
                    </p> 

                    <p> 
                        <input type='submit' value='Save my preferences »' /> 
                        <input type='hidden' value='1' name='submitted' />  
                        <input type='hidden' value='<?php echo $id; ?>' name='id' /> 
                    </p> 
                </form>
            <?php } else { ?> 
                <?php echo $message; ?> 
                <form action='preferences.php' method="get"> 
                    <p> 
                        <label for="email">Email</label><br /> 
                        <input type="text" name="email" class="text" />  
                    </p> 
                    <p> 
                        <input type='submit' value='Find »' /> 
                    </p> 
                </form> 
            <?php } ?> 
        </div> 
    </body> 
</html>

It's used to allow users to save their newsletter preferences.

Firstly, when the form loads, it looks like this.

When you click on the Find button, it takes the user to the next page.

Finally, once a user clicks on the Save my preferences button, it displays the page shown in the following image.

Sending Messages

In this section, we'll build the last page, which allows us to send messages.

Let's create the admin/messages_send.php file as shown in the following snippet.

<?php 
require_once '../vendor/autoload.php';
require_once 'config.php';

use Symfony\Component\Mailer\Transport;
use Symfony\Component\Mailer\Mailer;
use Symfony\Component\Mime\Email;

$response = $db->loginRequired();
if (!$response) {
    header('Location: login.php');
    exit;
}

$title = "Send Message";
$id = (int) $_GET['id']; 
$tab = 'mess';


if(isset($_POST['submitted'])) {  
    $query = "SELECT * FROM subscribers WHERE id=0 "; 
    $emails = array(); 

    foreach($_POST['newsletter'] as $n) { 
        if($n['send'] == "true") { 
            $nlid = $n['nlid']; 
            $e = $db->query("SELECT subscriber_id FROM subscriptions WHERE newsletter_id=$nlid");
            foreach($e as $s) { 
                $sqlids .= " OR id=".$s['subscriber_id']; 
            } 
            $query .= $sqlids; 
        } 
    } 

    $subscribers = $db->query($query);
    foreach($subscribers as $sub) {
        $emails[$sub['email']] = $sub['name'];
    }

    $from = array(FROM_EMAIL => FROM_NAME);
    $mess = $db->query("SELECT * FROM messages WHERE id=$id");
    $message = $mess[0];
    $subject = $message['subject'];
    $tid = $message['template_id']; 

    $data = $db->query("SELECT body FROM templates WHERE id=$tid LIMIT 1");
    $template = $data[0]['body'];

    if($message['rightcol'] == '') {
        $leftcol = $message['leftcol'];
        $body = str_replace('%content%', $leftcol, $template);
    } else {
        $leftcol = $message['leftcol'];
        $rightcol = $message['rightcol'];
        $b = str_replace('%leftcol%', $leftcol, $template);
        $body = str_replace('%rightcol%', $rightcol, $b);
    }

    $transport = Transport::fromDsn('smtp://username:password@hostname:port');
    $mailer = new Mailer($transport); 

    $email = (new Email())
        ->from($from)
        ->to($emails)
        ->subject($subject)
        ->html($body);
    $mailer->send($email);

    header('Location: index.php');
} 

$newsletters = $db->query("SELECT * FROM newsletters");

foreach($newsletters as $nl) { 
    $nls .= '
<input type="hidden" name="newsletter['.$nl["id"].'][nlid]" value="'.$nl['id'].'" />
<input type="checkbox" name="newsletter['.$nl["id"].'][send]" value="true" '.$checked.'/>
<label for="newsletter['.$nl["id"].']">'.$nl['name'].'</label> - '.$nl['description'].'<br />
'; 
} 

$mess = $db->query("SELECT * FROM messages WHERE id=$id");
$message = $mess[0];
$subject = $message['subject'];

$content = '<a href="messages_preview.php?id='.$id.'" class="large" target="_new">preview »</a><br />
<form action="messages_send.php?id='.$id.'" method="POST">
    <p>
        Subject: '.$subject.'<br />
    </p>

    <p>Send to:<br />
        '.$nls.'
    </p>

    <p>
        <input type="submit" value="Send »" />
        <input type="hidden" value="1" name="submitted" />
    </p>
</form>';

include 'layout.php';
?>

It's used to build the form, which lists the newsletters in the first place. When an admin selects newsletters and submits the form, we retrieve the subscribers that are subscribed to these selected newsletters. And finally, we send an email to them.

It's also important to note that I've used the Symfony Mailer library to send emails. You can install it with Composer as shown in the following snippet. You need to adjust the settings as per your environment. To check more on this topic, you can visit this article, which provides an in-depth guide to the Symfony Mailer library.

1	$composer require symfony/mailer

The form looks like this:

The Homepage

Now, let's build the admin home page, which displays overall statistics for our application.

Let's create the admin/index.php file with the following contents.

<?php 
require_once 'config.php'; 

$response = $db->loginRequired();
if (!$response) {
    header('Location: login.php');
    exit;
}

$users = $db->countQuery("SELECT COUNT(*) AS num FROM users");
$emails = $db->countQuery("SELECT COUNT(*) AS num FROM subscribers");
$subs = $db->countQuery("SELECT COUNT(*) AS num FROM subscriptions"); 
$nls = $db->countQuery("SELECT COUNT(*) AS num FROM newsletters");
$mess = $db->countQuery("SELECT COUNT(*) AS num FROM messages");
$temps = $db->countQuery("SELECT COUNT(*) AS num FROM templates");

$title = "Home"; 

$content = "<h3>current stats</h3> 
<p>$users user registered</p> 
<p>$emails subscribers</p> 
<p>$subs newsletter subscriptions</p> 
<p>$nls newsletters</p> 
<p>$mess messages</p> 
<p>$temps templates</p>"; 

include 'layout.php'; 
?>

And it looks like this:

Conclusion

And with that, we've reached the end of this article as well. We covered a lot of ground in the tutorial, creating a newsletter app completely from scratch!