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

Question

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: 
  event1, 
  event2
March 11th: 
  event4, 
  event5

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

Thanks!

EDIT:

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";
    }

What is the actual date type? I mean, is it (I hope) a datetime or timestamp, or do you mean that it is a string formatted as you say? — Nanne
– Nanne, Commented Mar 14, 2012 at 22:09
Your edit has GROUP BY created_day on the end of the SQL. You need to remove that otherwise it will only return one row per date. You can use DATE(`date`) instead of DATE_FORMAT(`date`,'%Y-%m-%d'). — user1191247
– user1191247, Commented Mar 14, 2012 at 23:14

Shehary · Accepted Answer · 2017-11-03 14:19:56Z

1

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";
$foo=array();
$result = mysql_query($query);
while($row = mysql_fetch_assoc($result)) {
    //some logic to test if it's safe to add the name
    $foo[$row['d']][]=$row['name'];
}

And then when i'd 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 it fits the format you set to yourself.

Hope that helped

edited Nov 3, 2017 at 14:19

Shehary

9,99810 gold badges47 silver badges75 bronze badges

answered Mar 14, 2012 at 22:56

Kharaone

5974 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Starx · Accepted Answer · 2012-03-14 22:23:57Z

1

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";
   }
}

answered Mar 14, 2012 at 22:23

Starx

79.3k50 gold badges187 silver badges266 bronze badges

Comments

Surasin Tancharoen · Accepted Answer · 2012-03-14 22:14:15Z

0

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

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

It does not really 'group' dates as your wish but I think you can use php to continue from this result.

answered Mar 14, 2012 at 22:14

Surasin Tancharoen

5,9284 gold badges36 silver badges40 bronze badges

Comments

Francisco Paulo · Accepted Answer · 2012-03-14 23:17:30Z

0

I agree with Kharaone, separate logic from display. That being said, I think that 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;

answered Mar 14, 2012 at 23:17

Francisco Paulo

6,36230 silver badges25 bronze badges

Collectives™ on Stack Overflow

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

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related