Jump to content


Photo

Need a soloution to a query sort


  • Please log in to reply
2 replies to this topic

#1 phporcaffeine

phporcaffeine
  • Members
  • PipPipPip
  • Advanced Member
  • 361 posts
  • LocationOhio, USA

Posted 19 April 2006 - 01:48 PM

First off -

Nothing is "broke" but I need to add some functionality, I just don't know how to go about it.

Let's start with some background information:

I have an area of a script called "View", and in view area the user can view database records. By default the view interface loads the records in ORDER BY [autonumber_column] SORT ASC.

Now I have a utility on the interface that allows you to specify "how many" records to show. All it really does is stop MySQL from reading the table to the end ( I use a php while loop and exit the loop after 'N' loops).

Anyhow, I have another piece that lets the users "resort" the records in DESC then ASC (it's a little button they click).

The issue comes in to play when they restrict how many records to view AND apply the resort. As you would imagine if I say "Show me 10 records out of 20, then after I get the result, resort in DESC", what would happen is that MySQL would resort in DESC starting at record 20, not record 10.

Of course, it all works fine if the user allows MySQL to show all the table records.

So I guess I need a way to sort a partial amount of MySQL records?

Any ideas are appreciated.
Thanks,

Ryan Huff
President & Founder, MyCodeTree
support@mycodetree.com | http://mycodetree.com

#2 bbaker

bbaker
  • Members
  • PipPipPip
  • Advanced Member
  • 127 posts
  • LocationNY

Posted 19 April 2006 - 02:48 PM

from your original query, you could get the highest number of your results for the [autonumber_column], then use it to do your new query.

Use WHERE <= [highest_number_from_autonumber_column]

Then ORDER BY [autonumber_column] DESC LIMIT 10


SELECT * FROM  tablename WHERE [autonumber_column] <= [highest_number_from_autonumber_column] ORDER BY [autonumber_column] DESC LIMIT 10


#3 phporcaffeine

phporcaffeine
  • Members
  • PipPipPip
  • Advanced Member
  • 361 posts
  • LocationOhio, USA

Posted 19 April 2006 - 03:04 PM

[!--quoteo(post=366443:date=Apr 19 2006, 10:48 AM:name=bbaker)--][div class=\'quotetop\']QUOTE(bbaker @ Apr 19 2006, 10:48 AM) View Post[/div][div class=\'quotemain\'][!--quotec--]
from your original query, you could get the highest number of your results for the [autonumber_column], then use it to do your new query.

Use WHERE <= [highest_number_from_autonumber_column]

Then ORDER BY [autonumber_column] DESC LIMIT 10
SELECT * FROM  tablename WHERE [autonumber_column] <= [highest_number_from_autonumber_column] ORDER BY [autonumber_column] DESC LIMIT 10
[/quote]


Perfect!

Too much Mt. Dew - and I stop thinking logically ... lol thx
Thanks,

Ryan Huff
President & Founder, MyCodeTree
support@mycodetree.com | http://mycodetree.com




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users