willpower Posted July 31, 2006 Share Posted July 31, 2006 Well sort off.I establish the date. I then want to go to a db containing start signs and find out which is the current star sign based on the date. I then want to list all start signs starting with the most current. ie Records retrieved could be in this order567891011121234I have lost the plot on this...can anyone help me to do this???Thanks in advancewill Quote Link to comment Share on other sites More sharing options...
BillyBoB Posted July 31, 2006 Share Posted July 31, 2006 do you want them to display ordered by lastest or starting from 5 then to the end and then 1-4 ??? Quote Link to comment Share on other sites More sharing options...
ryanlwh Posted July 31, 2006 Share Posted July 31, 2006 i think he wants the current month, then ascending order until december, then jan to last month, as in those horoscope articles.one question for willpower: how do you store the star signs dates? Quote Link to comment Share on other sites More sharing options...
willpower Posted July 31, 2006 Author Share Posted July 31, 2006 hi i have a startd and startm field - and a endd and endmie startd = 22startm = 07endd = 23endm = 08And yes I want them to display in chronological order. so if capricorn was the current sign I want to display the ALL but starting with capricorn...or if it were gemini the same etc.Hope that makes sense Quote Link to comment Share on other sites More sharing options...
ryanlwh Posted July 31, 2006 Share Posted July 31, 2006 hmm[code]SELECT * FROM signs ORDER BY startm*100+startd>=MONTH()*100+DAY() DESC,startm,startd[/code] Quote Link to comment Share on other sites More sharing options...
willpower Posted July 31, 2006 Author Share Posted July 31, 2006 that gives me a sytax error Quote Link to comment Share on other sites More sharing options...
willpower Posted July 31, 2006 Author Share Posted July 31, 2006 ps could you explain what that should have done so i can try and amend it also?Thanks Quote Link to comment Share on other sites More sharing options...
ryanlwh Posted July 31, 2006 Share Posted July 31, 2006 sorry, i forgot something in the MONTH and DAY[code]SELECT * FROM signs ORDER BY startm*100+startd>=MONTH(NOW())*100+DAY(NOW()) DESC,startm,startd[/code]basically startm*100+startd>=MONTH(NOW())*100+DAY(NOW()) is to figure out whether or not today is on or after the start of a starsign (1 if on or after, 0 if before). We use DESC because you want 0 to appear later (the months before this sign). then, after the first sort, we proceed to do it chronologically with startm and startd. Quote Link to comment Share on other sites More sharing options...
willpower Posted July 31, 2006 Author Share Posted July 31, 2006 oh....so close. I wish i understood the query.I now get the order i want but the starting record is out by one...i get virgo rather than libra....by the way...i hate horoscopes.any ideas?Will Quote Link to comment Share on other sites More sharing options...
ryanlwh Posted July 31, 2006 Share Posted July 31, 2006 damn, i make so many mistakes today :)[code]SELECT * FROM signs ORDER BY MONTH(NOW())*100+DAY(NOW()) >= startm*100+startd DESC,startm,startd[/code] Quote Link to comment Share on other sites More sharing options...
willpower Posted July 31, 2006 Author Share Posted July 31, 2006 lol. Thanks so much for your help....its now worse and starts with some other star sign ;)I bow to your greatness....regardless of how many mistakes you make!!!Will Quote Link to comment Share on other sites More sharing options...
willpower Posted August 1, 2006 Author Share Posted August 1, 2006 What can i say....THANK YOU Quote Link to comment Share on other sites More sharing options...
ryanlwh Posted August 1, 2006 Share Posted August 1, 2006 [code]SELECT * FROM signs ORDER BY endm*100+endd>=MONTH(NOW())*100+DAY(NOW()) DESC,startm,startd[/code] Quote Link to comment Share on other sites More sharing options...
willpower Posted August 14, 2006 Author Share Posted August 14, 2006 After all this time...i have noticed an issue...Capricorn comes last in the list rather than between sag and aquarius. Current list readsleo: correctVirgo : correctLibra: correctScorpio :correctSagitaruis: correctAquarius : correct in that it has looped back to the start but capricorn omiited Pisces: correctAries : correctTaurus : correctGemini : corectCancer : correctCapricorn : god damn itAny clues?PS is it to do with the fact that capricorn starts in month 12 and ends in month 1 Quote Link to comment Share on other sites More sharing options...
willpower Posted August 14, 2006 Author Share Posted August 14, 2006 bump Quote Link to comment Share on other sites More sharing options...
willpower Posted August 16, 2006 Author Share Posted August 16, 2006 double bump Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.