SitePoint Sponsor |
|
User Tag List
Results 1 to 10 of 10
Thread: pagination and pdo
-
Oct 29, 2008, 10:52 #1
- 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..
-
Oct 29, 2008, 11:15 #2
- 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?
-
Oct 29, 2008, 11:23 #3
- 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)
-
Oct 29, 2008, 11:25 #4
- 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.
-
Oct 29, 2008, 11:36 #5
- 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?
-
Oct 29, 2008, 11:48 #6
- Join Date
- Dec 2007
- Posts
- 348
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
I don't think MySQL supports scrollable cursors
-
Oct 29, 2008, 11:51 #7
- Join Date
- Oct 2008
- Posts
- 295
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Why not just do it with the LIMIT in query?
-
Oct 29, 2008, 11:54 #8
- Join Date
- Dec 2007
- Posts
- 348
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
-
Oct 29, 2008, 23:13 #9
- Join Date
- Jul 2008
- Posts
- 5,757
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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++;
}
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];
}
-
Nov 20, 2008, 18:13 #10
- Join Date
- Jul 2006
- Posts
- 200
- 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; }
is a function in a class:
Code:function init_statement($sql) { $stmt = self::$instance->prepare($sql); return $stmt; }
$t11 = $t1 . "%";
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