php Pagination with mysql

To start with you’ll see that we have our database connection file which we’re including into the page. Next you’ll see we’re setting three variables. ‘$tableName’, this refers to the table in your database that you want to get the data from, in our case it’s ‘countries_list’, ‘$targetpage’ refers to whatever you’ve saved the file as, in the case of the demo we’re saving the file as ‘index.php’. ‘$limit’ is the number of rows per page that we want to display from the database in the case of the demo you’ll see that we’re displaying just 10 rows.

Here are the main bullet points of what this file does in the simplest form.

Set the three variables at the top of the page declaring our database table, document filename, and number of results to display per page
Next we connect to the database and count the number of rows, so we know exactly how much data we’re dealing with
As the current page number is passed as a GET variable we check to see if a page number has been passed if there isn’t one then we set it to the first page
Then we have the query for the page content
Now we work out the next and previous page number as well as the last page number
Now we have a set of if statements, to determine which page the user is currently on relative to the total pages, so we can work out whether we need to display the first two page numbers with dots etc
Finally we echo ‘$paginate’ which will display our page numbers
Then we have our while loop which you can modify to suit your contents
<?php
include('connect.php');
$tableName="countries_list";
$targetpage = "index.php";
$limit = 10;
$query = "SELECT COUNT(*) as num FROM $tableName";
$total_pages = mysql_fetch_array(mysql_query($query));
$total_pages = $total_pages[num];
$stages = 3;
$page = mysql_escape_string($_GET['page']);
if($page){
$start = ($page - 1) * $limit;
}else{
$start = 0;
}
// Get page data
$query1 = "SELECT * FROM $tableName LIMIT $start, $limit";
$result = mysql_query($query1);
// Initial page num setup
if ($page == 0){$page = 1;}
$prev = $page - 1;
$next = $page + 1;
$lastpage = ceil($total_pages/$limit);
$LastPagem1 = $lastpage - 1;
$paginate = '';
if($lastpage /> 1)
{
$paginate .= "<div class='paginate'/>";
// Previous
if ($page /> 1){
$paginate.= "<a href='$targetpage?page=$prev'/>previous</a/>";
}else{
$paginate.= "previous"; }
// Pages
if ($lastpage < 7 + ($stages * 2)) // Not enough pages to breaking it up
{
for ($counter = 1; $counter <= $lastpage; $counter++)
{
if ($counter == $page){
$paginate.= "$counter";
}else{
$paginate.= "<a href='$targetpage?page=$counter'/>$counter</a/>";}
}
}
elseif($lastpage /> 5 + ($stages * 2)) // Enough pages to hide a few?
{
// Beginning only hide later pages
if($page < 1 + ($stages * 2))
{
for ($counter = 1; $counter < 4 + ($stages * 2); $counter++)
{
if ($counter == $page){
$paginate.= "$counter";
}else{
$paginate.= "<a href='$targetpage?page=$counter'/>$counter</a/>";}
}
$paginate.= "...";
$paginate.= "<a href='$targetpage?page=$LastPagem1'/>$LastPagem1</a/>";
$paginate.= "<a href='$targetpage?page=$lastpage'/>$lastpage</a/>";
}
// Middle hide some front and some back
elseif($lastpage - ($stages * 2) /> $page /amp;/amp; $page /> ($stages * 2))
{
$paginate.= "<a href='$targetpage?page=1'/>1</a/>";
$paginate.= "<a href='$targetpage?page=2'/>2</a/>";
$paginate.= "...";
for ($counter = $page - $stages; $counter <= $page + $stages; $counter++)
{
if ($counter == $page){
$paginate.= "$counter";
}else{
$paginate.= "<a href='$targetpage?page=$counter'/>$counter</a/>";}
}
$paginate.= "...";
$paginate.= "<a href='$targetpage?page=$LastPagem1'/>$LastPagem1</a/>";
$paginate.= "<a href='$targetpage?page=$lastpage'/>$lastpage</a/>";
}
// End only hide early pages
else
{
$paginate.= "<a href='$targetpage?page=1'/>1</a/>";
$paginate.= "<a href='$targetpage?page=2'/>2</a/>";
$paginate.= "...";
for ($counter = $lastpage - (2 + ($stages * 2)); $counter <= $lastpage; $counter++)
{
if ($counter == $page){
$paginate.= "$counter";
}else{
$paginate.= "<a href='$targetpage?page=$counter'/>$counter</a/>";}
}
}
}
// Next
if ($page < $counter - 1){
$paginate.= "<a href='$targetpage?page=$next'/>next</a/>";
}else{
$paginate.= "next";
}
$paginate.= "</div/>";
}
echo $total_pages.' Results';
// pagination
echo $paginate;
?/>
<ul/>
<?php
while($row = mysql_fetch_array($result))
{
echo '<li/>'.$row['country'].'</li/>';
}
?>

Here’s the css to style you pagination

.paginate {

font-family:Arial, Helvetica, sans-serif;

padding: 3px;

margin: 3px;

}

.paginate a {

padding:2px 5px 2px 5px;

margin:2px;

border:1px solid #999;

text-decoration:none;

color: #666;

}

.paginate a:hover, .paginate a:active {

border: 1px solid #999;

color: #000;

}

.paginate span.current {

margin: 2px;

padding: 2px 5px 2px 5px;

border: 1px solid #999;

font-weight: bold;

background-color: #999;

color: #FFF;

}

.paginate span.disabled {

padding:2px 5px 2px 5px;

margin:2px;

border:1px solid #eee;

color:#DDD;

}

li{

padding:4px;

margin-bottom:3px;

background-color:#FCC;

list-style:none;}

ul{margin:6px;

padding:0px;}

Download Lonk

php Pagination with mysql

0 comments:

Post a Comment

php Pagination with mysql

Next

Newer Post

Previous

Older Post

0 comments:

Post a Comment