All,
I just discovered this neat little gem in MySQL which makes it easy to
page large result sets:
* SELECT SQL_CALC_FOUND_ROWS *
FROM table
LIMIT 10, 10
* SELECT FOUND_ROWS()
The neat thing is that SQL_CALC_FOUND_ROWS will cause MySQL to tally up
all the rows that WOULD have matched your query if you hadn't used the
LIMIT and OFFSET clause to shorten your returned results. The next call
to FOUND_ROWS() will return that tally.
When developing paged list/search results this is VERY powerful
shorthand for generating the prev/next links and figuring out how many
pages to display.
I can't seem to find the equivalent of it in PostgreSQL! The only
options I see are:
1.
TWO queries. The first query will perform a SELECT COUNT(*) ...; and the second query performs the actualy SELECT ... LIMIT x OFFSET y;
2.
Using PHP row seek and only selecting the number of rows I need.
Here is an example of method number 2 in PHP:
<?php
//----------------------------------------------------------------------
function query_assoc_paged ($sql, $limit=0, $offset=0) {
$this->num_rows = false;
// open a result set for this query...
$result = $this->query($sql);
if (! $result) return (false);
// save the number of rows we are working with
$this->num_rows = @pg_num_rows($result);
// moves the internal row pointer of the result to point to our
// desired offset. The next call to pg_fetch_assoc() would return
// that row.
if (! empty($offset)) {
if (! @pg_result_seek($result, $offset)) {
return (array());
}
}
// gather the results together in an array of arrays...
$data = array();
while (($row = pg_fetch_assoc($result)) !== false) {
$data[] = $row;
// After reading N rows from this result set, free our memory
// and return the rows we fetched...
if (! empty($limit) && count($data) >= $limit) {
pg_free_result($result);
return ($data);
}
}
pg_free_result($result);
return($data);
}
//----------------------------------------------------------------------
?>
The next problem I have is that in the migration to PDO, there is no
'pg_result_seek' function equivalent. So, I guess that means that in
the PDO model, there is no option #2. Does that mean my only
alternative is to run option #1 in PostgreSQL?
I hate having to write 2 queries to get one set of data ... especially
when those queries start getting complex.
Dante
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
