Cycling through database records

[eluser]sore eyes[/eluser]
Can somebody advise me about cycling through database records. I want to show one record per page It’s a familiar database scenario. Navigation by buttons, ‘go first’ ‘go next’ etc. There are likely to be multiple users accessing the database at any one time.

For coding, there appears to be two navigation options for the buttons. Either one could use SELECT * FROM table LIMIT 17,1; for example, which would give the 18th record. This system uses the mysql record number.

Or one could use a given id for the records, say, client_id

I’m pretty much a newbie and can’t get an overall feel for which road to take.
My thoughts are that using the mysql record number has the disadvantage that with multiple uses the record set is constantly changing, making the navigation buttons more fragile. The client_id way enables one, when doing search, for example to end up at the same url address page that the navigation buttons are on.

[eluser]Michael Wales[/eluser]
Use the Pagination class to dynamically set your LIMIT offset.

[eluser]sore eyes[/eluser]
Hi Michael, thanks for that. Do you have any thoughts on the rest of query? I don't know which path to take? Regards.

[eluser]Pascal Kriete[/eluser]
It depends a little on the setup. If you don't need permalinks I would go with Mike's suggestion. On the other hand, if you need to link to them, you could query based on the creation time (add a timestamp to the entries), sort it, and limit to 1.

Code:

// Get these from the entry you're viewing
$current_id = 284;
$current_date = 28489384;

/* only including the pertinent AR statements (this would be 'next entry') */

// don't get this entry again
$this->db->where('id !=', $current_id);
// posted later than this one
// using >= since seconds are not very exact - the sort on id will weed out those that are posted at the same time.
$this->db->where('date >=', $current_date);

// Make sure the first result is that closest to the current entry
$this->db->order_by('date asc, id asc');

// And only get one
$this->db->limit(1);

Untested, but I think it get's the point across. Good luck .