Building an Email Campaign Using PHP and MySQL

Creating an effective email campaign involves several steps, from designing the email content to managing your subscriber list and sending out the emails. In this guide, we’ll walk through the process of building an email campaign using PHP and MySQL.

Prerequisites

• Basic understanding of PHP and MySQL
• A web server with PHP and MySQL installed
• Access to an email server or service (e.g., SMTP server, SendGrid, Mailgun)

1. Setting Up Your Database

First, you’ll need to create a MySQL database to store your subscribers and campaign details. Let’s create a database and tables for our email campaign.

1.1 Create the Database

CREATE DATABASE email_campaign;
USE email_campaign;

1.2 Create Tables

Subscribers Table

CREATE TABLE subscribers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(255) NOT NULL UNIQUE,
    name VARCHAR(255),
    subscribed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Campaigns Table

CREATE TABLE campaigns (
    id INT AUTO_INCREMENT PRIMARY KEY,
    subject VARCHAR(255) NOT NULL,
    body TEXT NOT NULL,
    sent_at TIMESTAMP NULL
);

2. Setting Up PHP Scripts

You’ll need several PHP scripts to handle different aspects of the email campaign:

2.1 Database Connection

Create a file named db.php for connecting to the MySQL database.

<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "email_campaign";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}
?>

2.2 Add Subscribers

Create a file named add_subscriber.php to add new subscribers.

<?php
include 'db.php';

if ($_SERVER["REQUEST_METHOD"] == "POST") {
    $email = $_POST['email'];
    $name = $_POST['name'];
    
    $sql = "INSERT INTO subscribers (email, name) VALUES ('$email', '$name')";
    
    if ($conn->query($sql) === TRUE) {
        echo "New subscriber added successfully";
    } else {
        echo "Error: " . $sql . "<br>" . $conn->error;
    }
}
?>
<form method="post" action="">
    Email: <input type="email" name="email" required>
    Name: <input type="text" name="name">
    <input type="submit" value="Subscribe">
</form>

2.3 Create a Campaign

Create a file named create_campaign.php to create a new email campaign.

<?php
include 'db.php';

if ($_SERVER["REQUEST_METHOD"] == "POST") {
    $subject = $_POST['subject'];
    $body = $_POST['body'];
    
    $sql = "INSERT INTO campaigns (subject, body) VALUES ('$subject', '$body')";
    
    if ($conn->query($sql) === TRUE) {
        echo "New campaign created successfully";
    } else {
        echo "Error: " . $sql . "<br>" . $conn->error;
    }
}
?>
<form method="post" action="">
    Subject: <input type="text" name="subject" required>
    Body: <textarea name="body" required></textarea>
    <input type="submit" value="Create Campaign">
</form>

2.4 Send Campaign

Create a file named send_campaign.php to send the email campaign to all subscribers.

<?php
include 'db.php';

function sendEmail($to, $subject, $body) {
    $headers = "From: your-email@example.com\r\n";
    $headers .= "Content-Type: text/html; charset=UTF-8\r\n";
    
    return mail($to, $subject, $body, $headers);
}

$sql = "SELECT * FROM campaigns ORDER BY id DESC LIMIT 1";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    $campaign = $result->fetch_assoc();
    $subject = $campaign['subject'];
    $body = $campaign['body'];
    
    $sql = "SELECT email FROM subscribers";
    $subscribers = $conn->query($sql);
    
    if ($subscribers->num_rows > 0) {
        while ($row = $subscribers->fetch_assoc()) {
            $email = $row['email'];
            sendEmail($email, $subject, $body);
        }
        
        $update_sql = "UPDATE campaigns SET sent_at = NOW() WHERE id = " . $campaign['id'];
        $conn->query($update_sql);
        
        echo "Campaign sent successfully";
    } else {
        echo "No subscribers found";
    }
} else {
    echo "No campaigns found";
}
?>

3. Testing Your Email Campaign

• Add Subscribers: Use the add_subscriber.php form to add test subscribers.
• Create a Campaign: Use the create_campaign.php form to create a new email campaign.
• Send Campaign: Run send_campaign.php to send the latest campaign to all subscribers.

4. Enhancements

• Email Validation: Add validation to ensure emails are correctly formatted.
• HTML Templates: Use HTML email templates for better formatting.
• Error Handling: Improve error handling for failed email deliveries.
• Queue Management: Implement a queue system for sending large volumes of emails to avoid server overload.