PHP/MySQL邮件发送脚本故障：邮件多发，需保障用户数据隔离

Hey there! Let's figure out why your users are getting duplicate emails with their custom reports. Based on the code snippet you shared and common pitfalls with this kind of script, here are the most likely causes and fixes:

1. You're Sending an Email for Every Row in the Query Result #

Looking at your SQL query, it pulls all oports records linked to a single user (db_users.id = '{$sqlid}'). If that user has multiple entries in the oports table, and your code sends an email inside the loop that iterates over query results, the user will get one email per row—way more than they need.

Fix: #

First, gather all the user's data into a single HTML table, then send one email with the full table. Here's a revised approach (plus we'll fix the critical SQL injection risk):

// Use prepared statements to avoid SQL injection (never skip this!)
$sql = "SELECT oports.id, oports.handlowiec, oports.data_rozp, oports.data_przed, 
               oports.nazwa, oports.city, oports.nip, oports.inic, db_users.email 
        FROM oports 
        INNER JOIN db_users ON db_users.id = oports.user_id 
        WHERE db_users.id = ?";

$stmt = $polaczenie->prepare($sql);
$stmt->bind_param("i", $sqlid); // Assuming $sqlid is an integer; adjust type if needed
$stmt->execute();
$result = $stmt->get_result();

// Build the full HTML table first
$htmlTable = "<table border='1'>
                <tr>
                    <th>ID</th><th>Handlowiec</th><th>Data Rozp</th><th>Data Przed</th>
                    <th>Nazwa</th><th>City</th><th>NIP</th><th>Inic</th>
                </tr>";

$userEmail = null;
while ($row = $result->fetch_assoc()) {
    // Capture the user's email (it'll be the same for all rows)
    if (!$userEmail) {
        $userEmail = $row['email'];
    }
    // Add each row to the table
    $htmlTable .= "<tr>
                    <td>{$row['id']}</td>
                    <td>{$row['handlowiec']}</td>
                    <td>{$row['data_rozp']}</td>
                    <td>{$row['data_przed']}</td>
                    <td>{$row['nazwa']}</td>
                    <td>{$row['city']}</td>
                    <td>{$row['nip']}</td>
                    <td>{$row['inic']}</td>
                  </tr>";
}
$htmlTable .= "</table>";

// Send ONE email with the complete table
if ($userEmail && $result->num_rows > 0) {
    $subject = "Your Custom Report";
    $headers = "MIME-Version: 1.0" . "\r\n";
    $headers .= "Content-type:text/html;charset=UTF-8" . "\r\n";
    
    mail($userEmail, $subject, $htmlTable, $headers);
}

$stmt->close();

2. Your User Loop Logic is Broken #

If you're running this script for multiple users (looping through all db_users entries), double-check that:

• You're resetting $sqlid correctly for each user
• The email send code isn't nested inside an inner loop (like the query result loop mentioned above)
• You aren't accidentally re-running the send logic multiple times per user

Quick Debug Tip: #

Add simple prints to confirm what's happening before each send:

echo "Preparing report for user ID: {$sqlid}, email: {$userEmail}\n";

3. Database Data Anomalies #

• Ensure db_users.id is a primary key (so no duplicate user IDs exist in the system)
• Verify oports.user_id doesn't have invalid entries that incorrectly link one user to another's data (unlikely, but worth checking)

内容的提问来源于stack exchange，提问作者Mateusz Nitka