MySql Query / PHP以相同的客户名称获取所有列值

Question

I'm not really sure how to word my problem for the title but here's what i'm trying to do. I have a table:

table1

-----------------------------------
| customer |   phone  | productid |
-----------------------------------
|   John   | 123-4567 |  P123     |
-----------------------------------
|   Bam    | 345-6789 |  P033     |
-----------------------------------
|   John   | 123-4567 |  P432     |
-----------------------------------
|   Gin   | 444-1234 |  P543     |
-----------------------------------
|   Bam    | 345-6789 |  P320     |
-----------------------------------
|   Bam    | 345-6789 |  P675     |
-----------------------------------

And I'm trying to get a result like this:

Customer: John Phone : [phone] Products: P123, P432

Customer: Bam Phone: [phone] Products: P033, P320, P675

Customer: Gin Phone: [phone] Products: P543

I'm not sure if there's a mysql query that would allow me to get this result but I did try to do it on PHP and didn't know where to start. Below is the php code that i'm currently using:

$sql = "SELECT * FROM table1 ORDER BY user";
$result = mysql_query($sql);

while ($row = mysql_fetch_array($result)) {

    echo '</br>Customer: '.$row['customer'].'</br>Phone: '.$row['phone'].'</br>Products: '.$row['productid'].'</br>';
}

I know that i need to add a code inside the while statements that would do "while it's the same user, concatenate productid in to a variable" but I just can't translate it into codes.

Hope this makes sense. Thanks in advance!

Answer 1

You can use GROUP_CONCAT:

SELECT 
  customer, 
  phone, 
  GROUP_CONCAT(productid SEPARATOR ', ') as product_ids
FROM table1
GROUP BY customer