求助：实现按参与活动分组生成Excel文件的PHP代码及SQL调试

我需要实现按参与者所参与的特定活动/事件筛选数据并生成Excel文件，现有一段PHP代码及SQL查询语句，但无法满足需求，恳请协助调试代码与SQL执行逻辑。

现有代码 #

$program_admin = $_SESSION['username'];
$actvty_ID = $_POST['id'];
//$activitydate = $_POST['activity_date'];

if(isset($_POST['export_in_excel'])) {
    $excel_sql = "SELECT *
                  FROM projectcode pj
                  INNER JOIN activities ac ON ac.projects_id = pj.projects_id
                  INNER JOIN participants re ON re.act_id = ac.id
                  WHERE pj.project_code='$program_admin'
                  ORDER BY re.id DESC "; //pj.project_code='$program_admin'
   
    $excel_result = mysqli_query($connect, $excel_sql);

    if(mysqli_num_rows($excel_result) > 0) {
        $excel_output .='
        <table class="excel_table" style="font-family:Calibri; border:1px solid #1f004d;">
        <tr><td></td></tr>
            <tr>
                <th style="text-align:center; font-weight:bold; border:1px solid #1f004d;">No.</th>
                <th colspan="2" style="text-align:center; font-weight:bold; border:1px solid #1f004d;">Name</th>
                <th style="text-align:center; font-weight:bold; border:1px solid #1f004d; ">Age</th>
                <th style="text-align:center; font-weight:bold; border:1px solid #1f004d; ">Gender</th>
                <th style="text-align:center; font-weight:bold; border:1px solid #1f004d; ">City/Municipality</th>
                <th style="text-align:center; font-weight:bold; border:1px solid #1f004d; ">Province</th>
                <th style="text-align:center; font-weight:bold; border:1px solid #1f004d; ">Contact No.</th>
                <th style="text-align:center; font-weight:bold; border:1px solid #1f004d; ">Email Address</th>
                <th style="text-align:center; font-weight:bold; border:1px solid #1f004d; ">Organization</th>
                <th style="text-align:center; font-weight:bold; border:1px solid #1f004d; ">Signature</th>
            </tr>
        ';
        $no = 1;
        while($excel_row = mysqli_fetch_array($excel_result)) {
            $excel_output .='
                <tr>
                    <td style="text-align:center;">'.$no.'</td>
                    <td>'.$excel_row["firstname"].'</td>
                    <td>'.$excel_row["lastname"].'</td>
                    <td>'.$excel_row["agerange"].'</td>
                    <td>'.$excel_row["gender"].'</td>
                    <td>'.$excel_row["city_municipality"].'</td>
                    <td>'.$excel_row["province"].'</td>
                    <td>'.$excel_row["mobileno"].'</td>
                    <td>'.$excel_row["email"].'</td>
                    <td>'.$excel_row["org_office"].'</td>
                    <td></td>
                    
                </tr>
            ';
            $no ++;
        }
        $excel_output .='</table>';
        header("Content-Type: application/xls");
        header("Content-Disposition: attachment; filename=Attendance Sheet.xls");
        echo $excel_output;
        
    }
}

当前问题 #

当前代码导出的Excel会包含该管理员名下所有活动的参与者，无法筛选出指定活动的人员数据，不符合“按特定活动筛选”的需求。

解决方案 #

1. 添加活动ID筛选条件 #

在SQL查询的WHERE子句中加入活动ID匹配条件，锁定目标活动。

2. 改用预处理语句防SQL注入 #

原代码直接拼接变量到SQL存在安全风险，替换为mysqli预处理语句。

3. 增加参数校验 #

确保活动ID参数有效，避免空值或无效值导致的错误查询。

修改后的完整代码 #

$program_admin = $_SESSION['username'];
$actvty_ID = $_POST['id'] ?? '';
//$activitydate = $_POST['activity_date'];

if(isset($_POST['export_in_excel']) && !empty($actvty_ID)) {
    // 预处理语句防止SQL注入
    $excel_sql = "SELECT *
                  FROM projectcode pj
                  INNER JOIN activities ac ON ac.projects_id = pj.projects_id
                  INNER JOIN participants re ON re.act_id = ac.id
                  WHERE pj.project_code = ? AND ac.id = ?
                  ORDER BY re.id DESC ";
   
    $stmt = mysqli_prepare($connect, $excel_sql);
    mysqli_stmt_bind_param($stmt, "si", $program_admin, $actvty_ID);
    mysqli_stmt_execute($stmt);
    $excel_result = mysqli_stmt_get_result($stmt);

    if(mysqli_num_rows($excel_result) > 0) {
        $excel_output = '
        <table class="excel_table" style="font-family:Calibri; border:1px solid #1f004d;">
        <tr><td></td></tr>
            <tr>
                <th style="text-align:center; font-weight:bold; border:1px solid #1f004d;">No.</th>
                <th colspan="2" style="text-align:center; font-weight:bold; border:1px solid #1f004d;">Name</th>
                <th style="text-align:center; font-weight:bold; border:1px solid #1f004d; ">Age</th>
                <th style="text-align:center; font-weight:bold; border:1px solid #1f004d; ">Gender</th>
                <th style="text-align:center; font-weight:bold; border:1px solid #1f004d; ">City/Municipality</th>
                <th style="text-align:center; font-weight:bold; border:1px solid #1f004d; ">Province</th>
                <th style="text-align:center; font-weight:bold; border:1px solid #1f004d; ">Contact No.</th>
                <th style="text-align:center; font-weight:bold; border:1px solid #1f004d; ">Email Address</th>
                <th style="text-align:center; font-weight:bold; border:1px solid #1f004d; ">Organization</th>
                <th style="text-align:center; font-weight:bold; border:1px solid #1f004d; ">Signature</th>
            </tr>
        ';
        $no = 1;
        while($excel_row = mysqli_fetch_array($excel_result)) {
            $excel_output .= '
                <tr>
                    <td style="text-align:center;">'.$no.'</td>
                    <td>'.$excel_row["firstname"].'</td>
                    <td>'.$excel_row["lastname"].'</td>
                    <td>'.$excel_row["agerange"].'</td>
                    <td>'.$excel_row["gender"].'</td>
                    <td>'.$excel_row["city_municipality"].'</td>
                    <td>'.$excel_row["province"].'</td>
                    <td>'.$excel_row["mobileno"].'</td>
                    <td>'.$excel_row["email"].'</td>
                    <td>'.$excel_row["org_office"].'</td>
                    <td></td>
                    
                </tr>
            ';
            $no ++;
        }
        $excel_output .= '</table>';
        // 使用标准Excel内容类型
        header("Content-Type: application/vnd.ms-excel");
        // 文件名加入活动ID便于区分
        header("Content-Disposition: attachment; filename=Attendance_Sheet_Activity_".$actvty_ID.".xls");
        echo $excel_output;
        
    } else {
        echo "该活动暂无参与者数据";
    }
} else {
    echo "请选择有效的活动";
}

关键修改说明 #

• SQL新增ac.id = ?条件，绑定$actvty_ID实现特定活动筛选
• 替换为预处理语句，彻底规避SQL注入风险
• 增加$actvty_ID非空校验，避免无效查询
• 修改文件名规则，加入活动ID方便区分不同活动的导出文件
• 修正Content-Type为标准的application/vnd.ms-excel，提升兼容性

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