You must have a really HUGE table/database. I'm don't know much of
MySQL but I think even a million records query returns in less than a
second on an average server.
Couldn't you be loading just the 40 first rows, and if you need to
sort it the other way, load the 40 at the other end (with a different
SQL query)? That's the obvious way of doing this.
On Jan 7, 12:48 am, ripcurlksm <kevin.mccorm...@cox.net> wrote:
> <--- 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-My...
> Sent from the jQuery General Discussion mailing list archive at Nabble.com.
