pagination and pdo

[old_iron]

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..

[old_iron]

actually, having run a quick search, I've never actually seen an implementation of pagination using PDO .. is it even possible directly?

[calkie_f]

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)

[old_iron]

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.

[calkie_f]

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?

[old_iron]

I don't think MySQL supports scrollable cursors

[TeNDoLLA]

Why not just do it with the LIMIT in query?

[old_iron]

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!).

[crmalibu]

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.

$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

$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.

[jim9]

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:

function init_statement($sql)
{
$stmt = self::$instance->prepare($sql);
return $stmt;
}

By the way:

$t11 = $t1 . "%";

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();
}
}