PHP+MySQL实现邮件防重复及按行数据发送至指定邮箱

Hey there! Let's walk through how to build this solution properly—sending personalized emails from your MySQL table using PHPMailer, and crucially, making sure we never send duplicate emails to anyone.

First, we need a way to track which emails have already been sent. Add two columns to your table to mark sent status and timestamp:

ALTER TABLE your_table_name 
ADD COLUMN email_sent TINYINT(1) DEFAULT 0,
ADD COLUMN sent_at DATETIME NULL;

• email_sent = 0 means the email hasn't been sent yet; 1 means it's been sent.
• sent_at records the exact time the email was sent, which helps with debugging later.

Note: Replace your_table_name with your actual table name.

First, get PHPMailer set up. If you use Composer, install it with:

composer require phpmailer/phpmailer

If you don't use Composer, download the PHPMailer files from its official repo and include them manually.

Here's a reusable function to initialize PHPMailer with your SMTP settings:

use PHPMailer\PHPMailer\PHPMailer;
use PHPMailer\PHPMailer\Exception;

// If using Composer
require 'vendor/autoload.php';
// If manual install, replace with:
// require 'path/to/PHPMailer/src/Exception.php';
// require 'path/to/PHPMailer/src/PHPMailer.php';
// require 'path/to/PHPMailer/src/SMTP.php';

function getMailer() {
    $mail = new PHPMailer(true);
    try {
        // SMTP Server Configuration
        $mail->isSMTP();
        $mail->Host       = 'smtp.your-provider.com'; // e.g., smtp.gmail.com for Gmail
        $mail->SMTPAuth   = true;
        $mail->Username   = 'your-email@example.com';
        $mail->Password   = 'your-app-password'; // For Gmail, use an App Password instead of your main password
        $mail->SMTPSecure = PHPMailer::ENCRYPTION_STARTTLS;
        $mail->Port       = 587;

        // Sender Details
        $mail->setFrom('your-email@example.com', 'Your App Name');
        $mail->isHTML(true); // Set to false if you want plain-text emails

        return $mail;
    } catch (Exception $e) {
        error_log("PHPMailer setup failed: {$mail->ErrorInfo}");
        return null;
    }
}

Now, connect to your database, pull only unsent records, send personalized emails, and mark them as sent once successful. We'll use PDO for secure database interactions:

// Database Connection (replace with your credentials)
$dsn = 'mysql:host=localhost;dbname=your_database_name;charset=utf8mb4';
$dbUser = 'your_db_username';
$dbPass = 'your_db_password';

try {
    $pdo = new PDO($dsn, $dbUser, $dbPass);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // Get only records that haven't been emailed yet
    $stmt = $pdo->prepare("SELECT id, tools, model, sn, expiration_date, user_email, `desc` FROM your_table_name WHERE email_sent = 0");
    $stmt->execute();
    $records = $stmt->fetchAll(PDO::FETCH_ASSOC);

    if (empty($records)) {
        echo "No unsent emails to process.";
        exit;
    }

    $mailer = getMailer();
    if (!$mailer) {
        echo "Failed to initialize email client. Check logs.";
        exit;
    }

    foreach ($records as $record) {
        try {
            // Add recipient
            $mailer->addAddress($record['user_email']);

            // Personalize email content
            $mailer->Subject = "Reminder: Your {$record['tools']} (Model {$record['model']}) Expires Soon";
            $mailer->Body    = "
                <p>Hi there,</p>
                <p>Just a quick reminder about your tool details approaching expiration:</p>
                <ul>
                    <li>Tool Type: {$record['tools']}</li>
                    <li>Model: {$record['model']}</li>
                    <li>Serial Number: {$record['sn']}</li>
                    <li>Expiration Date: {$record['expiration_date']}</li>
                    <li>Notes: {$record['desc']}</li>
                </ul>
                <p>Please take necessary action at your earliest convenience.</p>
                <p>Thanks,<br>Your App Team</p>
            ";
            // Plain-text fallback for non-HTML email clients
            $mailer->AltBody = "Hi there,\n\nJust a quick reminder about your tool details approaching expiration:\n\n- Tool Type: {$record['tools']}\n- Model: {$record['model']}\n- Serial Number: {$record['sn']}\n- Expiration Date: {$record['expiration_date']}\n- Notes: {$record['desc']}\n\nPlease take necessary action at your earliest convenience.\n\nThanks,\nYour App Team";

            // Send the email
            $mailer->send();
            echo "Email sent to {$record['user_email']} successfully.\n";

            // Mark the record as sent in the database
            $updateStmt = $pdo->prepare("UPDATE your_table_name SET email_sent = 1, sent_at = NOW() WHERE id = :id");
            $updateStmt->bindParam(':id', $record['id'], PDO::PARAM_INT);
            $updateStmt->execute();

        } catch (Exception $e) {
            error_log("Failed to send email to {$record['user_email']}: {$mailer->ErrorInfo}");
            echo "Failed to send email to {$record['user_email']}. Error logged.\n";
        } finally {
            // Clear recipients to avoid adding duplicate addresses in the next loop
            $mailer->clearAddresses();
        }
    }

} catch (PDOException $e) {
    error_log("Database error: {$e->getMessage()}");
    echo "Database error occurred. Check logs for details.";
}

• Always update the email_sent flag immediately after a successful send: Even if the script crashes mid-execution, already sent emails won't be reprocessed.
• Use database transactions (optional but recommended): To ensure the email send and database update are atomic (both succeed or both fail), wrap the send/update logic in a transaction:

  // Inside the foreach loop's try block:
  $pdo->beginTransaction();
  
  // ... send email ...
  
  $updateStmt->execute();
  $pdo->commit();
  
  // In the catch block:
  $pdo->rollBack();
  

• Prevent concurrent script runs: If you set up a cron job to run this script periodically, use a lock file or database lock to stop multiple instances from running at the same time (which could cause duplicates).

1. Set one test record's email_sent value to 0 and replace its user_email with your own email address.
2. Run localhost:88/run_email.php and check if you receive the email.
3. Verify the email_sent and sent_at fields in the database are updated correctly.
4. Check your server's error logs for any failed send attempts.

内容的提问来源于stack exchange，提问作者nur haryadi