Possible pagination approach with PDO
Results 1 to 4 of 4

Thread: Possible pagination approach with PDO

  1. #1
    High Energy Magic Dept. NogDog's Avatar
    Join Date
    Aug 2006
    Location
    Ankh-Morpork
    Posts
    13,986

    Possible pagination approach with PDO

    Just want to run this idea past any interested parties for thoughts on validity and correctness. Basic issue is I'm looking at some code that essentially runs the same query -- one that's pretty ugly and not terribly quick -- once to get the data to be displayed on the page (based on pagination offset/limit), then again to get the total count (so pagination knows how many pages there are. Here's the basic process I'm thinking of using to do it in one query:
    PHP Code:
    <?php

    // sample pagination values from some input or other
    $offset 20;
    $numPerPage 10;

    $sql "some complex query that takes more time than I'd like";
    $stmt $pdo->prepare($sql, array(PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL));
    $stmt->execute();
    // to be used by pagination functions for total items:
    $totalRows $stmt->rowCount();
    // populate array of data to be displayed on this page:
    $data = array();
    for(
    $ix 0$ix++; $ix $numPerPage) {
        
    $row $stmt->fetch(PDO::FETCH_NUMPDO::FETCH_ORI_ABS$offset $ix);
        if(
    $row != false) {
            
    $data[] = $row;
        }
        else {
            break;
        }
    }
    Thoughts? Suggestions?
    Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be." ~ from Nation, by Terry Pratchett

    "But the main reason that any programmer learning any new language thinks the new language is SO much better than the old one is because he’s a better programmer now!" ~ http://www.oreillynet.com/ruby/blog/...ck_to_p_1.html


    eBookworm.us

  2. #2
    Pna lbh ernq guvf¿
    Join Date
    Jul 2004
    Location
    Kansas City area
    Posts
    19,432
    My only suggestion would be to look into setting PDO::MYSQL_ATTR_USE_BUFFERED_QUERY to false; otherwise, I would think PHP is going to "buffer" rows in its own memory that you don't care about (which seems rather wasteful/inefficient to me).

    EDIT: Then again... I just re-read your post and realized that the use of MySQL was a complete assumption on my part. Was this assumption true, by any chance?

  3. #3
    High Energy Magic Dept. NogDog's Avatar
    Join Date
    Aug 2006
    Location
    Ankh-Morpork
    Posts
    13,986
    Nope, PostgreSQL.

    But I am trying to figure out where that query result set is buffered, saved, whatever for the reason you stated -- including wondering if it impacts the DB server itself in terms of memory and/or disk space. As it is possible, depending on search filters selected, for a lot of result rows (in theory over 400,000), it might be best to still do a separate query with just a "select count(*)" to get the total pagination number. I just hate the idea of running this query twice if I don't have to. (Apparently MySQL has some function that will return the total possible rows when you use offset/limit, but PostgreSQL appears not to have such a thing.)

    *sigh*
    Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be." ~ from Nation, by Terry Pratchett

    "But the main reason that any programmer learning any new language thinks the new language is SO much better than the old one is because he’s a better programmer now!" ~ http://www.oreillynet.com/ruby/blog/...ck_to_p_1.html


    eBookworm.us

  4. #4
    Senior Member
    Join Date
    Apr 2003
    Location
    Silver Lake
    Posts
    4,932
    Here's the MySQL function:
    http://dev.mysql.com/doc/refman/5.0/...ion_found-rows

    I tried googling around and most things I found suggest roughly the same approach, two queries in a begin/end block:
    http://www.postgresql.org/message-id...nda.lfix.co.uk

    I do recall looking at phpBB source code some time back and seem to recall that the full-text searches that you can do have their results stored in a searches table. If the search is painful, you might try storing the ids of the items returned by the search in a table temporarily?
    IMPORTANT: STOP using the mysql extension. Use mysqli or pdo instead.
    World War One happened 100 years ago. Visit Old Grey Horror for the agony and irony.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •