Page 1 of 1

I know I'm doing this wrong - grouping dates

Posted: Thu Apr 22, 2010 3:49 am
by rhecker
My MySQL database has an integer field startdate which holds the dates when various courses will start. These are in epoch format. I can easily retrieve all the course details by date, but what I want to do is group dates by month. The following code snippet is really clumsey and does not achieve what I am after, but it's as close as I can get. In the first query I obtain the sequence of months. What I can't do is figure out how to get the subgroup of course details to follow the appropriate month heading. I would appreciate any help anyone can give me with this.

Code: Select all

$currentdate = date (U);
$getmonth = mysql_query ("select startdate from course WHERE startdate > $currentdate order by startdate");
while ($month = mysql_fetch_array($getmonth)){
$stopmonth = $monthtext;	
extract ($month);
$monthtext = date('F Y', $startdate);
if ($stopmonth != $monthtext) {
	echo "<h2>$monthtext</h2>";
}

$getdate = mysql_query ("select id, startdate, enddate from course WHERE startdate > $currentdate order by startdate");
while ($date = mysql_fetch_array($getdate)){
extract ($date);
$start = date('F', $startdate);
echo "ID: $id DATE: $start<br/>";
}}

Re: I know I'm doing this wrong - grouping dates

Posted: Fri Apr 23, 2010 7:36 pm
by mecha_godzilla
What you might need to do is nest the whole of the $getdate section inside the while() test used after the $getmonth query, but update the WHERE clause so it looks something like

Code: Select all

$getdate = mysql_query ("SELECT id, startdate, enddate FROM course WHERE startdate > $currentdate AND month = $month ORDER BY startdate");
I'm sure the code probably isn't spot-on but you get the idea! Not sure if this is exactly what you want but post an example of how you want the data formatted if not then it will be easier to work out what the script needs to do.

HTH,

Mecha Godzilla

Re: I know I'm doing this wrong - grouping dates

Posted: Sat Apr 24, 2010 5:59 am
by rhecker
Mecha Godzilla, thanks for giving this your attention. Yacahuma in the database forum showed me how to use UNIXTIME(date_variable) to work with dates stored as integers in UNIX format. Being able to do this is really what I needed.