...

View Full Version : ordering by month



mint suite
11-22-2006, 04:10 PM
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??

GeXus
11-22-2006, 04:14 PM
Do a group by month in your query.

mint suite
11-22-2006, 05:04 PM
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>


<? } ?>

GeXus
11-22-2006, 05:09 PM
Change your query to this..



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

mint suite
11-22-2006, 05:24 PM
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>



<? } ?>

GeXus
11-22-2006, 05:30 PM
try this



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

mint suite
11-22-2006, 05:36 PM
hhhhmmmmm, we're back to January, 1970 again.


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

GeXus
11-22-2006, 05:43 PM
SELECT date as date, COUNT(*) as count FROM events
GROUP BY date
ORDER BY date desc


Does that work? :) Without the formating.

mint suite
11-22-2006, 05:46 PM
That's solved it - thanks.

I can stop banging my head on my table now.

:thumbsup:

guelphdad
11-22-2006, 07:03 PM
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?

mint suite
11-22-2006, 07:28 PM
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?

GeXus
11-22-2006, 07:56 PM
If you were using unix timestamp, then you could use date_format to get just the month, and do a distinct select.

mint suite
11-22-2006, 08:04 PM
Ok - I'll try that then.

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

GeXus
11-22-2006, 08:12 PM
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'

mint suite
11-22-2006, 08:22 PM
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?

GeXus
11-22-2006, 08:38 PM
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

mint suite
11-22-2006, 08:47 PM
I just tried that again - it still only displays january.

The date is of type: date.

Is that correct?

GeXus
11-22-2006, 08:51 PM
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..

mint suite
11-22-2006, 09:00 PM
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.

GeXus
11-22-2006, 09:01 PM
What order do you want? change desc to asc

mint suite
11-22-2006, 09:04 PM
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

GeXus
11-22-2006, 09:31 PM
It is sorting it right.. because it is sorting based on year...

mint suite
11-22-2006, 09:37 PM
are you sure that they are?

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

GeXus
11-22-2006, 09:40 PM
Do this


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

mint suite
11-22-2006, 09:49 PM
in phpmyadmin I get this:

December/2006
January/2007
November/2006

GeXus
11-22-2006, 10:05 PM
SELECT DATE_FORMAT( date, "%M/%Y" ) AS date FROM events GROUP BY date ORDER BY DATE_FORMAT( date, "%y%m%d" ) desc

mint suite
11-22-2006, 10:11 PM
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>


<? } ?>

GeXus
11-22-2006, 10:19 PM
How do you want it to be formated on the page?

mint suite
11-22-2006, 10:24 PM
by month in the correct year order.(asc)

i.e.

this month and the next two months would be:

November
December
January


cheers

GeXus
11-22-2006, 10:26 PM
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>

mint suite
11-22-2006, 10:36 PM
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?

GeXus
11-22-2006, 11:04 PM
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..

mint suite
11-22-2006, 11:17 PM
That's it.

Great stuff.

Thanks loads for all of that help.

:D

GeXus
11-22-2006, 11:21 PM
Awsome, yeah no prob.. :)



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum