ordering by month

Hi

I need to create a list of months to act as links. There will be a number of events in a database each with a date (example: 2oo6-1o-1o).

If there is at least one event in a given month I want to display the month's name (October) but only once, i.e. if I have 15 events in October I only want October to appear once as a link.

I can get the months to display but only per event, i.e. 15 times for 15 events.

Any ideas??

Do a group by month in your query.

Thanks for that - I've tried the code below but all I'm getting is January, 1970 in every li ????

I have no idea where that date is coming from......


<?
$query = "SELECT month(date), COUNT(*) FROM `events` GROUP BY `month(date)` ORDER BY `date` desc ";
$result = mysql_query($query) or die('could not get date info'.mysql_error());
while($month = mysql_fetch_array($result)){
$thisdate = $month['date'];
$event_date = explode("-", $thisdate);
?>
<li><a href='events-listings.php?m=<? echo $month['events_id'];?>'><? echo date ("F, Y", mktime (0,0,0,$event_date[1],$event_date[2],$event_date[0])); ?></a></li>


<? } ?>

Change your query to this..


SELECT month(date) as date, COUNT(*) as count FROM events
GROUP BY date
ORDER BY date desc

Ok,
So now the dates in my database : 2007-07-11 ,2024-03-20,2022-06-20

are displaying as

November, 2002
November, 2005
November, 2006

<?
$query = "SELECT month(date) as date, COUNT(*) as count FROM events GROUP BY date ORDER BY date asc ";
$result = mysql_query($query) or die('could not get date info'.mysql_error());
while($month = mysql_fetch_array($result)){
$thisdate = $month['date'];
$event_date = explode("-", $thisdate);
?>
<li><a href='events-listings.php?m=<? echo $month['events_id'];?>'><? echo date ("F, Y", mktime (0,0,0,$event_date[1],$event_date[2],$event_date[0])); ?></a></li>



<? } ?>

try this


SELECT date_format(date, '%M') as date, COUNT(*) as count FROM events
GROUP BY date
ORDER BY date desc

hhhhmmmmm, we're back to January, 1970 again.


Thanks for all this help by the way - you're definately on my christmas card list!

SELECT date as date, COUNT(*) as count FROM events
GROUP BY date
ORDER BY date desc


Does that work? :) Without the formating.

That's solved it - thanks.

I can stop banging my head on my table now.

:thumbsup:

That is not what you asked for. you said if you had multiple events in October you only wanted October to show up once. The last query you were given will give you a new row for every day in October that you have a date for. also why are you using PHP to format your dates when mysql will do it for you?

yes - I know. I spoke too soon. I didin't have any dates in my db that where in the same month of the same year.

could you suggest a way that I can get this working - i.e. only one instance of each month?

If you were using unix timestamp, then you could use date_format to get just the month, and do a distinct select.

Ok - I'll try that then.

Do I need to change the format that the date is in in my database?

yeah, the format would be unix timestamp.. you would insert the date by using the now() mysql function and the column should be of type 'datetime'

ok - the date is being added via a cms that uses a pop up calender that out puts the date as yyyy-mm-dd

I would need to convert this to unix timestamp before it goes into the db?

I've found information in changing from a time stamp to yyyy-mm-dd but I can't find anything that tells me how to do it the other way?

I just tested this on my end and it works fine, using the same date structure you have. yyyy-mm-dd

SELECT date_format( date, '%M' ) AS date
FROM date_test
GROUP BY date
ORDER BY date DESC

I just tried that again - it still only displays january.

The date is of type: date.

Is that correct?

Yeah i have date type..

Do you have phpmyadmin? try just running the query in there... see what you get.. get rid of the count(*) and everything...

also, can you do just a select * and show me what you have..

SELECT * FROM events GROUP BY date ORDER BY date asc

The above just gives a list of each month including repetition.

SELECT date_format( date, '%M' ) AS date FROM events GROUP BY date ORDER BY date asc

The above query using phpmyadmin doesn't give multiples of the same month but it doesn't put them in the correct order.

What order do you want? change desc to asc

They seem to be random:

2006-12-09, 2006-11-17, 2006-12-10, 2007-01-18

should show as

november
december
january

instead i got

december
january
november

It is sorting it right.. because it is sorting based on year...

are you sure that they are?

of course I want them in order of year, but I don't think that they are....

Do this

SELECT date_format( date, '%M/%Y' ) AS date FROM events GROUP BY date ORDER BY date asc

in phpmyadmin I get this:

December/2006
January/2007
November/2006

SELECT DATE_FORMAT( date, "%M/%Y" ) AS date FROM events GROUP BY date ORDER BY DATE_FORMAT( date, "%y%m%d" ) desc

Great - that works in phpmyadmin.

on my page i'm still getting january's.

Here's the full code incase something else is effecting it.

<?
$query = "SELECT DATE_FORMAT( date, '%M/%Y' ) AS date FROM events GROUP BY date ORDER BY DATE_FORMAT( date, '%y%m%d' ) desc";
$result = mysql_query($query) or die('could not get date info'.mysql_error());
while($month = mysql_fetch_array($result)){
$thisdate = $month['date'];
$event_date = explode("-", $thisdate);
?>
<li><a href='events-listings.php?m=<? echo date ("n", mktime (0,0,0,$event_date[1],$event_date[2],$event_date[0])); ?>'><? echo date ("F", mktime (0,0,0,$event_date[1],$event_date[2],$event_date[0])); ?></a></li>


<? } ?>

How do you want it to be formated on the page?

by month in the correct year order.(asc)

i.e.

this month and the next two months would be:

November
December
January


cheers

So your query should look like this
SELECT DATE_FORMAT( date, "%M" ) AS date FROM events GROUP BY date ORDER BY DATE_FORMAT( date, "%y%m%d" ) desc

And the rest would look like this

<li><a href='events-listings.php?m=<? echo $month['date']; ?>'><? echo $month['date']; ?></a></li>

That's excellent.

Before the link variable 'm' was a number that corresponded to the month (i.e. 1 for January)

Is it possible for 'm' to return to a number?

You could do this

SELECT DATE_FORMAT( date, "%M" ) AS date, DATE_FORMAT( date, "%y%m%d" ) AS date2
FROM events
GROUP BY date
ORDER BY DATE_FORMAT( date, "%y%m%d" ) DESC

Then you could change the order of %y%m%d, however you want..

That's it.

Great stuff.

Thanks loads for all of that help.

:D

Awsome, yeah no prob.. :)