Php mysql group by date with format yyyy-mm-dd

Question

I had a mysql table called events with fields: id, date and name. The date field has the format yyyy-mm-dd hh :: mm: ss edit: means that it is in the datetime format

I want to group events per day, and I was not sure how to approach this - is there a way to select only a month and a day from the field? or should I use PHP after selecting all the "events"

my ultimate goal is to have something like this:

March 10th: event1, event2 March 11th: event4, event5 

I found MySQL using datetime, only by date , but I'm not sure how to implement it:

 SELECT DATE_FORMAT(date, '%H%i'), DATE_FORMAT(date, '%M %D'), name FROM events ORDER BY date 

Thanks!

EDIT:

finished using this:

 $sql = "select team1, team2, DATE_FORMAT(date,'%Y-%m-%d') as created_day FROM games WHERE attack = '1' GROUP BY created_day"; $result = mysql_query($sql); $curDate = ""; while (list($team1, $team2, $date) = mysql_fetch_row($result)) { if ($date != $curDate) { echo "$date --------\n"; $curDate = $date; } echo "game data: $team1 $team2"; } 

Answer 1

You really have to use php to do this. But since most of the current system logical partitions are from the display, I would use only one pass, not (NUMBER OF DAYS + 1) SELECTs, and prepare an array that I can reuse later for my display.

 $query = "SELECT DATE_FORMAT(date, '%M %D') as d, name FROM yourtable ORDER BY date"; $foo=array(); $result = mysql_query($query); while($row = mysql_fetch_assoc($result)) { //some logic to test if it safe to add the name $foo[$row['d']][]=$row['name']; } 

And then, when I need it (through a template or your "view")

 foreach($foo as $date => $events) { echo $date . ":\n\t"; echo implode(",\n\t", $events); echo "\n"; } 

so that it matches the format you set yourself.

Hope this helps

Answer 2

If you use group by , you will not get one line. Therefore, you cannot access the group through AFAIK.

 $query = "SELECT distinct(DATE_FORMAT(date, '%M %D')) as d FROM yourtable"; $result = mysql_query($query); while($row = mysql_fetch_assoc($result)) { echo $row['d'] $sql = "SELECT * FROM yourtable WHERE DATE_FORMAT(date, '%M %D')='$row[d]'"; $rs = mysql_query($query); while($r = mysql_fetch_assoc($rs)) { echo "event"; } } 

Answer 3

I think from this question / answer, you can get something like this instead

 March 10th, event1 March 10th, event2 March 11th, event4 March 11th, event5 

Actually this is not a “group” as your wish, but I think you can use php to continue this result.

Answer 4

I agree with Haraone, separate logic from the display. That being said, I think something similar to this query might be what you are looking for:

 SELECT A FROM ( SELECT DATE_FORMAT(date,'%M %D:') AS A, DATE(date) AS B, 1 AS C FROM games GROUP BY DATE(date) UNION ALL SELECT name AS A, DATE(date) AS B, 2 AS C FROM games ) X ORDER BY B, C ASC;