close ad
Databridge V2 with MySQLi support IS Now Available!
open ad
View Menu

Technical Support Forums

Free, outstanding support from WebAssist and your colleagues

rating

Best Approach for sortable rows with a large recordset ?

Thread began 5/16/2011 7:03 am by CraigR | Last modified 5/16/2011 3:39 pm by Jason Byrnes | 1904 views | 7 replies |

CraigRBeta Tester

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.

Sign in to reply to this post

Jason ByrnesWebAssist

with large data sets, your best approach will be to use some sort of paging scheme.

I'm not to familiar with the jquery plugin you are referencing, but if there is a way to add paging to it, you could it to speed up the load time.


The only way to cut down on the load time is to cut down on the number of records being returned.

Sign in to reply to this post

CraigRBeta Tester

Thanks for the reply Jason.

the jquery plugin does have paging, but it seems that all of the recordset rows need to be processed first, so although I may only have a page displaying 25 rows, 10000 may be populating the array.

Don't know if this is a shortfall of the plugin or a symptom of client side paging.

I have 2 questions if you'll indulge me.

1. Using the dataAssist search method, the where clause is appended onto the unfiltered recordset, how can I add, say. LIMIT 500 to this so I get a maximum of 500 rows returned.

2. Do you know of any other ajax/javascript tools, which may be of benefit ? I think what i am looking for is to basically take a recordset, paginated server side, and add the javascript functionality to it.

Thanks for any help.

Sign in to reply to this post

Jason ByrnesWebAssist

1) you can add the limit statement directly to the recordset.

SELECT * FROM table name LIMIT 500

2) Sorry, I am not aware of a javascript plugin to accomplish this.

Sign in to reply to this post

CraigRBeta Tester

Sorry, I'm being really dim here.

My default recordset, rswhatever is "select field1, field2 etc from tablename"

the querystring created by the dataassist wizard, (called setQueryBuilderSource) appends the where clause onto the SQL select statement.

Don't I then need to append the LIMIT 500 onto the end of this statement before it is processed ?

Sign in to reply to this post

Jason ByrnesWebAssist

no, just add it to the recordset:
select field1, field2 etc from tablename LIMIT 500

Sign in to reply to this post

CraigRBeta Tester

I tried this and I get a syntax error.

I think it is because it puts the limit statement before the WHERE clause.

However, I have got it to work, by changing

$rswhatever = mysql_query($query_rswhatever, $database) or die(mysql_error());



to

$rswhatever = mysql_query($query_rswhatever." LIMIT 500", $database) or die(mysql_error());



Thanks for your help

Sign in to reply to this post

Jason ByrnesWebAssist

Hmmm, glad to hear you found a work around.

Sign in to reply to this post

Build websites with a little help from your friends

Your friends over here at WebAssist! These Dreamweaver extensions will assist you in building unlimited, custom websites.

Build websites from already-built web applications

These out-of-the-box solutions provide you proven, tested applications that can be up and running now.  Build a store, a gallery, or a web-based email solution.

Want your website pre-built and hosted?

Close Windowclose

Rate your experience or provide feedback on this page

Account or customer service questions?
Please user our contact form.

Need technical support?
Please visit support to ask a question

Content

rating

Layout

rating

Ease of use

rating

security code refresh image

We do not respond to comments submitted from this page directly, but we do read and analyze any feedback and will use it to help make your experience better in the future.

Close Windowclose

We were unable to retrieve the attached file

Close Windowclose

Attach and remove files

add attachmentAdd attachment
Close Windowclose

Enter the URL you would like to link to in your post

Close Windowclose

This is how you use right click RTF editing

Enable right click RTF editing option allows you to add html markup into your tutorial such as images, bulleted lists, files and more...

-- click to close --

Uploading file...