2

I have MySQL table like this,

id  time  day

1   1:55  Mon
2   2:00  Sun
3   1:00  Mon
4   3:00  Tue
5   2:30  Sun

I can filter all result using this query

SELECT * FROM time_slots

but how to filter result like this output

Sun
-----
2:00 2:30

Mon
-------
1:00 1:55

Tue
------
3:00

is this possible to get result using only MySQL or should I use PHP MySQL both?

plain jane
  • 1,009
  • 1
  • 8
  • 19
Gayan
  • 2,845
  • 7
  • 33
  • 60
  • Look at this: http://stackoverflow.com/questions/1202919/mysql-dayofweek-my-week-begins-with-monday and this: http://stackoverflow.com/questions/1126961/order-by-day-of-week-in-mysql – Babblo Dec 30 '13 at 03:36

2 Answers2

3

Use GROUP_CONCAT() with GROUP BY

SELECT 
  day,
  GROUP_CONCAT(`time` SEPARATOR ' ') AS times
FROM 
  time_slots
GROUP BY 
  day
ORDER BY
  `time` ASC
John Conde
  • 217,595
  • 99
  • 455
  • 496
  • @JohnConde this is perfect but how to manage single time slot from this, i mean when i loop result, can get concatenate result, if i want to delete single time slot, it is not possible – Gayan Dec 30 '13 at 03:47
  • If you want to modify the results you will either need to modify the query to use a WHERE clause that removes unwanted results or use PHP to sort and modify the results. – John Conde Dec 30 '13 at 03:48
1

Use FIELD() function to order by on day from Sun to Sat.

Try this: 

SELECT t.day, GROUP_CONCAT(t.time SEPARATOR ' ') AS times
FROM time_slots t
GROUP BY t.day
ORDER BY FIELD(t.day, 'Sun', 'Mon', 'Tue', 'Wed', 'Thurs', 'Fri', 'Sat'), t.time
Saharsh Shah
  • 28,687
  • 8
  • 48
  • 83