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

问题描述：

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 : 123-4567 Products: P123, P432

Customer: Bam Phone: 345-6789 Products: P033, P320, P675

Customer: Gin Phone: 444-1234 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!

我不确定如何为标题说出问题，但这就是我要做的事情。我有一张桌子： p>

table1 p>

  -------------------  ---------------- \ N | 客户| 电话|  productid | 
 ----------------------------------- 
 | 约翰|  123-4567 |  P123 | 
 ----------------------------------- 
 |  Bam |  345-6789 |  P033 | 
 ----------------------------------- 
 | 约翰|  123-4567 |  P432 | 
 ----------------------------------- 
 | 杜松子酒|  444-1234 |  P543 | 
 ----------------------------------- 
 |  Bam |  345-6789 |  P320 | 
 ----------------------------------- 
 |  Bam |  345-6789 |  P675 | 
 ----------------------------------- 
  code>  pre> \  n 
 我正试图得到这样的结果： p> 
 
 
 客户： strong> John 
 电话： strong>  123-4567 
 产品 strong>：P123，P432  p> 
 
 
 客户： strong> Bam 
 电话： strong> 345  -6789 
 产品： strong> P033，P320，P675  p> 
 
 
 客户 strong>：Gin 
 电话： strong>  444-1234 
 产品： strong> P543  p> 
 
 
我不确定是否有一个mysql查询可以让我得到这个结果但是我确实尝试过 它在PHP上，不知道从哪里开始。 下面是我目前使用的php代码： p> 
 
 

  $ sql =“SELECT * FROM table1 ORDER BY user”; 
 $ result = mysql_query（$ sql）;  
 
而（$ row = mysql_fetch_array（$ result））{
 
 echo'＆lt; / br＆gt;客户：'。$ row ['customer']。'＆lt; / br＆gt;电话：'。$ row  ['phone']。'＆lt; / br＆gt;产品：'。$ row ['productid']。'＆lt; / br＆gt;'; 
} 
  code>  pre> 
 
 我知道我需要在while语句中添加一个代码，“当它是同一个用户时，将productid连接到一个变量”，但我无法将其转换为代码。 p> 
 \  n 希望这是有道理的。 提前致谢！ p> 
  div>

答

You can use GROUP_CONCAT:

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

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

相关推荐