SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Member gekko's Avatar
    Join Date
    Aug 2001
    Posts
    16
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Sorting by dates question

    Hey guys,

    I'm trying to get a release list to sort by the date of when it'll be released. The problem is it's a release list for games, and i often encounter dates like TBA, Canned, May 2002, Q3 2001, Spring 2002, Early 2002 and so on.

    What i would like to know, is whether or not it's possible to sort games with those types of dates mixed in with the exact dates, like 11/05/2001. and if so, how would you go about doing it?

    What i was thinking was maybe having something a date field, and then a varchar field and attempting to sort them using both. maybe trying to make 0000-00-00 display TBA, and 2001-10-00 display just October 2001.

    But i am completely clueless on how i would do this. Any help would be nice, even just letting me know it's possible so i can start entering dates.

  2. #2
    SitePoint Enthusiast
    Join Date
    May 2000
    Location
    Bangkok,Thailand
    Posts
    95
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    You need to standardise

    First, you need to decide on a date system for yourself. ie if you want to do it seasonal (summer 2002, autumn 2006 etc) then when you get one like, Q4 2002, then figure out which of the seasons that falls into and use that or vice versa - by using different systems for the same period, you're just confusing the matter.

    Once you have that out of the way, I'd lean towards having two columns for a release date, one that would hold an actual date - eg 12/09/2001 and the second with a date "slogan" that would be autumn/summer/spring whatever.

    So, you'd end up with columns like this:

    realdate:dateslogan
    12/01/2000:NULL
    00/03/2002:Spring 2002 (I'm not sure when spring is where you are!)
    NULL:TBA
    15/01/2000:NULL

    So then you could do a few runs through the table - one to pick where realdate IS NULL (read TBA) they go to the top, then for all that are NOT NULL they are ordered by date, however if the dateslogan is NOT NULL then the date slogan is displayed instead of the realdate...

    I'm kind making this up as I go - so I'm sure someone can suggest a better way to do it, but my only other thought is that lots of different date formats confuse people, and where I live there are only two seasons - wet and dry (I'm in Thailand) - telling me that something has a spring release doesn't really mean much to me...

    HTH

    Stuart
    http://www.travelfish.org

  3. #3
    SitePoint Member gekko's Avatar
    Join Date
    Aug 2001
    Posts
    16
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I think i understand what you're trying to say. I don't choose to have all these weird dates, the companies set the date for each game and i'm forced to list what they choose.

  4. #4
    SitePoint Member Ramses's Avatar
    Join Date
    Jul 2001
    Location
    Montréal, Canada
    Posts
    15
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I share your problem gekko. I am run a movie site and am building a gaming site and am running into the same situation: dates, quarters, seasons, production status... To manage things easily, I do a similar thing to what Dr Gonzo proposed:

    He're are a few examples

    Column date: 2001-11-30
    Column period: (null)
    Column status: In Production
    Result: Game In Production, release date November 30th, 2001

    Column date: 2002-00-00
    Column period: 2Q
    Column status: (null)
    Result: Game to be released during Second Quarter of 2002

    Column date: (null) (or 9999-00-00)
    Column period: (null)
    Column Status: (null)
    Result: Game release date to be determined (TBA).

    I use 1S, 2S, 3S, 4S for seasons, 1Q, 2Q, 3Q, 4Q for quarters. This way, you can do a sort on date first, then period.

    Cheers!

  5. #5
    SitePoint Member gekko's Avatar
    Join Date
    Aug 2001
    Posts
    16
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    So how does the code look when you're displaying that?

    The way it's looking right now, i have 3 columns also. Here's an example of how i want them to work:

    Date column: 2001-11-00
    Period column: null
    Status column: null
    Results: November 2001

    Date column: 2001-11-05
    Period column: null
    Status column: null
    Results: November 5, 2001

    Date column: 2002-00-00
    Period column: Winter
    Status column: null
    Results: Winter 2002

    Date column: null
    Period column: null
    Status column: Canceled
    Results: Canceled

    Date column: null
    Period column: null
    Status column: null
    Results: TBA

    But displaying them all in the order of their release, seems like it's going to require a mess of code. Would i have to call up everything individually?

    Like get all the exact dates for November, then the November ones, then Winter... TBA, and Canceled. Not to mention have them listed alphabetically when there's multiple games with the same date.

    Is there an easy way to do this? Or am i stuck doing it the hard way?

  6. #6
    SitePoint Member Ramses's Avatar
    Join Date
    Jul 2001
    Location
    Montréal, Canada
    Posts
    15
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well, say you want to list the titles in chronological release date order. The SQL query would have to look a bit like this:

    SELECT *
    FROM game_titles
    ORDER BY date, period, status, title

    Note: I use 1S, 2S, 1Q, 2Q keywords instead of actual seasons and quarters. The main reason for that is that I can easily order the titles directly in the query. i.e. First Quarter (1Q=Jan,Feb,Mar) comes before Spring (1S=Mar,Apr,May... close enough), then comes Second Quarter (2Q=Apr,May,Jun) and so on. This way, I can order by period and have results line up properly (1Q, 1S, 2Q, 3Q...). Sure enough, the titles come up at the top of the list but are at least ordered straight, but by the time their release come up, chances are you'll get a complete date to post. Plus, quarters and seasons are simply ballpark figures. If you wish them undetermined (no date) titles to appear at the end of the list, you could break the database logic a bit and place large bogus dates for them, say Year 9999. Then weed out the year 9999 upon displaying the results and simply show the period.

    Use whatever PHP logic you wish to shorten dates (2004, or November 2002...) and convert period code to proper names (1Q=First Quarter, 3S=Fall...) where applicable.

    Another, more involving way could have the periods properly lined up in the list along with complete dates, but uses some more memory and processing time, well, just a bit more . Simply fetch the results and build a multidimentional array out of them. Then use whatever PHP logic flavor you fancy to sort through the array, using usort for instance. Then all you have to do is print out the array's content, and voila! A bit more messy, but sure beats multiple db queries. I haven't tried that method yet, but I'll surely give it a go soon and see how it fares.

    As usual, you mileage may vary...


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
  •