SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Addict
    Join Date
    Dec 2007
    Posts
    348
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    pagination and pdo

    normally when paginating result sets, you have to jump to a specific page (subset) within that result set, using mysql_data_seek or whatever and then a limit to say how many results are returned.

    how is this achieved by using a PDOStatement returned from a query? the object is Traversable but it's not an Iterator..

  2. #2
    SitePoint Addict
    Join Date
    Dec 2007
    Posts
    348
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    actually, having run a quick search, I've never actually seen an implementation of pagination using PDO .. is it even possible directly?

  3. #3
    SitePoint Enthusiast
    Join Date
    Mar 2001
    Location
    Hailsham, UK
    Posts
    62
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It is better to limit your results in SQL:
    select * from t where xyz limit 0, 10
    (where 0 is the first record and 10 is the number of records)

  4. #4
    SitePoint Addict
    Join Date
    Dec 2007
    Posts
    348
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I know that's an alternative way, but you also need to run a second query to get the total number of rows in most cases for pagination..

    I was just wondering how it was achieved with a PDO result because I can'ts eem to find an example of it done.

  5. #5
    SitePoint Enthusiast
    Join Date
    Mar 2001
    Location
    Hailsham, UK
    Posts
    62
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sorry that was unhelpful, I've only just started with PDO myself. Can the additional parameters of PRO:repare() and PDOStatement::fetch() help - using a scrollable cursor and fetching just the rows you want?

  6. #6
    SitePoint Addict
    Join Date
    Dec 2007
    Posts
    348
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I don't think MySQL supports scrollable cursors

  7. #7
    SitePoint Addict
    Join Date
    Oct 2008
    Posts
    295
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Why not just do it with the LIMIT in query?

  8. #8
    SitePoint Addict
    Join Date
    Dec 2007
    Posts
    348
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by TeNDoLLA View Post
    Why not just do it with the LIMIT in query?
    I can do that, yes.

    But I want to know if it can be done without it! lol

    I'm still umming and arring about switching to PDO or just carrying on with my own ready-made classes (which achieve pagination but don't have prepared statements.. yet!).

  9. #9
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2008
    Posts
    5,757
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by old_iron View Post
    I know that's an alternative way, but you also need to run a second query to get the total number of rows in most cases for pagination..
    Issuing a seperate select COUNT(*) is quite fast. With large result sets, it may be much faster to issue this second query for the count, than to make the database give you a ton of rows you don't need because you didn't want to use limit. You can also read up on SQL_CALC_FOUND_ROWS.

    But, in any case, you you dont need a function to seek. You can always buffer the rows yourself and just print the ones you want. The logic applies to any database result fetching.
    PHP Code:
    $min 20;
    $max 40;
    $row_num 0;
    while (
    $row some_db_fetch_row($res)) {
        if (
    $row_num >= $min && $row_num =< $max) {
            
    // its a row you want
        
    }
        
    $row_num++;

    If using mysql_query() for comparison, php internally buffers the entire database result before you even get a chance to iterate it anyway, so you aren't really saving any mentionable resources by using mysql_data_seek().

    pdo can make it a tiny bit easier
    PHP Code:
    $rows $stmt->fetchAll();
    echo 
    $num_rows count($rows);
    for (
    $i=20$i<40$i++) {
        
    // $rows[$i];

    Still wasteful, though. But if the result set is small...meh.

  10. #10
    SitePoint Zealot
    Join Date
    Jul 2006
    Posts
    198
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code:
    try
    {
    $sql = "SELECT count(petid) as total FROM pets WHERE petname like :t11";
    $db = db::getInstance();
    $stmt = db::init_statement($sql);
    db::exeSQL($sql, $t11, $stmt);
    $_SESSION['total_records'] = $stmt->fetch(PDO::FETCH_COLUMN);
    $trec = $_SESSION['total_records'];
    echo $trec; 
    $db = null;
    }
    catch (Exception $e)
    {
    $_SESSION['total_records'] = 0;
    }
    This line ($stmt = db::init_statement($sql) is a function in a class:
    Code:
    function init_statement($sql)
    {
    $stmt = self::$instance->prepare($sql);
    return $stmt;
    }
    By the way:
    $t11 = $t1 . "&#37;";
    This: db::exeSQL($sql, $t11, $stmt); is in class:
    function exeSQL($sql, $t11, $stmt)
    {
    if(isset($_SESSION['ownerpet']))
    {
    // echo "ownerpet is set";
    $stmt->bindParam(':id11', $t11, PDO::PARAM_INT);
    $stmt->execute();
    }
    else
    {
    $stmt->bindParam(':t11', $t11, PDO::PARAM_STR, 30);
    $stmt->execute();
    }
    }
    Last edited by jim9; Nov 20, 2008 at 18:18. Reason: code


Bookmarks

Posting Permissions

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