I would take this a step further and suggest cutting out a lot of
headache in your code by using a php framework to manage a lot of the
functions you want to use.
I would check out codeigniter.com. CI has pagination and db helpers
so you don't have to write all the code yourself.
On Jan 7, 8:38 am, tlphipps <tra...@brandyandtravis.com> wrote:
> 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.
