如何修改PHP脚本实现单脚本批量向多个邮箱发送不同活动的会议邮件

原代码核心问题 #

• 活动基础字段（title/start_date/meetingID等）仅在查询执行后读取了第一条记录的值，后续所有逻辑都复用第一个活动的信息，无法支持多活动并行发送
• 外层for循环无实际作用：$registerID是单个字符串，count($registerID)实际取的是字符串长度而非注册记录总数
• 未按活动/会话维度对参会者分组，所有参会者邮箱被合并后仅发送一次邮件，且用的是第一个活动的通知内容

调整后可支持多活动并行发送的代码 #

<?php
$events = new WA_MySQLi_RS("events",$rsvp,0);
$events->setQuery("Select * from (select * from registrants) t1 left join (select *, sessionID as thesessionID from sessions where sent is NULL and start_date = CURDATE() and CURRENT_TIME() >= (start_time - INTERVAL 30 MINUTE)) t2 on (t1.sessionID = t2.sessionID) left join (select *, eventID as theeventID from events) t3 on (t3.eventID = t2.eventID) left join (select * from parents) t4 on (t4.parentID= t1.parentID) left join (select * from zoom_google_meet) t5 on (t5.sessionID = t2.sessionID) left join (select * from events) t6 on (t6.eventID = t2.eventID) where virtual_inperson = 'Virtual' group by t1.registerID order by parent_lname");
$events->execute();

$notice_url1 = 'https://zoom.us/join';
$notice_url2 = 'https://meet.google.com/';
// 按会话ID分组存储每个会话的信息与对应参会者邮箱
$sessionGroups = [];

while(!$events->atEnd()) {
    $sessionId = $events->getColumnVal("thesessionID");
    // 过滤无匹配待发送会话的记录
    if(empty($sessionId)) {
        $events->moveNext();
        continue;
    }
    // 首次遇到该会话时存储会话基础配置
    if(!isset($sessionGroups[$sessionId])) {
        // 此处判断会议类型的字段请根据zoom_google_meet表的实际字段调整
        $isZoom = !empty($events->getColumnVal("meetingID"));
        $sessionGroups[$sessionId] = [
            'title' => $events->getColumnVal("title"),
            'start_date' => date("m/d/Y", strtotime($events->getColumnVal("start_date"))),
            'start_time' => date("h:i a", strtotime($events->getColumnVal("start_time"))),
            'meetingID' => $events->getColumnVal("meetingID"),
            'password' => $events->getColumnVal("password"),
            'join_url' => $isZoom ? $notice_url1 : $notice_url2,
            'emails' => []
        ];
    }
    // 收集有效参会者邮箱
    $email = trim($events->getColumnVal("parent_email"));
    if(!empty($email)) {
        $sessionGroups[$sessionId]['emails'][] = $email;
    }
    $events->moveNext();
}

if(!empty($sessionGroups)) {
    foreach($sessionGroups as $sid => $session) {
        $emailList = array_unique($session['emails']);
        if(empty($emailList)) continue;
        
        $emailStr = implode(",", $emailList);
        $message = "Thank you for signing up to attend {$session['title']} on {$session['start_date']} at {$session['start_time']}. To login to the meeting, please use the following information:

{$session['join_url']}
Meeting ID: {$session['meetingID']}
Password: {$session['password']}

Reminder: You must login to the meeting using the same name you registered with or you won't be allowed into the meeting.
";
        $message = wordwrap($message);
        $subject = "{$session['title']} Login Instructions | {$session['start_date']}";
        $headers = "From: schoolnews@cpsd.us\r\n";
        $headers .= "Bcc: {$emailStr}\r\n";
        
        if(mail('', $subject, $message, $headers)) {
            // 发送成功后标记会话为已发送，避免重复发送
            $updateSent = new WA_MySQLi_RS("updateSent", $rsvp, 0);
            $updateSent->setQuery("UPDATE sessions SET sent = 1 WHERE sessionID = ?");
            $updateSent->bindParam("i", $sid);
            $updateSent->execute();
        } else {
            print "Error: 无法为会话ID {$sid} 发送邮件，收件人列表：{$emailStr}<br>";
        }
    }
}
?>

补充说明 #

• 代码中判断会议类型（Zoom/Google Meet）的逻辑，请根据你zoom_google_meet表的实际存储字段调整
• 新增了发送成功后标记sessions表sent字段为1的逻辑，避免脚本每30分钟运行时重复发送通知
• 对同一活动的重复邮箱做了去重处理，避免同一用户收到同一活动的重复通知

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