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:
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";
}
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";
$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 . ":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";
}
}
