SitePoint Sponsor

User Tag List

Results 1 to 18 of 18
  1. #1
    SitePoint Zealot bopjo1's Avatar
    Join Date
    Jun 2007
    Location
    Tampa, FL
    Posts
    110
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Using PDO to select multiple rows

    Hi all,

    I know about using fetchAll() to select a bunch of records, but each book I've read on the subject says this is not smart if the number of records is greater than 100 or so because it requires too much memory.

    So how does one use PDO to select and display huge numbers of rows, if not with fetchAll()?

  2. #2
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    73 Post(s)
    Tagged
    0 Thread(s)
    'too much memory' is a relative thing.

    If your result rows are 2 integers, it's gonna take a -lot- of rows to generate too much memory.
    If your result rows are full text pages, it's gonna take a lot fewer.

    Generally speaking, most queries should be fine up to a couple thousand results.

    That said; how to handle the need to display huge numbers of rows.

    In what context would you need to display huge numbers of rows that couldnt benefit from a bit of Pagination?

  3. #3
    SitePoint Evangelist
    Join Date
    Aug 2005
    Location
    Winnipeg
    Posts
    498
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Using the fetch() method instead. fetchAll() consumes a lot of memory because it returns every record as a native PHP array - whereas fetch() returns a single record and expects you to either build the array manually or iterate each while outputting results - common mistake.

    Cheers,
    Alex
    The only constant in software is change itself

  4. #4
    SitePoint Zealot bopjo1's Avatar
    Join Date
    Jun 2007
    Location
    Tampa, FL
    Posts
    110
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I have one client that wants to display 200+ records per page.

  5. #5
    SitePoint Zealot bopjo1's Avatar
    Join Date
    Jun 2007
    Location
    Tampa, FL
    Posts
    110
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by PCSpectra View Post
    Using the fetch() method instead. fetchAll() consumes a lot of memory because it returns every record as a native PHP array - whereas fetch() returns a single record and expects you to either build the array manually or iterate each while outputting results - common mistake.

    Cheers,
    Alex
    If you have time, could you expound on this please? How do I use fetch() to display a large amount of records?

  6. #6
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    73 Post(s)
    Tagged
    0 Thread(s)
    a while loop over a fetch call would work... i'd still say that unless the records are very small, 200 records is a lot to display in a single result page.

  7. #7
    SitePoint Zealot bopjo1's Avatar
    Join Date
    Jun 2007
    Location
    Tampa, FL
    Posts
    110
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by StarLion View Post
    a while loop over a fetch call would work... i'd still say that unless the records are very small, 200 records is a lot to display in a single result page.
    Totally agree...but my client insists on it.

  8. #8
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    73 Post(s)
    Tagged
    0 Thread(s)
    while($row = $PDO->fetch(PDO::FETCH_ASSOC)) {
    //Echo Record
    }

  9. #9
    SitePoint Zealot bopjo1's Avatar
    Join Date
    Jun 2007
    Location
    Tampa, FL
    Posts
    110
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks starlion

    So this:

    Code:
    while ( $row = fetch( $r ) ) {
    Would be the equivalent of:

    Code:
    while ($row = mysql_fetch_assoc($r)) {
    Right?

  10. #10
    SitePoint Zealot bopjo1's Avatar
    Join Date
    Jun 2007
    Location
    Tampa, FL
    Posts
    110
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok, this worked for me. Thanks for help!!

    Code:
    <?php
    require_once( 'includes/DataObject.class.php' );
    $db = new DataObject();
    $conn = $db->connect();
    
    $q = "SELECT * FROM users";
    $stmt = $conn->prepare( $q );
    $stmt->execute();
    echo '<ul>';
    while( $row = $stmt->fetch() ) {
    	echo '<li>' . $row['first_name'] . '</li>';
    }
    echo '</ul>';
    ?>

  11. #11
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    73 Post(s)
    Tagged
    0 Thread(s)
    If you're very concerned about memory usage, you can specify the PDO::FETCH_ASSOC parameter to the optional first parameter.

    Without it, you get FETCH_BOTH, which returns something like:
    $row array(
    [0] => "Steve",
    ["firstname"] => "Steve"
    )

    (note the duplication - numerical and associative - hence BOTH)

  12. #12
    SitePoint Zealot bopjo1's Avatar
    Join Date
    Jun 2007
    Location
    Tampa, FL
    Posts
    110
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Excellent info, thanks again. I wasn't aware of the duplication.

  13. #13
    SitePoint Evangelist
    Join Date
    Aug 2005
    Location
    Winnipeg
    Posts
    498
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    200 results per page is a bit much, if your client insists on 200 per page and your concerned about performance (honestly it will probably take longer to render the results client side than select 200 records - which is faster than you can blink) I would strongly recommend making the pagina results an option via drop down or similar so when he experiences a slow rendering process he can optionally switch to a lower (more realistic) results per page count.

    Cheers,
    Alex
    The only constant in software is change itself

  14. #14
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    I guess there must be a way to select a "screens worth" of data at a time (say 50 rows?) using Ajax.

    When JS detects a "scroll to" value > 50% of the existing screen length, fetch another 50 records using the LIMIT clause.

    (a bit like how the Twitter website works - I often scroll on down back to when I was last online to read what the relatively few people I follow have said while I was offline - yes, weird I know)

  15. #15
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    73 Post(s)
    Tagged
    0 Thread(s)
    "When JS detects a "scroll to" value > 50% of the existing screen length, fetch another 50 records using the LIMIT clause."

    Needs a bit of refinement.

    50% of 50 = 25
    50% of 100 = 50 .. but we're just starting to look at the second set of data, and you're already loading the third....
    50% of 150 = 75 ... the third set isnt even on the screen yet and we're loading the fourth....

  16. #16
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    Thank you for adopting and embellishing my point.

    So what improvement do you suggest?

  17. #17
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    73 Post(s)
    Tagged
    0 Thread(s)
    Fixed Pixelheight from the bottom?

    EG: You know your records are 15px high.
    When are we at the bottom of the third set?
    15*<numrecordsondisplay>+<anyheaderheight>-<buffer>-<windowheight>

    So if we wanted to load new records when we start to see the bottom 25 records...

    trigger on the window.y >= (15*curnumrecords)+<headerheight>-(15*25)-window.height (Because IIRC window.y is measured from the top?)

    Not 100% verbose on my javascript.

  18. #18
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    Righto, I get your drift - my guess was really hazy, and clearly I was not talking from experience.

    Anyhow, there is an alternative for the OP to grab the records in chunks and display them and yet possibly keep his boss happy - but not too trivial to accomplish either.

    I wonder if in doing it this way we unearth a usability pattern?

    I found a library Item Pagination Pattern - Design Pattern Library - YDN - but that is not it

    Edit:

    Predictive Fetch - sounds more like it
    Last edited by Cups; May 5, 2011 at 15:30. Reason: I thought I found it, but it was not ...


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
  •