Simple php pagination using php and mysql

Simple pagination example using php and mysql

In this tutorial, we are going to discuss how to implement PHP pagination using PHP and MySQL with simple PHP code. In our previous tutorial, we have seen how to add,edit and delete records from database using PHP and MySQL .If we add more records the number of rows increases and they all display at a time in the table list so here comes PHP pagination concept to show the limited records per page. It could be 5,10 or 20 based on your requirement.

Below is the PHP code that has been used for PHP pagination. If you see first three lines of code after common.php is to read a total number of records from MySQL database. Based on the total count we are calculating $pages which will be used for pages in the HTML page. $page is the current pagination page which will be active in the pages. Next and Previous pagination buttons are enabled based on the current page value($page).

For pagination every time we are displaying ($showPages) 5 page numbers which will be increased after centerpage up to the number of total pages.

<?php include("common.php"); $countSQL = " SELECT * FROM tbl_users"; $countResult = $conn->query($countSQL);
	$count = $countResult->num_rows;
	
	$perPage = 1;
	
	$pages = ceil($count/$perPage);
	
	$page = $_GET["p"];
	
	if($page == "")
		$page = 1;
	
	if($page == 1){
		$startPage = 0;
	}else{
		$startPage = ($page-1)*$perPage;
	}
	
	
	$endPage = $perPage;
	
	$selSQL = " SELECT * FROM tbl_users LIMIT $startPage,$endPage ";
	$result = $conn->query($selSQL);
	
	$users = [];
	if($result->num_rows > 0){
		while($row = $result->fetch_assoc()) {
			$users[] = $row;
		}
	}
	$conn->close();
	
	$showpages = 5;
	if($pages>$showpages){
		$centerPage = ceil($showpages/2);
		if($page<=$centerPage){ $pageIndex = 1; }else{ $pageIndex = $page-$centerPage; } if($page>$centerPage){	
			$pageIndex = $pageIndex+1;
			$showpages = $pageIndex+($showpages-1);	
			if($showpages>$pages){
				$pageIndex = $pages - ($centerPage+1);
				$showpages = $pages;
			}			
		}
	}else{
		$pageIndex = 1;
		$showpages = $pages;
	}
	
?>

The HTML:

<html>
<head>
<title>Simple pagination example using php and mysql</title>

<link href="css/styles.css" rel="stylesheet" type="text/css" />

<?php include("../analytics.php"); ?>
</head>
<body>

<div id="container" class="wrapper">
	<?php include("../adbox1.php"); ?>
	
		
<h1>Simple pagination example using php and mysql</h1>

		
<div class="fltLeft add-user">

<a href=""><strong>Back to tutorial</strong></a>
</div>

		<?php if($msg){ ?>
			

"><?php echo $msg;?>

		<?php } ?>
		
<div class="table">
			
			
<div class="row header">
				
<div class="cell">Sno</div>
<div class="cell">	Name</div>
<div class="cell">Occupation</div>
<div class="cell">Location</div>

			</div>

			
			<?php if($users){
				for($i=0;$i<count($users);$i++){?>
				
<div class="row">
					
<div class="cell"><?php echo $i+1;?></div>

 
					
<div class="cell"><?php echo $users[$i]["firstname"]." ".$users[$i]["lastname"];?></div>

	
					
<div class="cell"><?php echo $users[$i]["occupation"];?>	</div>

					
<div class="cell"><?php echo $users[$i]["location"];?></div>

				</div>

				
				<?php } ?>
				
				
				<?php }else{ ?>
			
<div class="row">
		
				
<div class="errorMsg">No records found!</div>

			</div>

				<?php } ?>
		</div>

	

		<?php if($pages){ ?>
		
<div class="pagination">
			
<ul>
				<?php if($pages>=$page && $page!=1){ ?>
				
<li> <a href="index.php?p=<?php echo $page-1;?>">Prev</a></li>

				
				<?php } for($j=$pageIndex;$j<=$showpages;$j++){ ?>
				
					
<li <?php if($j==$page){?>class="active"<?php } ?>><a href="index.php?p=<?php echo $j; ?>"><?php echo $j; ?></a></li>

				<?php } ?>
				
				<?php if($page<$pages){ ?>
				
<li> <a href="index.php?p=<?php echo $page+1;?>">Next</a></li>

				
				<?php } ?>
			</ul>

	
		</div>

		<?php } ?>
	
	
<div class="clr">&nbsp;</div>

	<?php include("../adbox2.php"); ?>
	
<div class="clr">&nbsp;</div>

</div>


</body>
</html>

The CSS:

#container{
	margin:0 auto; width:768px;
	font-family: 'FranchiseRegular','Arial Narrow',Arial,sans-serif;
}
#container h1{
	font-family: 'BebasNeueRegular', 'Arial Narrow', Arial, sans-serif;
    font-size: 35px;
    line-height: 35px;
    position: relative;
    font-weight: 400;
	text-align: center;
}
#container input[type="text"] {
    width: 95%;
    margin-top: 4px;
    padding: 10px 5px 10px 15px;
    border: 1px solid rgb(178, 178, 178);
    -webkit-appearance: textfield;
    -webkit-box-sizing: content-box;
    -moz-box-sizing: content-box;
    box-sizing: content-box;
    -webkit-border-radius: 3px;
    -moz-border-radius: 3px;
    border-radius: 3px;
    -webkit-box-shadow: 0px 1px 4px 0px rgba(168, 168, 168, 0.6) inset;
    -moz-box-shadow: 0px 1px 4px 0px rgba(168, 168, 168, 0.6) inset;
    box-shadow: 0px 1px 4px 0px rgba(168, 168, 168, 0.6) inset;
}
.login-page{ 
	margin:20px auto;
	width:400px;
	border:1px #3d9db3 solid;
	padding:20px;
	border-radius: 15px;	
}
.login-page h2{
	margin-top: 0px;
	padding-bottom:10px;
	text-align:center;
	border-bottom:1px #3d9db3 solid;
}
.login.button{
	text-align:right;
}
.login.buttonLeft{
	text-align:left;
}
.login.button input,.login.buttonLeft input{
	min-width: 75px;
    cursor: pointer;
    background: rgb(61, 157, 179);
    padding: 5px;
    font-family: 'BebasNeueRegular','Arial Narrow',Arial,sans-serif;
    color: #fff;
    font-size: 16px;
    border: 1px solid rgb(28, 108, 122);
    margin-bottom: 10px;
    text-shadow: 0 1px 1px rgba(0, 0, 0, 0.5);
	border-radius:4px;
}
.login-page label{
	color:#405c60;
	position:relative;
	display:inline-block;
}

.errorMsg{color:#FF0000;}
.successMsg{color:#2cad0e}


.wrapper {
    margin: 0 auto;
    padding: 40px;
    max-width: 800px;
}
.table {
    margin: 0 0 10px 0;
    width: 100%;
    box-shadow: 0 1px 3px rgba(0, 0, 0, 0.2);
    display: table;
}

.table .row {
    display: table-row;
    background: #f6f6f6;
}
.table .row .cell{
	padding: 6px 12px;
	display: table-cell;
}
.table .row:nth-of-type(odd) {
    background: #e9e9e9;
}

.table .row.header {
    font-weight: 900;
    color: #ffffff;
    background: #ea6153;
}

.row.header.green {
    background: #27ae60;
}

.row.header.blue {
    background: #2980b9;
}

@media screen and (max-width: 580px){
.table {
    display: block;
}
.table .row {
    padding: 8px 0;
    display: block;
}
.table .row .cell {
    padding: 2px 12px;
    display: block;
}
}

.clr{
	clear:both;
}

.fltRight{
	float:right;
}
.fltLeft{
	float:left;
}

.add-user a{
	text-align:right;
}

.pagination ul {
    list-style-type: none;
	margin-left:0;
	padding-left:0;
}

.pagination ul li {
    display: inline-block;
    padding: 4px 8px;
    background: #5183AC;
    color: #FFF;
}
.pagination ul li.active{
	background:#ea6153;
}

.pagination ul li a{
	color:#FFF;
	text-decoration:none;
}

Below are the links for demo and download, Please feel free to comment for any help in code.

Download / Demo

You may also like...