Hi! I've got a problem I've been working on for weeks and now I'm completely stumped. I hope someone can help me out here.
I have a table with 3 columns - Name, Rank, LastDate
Name-varchar
Rank - int
LastDate - date/time
My table has 760 records. I am using PHP to display these items and am displaying 26 per page.
Right now I am using a 'select Name, Rank, LastDate order by LastDate DESC limit $offset,$limit'
This works great until I'm informed I now need to sort by Rank AND by the lastdate!!!
So here's the problem: If I do a query (since I'm paging the results at 26 a page) and I sort by LastDate and Rank the query ONLY pulls the first 26 records and then sorts them!!
In other words, with the old way (having only one sorting criteria) it would sort all the entries, then pull the top 26. Now, it seems to be sorting all the entries, then sorting only the 26 with the second criteria, then giving me those results.
Example: If one person is record #640 and his rank is #10 (being the highest) he doesn't show up on the first page because he is far below #26!
My only option seems to be to query the entire table ordering by LastDate.. then, after placing ALL the information in arrays, sort the array by Rank.
There has GOT to be an easier way to do this?! My intranet page get's quite a bit of traffic and I can't imagine querying the entire table each and every time someone clicks the >>NEXT page-link.
Thanks in advance for your input!
Elliot
I have a table with 3 columns - Name, Rank, LastDate
Name-varchar
Rank - int
LastDate - date/time
My table has 760 records. I am using PHP to display these items and am displaying 26 per page.
Right now I am using a 'select Name, Rank, LastDate order by LastDate DESC limit $offset,$limit'
This works great until I'm informed I now need to sort by Rank AND by the lastdate!!!
So here's the problem: If I do a query (since I'm paging the results at 26 a page) and I sort by LastDate and Rank the query ONLY pulls the first 26 records and then sorts them!!
In other words, with the old way (having only one sorting criteria) it would sort all the entries, then pull the top 26. Now, it seems to be sorting all the entries, then sorting only the 26 with the second criteria, then giving me those results.
Example: If one person is record #640 and his rank is #10 (being the highest) he doesn't show up on the first page because he is far below #26!
My only option seems to be to query the entire table ordering by LastDate.. then, after placing ALL the information in arrays, sort the array by Rank.
There has GOT to be an easier way to do this?! My intranet page get's quite a bit of traffic and I can't imagine querying the entire table each and every time someone clicks the >>NEXT page-link.
Thanks in advance for your input!
Elliot