Thread: fetching rows
Let say we have a select that returns 100 rows. I can fetch first 25 with simple sql: BEGIN WORK; DECLARE liahona CURSOR FOR SELECT * FROM films; FETCH [FORWARD] 25 IN liahona; CLOSE liahona; COMMIT WORK; but how I can fetch rows from 26 to 50? I mean withou fetching first 25. Or can I skip first 25? When i ask this you need to know that Im using PHP in web environment and try do this to get more performens from the script (now the script fetch all rows, then jump to needed row)
I hate to be the bearer of bad news, but if you're using PHP and you wanted to fetch just 25 rows at a time for a singlepage, and then fetch more when the user clicks on a NEXT button or link, you're completely out of luck. Each httptransaction is completely separate and so you can't maintain a cursor between pages. Perhaps LIMIT would help you here;but I've found it more useful to make sure there is a unique key for the order I'm displaying, and then say "WHERE key> highest_key_value_on_current_page" Join 18 million Eudora users by signing up for a free Eudora Web-Mail account at http://www.eudoramail.com
Nikolay Mijaylov wrote: > > Let say we have a select that returns 100 rows. > > I can fetch first 25 with simple sql: > > BEGIN WORK; > DECLARE liahona CURSOR FOR SELECT * FROM films; > FETCH [FORWARD] 25 IN liahona; > CLOSE liahona; > COMMIT WORK; > > but how I can fetch rows from 26 to 50? I mean withou fetching first 25. Or > can I skip first 25? you can't do that with a cursor, but you can use they mysql-ism called a limit clause. for example, to fetch rows 26-50 from that query, you'd do: select * from films limit 25,26; or select * from files limit 25 offset 26; -- Jeff Hoffmann PropertyKey.com
>>>>> "Jeff" == Jeff Hoffmann <jeff@propertykey.com> writes: Jeff> you can't do that with a cursor, but you can use they mysql-ism Jeff> called a limit clause. for example, to fetch rows 26-50 from Jeff> that query, you'd do: Jeff> select * from films limit 25,26; Jeff> or Jeff> select * from files limit 25 offset 26; Since there's no particular ordering of the output of such a query it is necessary to explicitly state an ordering key. Otherwise you'll quickly find that the phase of the moon has very significant influence on the produced results. :-) Especially in 7.*, as I understand it. Martin -- GPG public key: http://home1.stofanet.dk/factotum/gpgkey.txt
Hello, Nikolay, Don't use cursors; instead, use: " select * from films limit 25 offset 0 ; " and on the next query: " select * from films limit 50 offset 25 ; " and so on. You have to encode the current offset into the NEXT link, either making it into a button inside a form, with a hidden field containing the offset, or making it an HREF with URL-encoded data (HREF="myform.php?mycounter=50"). I don't remember in which version the LIMIT option appeared; if your version doesn't support it, move on to 7.0.2! Have fun! On Mon, 30 Oct 2000, Nikolay Mijaylov wrote: > Let say we have a select that returns 100 rows. > > I can fetch first 25 with simple sql: > > BEGIN WORK; > DECLARE liahona CURSOR FOR SELECT * FROM films; > FETCH [FORWARD] 25 IN liahona; > CLOSE liahona; > COMMIT WORK; > > but how I can fetch rows from 26 to 50? I mean withou fetching first 25. Or > can I skip first 25? > > When i ask this you need to know that Im using PHP in web environment and > try do this to get more performens from the script (now the script fetch > all rows, then jump to needed row) > > ------------------------------------------------------- Fight for a free world: no walls, no windows, no gates.
On Monday 30 October 2000 14:02, Jeff Hoffmann wrote: > Nikolay Mijaylov wrote: > > Let say we have a select that returns 100 rows. > > > > I can fetch first 25 with simple sql: > > > > BEGIN WORK; > > DECLARE liahona CURSOR FOR SELECT * FROM films; > > FETCH [FORWARD] 25 IN liahona; > > CLOSE liahona; > > COMMIT WORK; > > > > but how I can fetch rows from 26 to 50? I mean withou fetching first 25. > > Or can I skip first 25? > I've done some web pages that have paging. It did it with DECLARE to make a cursor then I used PostgreSQL's non-standard MOVE SQL command to start FETCHing from some offset depending on the page number. > you can't do that with a cursor, but you can use they mysql-ism called a > limit clause. for example, to fetch rows 26-50 from that query, you'd > do: > > select * from films limit 25,26; > > or > > select * from files limit 25 offset 26; -- -------- Robert B. Easter reaster@comptechnews.com --------- - CompTechNews Message Board http://www.comptechnews.com/ - - CompTechServ Tech Services http://www.comptechserv.com/ - ---------- http://www.comptechnews.com/~reaster/ ------------
"Robert B. Easter" wrote: > > > you can't do that with a cursor, but you can use they mysql-ism called a > > limit clause. for example, to fetch rows 26-50 from that query, you'd > > do: > > > > select * from films limit 25,26; > > > > or > > > > select * from files limit 25 offset 26; I did know it was possible but did not know how to do, thanks (It was not my question but I'm glad to see the solution:) and how can I know how many rows are returned by the query? -- Arnaud ( http://www.ressource-toi.org )
On Friday 17 November 2000 04:01, Arnaud Vandyck wrote: > "Robert B. Easter" wrote: > > > you can't do that with a cursor, but you can use they mysql-ism called > > > a limit clause. for example, to fetch rows 26-50 from that query, > > > you'd do: > > > > > > select * from films limit 25,26; > > > > > > or > > > > > > select * from files limit 25 offset 26; > > I did know it was possible but did not know how to do, thanks (It was > not my question but I'm glad to see the solution:) > > and how can I know how many rows are returned by the query? > I don't know exactly. I don't know of any way to find the total number of rows in a cursor. If you really need to know, you'll have to run a count(*) first, then make the cursor using the same select almost. Once you get the count(*), you can then use MOVE and FETCH to get the page you want. If someone has done it a better way, I'd like to hear how. However, sometimes it is possible to cache a count(*) value somewhere in the database so it doesn't have to be found everytime - it depends on your database and what the select is if you can store the count in advance somehow. > -- > Arnaud > ( http://www.ressource-toi.org ) -- -------- Robert B. Easter reaster@comptechnews.com --------- - CompTechNews Message Board http://www.comptechnews.com/ - - CompTechServ Tech Services http://www.comptechserv.com/ - ---------- http://www.comptechnews.com/~reaster/ ------------
On Fri, Nov 17, 2000 at 10:01:28AM +0100, Arnaud Vandyck wrote: > "Robert B. Easter" wrote: > > > > > you can't do that with a cursor, but you can use they mysql-ism called a > > > limit clause. for example, to fetch rows 26-50 from that query, you'd > > > do: > > > > > > select * from films limit 25,26; > > > > > > or > > > > > > select * from files limit 25 offset 26; > > I did know it was possible but did not know how to do, thanks (It was > not my question but I'm glad to see the solution:) > > and how can I know how many rows are returned by the query? If you use ruby's interface to postgresql you can use num_tuples to determine how many rows are returned by the query. Ruby is an exiting young language somewhat like python. Johann. -- J.H. Spies - Tel. 082 782 0336 / 023 55 11 568 "A Song for the sabbath day. It is a good thing to give thanks untothe LORD, and to sing praises unto thy name, O most High." Psalms 92:1