Could you post some example code if you get the chance Jeffrey?
Jeffrey Kretz wrote: > > > I solved this for my own project in this way: > > 1. Server-side code renders the first page of the grid, also passing a > value of total results. > 2. If the total results are few enough (season to taste), fire an ajax > call > that immediately loads the entire result set into memory. > 3. As the user re-sorts the results, re-sort in memory and display the > correctly sorted/paged results. > 4. If the total results are too large to make this feasible, do an ajax > call for each paged/sorted result set. > > JK > > -----Original Message----- > From: jquery-en@googlegroups.com [mailto:jquery...@googlegroups.com] On > Behalf Of ripcurlksm > Sent: Tuesday, January 06, 2009 6:48 PM > To: jquery-en@googlegroups.com > Subject: [jQuery] In a pickle -- JavaScript Pagination vs. PHP/MySQL > Pagination > > > > <--- here is a picture of me > > > I have a jQuery sortable table with jQuery pagination, which is being fed > from PHP/MySQL-- and now that I have it setup, I am in a pickle. It > appears > I can only have cake or eat it. I want a table that loads fast from MySQL, > that I can paginate (for performance) AND i can sort, however when you > break > the SQL rows returned, you can only sort the table based on the limited > results (ie- sorting a table based on 40 returned results, instead of > sorting based on the 800 total rows that the query yields) > > [B]Javascript Pro:[/B] Sexy sortable tables & pagination > [B]Javascript Con:[/B] Must load entire MySQL result to allow proper > sorting, however the database query is taking ~12 seconds to load > > To demonstrate, take a set of results broken up on two pages. > 1 > 2 > 3 > ---new page--- > 4 > 5 > 6 > > My problem is that when I sort this column (highest to lowest), it only > sorts whats loaded (in this case 3 rows): > 3 > 2 > 1 > > When I want this: > 6 > 5 > 4 > > Possible solutions: > 1) Static Output -- Everything is working fine, except for my 12 second > wait > for my table to load from MySQL. So I could create a hack to load a static > HTML file instead of querying the database. The issue I see with this, is > when a user does complex searches, I will have to output multiple static > files. > > 2) Ajax-ish output -- I have the tablesorter and pagination currently > loading from an "Ajax-ish" file, which does the SQL query, handles the > MySQL > offset and returns the proper rows to the page without doing a refresh. > Now, > if there is some way to modify this ajax script, so that it can also ORDER > BY the SQL query (in addition to its current offset function), however > there > would need to be some sort of callback when a column header is clicked in > the javascript, to the ajax script, to add the ORDER BY clause and return > the results... eh. > > So Im in a pickle, keep in mind I have everything working, sortable table, > pagination, but my 800 row query is jsut taking too long to load (its > joining several other tables as it loads.) > > Here is my current code to contain the table results, and ajax file to > load > the SQL and dynamically handle the results without needing a page refresh. > > results.php > <?php > include('include/scripts.inc.php'); > include('conn/conn.inc.php'); > > dbConnect(); > $sql = 'SELECT COUNT(*) FROM company'; > $res = mysql_query($sql); > $total = mysql_result( $res, 0 ); > > ?> > <html> > <head> > <script type="text/javascript" > src="include/jquery-latest.js"></script> > <script type="text/javascript" > src="include/tablesorter/addons/pagination/jquery.pagination.js"></script> > > > > <script type="text/javascript"> > function pageselectCallback(page_id, jq){ > var first = (page_id*10)+1, second = (page_id*10)+40; > $('#Searchresult').text("Showing search results " + first + '-' > + > second); > $.ajax({ > type:'GET', > url:'test-ajax.php', > data:'offset=' + first + '&limit=40', > success:function(msg) { > $('#ajaxContent').html(msg); > } > }); > } > $(document).ready(function(){ > $("#pagination").pagination( <?php echo $total;?>, { > num_edge_entries: 2, > num_display_entries: 8, > callback: pageselectCallback > }); > > pageselectCallback(0); > }); > </script> > <title>database</title> > </head> > <body> > <div class="pagination" id="pagination"></div><br clear="all"/> > <div id="Searchresult"></div><br /> > <div id="ajaxContent"></div> > > </body> > </html> > > test-ajax.php (to load the next page of MySQL results) > <?php > $offset = $_GET['offset']; > $limit = $_GET['limit']; > $conn = mysql_connect( 'localhost', 'root', 'mypass'); > if ( is_resource( $conn ) ) { > if ( !mysql_select_db('foo', $conn) ) { > echo '<p>Can not select db.</p>'; > } > $result = mysql_query('SELECT * from company LIMIT ' . $offset . ',' > . > $limit); > if ( is_resource( $result ) ) { > while ( $row = mysql_fetch_assoc( $result ) ) { > echo $row['story']; > } > } > > } > ?> > > > Any thoughts on how to allow sorting of tables with pagination and not > make > the user wait 12 seconds to view the content? > -- > View this message in context: > http://www.nabble.com/In-a-pickle----JavaScript-Pagination-vs.-PHP-MySQL-Pag > ination-tp21323852s27240p21323852.html > Sent from the jQuery General Discussion mailing list archive at > Nabble.com. > > > > -- View this message in context: http://www.nabble.com/In-a-pickle----JavaScript-Pagination-vs.-PHP-MySQL-Pagination-tp21323852s27240p21348272.html Sent from the jQuery General Discussion mailing list archive at Nabble.com.
