Sorting & Pagination

I'm planning to query a table, and to create a table/grid of approximately 12 or 14 columns. Creating the initial query and the initial order by shouldn't be to difficult to figure out.

But than I would like to be able to re-sort the data based on the various columns asc/desc which clicking each column header. Preferably I would also like to have the data paginated into multiple 'pages'.. ie, a query of 100 rows of data would be 4 pages of 25, etc.

Can this be done running the query once, building the array, and than re-sorting the array based on the columns sorts? Or does anyone have any basic samples on beginning to set it up?

Thanks
PT

This can be done using an array; however, the only way to do it would be to fetch every record into this array to start with. This may be a huge burden in the amount of processing / memory used for this simple task.
Pagination works by (preferably) reducing the size of the result set to a limited value of 25 records for example. You would need to re-query the data with you're new order by if you're using the LIMIT (or TOP, or whatever controls depending on you're engine in use).
From a pure PHP perspective, this is impossible. It will limit you to 25 records and attempting to sort it would result in a requery anyway. Because of this, its best to just make use of LIMIT calls.

Jay has a pagination class on his site:

http://www.jaygilford.com/php/completely-customisable-php-pagination-class/

Even if that doesn't exactly suit your needs, it'll give you an idea of how to go about things.

If you don't want to have to re-query your database, you'd have to use javascript to store all of your results and do all of the sorting. This would be a nightmare, especially if you have any significant number of results.

The best way to do this is to do a new query every time you sort or go to a new page. You don't have to mess with javascript, which is slow and not well-suited for this task. Use Javascript, instead, to do asynchronous posts to a PHP script to prevent full page loads. It should still be very quick.