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
