php sample script for pagination

Question

Can anyone suggest a good php script for pagination where one want to display lot of items from database pagewise

Answer 1

The secret is in SQL's LIMIT x OFFSET y clauses

Answer 2

Following link can help you, its has easy to use description too

http://www.strangerstudios.com/sandbox/pagination/diggstyle.php

If you would like to have a tutorial then you should read on

Answer 3

This isn't hard to do. Here's a recipe for a quick & simple system:

1. Do the SELECT query, add the clause LIMIT PERPAGE*(PAGENUM-1),PERPAGE Note that the all in caps variables could be GET parameters to your script.

2. Find the total number of results for your SELECT query if there wasn't a LIMIT clause (there are methods to do this, for instance, MySQL's SELECT "SQL_CALC_FOUND_ROWS * FROM . . .", which would be integrated into #1.) Call this N

3. Display to the user the data, along with "Showing items PERPAGE*(PAGENUM-1)+1 to PERPAGE*PAGE of N"

I would post an example, but my queries and DB handle are handled elsewhere in my code.

Answer 4

A plain and simple one in purpose to learn from

<?
$per_page=10;

//put FROM and WHERE parts into separate  variable
$from_where="FROM table WHERE filter=1";
//getting total number of records 
$res=mysql_query("SELECT count(id) ".$from_where);
$row=mysql_fetch_row($res);
$total_rows=$row[0];

//Process GET variables to get $start value for LIMIT
if (isset($_GET['page'])) $CUR_PAGE=($_GET['page']); else $CUR_PAGE=1;
$start=abs(($CUR_PAGE-1)*$per_page);

//getting records from database into array
$query="SELECT * $from_where ORDER BY date DESC LIMIT $start,$per_page";
$res=mysql_query($query);
while ($row=mysql_fetch_array($res)) $DATA[++$start]=$row;

//Getting page URL without query string
$uri=strtok($_SERVER['REQUEST_URI'],"?")."?";

//create a new query string without a page variable
if (isset($_GET['page'])) unset($_GET['page']);
if (count($_GET)) {
  foreach ($_GET as $k => $v) {
    if ($k != "page") $uri.=urlencode($k)."=".urlencode($v)."&";
  }
}

//getting total number of pages and filling an array with links
$num_pages=ceil($total_rows/$per_page);
for($i=1;$i<=$num_pages;$i++) $PAGES[$i]=$uri.'page='.$i;

//and here goes a simple template
?>
Total records: <b><?=$total_rows?></b><br><br>
<? foreach ($DATA as $i => $row): ?>
<?=$i?>. <a href="?id=<?=$row['id']?>"><?=$row['title']?></a><br>
<? endforeach ?> 

<br>
Pages: 
<? foreach ($PAGES as $i => $link): ?>
<? if ($i == $CUR_PAGE): ?>
<b><?=$i?></b>
<? else: ?> 
<a href="<?=$link?>"><?=$i?></a>
<? endif ?> 
<? endforeach ?> 

Answer 5

i got a good pagination example:

  <?php
    /*
        Place code to connect to your DB here.
    */
    include('config.php');  // include your code to connect to DB.

    $tbl_name="";       //your table name
    // How many adjacent pages should be shown on each side?
    $adjacents = 3;

    /* 
       First get total number of rows in data table. 
       If you have a WHERE clause in your query, make sure you mirror it here.
    */
    $query = "SELECT COUNT(*) as num FROM $tbl_name";
    $total_pages = mysql_fetch_array(mysql_query($query));
    $total_pages = $total_pages[num];

    /* Setup vars for query. */
    $targetpage = "filename.php";   //your file name  (the name of this file)
    $limit = 2;                                 //how many items to show per page
    $page = $_GET['page'];
    if($page) 
        $start = ($page - 1) * $limit;          //first item to display on this page
    else
        $start = 0;                             //if no page var is given, set start to 0

    /* Get data. */
    $sql = "SELECT column_name FROM $tbl_name LIMIT $start, $limit";
    $result = mysql_query($sql);

    /* Setup page vars for display. */
    if ($page == 0) $page = 1;                  //if no page var is given, default to 1.
    $prev = $page - 1;                          //previous page is page - 1
    $next = $page + 1;                          //next page is page + 1
    $lastpage = ceil($total_pages/$limit);      //lastpage is = total pages / items per page, rounded up.
    $lpm1 = $lastpage - 1;                      //last page minus 1

    /* 
        Now we apply our rules and draw the pagination object. 
        We're actually saving the code to a variable in case we want to draw it more than once.
    */
    $pagination = "";
    if($lastpage > 1)
    {   
        $pagination .= "<div class=\"pagination\">";
        //previous button
        if ($page > 1) 
            $pagination.= "<a href=\"$targetpage?page=$prev\">&#171; previous</a>";
        else
            $pagination.= "<span class=\"disabled\">&#171; previous</span>";    

        //pages 
        if ($lastpage < 7 + ($adjacents * 2))   //not enough pages to bother breaking it up
        {   
            for ($counter = 1; $counter <= $lastpage; $counter++)
            {
                if ($counter == $page)
                    $pagination.= "<span class=\"current\">$counter</span>";
                else
                    $pagination.= "<a href=\"$targetpage?page=$counter\">$counter</a>";                 
            }
        }
        elseif($lastpage > 5 + ($adjacents * 2))    //enough pages to hide some
        {
            //close to beginning; only hide later pages
            if($page < 1 + ($adjacents * 2))        
            {
                for ($counter = 1; $counter < 4 + ($adjacents * 2); $counter++)
                {
                    if ($counter == $page)
                        $pagination.= "<span class=\"current\">$counter</span>";
                    else
                        $pagination.= "<a href=\"$targetpage?page=$counter\">$counter</a>";                 
                }
                $pagination.= "...";
                $pagination.= "<a href=\"$targetpage?page=$lpm1\">$lpm1</a>";
                $pagination.= "<a href=\"$targetpage?page=$lastpage\">$lastpage</a>";       
            }
            //in middle; hide some front and some back
            elseif($lastpage - ($adjacents * 2) > $page && $page > ($adjacents * 2))
            {
                $pagination.= "<a href=\"$targetpage?page=1\">1</a>";
                $pagination.= "<a href=\"$targetpage?page=2\">2</a>";
                $pagination.= "...";
                for ($counter = $page - $adjacents; $counter <= $page + $adjacents; $counter++)
                {
                    if ($counter == $page)
                        $pagination.= "<span class=\"current\">$counter</span>";
                    else
                        $pagination.= "<a href=\"$targetpage?page=$counter\">$counter</a>";                 
                }
                $pagination.= "...";
                $pagination.= "<a href=\"$targetpage?page=$lpm1\">$lpm1</a>";
                $pagination.= "<a href=\"$targetpage?page=$lastpage\">$lastpage</a>";       
            }
            //close to end; only hide early pages
            else
            {
                $pagination.= "<a href=\"$targetpage?page=1\">1</a>";
                $pagination.= "<a href=\"$targetpage?page=2\">2</a>";
                $pagination.= "...";
                for ($counter = $lastpage - (2 + ($adjacents * 2)); $counter <= $lastpage; $counter++)
                {
                    if ($counter == $page)
                        $pagination.= "<span class=\"current\">$counter</span>";
                    else
                        $pagination.= "<a href=\"$targetpage?page=$counter\">$counter</a>";                 
                }
            }
        }

        //next button
        if ($page < $counter - 1) 
            $pagination.= "<a href=\"$targetpage?page=$next\">next &#187;</a>";
        else
            $pagination.= "<span class=\"disabled\">next &#187;</span>";
        $pagination.= "</div>\n";       
    }
?>

    <?php
        while($row = mysql_fetch_array($result))
        {

        // Your while loop here

        }
    ?>

<?=$pagination?>

//////////////////////////////////////STYLE//////////////////////////////////////

    div.pagination {
    padding: 3px;
    margin: 3px;
}

div.pagination a {
    padding: 2px 5px 2px 5px;
    margin: 2px;
    border: 1px solid #AAAADD;

    text-decoration: none; /* no underline */
    color: #000099;
}
div.pagination a:hover, div.pagination a:active {
    border: 1px solid #000099;

    color: #000;
}
div.pagination span.current {
    padding: 2px 5px 2px 5px;
    margin: 2px;
        border: 1px solid #000099;

        font-weight: bold;
        background-color: #000099;
        color: #FFF;
    }
    div.pagination span.disabled {
        padding: 2px 5px 2px 5px;
        margin: 2px;
        border: 1px solid #EEE;

        color: #DDD;
    }

Answer 6

here's a very good PDF on the subject http://www.percona.com/ppc2009/PPC2009_mysql_pagination.pdf found on http://www.mysqlperformanceblog.com/

Answer 7

Although the guide is in Danish, I believe you can easily understand and use the abstract class from this site: http://www.jensgram.dk/web/e-artikler/1265 (near the bottom).

All you need to do is implement the renderItem() method and you're off:

class PaginatedGuestbook extends Paginator {
    protected function renderItem(&$row) {
        $o = "\Post from " . $row['name'] . "<br />\n"
           . "Header: <b>" . $row['header'] . "</b><br />\n"
           . "Text: " . $row['body'] . "<br />\n";

            // You can do whatever you want with the $row array

            return $o;
        }
    }

$gb = new PaginatedGuestbook(
          'guestbook_table', // Table(s)
          'tstamp, name, header, body', // Fields to populate in renderItem's $row
          'hidden=0 AND deleted=0', // Condition
          'tstamp DESC', // Ordering
          5 // Items per page
      );
print $gb->renderItems();
print "<br />\n\n";
print $gb->renderNavigation('paginated.php');

Furthermore, you can customize links etc.