function: db results pagination

Small, short code snippets that other people may find useful. Do you have a good regex that you would like to share? Share it! Even better, the code can be commented on, and improved.

Moderator: General Moderators

Post Reply
qads
DevNet Resident
Posts: 1199
Joined: Tue Apr 23, 2002 10:02 am
Location: Brisbane

function: db results pagination

Post by qads »

function shows total number of pages and "displaying X out of XX pages"....

Code: Select all

<?php
function nextpage($table,$perpage,$start,$page, $query)
{
$start = (int)$start;
$query = "SELECT count(*) as count FROM $table $query"; 
$result = mysql_query($query);
$row = mysql_fetch_array($result);
$numrows = $row[0];
$b_link = "<<";
if($start > 0)
{
$b_link = "<a href="".$page."&start=".($start - $perpage).""><<</a>";
}
$r .=  "$b_link ";
$pages = ceil($numrows / $perpage);
$current_page = ceil($start / $perpage) + 1;
$link = 1;
$s = 0;
for($ba = 1; $ba <= $pages; $ba++)
{
$li = "<a href="$page&start=$s">$link</a> ";
if($ba == $current_page)
{
$li = "<b>$ba</b> ";
}
$page_links .= $li;
$s = $perpage * $link;
$link++;
}
$f_link = ">>";
if($numrows > ($start + $perpage))
{
$f_link = " <a href="".$page."&start=".($start + $perpage)."">>></a>";
}
$r .= "$page_links $f_link<br />Displaying Page ".($current_page)." of $pages<br /></b></font>\n\n\n";
if($numrows != 0)
{
return $r;
}
}
?>
example:

Code: Select all

<?php
$start = (int)$_GET['start'];
$perpage = 25;//change this to whatever number you like.
$query = mysql_query("select ID from users where ID > 10 limit $start, $perpage");
while($data = mysql_fetch_array($query))
{
echo $data['ID']."<br >";
}
//nextpage(tablename,perpage,start,pageurl, where clause);
echo nextpage("users",$perpage,$start,$_SERVER['PHP_SELF'], "where ID > 10");
?>
UPDATE - 17/07/2004
Last edited by qads on Fri Jul 16, 2004 6:11 pm, edited 3 times in total.
rapaddict_dot_com
Forum Commoner
Posts: 27
Joined: Tue Mar 16, 2004 4:54 pm

Post by rapaddict_dot_com »

is there any way of making it so this shows the number of pages too?

like instead of just "PREVIOUS - NEXT"

make it say "PREVIOUS - 1 2 3 4 5 - NEXT"
rapaddict_dot_com
Forum Commoner
Posts: 27
Joined: Tue Mar 16, 2004 4:54 pm

Post by rapaddict_dot_com »

nevermind, I got it
McGruff
DevNet Master
Posts: 2893
Joined: Thu Jan 30, 2003 8:26 pm
Location: Glasgow, Scotland

Post by McGruff »

There's a bit too much going on in that fn for my liking. What if you want to paginate something other than a query? You can't because the fn expects a query.

The fn also contains javascript. What if the user has js turned off?

PS: sorry if that sounds negative - not meant to be.
fastfingertips
Forum Contributor
Posts: 242
Joined: Sun Dec 28, 2003 1:40 am
Contact:

Post by fastfingertips »

I cannot agree with your snipet.

For the simple reason that in the situation of a large content your select is bringing an unwanted volume of information. You should add parameters to your select and in this way to bring only the range of the current page.
qads
DevNet Resident
Posts: 1199
Joined: Tue Apr 23, 2002 10:02 am
Location: Brisbane

Post by qads »

if it only counts the page range, then how will it know how many records there are in total?, it doest fetch anything from the db execpt the number of records.

btw, this is from ages ago, its just a copy and paste, i got a much better one now, with page numbers and all :P.

will post it soon.
jonas
Forum Commoner
Posts: 96
Joined: Sun May 23, 2004 9:25 pm

Post by jonas »

Yes, I am looking for a code like this for when I program my forums for my site.
Thank you very much, sir! An updated one would be much obliged!
bla5e
Forum Contributor
Posts: 234
Joined: Tue May 25, 2004 4:28 pm

Post by bla5e »

wow this was really useful! nice post
kettle_drum
DevNet Resident
Posts: 1150
Joined: Sun Jul 20, 2003 9:25 pm
Location: West Yorkshire, England

Post by kettle_drum »

Should store the function output somewhere, as most people would want to show previous/next stuff at both the top and bottom of the page and you dont want to keep calling the database query.
jonas
Forum Commoner
Posts: 96
Joined: Sun May 23, 2004 9:25 pm

Post by jonas »

Any updates to when the new code will be released?
qads
DevNet Resident
Posts: 1199
Joined: Tue Apr 23, 2002 10:02 am
Location: Brisbane

Post by qads »

right, just pasted the new one, remember, its just a paste, it was useing mysql class, so i had to chage it back to normal functions, but if u spot somethig, let me know.

- good luck
McGruff
DevNet Master
Posts: 2893
Joined: Thu Jan 30, 2003 8:26 pm
Location: Glasgow, Scotland

Post by McGruff »

fastfingertips wrote:I cannot agree with your snipet.

For the simple reason that in the situation of a large content your select is bringing an unwanted volume of information. You should add parameters to your select and in this way to bring only the range of the current page.
From Eclipse docs:
* This class might seem inefficient, exactly because it doesn't use mentioned
* database-specific SQL commands. However, for one very good reason, that
* isn't really true: it is almost always necessary to know the total number
* of pages in a query result - for example to be able to show a page
* navigator - and as this value is computed from the total number of rows in
* the query, the only way to get this number is by executing the full query.
* This completely eliminates the possible gain in efficiency when using
* <code>LIMIT</code>- or <code>SELECT TOP</code>-clauses, because in that
* case an additional (counting) query must be executed. Also, consider that
* most queries specify an ORDER BY clause, and remember that a DBMS can only
* compute this ordering by examining all rows in the result, even when just
* the first 10 are selected.
Not all dbs support LIMIT anyway.

Pagination all boils down to some pretty simple arithmetic which might be best encapsulated in its own class:

Code: Select all

/*
    CLASS Indexer

    The simple arithmetic required for pagination-type operations.
    An iterator would seek to the getOffset() value and call isValid() per iteration.
    The getNumPages method is provided for pagination link creation.

*/
class Indexer
{
    var $i = 0;

    /*
        param (integer) $total_rows
        param (integer) $subset_size
    */
    function Indexer($total_rows, $subset_size)
    {
        $this->total_rows  = $total_rows;
        $this->subset_size = $subset_size;
    }
    
    /*
        return (integer)
    */
    function getOffset($page_number = 1)
    {
        return ($this->_pageNumber($page_number) - 1) * $this->subset_size;
    }

    /*
        Simple counter called once per iteration.      
        return (integer)
    */
    function isValid()
    {
        return (++$this->i < $this->subset_size);
    }

    /*
        return (integer)
    */
    function getNumPages()
    {
        return ceil($this->total_rows/$this->subset_size);
    }

    
    //////////////////////////////////////////
    //              PRIVATE                 //
    //////////////////////////////////////////


    /*
        Filter invalid page numbers.
        return (integer)
    */
    function _pageNumber($page_number) 
    {
        if($page_number <= 0)
        {
            return 1;

        } elseif($page_number > ceil($this->total_rows/$this->subset_size)) {

            return $this->getNumPages();
        
        } else {
        
            return $page_number;
        }
    }


}
This (almost) allows you to use normal iterators for pagination rather than something like Eclipse's PagedQuery/PagedQueryResult:

Code: Select all

/*
        $it - an iterator
        $indexer - instance of Indexer, above
    */

    for($it->reset($indexer->getOffset()); ($it->isValid() and $indexer->isValid()); $it->next())
    {
        // etc
    }
As you may have noticed, reset() must be edited to accept an $offset arg and seek to that offset. Easy to do (and a default value of 0 allows reset() to behave as normal).
Last edited by McGruff on Mon Aug 08, 2005 6:57 pm, edited 1 time in total.
jonas
Forum Commoner
Posts: 96
Joined: Sun May 23, 2004 9:25 pm

Post by jonas »

Does the first posts code have page numbers?

How would we add page numbers?
McGruff
DevNet Master
Posts: 2893
Joined: Thu Jan 30, 2003 8:26 pm
Location: Glasgow, Scotland

Post by McGruff »

$pages defines the total pages.
Post Reply