Results 1 to 2 of 2
Thread: Sorting by two dates
Jul 28, 2011, 11:53 #1
- Join Date
- Oct 2003
- P Town
- 0 Post(s)
- 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 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
Jul 30, 2011, 02:11 #2Code:
AND YEAR( date.debut_date ) <= 1989 AND YEAR( date.end_date ) >= 1980Guido - Community Team Leader
The Votes Are In: The Winners of the 2013 Community Awards are...
Blog - Free Flash Slideshow Widget