there is no additional light to see, when it comes to mysql<!-- DISCLAIMER TO RUDY: I'm sorry. I know. Bad Stephen. Proprietary Syntax is naughty. Feel free to show me the light. -->
Oh! Wow! I figured there was some standard way to deal with this that I was kludging around. hmm... don't feel so slackardly now :) thanks!
if you use unix timestamp format, you must use the FROM_UNIXTIME function
this gives datetime values, thus MONTH, MONTHNAME and YEAR are also correct
DISTINCT is also correct, as you want unique results from multiple values per month/year
now, your WHERE clause is where all the action is
and it's easier than you think ;o)
SELECT DISTINCT MONTH(FROM_UNIXTIME(startDate)) as sMNum , MONTHNAME(FROM_UNIXTIME(startDate)) as sMName , YEAR(FROM_UNIXTIME(startDate)) as sYear FROM event WHERE endDate > UNIX_TIMESTAMP(current_date) ORDER BY sYear, sMNum
in the WHERE clause, the UNIX_TIMESTAMP function is applied to a constant value, with the result compared to your unix column value, rather than applying FROM_UNIXTIME to your column value to compare to the current date constant value
This works a treat, except for my primary concern... I entered some data to have an event start in one month and end in another where no event started... that month doesn't come back in the query. The scenario I'm envisioning (please forgive me restating it from my previous post) is that a user may come to the site, wish to view what is happening in say May, in which no events start but one ends, not see it available in the dropdown this query will generate and go on about there way, because they're really trying to figure out something to do in May...
hopefully that's not just the mess of gibbering nonsense I fear it may be.
this makes for indexability
Que?
also, notice that i've rearranged your ORDER BY into a more conventional sequence
:^) Thanks, I missed that too.
Thanks!!!
- Stephen
(who really needs to pony up and take another course in SQL, cause it's obviously not all gelled)
____ � The WDVL Discussion List from WDVL.COM � ____ To Join wdvltalk, Send An Email To: mailto:[EMAIL PROTECTED] or use the web interface http://e-newsletters.internet.com/discussionlists.html/ Send Your Posts To: [email protected] To change subscription settings, add a password or view the web interface: http://intm-dl.sparklist.com/read/?forum=wdvltalk
________________ http://www.wdvl.com _______________________
You are currently subscribed to wdvltalk as: [email protected] To unsubscribe send a blank email to [EMAIL PROTECTED] To unsubscribe via postal mail, please contact us at: Jupitermedia Corp. Attn: Discussion List Management 475 Park Avenue South New York, NY 10016
Please include the email address which you have been contacted with.
