March 14, 2012

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

Question by porfuse

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

I want to group the events by day, and I wasn’t sure how to approach this- is there a way to select only the month and day from the field? or should i use PHP after I select all the “events”

my end goal is to have something like this:

March 10th: 
March 11th: 

I found MySQL select using datetime, group by date only 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



ended up 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 by Kharaone

You should indeed use php to get this done. But since most of current system sepate logic from display, I’d use only one pass and 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";
$result = mysql_query($query);
while($row = mysql_fetch_assoc($result)) {
    //some logic to test if it's safe to add the name


And then when i’d need it (through a template or your “view”)

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

so it fits the format you set to yourself.

Hope that helped

Answer by Starx

If you use group by you will not get one row out of it. So the way you want is not possible through Group By 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";

Author: Nabin Nepal (Starx)

Hello, I am Nabin Nepal and you can call me Starx. This is my blog where write about my life and my involvements. I am a Software Developer, A Cyclist and a Realist. I hope you will find my blog interesting. Follow me on Google+


Please fill the form - I will response as fast as I can!