#1
  1. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Sep 2006
    Posts
    1,923
    Rep Power
    533

    Pagination with one query


    Typical pagination will do something which determines the total number of records, and uses the desired per page and the current page to display the pertinent records and display something like "Page 3 of 12". Below is some example code. Is it possible to do this with one query? I know that SQL_CALC_FOUND_ROWS will provide the count if there wasn't a limit. Thanks

    PHP Code:
    $records_per_page=10;
    $pageIndex=(int)$_GET['pageIndex'];
    $stmtdb::db()->query('SELECT COUNT(*) FROM myTable');
    $totalRecords=$stmt->fetchColumn();
    $pageCount=max(ceil($totalRecords/$records_per_page),1);
    $currentPage=min($pageIndex,$pageCount);
    $stmt db::db()->query('SELECT * FROM myTable LIMIT '.($currentPage 1) * $records_per_page.','.$records_per_page); 
  2. #2
  3. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,957
    Rep Power
    1046
    Hi,

    I question the whole error-correction logic.

    If I enter invalid data (like a non-numeric or a nonexistent page number), I want to be told, not silently corrected by a virtual nanny. I don't know your users, but I'm pretty sure most people do not appreciate this kind of forced blessing.

    If the user requested page 13 from 12 pages, tell them. Something went wrong, so they should know and decide what to do next. Do not silently assume that they meant page 12. This can lead to a lot of confusion. The same is true for non-numeric input. A pageIndex=abc is an invalid reference, not “Let's just say you asked me for page 1”.

    The classical LIMIT approach, despite being extremely popular, also has issues. Most websites simply do not work like a book. They change all the time, new entries get added, unwanted entries may get deleted. Static pagination doesn't work very well for that.
    The 6 worst sins of securityHow to (properly) access a MySQL database with PHP

    Why can’t I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Sep 2006
    Posts
    1,923
    Rep Power
    533
    Originally Posted by Jacques1
    I question the whole error-correction logic.
    I don't think you understand. It has nothing to do with error-correction logic. When the user requests records, I wish to provide those records in bite (not byte ) size pieces, and give one of the typical "1 2 3 ... 43 44 next" menus at the bottom.
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Sep 2006
    Posts
    1,923
    Rep Power
    533
    After going to the link you posted, you appear to understand, however, I still do not understand what error-correction logic has to do with it.
  8. #5
  9. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,957
    Rep Power
    1046
    Um, what? The one and only reason for even doing the first query as opposed to using SQL_CALC_FOUND_ROWS is this line:

    PHP Code:
    $currentPage=min($pageIndex,$pageCount); 
    That is, all page numbers greater than the last page are automatically mapped to the last page -- which requires you to get the total number of pages before you do the actual query.

    Im saying that this correction (what else do you call it?) together with the int casting on top of the script are bad, because they silently change the user input when you should actually show a proper error message.

    But now it sounds like you never wanted this line of code? I'm confused. Either way, if the correction goes away, the first query is useless and can simply be deleted.

    The rest depends on whether or not you want to stick to static pagination. Personally, I couldn't think of a user who would want to jump specifically to page 43 when the content of the pages isn't even constant.
    The 6 worst sins of securityHow to (properly) access a MySQL database with PHP

    Why can’t I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Sep 2006
    Posts
    1,923
    Rep Power
    533
    Could we start over?

    All I want is a good approach to displaying the number of blocks of available records and displaying which block is being viewed (so I could show the "1,2,3.... 5 ... 12,13,14), and displaying the results of the viewed block.

    http://www.mysqlperformanceblog.com/...lc_found_rows/ states that it is faster to perform a COUNT query before the query to display the results instead of using SQL_CALC_FOUND_ROWS. The blog is old and likely is no longer applicable, and I was planning on using one query only if possible and using SQL_CALC_FOUND_ROWS.

    Thanks
  12. #7
  13. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,957
    Rep Power
    1046
    Well, then what stops you from doing one query?

    PHP Code:
    <?php

    $entriesPerPage 
    10;


    if ( !isset(
    $_GET['page']) || (ctype_digit($_GET['page']) && $_GET['page'] >= 0) )
    {
        
    $page = isset($_GET['page']) ? $_GET['page'] : 1;

        
    $entries $db->query('
            SELECT SQL_CALC_FOUND_ROWS
                foo
                , bar
            FROM
                some_table
            WHERE
                whatever
            LIMIT
                ' 
    intval(($page 1) * $entriesPerPage) . ', ' intval($entriesPerPage) . '
        '
    );

        
    $totalEntriesStmt $db->query('
            SELECT
                FOUND_ROWS()
        '
    );
        
    $totalEntries $totalEntriesStmt->fetchColumn();
        
    $totalPages ceil($totalEntries $entriesPerPage);

        if (
    $entries->rowCount)
        {
            
    // display the page content
        
    }
        else
            echo 
    htmlEscape('There is no page ' $_GET['page'] . '. Please choose a page number from the navigation.');
    }
    else
        echo 
    htmlEscape('Invalid page number.');
    Don't fumble with optimizations before you have solid evidence that there's actually a performance issue.

    The problem of doing two separate queries should be obvious: If the entries change in between, the pagination may not match the actual number of entries.

    Comments on this post

    • NotionCommotion agrees : Thanks, let me play with it.
    The 6 worst sins of securityHow to (properly) access a MySQL database with PHP

    Why can’t I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".

IMN logo majestic logo threadwatch logo seochat tools logo