PHP 邮件跟踪打开

wen PHP项目 2026-08-10 134

我将为您提供一个完整的PHP邮件跟踪系统实现方案。

PHP 邮件跟踪打开

邮件跟踪系统实现

数据库表结构

-- 邮件跟踪表
CREATE TABLE email_tracking (
    id INT AUTO_INCREMENT PRIMARY KEY,
    email_id VARCHAR(64) NOT NULL UNIQUE,
    recipient_email VARCHAR(255) NOT NULL,
    subject VARCHAR(255),
    sent_at DATETIME NOT NULL,
    opened_at DATETIME,
    opened_count INT DEFAULT 0,
    user_agent TEXT,
    ip_address VARCHAR(45),
    status ENUM('sent', 'opened', 'clicked') DEFAULT 'sent',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_email_id (email_id),
    INDEX idx_recipient (recipient_email)
);
-- 点击跟踪表
CREATE TABLE email_links (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tracking_id INT NOT NULL,
    original_url TEXT NOT NULL,
    redirect_url VARCHAR(255) NOT NULL,
    click_count INT DEFAULT 0,
    last_clicked_at DATETIME,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (tracking_id) REFERENCES email_tracking(id)
);
-- 点击日志表
CREATE TABLE email_clicks (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tracking_id INT NOT NULL,
    link_id INT NOT NULL,
    clicked_at DATETIME NOT NULL,
    user_agent TEXT,
    ip_address VARCHAR(45),
    referrer TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (tracking_id) REFERENCES email_tracking(id),
    FOREIGN KEY (link_id) REFERENCES email_links(id)
);

邮件发送类

<?php
class EmailTracker {
    private $pdo;
    public function __construct($pdo) {
        $this->pdo = $pdo;
    }
    /**
     * 生成邮件跟踪ID
     */
    private function generateTrackingId() {
        return md5(uniqid(mt_rand(), true));
    }
    /**
     * 发送邮件并记录跟踪
     */
    public function sendWithTracking($recipient, $subject, $htmlContent) {
        // 生成跟踪ID
        $trackingId = $this->generateTrackingId();
        // 保存发送记录
        $stmt = $this->pdo->prepare(
            "INSERT INTO email_tracking (email_id, recipient_email, subject, sent_at) 
             VALUES (?, ?, ?, NOW())"
        );
        $stmt->execute([$trackingId, $recipient, $subject]);
        $trackingRecordId = $this->pdo->lastInsertId();
        // 创建跟踪像素 (1x1透明图片)
        $trackingPixelUrl = "https://yourdomain.com/track.php?id={$trackingId}";
        $trackingHtml = "<img src='{$trackingPixelUrl}' width='1' height='1' alt='' style='display:none'>";
        // 处理链接跟踪
        $processedHtml = $this->processLinks($htmlContent, $trackingRecordId);
        // 组合最终内容
        $finalContent = $processedHtml . $trackingHtml;
        // 发送邮件（使用PHP mail或第三方库）
        $this->sendMail($recipient, $subject, $finalContent);
        return $trackingId;
    }
    /**
     * 处理邮件中的链接，添加跟踪
     */
    private function processLinks($htmlContent, $trackingRecordId) {
        $pattern = '/href=["\'](.*?)["\']/i';
        return preg_replace_callback($pattern, function($matches) use ($trackingRecordId) {
            $url = $matches[1];
            // 跳过不需要跟踪的链接
            if (strpos($url, 'http') !== 0) {
                return $matches[0];
            }
            // 创建链接跟踪记录
            $stmt = $this->pdo->prepare(
                "INSERT INTO email_links (tracking_id, original_url, redirect_url) 
                 VALUES (?, ?, ?)"
            );
            $redirectUrl = "https://yourdomain.com/click.php?id={$trackingRecordId}&url=" . urlencode($url);
            $stmt->execute([$trackingRecordId, $url, $redirectUrl]);
            return 'href="' . $redirectUrl . '"';
        }, $htmlContent);
    }
    /**
     * 发送邮件
     */
    private function sendMail($to, $subject, $htmlContent) {
        $headers = "MIME-Version: 1.0\r\n";
        $headers .= "Content-type: text/html; charset=UTF-8\r\n";
        $headers .= "From: Your Company <noreply@yourdomain.com>\r\n";
        mail($to, $subject, $htmlContent, $headers);
    }
}

跟踪像素处理文件 (track.php)

<?php
// track.php - 处理打开跟踪
require_once 'config.php';
// 获取跟踪ID
$trackingId = isset($_GET['id']) ? $_GET['id'] : '';
$recipient = isset($_GET['r']) ? base64_decode($_GET['r']) : '';
if (empty($trackingId)) {
    // 输出1x1透明像素
    header('Content-Type: image/gif');
    echo base64_decode('R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7');
    exit;
}
try {
    $pdo = new PDO('mysql:host=localhost;dbname=yourdb', 'user', 'pass');
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    // 更新打开记录
    $stmt = $pdo->prepare("
        UPDATE email_tracking 
        SET opened_at = IFNULL(opened_at, NOW()),
            opened_count = opened_count + 1,
            user_agent = ?,
            ip_address = ?,
            status = 'opened'
        WHERE email_id = ?
    ");
    $userAgent = $_SERVER['HTTP_USER_AGENT'] ?? '';
    $ipAddress = $_SERVER['REMOTE_ADDR'] ?? '';
    $stmt->execute([$userAgent, $ipAddress, $trackingId]);
    // 输出1x1透明像素
    header('Content-Type: image/gif');
    header('Content-Length: 42');
    echo base64_decode('R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7');
} catch (Exception $e) {
    // 错误日志
    error_log("Tracking error: " . $e->getMessage());
    // 仍然输出透明像素
    header('Content-Type: image/gif');
    echo base64_decode('R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7');
}

链接点击处理 (click.php)

<?php
// click.php - 处理链接点击
require_once 'config.php';
$linkId = isset($_GET['id']) ? intval($_GET['id']) : 0;
$trackingId = isset($_GET['tid']) ? intval($_GET['tid']) : 0;
if ($linkId <= 0) {
    header('Location: /');
    exit;
}
try {
    $pdo = new PDO('mysql:host=localhost;dbname=yourdb', 'user', 'pass');
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    // 获取原始链接
    $stmt = $pdo->prepare("SELECT * FROM email_links WHERE id = ?");
    $stmt->execute([$linkId]);
    $link = $stmt->fetch(PDO::FETCH_ASSOC);
    if ($link) {
        // 记录点击
        $clickStmt = $pdo->prepare("
            INSERT INTO email_clicks (tracking_id, link_id, clicked_at, user_agent, ip_address, referrer) 
            VALUES (?, ?, NOW(), ?, ?, ?)
        ");
        $userAgent = $_SERVER['HTTP_USER_AGENT'] ?? '';
        $ipAddress = $_SERVER['REMOTE_ADDR'] ?? '';
        $referrer = $_SERVER['HTTP_REFERER'] ?? '';
        $clickStmt->execute([$trackingId, $linkId, $userAgent, $ipAddress, $referrer]);
        // 更新点击统计
        $updateStmt = $pdo->prepare("
            UPDATE email_links 
            SET click_count = click_count + 1,
                last_clicked_at = NOW()
            WHERE id = ?
        ");
        $updateStmt->execute([$linkId]);
        // 重定向到原始URL
        header('Location: ' . $link['original_url']);
        exit;
    }
} catch (Exception $e) {
    error_log("Click tracking error: " . $e->getMessage());
}
header('Location: /');
exit;

查询报告功能

<?php
class EmailTrackingReport {
    private $pdo;
    public function __construct($pdo) {
        $this->pdo = $pdo;
    }
    /**
     * 获取邮件打开统计
     */
    public function getOpenStats($emailId) {
        $stmt = $this->pdo->prepare("
            SELECT * FROM email_tracking 
            WHERE email_id = ? 
        ");
        $stmt->execute([$emailId]);
        return $stmt->fetch(PDO::FETCH_ASSOC);
    }
    /**
     * 获取邮件列表统计
     */
    public function getEmailListStats() {
        $query = "
            SELECT 
                email_id,
                recipient_email,
                subject,
                sent_at,
                opened_at,
                opened_count,
                status
            FROM email_tracking
            ORDER BY sent_at DESC
            LIMIT 50
        ";
        return $this->pdo->query($query)->fetchAll(PDO::FETCH_ASSOC);
    }
    /**
     * 获取链接点击统计
     */
    public function getLinkStats($emailId) {
        $stmt = $this->pdo->prepare("
            SELECT 
                el.original_url,
                el.click_count,
                el.last_clicked_at,
                COUNT(ec.id) as total_clicks
            FROM email_links el
            LEFT JOIN email_clicks ec ON el.id = ec.link_id
            WHERE el.tracking_id = (
                SELECT id FROM email_tracking WHERE email_id = ?
            )
            GROUP BY el.id
        ");
        $stmt->execute([$emailId]);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }
    /**
     * 生成HTML报告
     */
    public function generateReport($emailId) {
        $emailStats = $this->getOpenStats($emailId);
        $linkStats = $this->getLinkStats($emailId);
        $html = "<h2>邮件统计报告</h2>";
        $html .= "<p>发送给: " . htmlspecialchars($emailStats['recipient_email']) . "</p>";
        $html .= "<p>发送时间: " . $emailStats['sent_at'] . "</p>";
        if ($emailStats['opened_at']) {
            $html .= "<p>打开时间: " . $emailStats['opened_at'] . "</p>";
            $html .= "<p>打开次数: " . $emailStats['opened_count'] . "</p>";
            $html .= "<p>IP地址: " . htmlspecialchars($emailStats['ip_address']) . "</p>";
        } else {
            $html .= "<p>状态: <span style='color:red'>未打开</span></p>";
        }
        if (!empty($linkStats)) {
            $html .= "<h3>链接点击统计:</h3>";
            $html .= "<ul>";
            foreach ($linkStats as $link) {
                $html .= "<li>" . htmlspecialchars($link['original_url']) . 
                        " - 点击次数: " . $link['total_clicks'] . "</li>";
            }
            $html .= "</ul>";
        }
        return $html;
    }
}

使用示例

<?php
// send_email.php
require_once 'EmailTracker.php';
$pdo = new PDO('mysql:host=localhost;dbname=yourdb', 'user', 'pass');
$tracker = new EmailTracker($pdo);
$htmlContent = "
    <h1>产品更新通知</h1>
    <p>点击<a href='https://example.com/product'>这里</a>查看新产品</p>
    <p>访问我们的<a href='https://example.com/blog'>博客</a></p>
";
// 发送邮件并跟踪
$trackingId = $tracker->sendWithTracking(
    'recipient@example.com',
    '产品更新',
    $htmlContent
);
echo "邮件已发送，跟踪ID: " . $trackingId;
// 查看统计
$report = new EmailTrackingReport($pdo);
echo $report->generateReport($trackingId);