我有以下查询，从3个表拉。我将以每行一个家庭结束，但每个家庭有多个孩子。我希望能够显示所有的孩子年龄在家庭行。我想打开另一个连接/查询，但发现有一个更聪明的方法。
查询: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
您需要使用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
ORDER BY job。created_on DESC
遵循这个问题:来自多个表的嵌套数组。
参考问题中的第二个选项，它解释了如何从JOIN查询中减去数据。
注:这是我问过自己的一个问题，关于你在这里要做的实现。如果您需要更多关于如何实现它的指导，请在评论中询问…
下面是在代码中实现它的一种方法(注意您应该通过"fam_firstname"来排序JOIN查询，以便此代码为您工作):
/* init temp vars to save current family's data */
$current = null;
$fam_firstname = null;
$children = array();
while ($row = mysql_fetch_assoc($result))
{
/*
if the current id is different from the previous id:
you've got to a new family.
print the previous family (if such exists),
and create a new one
*/
if ($row['fam_firstname'] != $fam_firstname )
{
// in the first iteration,
// current (previous family) is null,
// don't print it
if ( !is_null($current) )
{
$current['children'] = $children;
/*
Here you print the whole line
I'm just dumping it all here, but you can print
it more nicer...
*/
var_dump($current);
$current = null;
$fam_firstname = null;
$children = array();
}
// create a new family
$current = array();
$current['fam_firstname'] = $row['fam_firstname'];
/*
Add more columns value here...
*/
// set current as previous id
$fam_firstname = $current['fam_firstname'];
}
// you always add the phone-number
// to the current phone-number list
$children[] = $row['child_firstname'] . " is " . $row['child_age'] . " years old";
}
}
// don't forget to print the last family (saved in "current")
if (!is_null($current))
/*
Here you print the whole line
I'm just dumping it all here, but you can print
it more nicer...
*/
var_dump($current);