Page 1 of 1
need query that groups dates by month
Posted: Tue Apr 20, 2010 8:49 pm
by rhecker
I have a MySQL database of events. There might be several events each month. I can generate a list of the events, ordered by date, but what I can't figure out, is how to separate them by month.
In other words, I want to end up with a list like the following:
APRIL
4/3/2010 Beginning Yoga
4/6/2010 Advanced Yoga
4/20/2010 Flower arranging
MAY
5/7/2010 Introduction to Botony
5/9/2010 Advanced Yoga
I know how to retrieve the month names using date(F) but I don't know how to create the subgroups based on month. The dates are stored as Epoch values.
Any help with how to write this query appreciated.
Re: need query that groups dates by month
Posted: Fri Apr 23, 2010 6:10 pm
by yacahuma
what will be the input , just the year
Or are you going to loop for year , month?
Re: need query that groups dates by month
Posted: Fri Apr 23, 2010 6:26 pm
by rhecker
I will need to use month and year so that January 2011 doesn't come before June 2010. Below is as far as I got with the code, but it doesn't work. I think I have two issues. Firstly, since the dates are stored as integers in the database, I can't use MySQL to group the startdate column by month and year, so I have to have PHP do it. Secondly, I just don't know how to write the sequence of loops that will result in the format I show in my first post. All ideas appreciated!
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: need query that groups dates by month
Posted: Fri Apr 23, 2010 8:31 pm
by yacahuma
so you are not using mysql date??
Re: need query that groups dates by month
Posted: Fri Apr 23, 2010 8:38 pm
by rhecker
Right, I cannot use MySQL date because the dates are stored in Epoch format.
Re: need query that groups dates by month
Posted: Fri Apr 23, 2010 9:37 pm
by yacahuma
Code: Select all
select FROM_UNIXTIME(date1),EXTRACT(YEAR_MONTH FROM FROM_UNIXTIME(date1))
from test WHERE EXTRACT(YEAR_MONTH FROM FROM_UNIXTIME(date1))=201004
Re: need query that groups dates by month
Posted: Sat Apr 24, 2010 5:49 am
by rhecker
That's great, thanks! Being able to work with the UNIX dates as dates in the queries is exactly what I really needed.