Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Page 1 of 3 123 LastLast
Results 1 to 15 of 34
  1. #1
    New Coder
    Join Date
    Jun 2006
    Posts
    62
    Thanks
    2
    Thanked 0 Times in 0 Posts

    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??

  • #2
    New Coder
    Join Date
    Nov 2006
    Posts
    30
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Do a group by month in your query.

  • #3
    New Coder
    Join Date
    Jun 2006
    Posts
    62
    Thanks
    2
    Thanked 0 Times in 0 Posts
    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......

    Code:
    <?
    $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>
    			
    					
    <? } ?>

  • #4
    New Coder
    Join Date
    Nov 2006
    Posts
    30
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Change your query to this..

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

  • #5
    New Coder
    Join Date
    Jun 2006
    Posts
    62
    Thanks
    2
    Thanked 0 Times in 0 Posts
    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

    Code:
    <?
    				$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>
    				
    					
    					
    				<? } ?>

  • #6
    New Coder
    Join Date
    Nov 2006
    Posts
    30
    Thanks
    0
    Thanked 0 Times in 0 Posts
    try this

    Code:
    SELECT date_format(date, '%M') as date, COUNT(*) as count FROM events 
    GROUP BY date
    ORDER BY date desc
    Last edited by GeXus; 11-22-2006 at 04:33 PM.

  • #7
    New Coder
    Join Date
    Jun 2006
    Posts
    62
    Thanks
    2
    Thanked 0 Times in 0 Posts
    hhhhmmmmm, we're back to January, 1970 again.


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

  • #8
    New Coder
    Join Date
    Nov 2006
    Posts
    30
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Code:
    SELECT date as date, COUNT(*) as count FROM events 
    GROUP BY date
    ORDER BY date desc
    Does that work? Without the formating.

  • #9
    New Coder
    Join Date
    Jun 2006
    Posts
    62
    Thanks
    2
    Thanked 0 Times in 0 Posts
    That's solved it - thanks.

    I can stop banging my head on my table now.


  • #10
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,631
    Thanks
    4
    Thanked 147 Times in 138 Posts
    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?

  • #11
    New Coder
    Join Date
    Jun 2006
    Posts
    62
    Thanks
    2
    Thanked 0 Times in 0 Posts
    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?

  • #12
    New Coder
    Join Date
    Nov 2006
    Posts
    30
    Thanks
    0
    Thanked 0 Times in 0 Posts
    If you were using unix timestamp, then you could use date_format to get just the month, and do a distinct select.

  • #13
    New Coder
    Join Date
    Jun 2006
    Posts
    62
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Ok - I'll try that then.

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

  • #14
    New Coder
    Join Date
    Nov 2006
    Posts
    30
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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'

  • #15
    New Coder
    Join Date
    Jun 2006
    Posts
    62
    Thanks
    2
    Thanked 0 Times in 0 Posts
    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?


  •  
    Page 1 of 3 123 LastLast

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •