SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Evangelist dev_cw's Avatar
    Join Date
    Dec 2005
    Posts
    454
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Output events grouped by date. Can I do this with a single query?

    My objective is to output a range of events grouped by date. Each event has a start date and an end date (in unixtimestamp format). This is what I am trying to do:

    date 1
    --event
    --event
    --event

    date 2
    --event

    date 3
    --event
    --event
    ...
    I can do 2 queries one to get the dates and a second to iterate through the events. However I keep thinking that i should be able to do this with one query. Am i wrong or have I missed the obvious?
    "You can just hang outside in the sun all day tossing a ball around...
    Or you can sit at your computer and do something that matters."
    - Cartman

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    yes, you can do it with a single query

    just make sure it ends with...

    ... ORDER BY eventstartdate, eventdetails

    this "grouping" is actually just sequencing, and is not related to the grouping created by the GROUP BY clause

    in your php program (i'm guessing you're using php because you mentioned unix times), as you loop over the result set rows returned by the query, you would simply detect the control break when the date changes, to print a new header for the new date before you resume printing event details
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Evangelist dev_cw's Avatar
    Join Date
    Dec 2005
    Posts
    454
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    in your php program (i'm guessing you're using php because you mentioned unix times), as you loop over the result set rows returned by the query, you would simply detect the control break when the date changes, to print a new header for the new date before you resume printing event details
    It took me a little effort to understand this (it is early in the morning here, not much coffee yet). What I was trying to have the query do is actually done by PHP without the second query. What I do is tell php to watch the date and have it output only when the date changes. I get it, it seems so obvious now, thanks.
    "You can just hang outside in the sun all day tossing a ball around...
    Or you can sit at your computer and do something that matters."
    - Cartman

  4. #4
    SitePoint Evangelist dev_cw's Avatar
    Join Date
    Dec 2005
    Posts
    454
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I have a new question but same issue: I would like to have the event show up on all the dates within its start/end dates: So if an event has a start date os Jan 1 and an end date of jan 5 it will be displayed on every date for the event (ie: Jan 1, Jan 2, Jan 3, Jan 4, Jan 5). Can this be done with the same query or will I need to iterate through each date (2 queries)?
    "You can just hang outside in the sun all day tossing a ball around...
    Or you can sit at your computer and do something that matters."
    - Cartman

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    it would need to be done with a different query

    you can actually do it more efficiently in php -- just use the above query to return your events, then in php have an array of dates and loop over both the dates and the events
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Evangelist dev_cw's Avatar
    Join Date
    Dec 2005
    Posts
    454
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you. This has been very helpful. One of the big challenges for me in developing is knowing the right tool for the task at hand.
    "You can just hang outside in the sun all day tossing a ball around...
    Or you can sit at your computer and do something that matters."
    - Cartman


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
  •