MySQL: Group Records By Date

Question

Let's say I have a table order as following:

o_id | o_uid | o_date
1    | 5     | June 10, 2015
2    | 1     | June 10, 2015
3    | 8     | June 10, 2015
5    | 15    | June 11, 2015
6    | 11    | June 11, 2015
7    | 16    | June 12, 2015
8    | 19    | June 12, 2015

I tried running the following query:

SELECT o_id, o_uid FROM order GROUP BY o_date

I thought it will give me result as follows:

[
    "June 10, 2015" => [
        [
            "o_id" => 1
            "o_uid" => 5
        ],
        [
            "o_id" => 2
            "o_uid" => 1
        ],
        [
            "o_id" => 3
            "o_uid" => 8
        ]
    ],
    "June 11, 2015" => [
        [
            "o_id" => 4
            "o_uid" => 15
        ],
        [
            "o_id" => 5
            "o_uid" => 11
        ]
    ],
    ...
]

The query does not provide results as expected. I can use PHP to get the required results but why not use MySQL if you can and finish things off in a line. The GROUP BY Clause is quite confusing. Is there any other clause which can group records by date?

The result I get: SQL Fiddle

Answer 1

use

SELECT o_id, GROUP_CONCAT(o_id),GROUP_CONCAT(o_uid),o_date FROM `order`  
GROUP BY o_date

And then separate comma separated list using php/do manipulation accordingly.

Output : Formatter output from sqlfiddle.

o_id    GROUP_CONCAT(o_id)  GROUP_CONCAT(o_uid) o_date
1          1,2,3            5,1,8               June 10, 2015
4          4,5              15,11               June 11, 2015
6          6,7              16,19               June 12, 2015

Fiddle