grouping calendar results by month

[max_naylor]

Hello... this is my first post here and I need some help. I’m completely new to PHP and MySQL, but I am fluent in HTML and CSS... i.e. I can make websites look pretty but not make them do very much. I am trying to write a script which will support an events calendar. I have a MySQL database set up on my server, and I have been able to get a basic PHP query up and running which takes events from the database and plonks them out onto a page, into a nice neat CSS table.

This works fine, however my question revolves around how I can get these events to separate by month. I would like the events to be grouped by subheading, i.e. so it says October 2008, and underneath which are all the events for October 2008, then the next subheading November, December etc. The current month needs to be the first heading on display. I can’t figure out how to make the events appear underneath each heading, as all my query does at the moment is print out the entire contents of the database into a nice little table. Any help or suggestions as to how I might do this would be greatly appreciated. My exisiting PHP code is below:

<?php
include 'config.php';
include 'opendb.php';

$eventquery = 'SELECT * FROM `Calendar Events` LIMIT 0, 30 ';
$eventresult = mysql_query($eventquery);

while($row = mysql_fetch_array($eventresult, MYSQL_ASSOC))
{
echo "<div id=\"eventrow\"><div id=\"eventdate\"><strong>{$row['event_date']} {$row['event_month']}{$row['event_enddate']} {$row['event_endmonth']}</strong></div> <div id=\"eventtitle\"><img src=\"images/flag_{$row['event_flag']}.gif\" align=\"absmiddle\"/>&nbsp;&nbsp;" .
"{$row['event_name']}</div>" .
"<div id=\"eventlocation\">{$row['event_location']}</div>" .
"<div id=\"eventlink\"><a href=\"{$row['event_link']}\">Website &raquo;</a></div></div>";
}

include 'closedb.php';
?>

[edited by: eelixduppy at 11:58 pm (utc) on Oct. 28, 2008]
[edit reason] removed URL [/edit]

[mooger35]

what type are the date fields in your mysql database?

varchar, timestamp, datetime, date, etc...?

[Anyango]

<?php
include 'config.php';
include 'opendb.php';

$eventquery = 'SELECT * FROM `Calendar Events` ORDER BY event_month LIMIT 0, 30 ';
$eventresult = mysql_query($eventquery);
$lastPrintedMonth="";
while($row = mysql_fetch_array($eventresult, MYSQL_ASSOC))
{
if($row['event_month']!=$lastPrintedMonth)
{
echo "<br>{$row['event_month']}<br>"; //add all your row formatting here please
$lastPrintedMonth=$row['event_month'];
}

echo "<div id=\"eventrow\"><div id=\"eventdate\"><strong>{$row['event_date']} {$row['event_month']}{$row['event_enddate']} {$row['event_endmonth']}</strong></div> <div id=\"eventtitle\"><img src=\"images/flag_{$row['event_flag']}.gif\" align=\"absmiddle\"/>&nbsp;&nbsp;" .
"{$row['event_name']}</div>" .
"<div id=\"eventlocation\">{$row['event_location']}</div>" .
"<div id=\"eventlink\"><a href=\"{$row['event_link']}\">Website &raquo;</a></div></div>";
}

include 'closedb.php';
?>

[edited by: Anyango at 3:54 am (utc) on Oct. 29, 2008]

[max_naylor]

Thank you, this is exactly the solution I was looking for! I do have another small question though, is there any way to do multiple sorts? What I’m looking for is a way to have the list sorted by event_month, and then within that the events being sorted by event_date... to keep all of the events in date order within the month headings. Is this doable?

[nick279]

You can normally go SORT BY `field1`, `field2` and it will sort multiple times

[max_naylor]

Thanks, this solved the issue.