PHP按单字段分组多值字段的foreach循环问题及DataTable实现

需求说明 #

现有两个数据库表：

• commitment表：包含ref_no（编号）、comm_date（承诺日期）字段
• collection表：包含ref_no（关联编号）、amount（收款金额）、trnx_date（收款日期）字段

需在DataTable中实现：

• 按ref_no分组展示
• 显示每条ref_no对应的comm_date
• 展示该ref_no下所有收款记录的汇总，格式为金额(日期) + 金额(日期) + ... = 总金额；若无收款记录则显示0

现有问题 #

原SQL通过LEFT JOIN后GROUP BY，仅能获取单条收款记录，无法汇总所有收款信息；控制器循环也仅处理单条记录，无法生成目标格式的汇总文本。

解决步骤 #

1. 修改模型中的SQL查询 #

利用MySQL的GROUP_CONCAT拼接所有收款记录，SUM计算总金额，IFNULL处理无收款记录的场景：

SELECT 
    c.ref_no,
    c.comm_date,
    -- 拼接每条收款记录：金额(日期)，用+分隔
    GROUP_CONCAT(CONCAT(t.amount, '(', DATE_FORMAT(t.trnx_date, '%Y-%m-%d'), ')') SEPARATOR ' + ') AS coll_details,
    -- 计算总金额，无记录则返回0
    IFNULL(SUM(t.amount), 0) AS total_amount
FROM commitment c
LEFT JOIN collection t ON c.ref_no = t.ref_no
GROUP BY c.ref_no, c.comm_date

2. 调整控制器代码 #

处理SQL返回的拼接字符串与总金额，生成符合要求的汇总文本：

public function collection_statement_list() {
    // Datatables变量处理
    $draw = intval($this->input->get("draw"));
    $start = intval($this->input->get("start"));
    $length = intval($this->input->get("length"));
    
    $fetch = $this->Project_model->get_collection_statement();
    $data = array();

    foreach($fetch->result() as $r) {
        $ref_no = $r->ref_no;
        $comm_date = $this->Project_model->set_date_format($r->comm_date);
        
        // 生成收款汇总文本
        if ($r->total_amount == 0) {
            $coll_summary = '0';
        } else {
            // 给日期添加样式，拼接详情与总金额
            $formatted_details = str_replace('(', '<span class="text-primary"><small>(', $r->coll_details);
            $formatted_details = str_replace(')', ')</small></span>', $formatted_details);
            $coll_summary = "{$formatted_details} = {$r->total_amount}";
        }
        
        $data[] = array(
            $ref_no,
            $comm_date,
            $coll_summary,
        );
    }

    // 获取总记录数（需单独查询，避免GROUP BY影响计数）
    $total_records = $this->Project_model->get_total_commitments();
    
    $output = array(
        "draw" => $draw,
        "recordsTotal" => $total_records,
        "recordsFiltered" => $total_records,
        "data" => $data
    );
    echo json_encode($output);
    exit();
}

3. 补充模型中的总记录数查询 #

添加方法获取commitment表总记录数，用于DataTable的计数：

public function get_total_commitments() {
    return $this->db->count_all('commitment');
}

注意事项 #

• GROUP_CONCAT默认有长度限制（默认1024字符），若收款记录较多，可通过SET GLOBAL group_concat_max_len = 10000;临时调整，或在MySQL配置文件中永久设置
• 若需支持DataTable的搜索、排序功能，需在SQL中添加对应的WHERE、ORDER BY子句处理

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