How to write a SQL query to group records by field and return an array in the result?

For example, there is a table with a history of orders. There are fields ID , ID_CLIENT , SUMMA , ORDER :

  • ID = This is the order number.
  • ID_CLIENT = Client ID to which the order belongs
  • SUMMA = Order Amount
  • ORDER = Order List

With a normal query, select * from order group by id_client returns one order from client.

I need to end up with a three-dimensional array like this:

  • $list[ID_CLIENT][ID_ORDER] - Array with order information
  • $list[ID_CLIENT] - Array with orders
  • $list = Array with clients
  • only with pens in php code you collect, group_concat has limitations - BOPOH
  • If you are given an exhaustive answer, mark it as correct (a daw opposite the selected answer). - Nicolas Chabanovsky ♦

1 answer 1

PDO and one of its magic constants are suitable for this, namely PDO :: FETCH_GROUP , for which you will need to select id_client as the first column. In this case, all the records will be grouped exactly as it should:

 $sql = "SELECT id_client, * FROM ..."; $data = $pdo->query($sql)->fetchAll(PDO::FETCH_GROUP);

GROUP BY is not needed

  • I'm afraid in the second dimension ID_ORDER will not be (there will just be an array of 0,1,2 ...), and the vehicle wants it ... - Mike
  • He is not needed there. - Ipatiev