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

Question

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 1

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.

Answer 2

SELECT  
  a.animal
  GROUP_CONCAT(a.name) names
FROM animals a
GROUP BY a.animal

Note check my changed query and change your php fragment $row['name'] to $row['names']

Update change your loop to:

        foreach ($pdo->query($sql) as $row) {
            echo '<tr><td>'.$row['animal'].' </td>';
            echo '<td>'.$row['names'].' </td></tr>';
        }

Answer 3

You can acheive it using GROUP BY AND GROUP_CONCAT.

GROUP BY is used in conjunction with aggregate functions to group the result by one or more columns.

GROUP_CONCAT is a aggregate function used to concatenate the columns which is being grouped.

---

So in your case you should GROUP BY animal column and apply GROUP_CONCAT on the same. Like this:

SELECT  
  A.animal
  GROUP_CONCAT(A.name)
FROM animals A
GROUP BY A.animal