Jarla Jarla - 5 months ago 26
PHP Question

How can I group rows with the same value from mySQL into php?

This is my mySQL table

animals
:

╔══════════╦══════╗
║ animal ║ name ║
╠══════════╬══════╣
║ dog ║ sam ║
║ cat ║ fred ║
║ cat ║ todd ║
║ bird ║ alan ║
╚══════════╩══════╝


I want to select all data into a table:

$sql = 'SELECT * FROM animals';
foreach ($pdo->query($sql) as $row) {
echo '<td>'.$row['animal'].' </td>';
echo '<td>'.$row['name'].' </td>';
}


My result is:

╔══════════╦══════╗
║ dog ║ sam ║
║ cat ║ fred ║
║ cat ║ todd ║
║ bird ║ alan ║
╚══════════╩══════╝


But I want to output rows with the same animal only once, but with all the names in one row, like this:

╔══════════╦═════════════╗
║ dog ║ sam ║
║ cat ║ fred, todd ║
║ bird ║ alan ║
╚══════════╩═════════════╝


I have no idea how to achieve this. I try to think in this direction:

SELECT * FROM animals GROUP BY animal


But I am stuck! I am happy for every hint!

Answer

MySQL has a non-standard SQL function called GROUP_CONCAT specifically to do this.

Use as:

SELECT  animal, GROUP_CONCAT(name) as grouped_name  FROM animals GROUP BY animal

Use in PHP:

 $sql = ' SELECT  animal, GROUP_CONCAT(name) as grouped_name  FROM animals GROUP BY animal';
 foreach ($pdo->query($sql) as $row) {
     echo '<td>'.$row['animal'].' </td>';
     echo '<td>'.$row['grouped_name'].' </td>'; 
 }

Note how the column with the group is renamed/aliased to grouped_name this is because that column is not the name column anymore. You can refer to this column by its alias grouped_name in your sql result.