PHP在线邮件营销管理系统实战

在数字化营销的时代，邮件营销依然是一种高效且成本较低的宣传手段。本文将带你一步步用PHP构建一个在线邮件营销管理系统，助你轻松管理邮件发送、订阅者列表和营销效果分析。

项目背景与需求分析

邮件营销管理系统的核心需求包括：

1. 用户订阅管理：允许用户订阅和退订邮件列表。
2. 邮件模板管理：创建和管理邮件模板。
3. 邮件发送管理：批量发送邮件并跟踪发送状态。
4. 数据分析：统计邮件打开率、点击率等关键指标。

技术选型

• 后端语言：PHP
• 数据库：MySQL
• 前端框架：Bootstrap
• 邮件发送服务：PHPMailer

系统架构设计

数据库设计

1. users表：存储订阅者信息。

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

2. templates表：存储邮件模板。

   CREATE TABLE templates (
      id INT AUTO_INCREMENT PRIMARY KEY,
      subject VARCHAR(255) NOT NULL,
      content TEXT NOT NULL,
      created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
   );

3. campaigns表：存储邮件发送记录。

   CREATE TABLE campaigns (
      id INT AUTO_INCREMENT PRIMARY KEY,
      template_id INT,
      sent_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
      FOREIGN KEY (template_id) REFERENCES templates(id)
   );

后端逻辑

1. 用户订阅与退订：

   // 订阅
   function subscribe($email) {
      $conn = new mysqli("localhost", "user", "password", "database");
      $stmt = $conn->prepare("INSERT INTO users (email) VALUES (?)");
      $stmt->bind_param("s", $email);
      $stmt->execute();
      $stmt->close();
      $conn->close();
   }
   
   // 退订
   function unsubscribe($email) {
      $conn = new mysqli("localhost", "user", "password", "database");
      $stmt = $conn->prepare("DELETE FROM users WHERE email = ?");
      $stmt->bind_param("s", $email);
      $stmt->execute();
      $stmt->close();
      $conn->close();
   }

2. 邮件模板管理：

   // 创建模板
   function createTemplate($subject, $content) {
      $conn = new mysqli("localhost", "user", "password", "database");
      $stmt = $conn->prepare("INSERT INTO templates (subject, content) VALUES (?, ?)");
      $stmt->bind_param("ss", $subject, $content);
      $stmt->execute();
      $stmt->close();
      $conn->close();
   }
   
   // 获取模板
   function getTemplates() {
      $conn = new mysqli("localhost", "user", "password", "database");
      $result = $conn->query("SELECT * FROM templates");
      $templates = [];
      while ($row = $result->fetch_assoc()) {
          $templates[] = $row;
      }
      $conn->close();
      return $templates;
   }

3. 邮件发送管理：

   
   use PHPMailer\PHPMailer\PHPMailer;
   use PHPMailer\PHPMailer\Exception;
   
   require 'path/to/PHPMailer/src/Exception.php';
   require 'path/to/PHPMailer/src/PHPMailer.php';
   require 'path/to/PHPMailer/src/SMTP.php';
   
   function sendEmail($templateId) {
      $conn = new mysqli("localhost", "user", "password", "database");
      $template = $conn->query("SELECT * FROM templates WHERE id = $templateId")->fetch_assoc();
      $users = $conn->query("SELECT email FROM users");
   
      $mail = new PHPMailer(true);
      $mail->isSMTP();
      $mail->Host = 'smtp.example.com';
      $mail->SMTPAuth = true;
      $mail->Username = 'your_email@example.com';
      $mail->Password = 'your_password';
      $mail->SMTPSecure = 'tls';
      $mail->Port = 587;
   
      $mail->setFrom('your_email@example.com', 'Mailer');
      $mail->isHTML(true);
   
      while ($user = $users->fetch_assoc()) {
          $mail->addAddress($user['email']);
          $mail->Subject = $template['subject'];
          $mail->Body = $template['content'];
          $mail->send();
          $mail->clearAddresses();
      }
   
      $stmt = $conn