Just to follow this up a little, I'm guessing the slow loading you see
has nothing to do with MySQL and everything to do with trying to load
800 rows worth of data into the browser. So I would guess that your
option 1 would not give you any advantage since the browser would
still take several seconds to load that many rows in an HTML table.
Just my 2 cents.
On Jan 6, 9:22 pm, Ricardo Tomasi <ricardob...@gmail.com> wrote:
> 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.
