SitePoint Sponsor

User Tag List

Results 1 to 13 of 13
  1. #1
    Can we go to a 48 hour day?
    Join Date
    May 2002
    Location
    MI
    Posts
    906
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    help in best way to get months out of date list

    I have a table with a bunch of events recorded for specific days. The dates are just mysql date fields. What I want to do is get a list of months that have events in them. So if I have any event in october I would be able to get 2004-10-01 back, even if the date was 2004-10-14.

    What I will do then is provide a drop down list of months that have events so users can pull up the calendar for that month.

    Does anyone have any suggestions for doing this in a decent manner? There has to be a better way than searching through all the events without iterating through them all.

    Thanks for the help.
    mitechie.com
    "Techies just think a little differently
    ...at least that is what they keep telling me."

  2. #2
    SitePoint Enthusiast Refresh's Avatar
    Join Date
    Jul 2004
    Location
    Lausanne, Switzerland
    Posts
    46
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    PHP Code:
    <?php

    define
    ('EVENTS_TABLE''event');
    //should the events be available, or do we just want a month list?
    define('STORE_EVENTS'true);

    /*
    $sql = 'SELECT * FROM ' . EVENTS_TABLE . ' ORDER BY date ASC';
    $res = mysql_query($sql) or die(mysql_error());
    $events = mysql_fetch_array($res);
    */

    $events = array(
        array(
    'date' => '2003-12-25''description' => 'Christmas'), 
        array(
    'date' => '2003-12-31''description' => 'party'), 
        array(
    'date' => '2004-01-01''description' => 'headache'
    );

    //couldn't come up with a better var name...
    $calendarEvents = array();

    function 
    populateCalendarWithEvents($eventList)
    {
        foreach (
    $eventList as $event) {
            
    $currentEventDate $event['date'];
            
    $month substr($currentEventDate52);
            
    $year substr($currentEventDate04);
            
            if ( !
    STORE_EVENTS ) {
                       
    $calendarEvents[$year] = $month;
            } else {
                
    //the ending [] is important if you want to store multiple events for one month
                       
    $calendarEvents[$year][$month][] = $event;
                
            }
        }
        return 
    $calendarEvents;
    }

    var_dumppopulateCalendarWithEvents($events));

    if ( 
    STORE_EVENTS ) {
        
    //example use
        
    $activity populateCalendarWithEvents($events);
        
        foreach(
    $activity as $ykey => $yval) {
            foreach (
    $yval as $mkey => $mval){
                echo 
    "there is an event for " $ykey ' ' $mkey "\n";
                foreach (
    $mval as $key => $val){
                    echo 
    ' year='.$ykey ' month='.$mkey ' event='.$val['description'];
                }
            }
        }
    }

    ?>
    Hope I understood you right... As for (not) iterating through all elements, I don't understand how you can decide to skip it if you don't know its value? =)

    Feel free to comment on my code. It's just a proof of concept, it could easily be refactored/modified to suit your precise needs.

    Jean-Marie
    Last edited by Refresh; Sep 24, 2004 at 08:29. Reason: minor typos in the php code

  3. #3
    Can we go to a 48 hour day?
    Join Date
    May 2002
    Location
    MI
    Posts
    906
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I am not trying to get all of the events out of the table. I do that on a month by month basis.

    What I want to do is supply a user with a drop down to select which calendar to view.

    So the user would get a select box with
    Oct 2004
    Jan 2005
    Feb 2005

    Now if there are no events in Nov, then I don't want to show that calendar month as an option.

    I figure I just need one event in any month to trigger showing that month and that is why I don't want to iterate through the whole database.
    mitechie.com
    "Techies just think a little differently
    ...at least that is what they keep telling me."

  4. #4
    SitePoint Guru
    Join Date
    Jul 2004
    Location
    Raleigh, NC
    Posts
    783
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    can you use sql to make virtual columns for 'month' and 'year' and use GROUP BY?

  5. #5
    Can we go to a 48 hour day?
    Join Date
    May 2002
    Location
    MI
    Posts
    906
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Darchangel
    can you use sql to make virtual columns for 'month' and 'year' and use GROUP BY?
    That is where I am headed. I just need to figure out enough mysql date functions for working the month/years out of the mysql date field.
    mitechie.com
    "Techies just think a little differently
    ...at least that is what they keep telling me."

  6. #6
    SitePoint Enthusiast Refresh's Avatar
    Join Date
    Jul 2004
    Location
    Lausanne, Switzerland
    Posts
    46
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    then maybe it's wise to ask in the sql forums? =)

  7. #7
    SitePoint Guru
    Join Date
    Jul 2004
    Location
    Raleigh, NC
    Posts
    783
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by deuce868
    That is where I am headed. I just need to figure out enough mysql date functions for working the month/years out of the mysql date field.
    i've never done this before but this looks like what you want
    Code:
    SELECT *
    FROM table
    WHERE event != ''
      AND event IS NOT NULL
    GROUP BY MONTHNAME
    here are the MySQL Date and Time Functions

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Darchangel, you should never use "select star" with GROUP BY

    deuce868, here's what you want:
    Code:
    select date_format(eventdate,"%b %Y") as display_monthyear
         , date_format(eventdate,"%Y%m") as sort_monthyear
      from events
    group
        by display_monthyear
         , sort_monthyear  
    order
        by sort_monthyear desc
    slightly more complex than it needs to be just to select distinct month/year combinations, but i figured you'd want them in a specific order
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Enthusiast
    Join Date
    May 2004
    Location
    Texas
    Posts
    55
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Darchangel, you should never use "select star" with GROUP BY
    Why? How should you do it?

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    because it is invalid sql

    in every database except mysql

    but even mysql warns you that it produces unpredictable results if the "hidden" columns are not in a one-to-one relationship with the columns listed

    you must list the exact columns you want in the SELECT list

    then repeat every non-aggregate expression in the GROUP BY

    right:
    select foo, bar, count(*) from ... group by foo, bar

    wrong:
    select foo, bar, count(*) from ... group by foo

    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Guru
    Join Date
    Jul 2004
    Location
    Raleigh, NC
    Posts
    783
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Mysterion
    Why? How should you do it?
    r937 is absolutely correct. see above

  12. #12
    SitePoint Enthusiast
    Join Date
    May 2004
    Location
    Texas
    Posts
    55
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I see. Thanks.

  13. #13
    Can we go to a 48 hour day?
    Join Date
    May 2002
    Location
    MI
    Posts
    906
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    Darchangel, you should never use "select star" with GROUP BY

    deuce868, here's what you want:
    Code:
    select date_format(eventdate,"%b %Y") as display_monthyear
          , date_format(eventdate,"%Y%m") as sort_monthyear
       from events
     group
         by display_monthyear
          , sort_monthyear  
     order
     by sort_monthyear desc
    slightly more complex than it needs to be just to select distinct month/year combinations, but i figured you'd want them in a specific order
    That was awesome! Thanks so much for the help, it was exactly what I was looking for.
    mitechie.com
    "Techies just think a little differently
    ...at least that is what they keep telling me."


Bookmarks

Posting Permissions

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