How to make a scrolling list?

martinj

This must be an easy one for somebody out there. I want to display a list of records on the page 10 items long with a next and previous button. What is the best way to write SQL statements to get this job done?

I looked into cursors, but Postgres only maintains these within a transaction boundary. Can't keep a transaction open across Pages can I?

There is also a problem simply using the WHERE clause for greater than or less than searches on a field because I want to order my list on fields that are not required to be unique. Thus is field XYZ contains 30 records with the value 'brown' when I try to scroll through that portion of the list we will get stuck.

Currently I am using the LIMIT command in my SQL SELECT to identify the exact record numbers to display. This however, is a time costly SQL command for larger databases.

Any ideas?

Anon

There are a couple articles here at PHPbuilder dedicated to this subject. You might also want to search through the forums. This same question has been answered many times.

martinj

OK I give up. I've been searching the archives. I've found articles on how to create a list box and scroll through that. However, I cannot load all the data from a table with 300,000 records into a list box everytime a page loads.

I need to use PostgreSQL to read 10-20 records from a 300,000 table. Then I need a function to get the next or previous 10-20 records for display on the next page. The challenge is that I need the next or previous select to be sorted on a field that may not be unique.

For example:

name id

a 3
b 6
c 7
dog 1
dog 4
dog 8
dog 9
e 2

How to create a SELECT statement to scroll through this list properly?

Initialize list:
SELECT name, id from table where name>="" order by name limit 3;

Get previous list:
SELECT name, id from table where name<="1st name on previous list" order by name limit 3;

Get next list:
SELECT name, id from table where name>="last name on previous list" order by name limit 3;

The following code will not work properly when we get to the area of the list where name=dog.

I'm sure that I am missing something easy here. SQL power should be able to conquer this simple problem. Thanks for any help.

Jeff