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.
need query that groups dates by month
Moderator: General Moderators
Re: need query that groups dates by month
what will be the input , just the year
Or are you going to loop for year , month?
Or are you going to loop for year , month?
Re: need query that groups dates by month
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
so you are not using mysql date??
Re: need query that groups dates by month
Right, I cannot use MySQL date because the dates are stored in Epoch format.
Re: need query that groups dates by month
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
That's great, thanks! Being able to work with the UNIX dates as dates in the queries is exactly what I really needed.