[PHP] paging results in large resultsets: mysql vs postgresql?
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
Re: [PHP] paging results in large resultsets: mysql vs postgresql?
At 11:11 AM -0500 6/15/06, D. Dante Lorenso wrote: 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; -snip- I hate having to write 2 queries to get one set of data ... especially when those queries start getting complex. I do exactly number 1 in http://ancientstones.com/catalog.php Keep in mind that the first query will tell you how many record are in that specific sort and then the second will present just those items. You have to work out the pageNumber, maxPageNumber, and numberOfPages and then it's simple. If you're concerned about the complexity of the query, then it makes sense to break it down to simpler steps. You should see the back-end of my site where if you are give a specific item, then where do you place it in a page given the user's search criteria. It made for an interesting exorcise. tedd -- http://sperling.com http://ancientstones.com http://earthstones.com -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] paging results in large resultsets: mysql vs postgresql?
On Thu, June 15, 2006 11:11 am, D. Dante Lorenso wrote: I just discovered this neat little gem in MySQL which makes it easy to page large result sets: * SELECT SQL_CALC_FOUND_ROWS * * SELECT FOUND_ROWS() I can't seem to find the equivalent of it in PostgreSQL! The only options I see are: 2. Using PHP row seek and only selecting the number of rows I need. 3. use the built-in cursor of PostgreSQL which pre-dates MySQL LIMIT and OFFSET clauses, which are non-standard hacks Rasmus introduced back in the day. -- Like Music? http://l-i-e.com/artists.htm -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] paging results in large resultsets: mysql vs postgresql?
Richard Lynch wrote: 3. use the built-in cursor of PostgreSQL which pre-dates MySQL LIMIT and OFFSET clauses, which are non-standard hacks Rasmus introduced back in the day. Care to elaborate? Cast into context of PDO if you can...? Dante -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] paging results in large resultsets: mysql vs postgresql?
On Thu, June 15, 2006 7:15 pm, D. Dante Lorenso wrote: Richard Lynch wrote: 3. use the built-in cursor of PostgreSQL which pre-dates MySQL LIMIT and OFFSET clauses, which are non-standard hacks Rasmus introduced back in the day. Care to elaborate? Cast into context of PDO if you can...? I've forgotten the exact cursor syntax, but it's like: $query = declare cursor foo as select complicated query here; $foo = pg_exec($connection, $query); $query = select 10 from foo; $ten_rows = pg_exec($connection, $query); I have completely forgotten the cursor query to get the number of rows, but it's in the docs. You are so totally on your own with that bleeding-edge PDO [bleep]. Sorry. -- Like Music? http://l-i-e.com/artists.htm -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] Paging results
Can somebody give me a url of an example or an example of how to page results so they can be view by clicking page 1 page 2 or back and next? results obtained from mysql database. Thanks Randy -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP] Paging results
On Thu, 15 Feb 2001 07:12, Randy Johnson wrote: Can somebody give me a url of an example or an example of how to page results so they can be view by clicking page 1 page 2 or back and next? results obtained from mysql database. Thanks Randy http://px.sklar.com/code.html?code_id=77 and leave out the require at the beginning. You'll need to hack it around a bit (lot?) to suit your circumstances, but the code to keep track of where you are is in there. -- David Robley| WEBMASTER Mail List Admin RESEARCH CENTRE FOR INJURY STUDIES | http://www.nisu.flinders.edu.au/ AusEinet| http://auseinet.flinders.edu.au/ Flinders University, ADELAIDE, SOUTH AUSTRALIA -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP] Paging results
I guess there are good articles on this at phpbuilder.com http://www.phpbuilder.com/columns/rod20001214.php3 http://www.phpbuilder.com/columns/rod2221.php3 hope that helps best regards Ankur Verma HCL Technologies A1CD, Sec -16 Noida, UP India - Original Message - From: "Randy Johnson" [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, February 15, 2001 2:12 AM Subject: [PHP] Paging results Can somebody give me a url of an example or an example of how to page results so they can be view by clicking page 1 page 2 or back and next? results obtained from mysql database. Thanks Randy -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED] -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
