[jQuery] In a pickle -- JavaScript Pagination vs. PHP/MySQL Pagination

[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 '

Can not select db.

';
    }
    $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-Pagination-tp21323852s27240p21323852.html
Sent from the jQuery General Discussion mailing list archive at Nabble.com.