www.webdeveloper.com
Results 1 to 3 of 3

Thread: function to help paginate database record traversing

  1. #1
    Join Date
    Mar 2005
    Location
    planet earth
    Posts
    121

    Lightbulb 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.:

    <form name="listtnf" method="post" action="admin.php"><input type="hidden" name="action" value="rtrans" /><input type="hidden" name="pid" value="<?php echo $pager[2] ?>" /><a href="admin.php" onclick="document.listtnf.submit(); return false">Next</a></form>

    here is the function (based on 20 records per page):

    function paginate($table, $pid) {

    include 'includes/dbconn.php';
    $ppx=$db->query("select id from $table order by id desc");
    $xc=1;
    $pc=1;
    while ($xid=$ppx->fetch_row()) {
    if ($xc%20==0) $page[]=$pc++;
    $stack[]=$xid[0];
    $xc++; }
    $xc--;
    $np=floor($xc/20);
    if (!$np) $np=1;
    if (($xc/20) > $np) { $np=$np+1; $page[]=$pc++; }

    if ($pid==1) {
    $pager[0]=$stack[19];
    $pager[1]=$stack[0];
    } else {
    if ($stack[($pid*20)]) {
    $pager[0]=$stack[($pid*20)-1];
    $pager[1]=$stack[($pid*20)-20];
    } else {
    $pager[0]=$stack[$xc-1];
    $pager[1]=$stack[$pid*20-20];
    }
    }
    $ppid=$pid-2;
    if (($pid+1)>$np) $pager[2]=$page[0]; else $pager[2]=$page[$pid];
    if (($pid-1)<1) $pager[3]=$page[$np-1]; else $pager[3]=$page[$pid-2];
    $pager[4]=$np;
    return $pager;
    }

    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:

    <form name="vsfn" method="post" action="admin.php" ><input type="hidden" name="action" value="vsale" /><input type="hidden" name="bid" value="<?php echo $nxtid[0] ?>" /><a href="admin.php" onclick="document.vsfn.submit(); return false">Next</a></form>

    ..and here is the function:

    function nextid($table, $bid) {

    include 'includes/dbconn.php';
    $ppx=$db->query("select id from $table order by id");
    $xc=0;
    while ($xid=$ppx->fetch_row()) {
    $stack[]=$xid[0];
    if ($xid[0]==$bid) $cpa=$xc;
    $xc++; }
    $xc--;
    if (($cpa+1)>$xc) $nextid[0]=$stack[0]; else $nextid[0]=$stack[$cpa+1];
    if (($cpa-1)<0) $nextid[1]=$stack[$xc]; else $nextid[1]=$stack[$cpa-1];
    return $nextid;
    }

    (obviously these two functions would sit in an external file and get called)


    any suggestions to make this simpler or better i'm happy to hear!
    captain, i've spotted an M-class planet!

    http://eupeople.net/forum

  2. #2
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,327
    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).
    PHP Code:
    $itemsPerPage 10;
    $pageNbr = (!empty($_GET['page_number'])) ? (int) $_GET['page_number'] : 1;
    $offset = ($pageNbr 1) * $itemsPerPage;
    $query "SELECT * FROM table_name ORDER BY timestamp_field LIMIT $itemsPerPage OFFSET $offset"
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

  3. #3
    Join Date
    Mar 2005
    Location
    planet earth
    Posts
    121

    Smile

    hi thx for your reply

    Quote Originally Posted by NogDog View Post
    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.

    Quote Originally Posted by NogDog View Post
    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.
    Last edited by hitecbill; 07-15-2013 at 04:58 PM.
    captain, i've spotted an M-class planet!

    http://eupeople.net/forum

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles