If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

function to help paginate database record traversing

problem: you have a series of records to traverse inside your CMS (customers, users, blog entries, whatever). in managing this content sometimes records can get deleted, so you have to traverse records and not record IDs. so you build an array of the entries with the IDs as values and traverse that.

firstly the paginate function, usage: $pager=paginate(transaction, $pid);, where transaction is the table name and $pid is the current page number.

based upon the total number of records found, this will return $pager[0] and $pager[1] for the record IDs that you should use in SQL to select the right set of records, i.e. where id between $pager[0] and $pager[1] order by id desc, as the selection criteria. it will also return $pager[2] as the next page, $pager[3] as the previous page and $pager[4] as the last page and you can then use them as values for hidden form elements posting back to the admin.php page (or whatever you call your CMS page) for subsequent processing. (the first page value will obviously be 1), e.g.:

secondly we have individual record traversal. once we've clicked on the individual record in the records overview, we go to view the individual record and we want to be able to 'next' and 'previous' to other records.

usage: $nxtid=nextid(transaction, $bid);, where transaction is the table name and $bid is the current record being viewed.

this will return $nxtid[0] as the next record ID and $nxtid[1] as the previous one. example of use:

A potential issue I see is that it would appear to assume that the database field referenced by $pid is sequentially numbered without gaps. The typical pagination approach avoids this concern by using a LIMIT along with an OFFSET (instead of the BETWEEN clause you used), where LIMIT is the number of items per page and OFFSET is the page number minus 1 times the items per page (so that you start with an offset of 0).

A potential issue I see is that it would appear to assume that the database field referenced by $pid is sequentially numbered without gaps.

actually $pid is the page number calculated by grabbing all of the records and then working out the pages from that number, it's not got directly from the db. pages will naturally be sequential because they are merely divisions of this total. on arrival, with no $pid set, it will assume page 1.

that potential lack of sequence in the individual ID numbers of the records is the point of these functions.

Originally Posted by NogDog

The typical pagination approach avoids this concern by using a LIMIT along with an OFFSET (instead of the BETWEEN clause you used), where LIMIT is the number of items per page and OFFSET is the page number minus 1 times the items per page (so that you start with an offset of 0).

i see, that is an option, but the problem is that you do not know what the offset is. when you assemble an array you can use between because you have calculated the array position and obtained the ID values at those positions, to use as your upper and lower limits.