Problem
I have a table of groups, and a table of members. Each member belongs to one group. This is how it looks (more groups then 2 are possible):
Groups
id | name | year | ------------------ 1 | Nice | 1966 | 2 | Hard | 2012 |
Member
id | name | idGroup | ---------------------- 1 | Bernd | 1 | 2 | Max | 1 | 3 | Tom | 2 | 4 | Bob | 1 | 5 | Alice | 2 |
I want to compute a table, where each group is listed once, together with all its members, like this:
name | year | members | ------------------------------- Nice | 1966 | Bernd, Max, Bob | Hard | 2012 | Tom, Alice |
At the moment, I use two queries to get this table:
<table>
<tr>
<td>Groupname</td>
<td>Year</td>
<td>Members</td>
</tr>
<?php
$db = new mysqli("localhost","user","pass","database");
$sql = "SELECT id, name, year FROM groups";
$groupsResult = $db->query($sql);
while($group = $groupsResult->fetch_assoc())
{
$groupName = htmlspecialchars($group['name']);
$groupYear = htmlspecialchars($group['year']);
$members = "";
$stmt = $db->prepare('SELECT name FROM member WHERE idGroup = ?');
$stmt->bind_param('i',$group['id']);
$stmt->execute();
$memberResult = $stmt->get_result();
while($member = $ergebnis->fetch_assoc())
{
if(!empty($members)) $members .= ", ";
$members .= htmlspecialchars($member["name"]);
}
?>
<tr>
<td><?php echo $groupName; ?></td>
<td><?php echo $groupYear; ?></td>
<td><?php echo $members; ?></td>
</tr>
<?php
}
?>
</table>
But I have the feeling that this is not a good approach.
I know that I can get with an INNER JOIN the result.
id | name | groupName | year ------------------------------ 1 | Bernd | Nice | 1966 2 | Max | Nice | 1966 3 | Tom | Hard | 2012 4 | Bob | Nice | 1966 5 | Alice | Hard | 2012
but this does not seem to help me. Is it possible to get with one SQL directly the result:
name | year | members | ------------------------------- Nice | 1966 | Bernd, Max, Bob | Hard | 2012 | Tom, Alice |
or is there another way how I can optimize this code?
Solution
$sql = <<<'EOSQL'
SELECT g.name, g.year, GROUP_CONCAT(m.name SEPARATOR ', ') AS members
FROM groups g INNER JOIN members m on g.id = m.idGroup
GROUP BY m.idGroup
EOSQL;
The key insight here is that GROUP_CONCAT and GROUP are what you need to combine multiple rows of the result into one. Note that GROUP is a SQL name. It is just coincidence that you also have a table named groups.
I also used a Nowdoc because the SQL string is using multiple lines. I picked Nowdoc because we aren’t doing any interpolation or other escaping in the string.
It’s obsolete now (assuming you use the INNER JOIN version), but in
while($member = $ergebnis->fetch_assoc())
{
if(!empty($members)) $members .= ", ";
$members .= htmlspecialchars($member["name"]);
}
It looks like you forgot to change a variable name. I think that $ergebnis should be $memberResult. But the main point I wanted to make is that at the expense of some mildly duplicate code, you don’t need to check every iteration to see if it is the first.
if ($member = $memberResult->fetch_assoc())
{
$members = htmlspecialchars($member['name']);
while ($member = $memberResult->fetch_assoc())
{
$members .= ', ' . htmlspecialchars($member['name']);
}
}
On the first row, you just set the variable. On subsequent rows, you append the separator and the new value to the variable. This produces the same result without all the checks to see if you are on the first iteration.
I find it more readable to put a space between while and the opening parenthesis. That makes it easier to see that we are not making a function call. The additional whitespace also breaks up the line and makes it easier for a human to parse (the compiler doesn’t care either way).
In PHP, a double-quoted string allows variable interpolation. So if I’m not using variable interpolation, I use a single-quoted string. There’s a slight performance benefit. But the real advantage is that when I see a single-quoted string, I know that I don’t have to look for variables being interpolated.
?>
I find it more readable to always put the ?> at the far left (no indent) when the PHP block spans multiple lines. Perhaps that is just me, but I find it easier to scan for ?> if they are always in the same place.