php - 显示 1 行内的多行数据 MySQL PHP

Question

我有以下从 3 个表中提取的查询。我最终会每行有 1 个家庭，但每个家庭有多个 child 。我希望能够显示家庭行中所有 child 的年龄。我考虑过打开另一个连接/查询，但认为有一种更聪明的方法。

查询:

SELECT 
    families.*, job.*, children.*, families.first_name AS fam_firstname, children.first_name AS child_firstname
FROM job
    LEFT OUTER JOIN families ON job.fam_id = families.fam_id
    LEFT OUTER JOIN children ON families.fam_id = children.fam_id
WHERE 
    job.published = 2 
GROUP BY job.job_id
ORDER BY job.created_on DESC

循环:

if ($result = $mysqli->query($query)) {

    $from = new DateTime($row['dob']);
    $to   = new DateTime('today');

    while ($row = $result->fetch_assoc()) {
         echo '<tr>';
         echo '<td>' .$row['fam_firstname']. '</td>';
         echo '<td>' .$row['last_name'].'</td>';

         /* Looking to list all children ages. Separate by comma or break  */
         echo '<td>' . $from->diff($to)->y .'</td>';

         echo '</tr>';
    }

    $result->free();
}

期望的输出:

Family First Name  |   Family Last Name   |   Child 1 Age, Child 2 Age

Answer 1

您需要使用mysql group_concat函数来实现这一点:

SELECT 
    families.*, group_concat(children.age)
FROM job
    LEFT OUTER JOIN families ON job.fam_id = families.fam_id
    LEFT OUTER JOIN children ON families.fam_id = children.fam_id
WHERE 
    job.published = 2 
group by families.fam_id

按 job.created_on DESC 排序