I've always been a big fan of javascript for doing any pagination. Firstly, because it allows live, comparatively fast sorting of results. Secondly, because mysql is a bunch faster if it's not ordering or filtering results itself, and rather just returning a chunk of them. Thirdly, it's a lot less annoying for the server. You could transmit the same page to the user 3 times with different data, but the same presentation 3 different times for 3 pages worth of results. Or, you could just have the server dump down a block of results without needing page reloads.
On Jan 8, 4:16 am, ripcurlksm <kevin.mccorm...@cox.net> wrote: > 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 apickle-- 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 apickle. 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 apickle, 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-My... > > 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-My... > Sent from the jQuery General Discussion mailing list archive at Nabble.com.
