php mysql date 格式,PHP的MySQL的日期与yyyy-mm-dd格式

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

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

}

解决方案

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