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()?

‘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?

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

I have one client that wants to display 200+ records per page.

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

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.

while($row = $PDO->fetch(PDO::FETCH_ASSOC)) {
//Echo Record
}

Thanks starlion

So this:

while ( $row = fetch( $r ) ) {

Would be the equivalent of:

while ($row = mysql_fetch_assoc($r)) {

Right?

Ok, this worked for me. Thanks for help!!

<?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>';
?>

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)

Excellent info, thanks again. I wasn’t aware of the duplication.

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

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)

“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…

Thank you for adopting and embellishing my point.

So what improvement do you suggest?

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 >= (15curnumrecords)+<headerheight>-(1525)-window.height (Because IIRC window.y is measured from the top?)

Not 100% verbose on my javascript.

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