如何调整查询逻辑实现全量用户的会员月度缴费状态表格展示

问题根源 #

原查询使用INNER JOIN关联用户表和缴费表，仅会保留两张表中匹配成功的行，没有任何缴费记录的用户会被直接过滤，所以无法展示全量用户。

快速修复方案 #

1. 修改SQL查询 #

将关联逻辑改为LEFT JOIN，同时分组字段改为用户表的主键，避免无缴费记录的用户分组失败，可根据需求指定查询年份：

function getUserWithInvoices ($targetYear = 2024)
{ 
    global $pdo;
    // LEFT JOIN保留所有用户，筛选指定年份的缴费记录，无记录的字段返回NULL
    $stmt = $pdo->prepare("
        SELECT mv.id, mv.firstname, mv.lastname, 
               GROUP_CONCAT(inv.month) as paid_months
        FROM users mv 
        LEFT JOIN invoices inv ON mv.id = inv.user_id AND inv.year = :year
        GROUP BY mv.id
    ");
    $userlist_with_invoices = [];
    if ($stmt->execute(['year' => $targetYear])) {
        while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
            // 把已缴费的月份转成数组，无记录则为空数组
            $paidMonths = $row['paid_months'] ? explode(',', $row['paid_months']) : [];
            $userlist_with_invoices[] = new UserlistWithInvoice(
                $row['id'],
                $row['firstname'],
                $row['lastname'],
                $paidMonths
            );
        }
    }
    return $userlist_with_invoices;
}

2. 简化UserlistWithInvoice逻辑 #

在类中新增判断月份是否缴费的方法，不用单独定义12个month属性：

class UserlistWithInvoice {
    public $firstname;
    public $lastname;
    private $paidMonths;

    public function __construct($id, $firstname, $lastname, $paidMonths) {
        $this->firstname = $firstname;
        $this->lastname = $lastname;
        $this->paidMonths = array_flip($paidMonths); // 转成键值对加快查找
    }

    public function isPaid($month) {
        return isset($this->paidMonths[$month]) ? '√' : '';
    }
}

3. 优化前端渲染逻辑 #

循环输出12个月份的列，减少重复代码：

<table id="memberfee_table" class="table table-striped table-bordered">
    <thead>
        <tr>
            <th>名</th>
            <th>姓</th>
            <th>Jan.</th><th>Feb.</th><th>Mar.</th><th>Apr.</th>
            <th>Mai</th><th>Jun.</th><th>Jul.</th><th>Aug.</th>
            <th>Sep.</th><th>Okt.</th><th>Nov.</th><th>Dez.</th>
        </tr>
    </thead>
    <tbody>
    <?php foreach ($userlist_with_invoices as $user): ?>
        <tr>
            <td><?= $user->firstname ?></td>
            <td><?= $user->lastname ?></td>
            <?php for ($i = 1; $i <= 12; $i++): ?>
            <td><?= $user->isPaid($i) ?></td>
            <?php endfor; ?>
        </tr>
    <?php endforeach; ?>
    </tbody>
</table>

更推荐的实现思路 #

如果数据量不大，完全可以拆分两次查询，逻辑更清晰，避免GROUP_CONCAT的长度限制、分隔符冲突等潜在问题：

1. 第一次查询全量用户列表
2. 第二次查询指定年份的所有缴费记录，按userid分组存储为[userid => [1=>true,3=>true]]格式的缴费映射表
3. 渲染用户时直接从映射表中查询对应月份是否缴费即可

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