我将为您提供一个完整的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);
注意事项
- 隐私合规：确保符合GDPR等隐私法规
- 邮件服务商限制：某些邮件服务商可能会阻止跟踪像素
- 性能优化：跟踪像素应快速响应
- 安全措施：防止滥用和伪造
这个系统可以准确跟踪邮件的打开和点击情况,为邮件营销提供重要数据支持。
