How to create a simple php and mysql pagination

Live example

I have prepared a live demo, in which i fetch some products, (actually the products in the database table are 20), and display four of them in each page. You can set any number you want in the php code.

In the pagination buttons, we have the pages displayed in numbers. We have a first page and a last page button. And previous and next buttons.
Also we are showing in text in which page we are on.

Try out the demo to have a better understanding on what we are going to code.

Now that you have played around with the demo, let's start the tutorial from the project's folder.

Project's folder

Le'ts see the files that we need to code the above application.

• We need an index.php file, which is our main screen.
• We need a script.php file to query the database and fetch the results.
• And also we need a styles.css stylesheet, to make the application look pretty.
  I will not go through the css file, i will mention only the important stuff that we need for this tutorial.
  If you download the source code the css file is included.

Now that we have seen what files we need, let's code.

The products database table

Everything starts from the database table.
In the mysql database i have a table called products. The table has an id, and a product_name column, and the total number of rows are 20. It's a simple table with two columns created that way for the sake of simplicity for this tutorial.

Now that we have seen the structure of the database table, let's start writing our script.php file.

The php code

The first thing that we will do in the php file is to connect to the mysql server and the database.

<?php 
	// connecting to the database.
	$mysqli = new mysqli('localhost', 'mysql-username', 'mysql-password', 'database-name');
	if($mysqli->connect_errno != 0){
		echo $mysqli->connect_error;
		exit();
	}else{
		$mysqli->set_charset("utf8mb4");	
	}

• In line 3 we create a variable named $mysqli, and we assign a new mysqli object to it.
  We pass-in the mysqli object our database connection details.
• In line 4, we check if the database connection is successful by checking the connect error number property (connect_errno), and if it is not zero (!=0), we echo out the mysqli connection error in line 5, and we exit the script in line 6.
  Else if we have a successful connection, we set the returned character encoding to "utf8mb4", in line 8. This is useful if we have Greek or Cyrillic, in the database.

Next we are going to set some basic variables that we will need for the code to work.

	// get the total nr of rows.
	$records = $mysqli->query("select * from products");
	$nr_of_rows = $records->num_rows;
	
	// Setting the number of rows to display in a page.
	$rows_per_page = 4;

	// calculating the nr of pages.
	$pages = ceil($nr_of_rows / $rows_per_page);

	// Setting the start from, value.
	$start = 0;

• We are going to get the total number of products (rows), so in line 12 we selecting everything from the products table, and in line 13 we use the num_rows property to give us the number of rows, and store them in the $nr_of_rows variable.
• In line 16, we set how many rows we want to display in each page. In this case we want 4 results in every page. You can use any number you feel that fits your application.
• In line 19 we calculate the number of pages needed to display the whole content. So we divide the number of total rows, by the rows that we want to show per page.
  Example: if we have 20 rows in the database and we divide them by 4, we will need 5 pages to display the whole content.
  By using the ceil() function we round up the result to the nearest integer. This way, if the last page contains less than 4 results, we can still display them. You can try this out by setting the $rows_per_page variable to 3.
• And in line 22 we set in the $start variable the value from which our select query starts, which is zero. This variable will change every time we click on a different pagination button.
  We are going to use the $start, and the $row_per_page variable in our select query in the next code-block. You will see.

In this code-block we are going to change the value of the $start variable, according to the incoming page number. Let me explain. Every time we click on a pagination button we will send a GET request with the number of the page that we want to show. You will see this in the index.php file when we write the html code.

	// If the user clicks on the pagination buttons.
	if(isset($_GET['page-nr'])){
		$page = $_GET['page-nr'] - 1;
		$start = $page * $rows_per_page;
	}

	// Query the database based on the calculated $start value, 
	// and the fixed $rows_per_page value.
	$result = $mysqli->query("SELECT * FROM products LIMIT $start, $rows_per_page");

• In line 25 we are checking if there is an incoming GET['page-nr'] request. The 'page-nr' key is holding the number of the page we want to display.
• Next in line 26 we subtract from the incoming page number, - 1, and store the result in the $page variable. We are doing this because we initiated the $start variable to zero, ($start = 0).
  If we don't start from zero, and set the start variable to 1 ($start = 1) we will miss the first row from our table. So we have to subtract -1 from the incoming "page-nr" to balance that out. Hope it makes sense.
  Play around with the start value to see how the pagination behaves.
• In line 27 we set the new starting point by multiplying the $page number by the $rows_per_page. This will give our query, (in line 32), the new starting point.
• And in line 32 we perform the database query, where we selecting from the products table, the rows that we want, using the LIMIT keyword which allow us to set the starting row and the offset value. The $start variable sets the starting row, and the $row_per_page variable sets the offset.
  
• That is all the php code that we need. Now let's move to the index file to structure our html.

The index file

We are going to start with a simple html structure, but we have to include the script.php file at the top of the page so we can have access to the php code.

<?php include "script.php" ?>
<!DOCTYPE html>
<html lang="en">
<head>
	<meta charset="UTF-8">
	<meta name="viewport" content="width=device-width, initial-scale=1.0">
	 <link rel="stylesheet" href="styles.css">
	<title>php and mysql pagination</title>
</head>
<body>

</body>
</html>	

Display the database results

Next we are going inside the body tags and display the results that the select query sends from the php file in line 32. (line 32 in the script.php file).

<body>
	<div class="content">
		<?php 
			while($row = $result->fetch_assoc()){
				?>
					<p><?php echo $row['id'] .' - '. $row["product_name"] ?></p>
				<?php
			}
		?>
	</div>

We will loop through the $result variable and fetch the data as an associative array ($result->fetch_assoc()), and display the product id and the product_name in a paragraph element in line 15.
The $result variable is holding the mysqli result-set from our select query that we have in the php file.

Display the page info

Next we are going to display in text, the page that we are on, ie.(Showing 1 of 5)

	
	<div class="page-info">
		<?php 
			if(!isset($_GET['page-nr'])){
				$page = 1;
			}else{
				$page = $_GET['page-nr'];
			}
		?>
		Showing  <?php echo $page ?> of <?php echo $pages; ?> pages
	</div>

• The logic here is that, if we don't have a GET request, in line 24, we set the $page variable to 1, because we just landed on the page. Else if we click on a pagination button, (you will see that in the next code-block), we assign to the $page variable the page number that the GET request sends. Line 27.
• And in line 30, we display the $page number and the total $pages number.
  We have defined the $pages variable in the php file in line 19, and it holds the total number of pages.

Display the go to first page button

Next we are going to create a div element which will contain all our pagination buttons.

	<div class="pagination">

	</div>	

Next inside our pagination element we are going to create our first pagination button, which is the "go to the first page" button. Although we have links i will refer to them as buttons because we style them in the css file to look like buttons.

		<!-- Go to the first page -->
		<a href="?page-nr=1">First</a>

The logic here is simple. We send a GET request to the same page, (that's why we included the script.php file), and set the "page-nr" key to 1. This will display our first set of rows, (products).

Display the previous page button

Next we are going to create the "Go to the previous page" button.

		<!-- Go to the previous page -->
		<?php 
			if(isset($_GET['page-nr']) && $_GET['page-nr'] > 1){
				?> <a href="?page-nr=<?php echo $_GET['page-nr'] - 1 ?>">Previous</a> <?php
			}else{
				?> <a>Previous</a>	<?php
			}
		?>

We are going to check in line 39, if there is a GET request, and if the "page-nr" is greater than 1.

If this is the case, we subtract -1 from the current page that we are on, ($_GET['page-nr'] - 1), and send the GET request with the new value, in line 40. This way will go to the previous page.

Else we display the previous button without the href attribute, to disable the button in line 42.

This will happening if we are on the first page. There is no logic to go to the previous page if we are on the first page. There is no previous page.

Display the page number buttons

Next we are going to display every individual page by their number. I will put every page button inside a div element to group them together. This is only for css purposes.

		<!-- Output the page numbers -->
		<div class="page-numbers">
			<?php 
				if(!isset($_GET['page-nr'])){
					?> <a class="active" href="?page-nr=1">1</a> <?php
					$count_from = 2;
				}else{
					$count_from = 1;
				}
			?>
			
			<?php
				for ($num = $count_from; $num <= $pages; $num++) {
					if($num == @$_GET['page-nr']) {
						?> <a class="active" href="?page-nr=<?php echo $num ?>"><?php echo $num ?></a> <?php
					}else{
						?> <a href="?page-nr=<?php echo $num ?>"><?php echo $num ?></a> <?php
					}
				}
			?>
		</div>

• First of all, i have in the css file an active class which will change the background, and the text color of the page that we are currently on.

  a.active{
  	background-color: #0d81cd;
  	color: #fff;
  }
  		

• Let's break down the first php block. Which purpose is to create a button and set the active class to the first page number when the page first loads.

  	<?php 
  		if(!isset($_GET['page-nr'])){
  			?> <a class="active" href="?page-nr=1">1</a> <?php
  			$count_from = 2;
  		}else{
  			$count_from = 1;
  		}
  	?>
  		

  • In line 49, we check if there is NOT (!) a GET request. If this is the case the user has just landed on the page, so we are going to create a button with the active class to highlight it.

    And we will create a variable named $count_from and set its value to 2. We are gonna use the $count_from variable as our starting point in the for loop that we have further down.

    We are doing this because i want to skip the first page button in the for loop so we don't display it twice.

  • But if there is a GET request i will set the $count_from variable to 1, as we normal would.
    You will see this in the next php block where we have the for loop.

• Next we have the for loop to display the page buttons.

  	<?php
  		for ($num = $count_from; $num <= $pages; $num++) {
  			if($num == @$_GET['page-nr']) {
  				?> <a class="active" href="?page-nr=<?php echo $num ?>"><?php echo $num ?></a> <?php
  			}else{
  				?> <a href="?page-nr=<?php echo $num ?>"><?php echo $num ?></a> <?php
  			}
  		}
  	?>
  		

  • In line 58 we have the for loop, and as you can see we use the $count_from variable in the first argument. As the second argument we have the number of $pages, remember that we have defined the $pages variable in the php file in line 19.
  • Inside the for loop, in line 59, we have an if statement and we check if the $num variable is equal to the GET request's "page-nr". If this is the case we display the link, (button), with the active class to highlight the current page number. And in the href attribute we send a GET request with the current $num value, which corresponds to the current page. Hope it makes sense.
  • In the else clause we display the same link (button), but without the active class.
    In this way we achieve the current page's highlight.

  • If we didn't highlighted the current page, we needed only three lines of code to display the page numbers.
    But we would have a poor user experience.

    	<!-- Output the page numbers -->
    	<div class="page-numbers">
    		<?php
    			for ($num = 1; $num <= $pages; $num++) {
    				?> <a href="?page-nr=<?php echo $num ?>"><?php echo $num ?></a> <?php
    			}
    		?>
    	</div>	
    				

Display the next page button

Next and outside the "page-numbers" div element we are going to create the "Go to the next page" button.

	<!-- Go to the next page -->
	<?php 
		if(isset($_GET['page-nr'])){
			if($_GET['page-nr'] >= $pages){
				?> <a>Next</a> <?php
			}else{
				?> <a href="?page-nr=<?php echo $_GET['page-nr'] + 1 ?>">Next</a> <?php
			}
		}else{
			?> <a href="?page-nr=2">Next</a> <?php
		}
	?>

The logic here is as follows:

• In line 70 we check if there is a GET request. If not that means that we are on the first page so in line 77 in the else clause, we display a link (button) that sends us to the second page (page-nr=2).
• If there is a GET request we have another if statement in line 71, that checks if the current "page-nr" is greater, or equal to the total number of pages (>= $pages).
  If this is the case then we are on the last page and there is no page left to go. So we display the Next button without the href attribute to disable it in line 72.

• Else if the current "page-nr" is less than the total pages number, then in line 74, we display the next link (button), and in the query string we increment by 1 the current "page-nr" ($_GET['page-nr'] + 1) . This will send us to the next page.

  Guys i hope my code explanation is understandable, please take the time and leave some feedback.

Display the last page button

In the last page button we simply say in the query string to go to the last page, (page-nr=<?php echo $pages ?>).

	<!-- Go to the last page -->
	<a href="?page-nr=<?php echo $pages ?>">Last</a>

The CSS code

And last we have the css code.

*{
	margin: 0;
	padding: 0;
	box-sizing: border-box;
	font-size: 20px;
}

body{
	font-family: sans-serif;
	min-height: 100vh;
	color: #555;
	padding: 30px;
}

a{
	display: inline-block;
	text-decoration: none;
	color: #006cb3;
	padding: 10px 20px;
	border: thin solid #d4d4d4;
	transition: all 0.3s;
	font-size: 18px;
}

a.active{
	background-color: #0d81cd;
	color: #fff;
}
.page-info{
	margin-top: 90px;
	font-size: 18px;
	font-weight: bold;
}

.pagination{
	margin-top: 20px;
}
.content p{
	margin-bottom: 15px;
}
.page-numbers{
	display: inline-block;
}

Summary

And that's it, we completed exactly the demo application that you saw at the top of the page. I hope i manage to explain the whole code in a understandable way. I will much appreciate any feedback even if you didn't liked it. or not understand it.

Source code

If you feel like avoiding all the copying and pasting, you can buy me a coffee and get the source code in a zip file. Get the source code

Buy me a coffee