Categorising by date

This one’s a challenge!

[max_naylor]

Hello... I’m posting again in relation to a website I asked about a few months ago. I have the following script:

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

$eventquery = 'SELECT * FROM `Calendar Events` ORDER BY `event_date` , `event_enddate` ASC LIMIT 0 , 100';
$eventresult = mysql_query($eventquery);
$month = date("M");
$lastPrintedMonth="";
while($row = mysql_fetch_array($eventresult, MYSQL_ASSOC))
{
$converteddate = strtotime($row['event_date']);
$extractedmonth = $converteddate['mon'];
if($extractedmonth!=$lastPrintedMonth)
{
echo "<div id=\"dateheader\">$extractedmonth</div>";
$lastPrintedMonth=$extractedmonth;
}

echo "<div id=\"eventrow\"><div id=\"eventdate\"><strong>{$row['event_date']}</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>" ;
}
?>

I would like it to extract the month and year from a date string in the format YYYY-MM-DD (stored in $row['event_date']) and create subheadings for each month and year. I had this working fine until I switched from a varchar to a date field, which was required because of expansion elsewhere in the site. As it stands, the script above does not work, and I was wondering if it were possible to modify it so it does, including headers for years. At the moment, the script ignores the months and just echoes a nice list of events.

Thanks,

Max

[lokeshshettyk]

You may try and modify your query to get the MONTH and YEAR from event_date - more on this [dev.mysql.com...]

Cheers :)

[max_naylor]

That would be useful, but how would I get that working, like this?

SELECT * FROM `Calendar Events` MONTHNAME(`event_date`) ORDER BY `event_date` , `event_enddate` ASC LIMIT 0 , 100

[Alcoholico]

SELECT *, MONTHNAME(`event_date`) AS monthname FROM `Calendar Events`  ORDER BY `event_date` , `event_enddate` ASC LIMIT 0 , 100