Best Approach for sortable rows with a large recordset ?
I have a detailed search form with a number of fields created with the aid of dataassist search.
A nice approach would be to have the resulting table of data sortable by column.
Eg clicking on the column header
I have had 3 goes at this, and am wondering on the best approach.
(I have the results table on the same page as the search form to allow progressive filtering, and store each of the search parameters as session variables, so when the page reloads, any previously entered values are retained)
Potentially, there could be up to 20000 rows returned in the recordset if the customer does not apply a filter.
First Try.
Totally php server side filtering and pagination.
Using dataassist, and pagination at 25 rows per page, the resulting page is presented quickly
If I add more parameters to the search form and re-filter, the updated recordset is also presented very quickly.
Using this approach, I could add a sort parameter to each of the table headers and use this to re-sort the recordset and reload the page, but this means a page reload each time
Second try. Add javascript tablesort (using tablesort 2, a jquery plugin) to the resulting data.
This works really well when only a couple of hundred rows are returned, but with large amounts of data (a few thousand rows), it takes a while (sometimes 30 seconds) to load the page
Using this method means I can click on any column header to sort the table, but with large recordsets the initial time to load the page is just too slow.
Third try.
Create a spry dataset using an xml source created from the filtered recordset.
Don’t know if this is worth persuing. I can easily create an xml file from a recordset but am unable to filter in situ and then redirect to the results page. Neither have I devised a way to have both the search and results on the same page whilst somehow updating the xml. Don’t want to spend too much time on this if it’s a dead end
Is there a ‘solution’ to this, or is it necessary to stick to a server side solution for large recordsets.
Any advice appreciated.
