SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Zealot
    Join Date
    Oct 2003
    Location
    P Town
    Posts
    167
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Sorting by two dates

    Ok, I have a database where I am storing TV shows and I want users to be able to specify a decade and get all item that aired during that decade.

    SELECT m.id,
    (SELECT debut_date FROM tv_release_info as date WHERE date.tv_ref = m.id ORDER BY date.creation_date DESC LIMIT 1) as reldate,
    (SELECT title FROM tv_title as md WHERE md.tv_ref = m.id ORDER BY md.creation_date DESC LIMIT 1) as item_title,
    (SELECT overview FROM shared_overview as ov WHERE ov.parent_ref = m.id AND ov.type = 'tv' ORDER BY ov.creation_date DESC LIMIT 1) as overview
    FROM tv as m
    INNER JOIN tv_release_info as date
    ON date.tv_ref = m.id
    AND year( date.debut_date ) between 1980 and 1989

    I am not the most versed in MYSQL and this one has me pretty perplexed. It works fine for things like movies that only have a release date but I am not sure even where to start on making it work as a range. I tried doing this "AND year( date.debut_date ) between 1980 and 1989 OR year( date.end_date ) between 1980 and 1989" And it kind of works but if something aired from 70's - 90's and the user enters 80's as the decade sort it will not return it.

    *Edit* Forgot to mention the date is stored as a mysql datetime

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,506
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Code:
    AND YEAR( date.debut_date ) <= 1989 
    AND YEAR( date.end_date ) >= 1980


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
  •