Jump to content

Archived

This topic is now archived and is closed to further replies.

willpower

Select starting from record 5 then BACK to record 1

Recommended Posts

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 order

5
6
7
8
9
10
11
12
1
2
3
4

I have lost the plot on this...can anyone help me to do this???

Thanks in advance

will

Share this post


Link to post
Share on other sites
do you want them to display ordered by lastest or starting from 5 then to the end and then 1-4 ???

Share this post


Link to post
Share on other sites
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?

Share this post


Link to post
Share on other sites
hi i have a startd and startm field - and a endd and endm

ie
startd = 22
startm = 07
endd = 23
endm = 08

And 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

Share this post


Link to post
Share on other sites
hmm
[code]SELECT * FROM signs ORDER BY startm*100+startd>=MONTH()*100+DAY() DESC,startm,startd[/code]

Share this post


Link to post
Share on other sites
ps could you explain what that should have done so i can try and amend it also?

Thanks

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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]

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
[code]SELECT * FROM signs ORDER BY endm*100+endd>=MONTH(NOW())*100+DAY(NOW()) DESC,startm,startd[/code]

Share this post


Link to post
Share on other sites
After all this time...i have noticed an issue...Capricorn comes last in the list rather than between sag and aquarius. 

Current list reads

leo: correct
Virgo : correct
Libra: correct
Scorpio :correct
Sagitaruis: correct
Aquarius : correct in that it has looped back to the start but capricorn omiited
Pisces: correct
Aries : correct
Taurus : correct
Gemini : corect
Cancer : correct
Capricorn : god damn it

Any clues?
PS is it to do with the fact that capricorn starts in month 12 and ends in month 1

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.