What is the best procedure to scroll through a list of database records? I am using PHP to present a user interface to a list of customer records. I want to display X records at a time and provide buttons to sort the list by every displayed field as well as next and previous buttons. Since I am using PHP the user interface cannot be contained within one database connection or transaction, thus cursors cannot be used. (It would be great if there was such a thing as a cursor that persisted between database connections and transactions).
The problem specifically stated is how can I use SQL to scroll through records X at a time when the fields I am qualifying by and sort by are not required to be unique? Example below....
First search = select id, name, phone from account where name>='martin' order by name asc limit 3;
1, martin, 123-456-7890
5, martin, 345-123-7890
9, martin, 654-987-1234
Previous search = select id, name, phone from account where name<='use first NAME of current list' order by name desc limit 3;
This will not work because our database has more than one 'martin';
Next search = select id, name, phone from account where name>='use last NAME of current list ' order by name asc limit 3;
One solution could be if SQL had a means to identify the specific starting record for a relative search. Then I could specifically identify the first record in my list by its unique Id and get all the records from that point on. Does SQL or PostgreSQL support this concept?
My current implementation uses the LIMIT x, y feature to scroll through the whole list by numerical index. But this is not going to work for tables of any consequential size.
Thanks for any help or ideas,
Jeff
Jeff Martin
704 Torrey Lane, Apartment D
Boalsburg, PA 16827
H814-466-7791
jeff@dgjc.org
www.dgjc.org