• Skip to main content
  • Skip to primary sidebar

Technical Notes Of
Ehi Kioya

Technical Notes Of Ehi Kioya

  • Forums
  • About
  • Contact
MENUMENU
  • Blog Home
  • AWS, Azure, Cloud
  • Backend (Server-Side)
  • Frontend (Client-Side)
  • SharePoint
  • Tools & Resources
    • CM/IN Ruler
    • URL Decoder
    • Text Hasher
    • Word Count
    • IP Lookup
  • Linux & Servers
  • Zero Code Tech
  • WordPress
  • Musings
  • More
    Categories
    • Cloud
    • Server-Side
    • Front-End
    • SharePoint
    • Tools
    • Linux
    • Zero Code
    • WordPress
    • Musings
Home » Backend (Server-Side) » Implementing Pagination Using PHP And MySQL

Implementing Pagination Using PHP And MySQL

April 4, 2020 by Chinomnso 3 Comments

The SELECT statement in SQL is a simple but powerful one. A single SELECT query can return thousands of results. When building blogs and websites, you usually don’t want to display thousands of results on a single page. This would impact negatively on page load time and create a bad user experience.

To solve the problem of displaying many database records on a website, the standard best practice is to split the results across multiple web pages. This is called pagination. And this article will explain how to do this using PHP and MySQL.

For the sake of beginners, we will be using procedural code in our examples. You can make a class out of the snippets you find here if you write object-oriented PHP.

How Pagination Works

  • First, using an SQL query, the total number of records to be displayed is calculated.
  • Next, using MySQL’s LIMIT clause, starting and ending points are defined in the SQL query.

Let’s write the code for each of these points and explain the blocks of code.

Our first piece of code will use a pagenum URL parameter. This will help us ascertain what page the user is on. So, our URL will be something like http://example.com/people.php?pagenum=2

The pagenum parameter has to be passed in the URL to let the PHP script know what page number is being processed. The following snippet illustrates how the page number works in the script.

if (isset($_GET['pagenum'])) {
	$pagenum = $_GET['pagenum'];
} else {
	$pagenum = 1;
}

First, we check to verify if the pagenum parameter is passed to the URL. If it is present in the URL, a variable ($pagenum) is created to store the value of the parameter.

If the parameter has not been passed, the variable is still created, but its value is set to 1. This way, if the URL doesn’t have the parameter you checked for in the script, the query returns the first set of records in your database table.

The next step is to create another variable to store the total number of records to be displayed per page. This will serve as the “end” value for the LIMIT clause in the MySQL SELECT query. We will also create another variable to hold the “start” value of the LIMIT clause for the query. Here’s the snippet:

$recordsPerPage = 20;
$start = ($pagenum-1) * $recordsPerPage;

To get the total number of pages, we first get the total number of records we want to select, and then we divide that figure by the number of records per page.

For this to work properly, we will use the PHP ceil() function. In the event that the result of our above division is a float, our ceil() function will help round the result of the division up to the nearest higher integer.

Here’s how it is done:

$getTotalRecords = "SELECT COUNT(*) FROM tableName";
$result = mysqli_query($conn, $getTotalRecords);
$totalRecords = mysqli_fetch_array($result)[0];
$totalPages = ceil($totalRecords / $recordsPerPage);

Our SQL query for the pagination would look like:

SELECT * FROM tableName LIMIT $start, $totalPages;

Now, the core functionality has been set up. We need to set up the buttons and make sure they work properly. The buttons we will use are FIRST, PREVIOUS, NEXT and LAST. To simplify things and help with CSS styling, we will be using Bootstrap which has a built-in class for pagination. The Bootstrap stuff isn’t shown here.

<ul class="pagination">
	<li>
		<a href="?pagenum=1">First</a>
	</li>
	<li class="<?php if($pagenum <= 1){ echo 'disabled'; } ?>">
		<a href="<?php if($pagenum <= 1){ echo '#'; } else { echo "?pageno=".($pagenum - 1); } ?>">Prev</a>
	</li>
	<li class="<?php if($pagenum >= $totalPages){ echo 'disabled'; } ?>">
		<a href="<?php if($pagenum >= $totalPages){ echo '#'; } else { echo "?pagenum=".($pagenum + 1); } ?>">Next</a>
	</li>
	<li>
		<a href="?pagenum=<?php echo $totalPages; ?>">Last</a>
	</li>
</ul>

In the snippet above, the button labelled FIRST links to the first page of results.

For the button labelled PREV, there is some logic. If the current page is the first page, the CSS class is set to “disabled” using PHP. That way, a user cannot click it.

If the page is the first, there is no result to be displayed if the button is clicked. So, the link is disabled by assigning “#” as the value of the HREF attribute. If the user is NOT on the first page, the pagenum attribute is decremented by one and passed to the URL.

For the NEXT button, we reverse the logic applied to the PREV button. And for the LAST button, we place the variable holding the value of the total number of pages as the value of the pagenum parameter to be passed to the URL.

The whole script is dumped below, including basic markup for the page. You can copy and paste all the code below and tweak a few things. The comments in the code indicate what to tweak.

PHP Pagination Code:

<?php
	if (isset($_GET['pagenum'])) {
		$pagenum = $_GET['pagenum'];
	} else {
		$pagenum = 1;
	}
	$recordsPerPage = 10;
	$start = ($pagenum - 1) * $recordsPerPage;
	$conn = mysqli_connect("host", "user", "pass", "dbname"); //adjust the connection parameters to work with your dev environment.
	
	// Check connection
	if (mysqli_connect_errno()) {
		echo "Failed to connect to MySQL: " . mysqli_connect_error();
		die();
	}
	$fetchPages = "SELECT COUNT(*) FROM tableName"; //change "tableName" to your table name.
	$result = mysqli_query($conn, $fetchPages);
	$totalRecords = mysqli_fetch_array($result)[0];
	$totalPages = ceil($totalRecords / $recordsPerPage);
	$sql = "SELECT * FROM state LIMIT $start, $recordsPerPage";
	$res_data = mysqli_query($conn, $sql);
	while ($row = mysqli_fetch_array($res_data)) {
		echo "<li>" . $row['name'] . "</li>"; //change 'name' to the appropriate offset from your database table.
	}
	mysqli_close($conn);
?>

HTML Pagination Code:

<ul class="pagination">
	<li>
		<a href="?pagenum=1">First</a>
	</li>
	<li class="<?php if ($pagenum <= 1) { echo 'disabled'; } ?>">
		<a href="<?php if ($pagenum <= 1) { echo '#'; } else { echo "?pagenum=" . ($pagenum - 1); } ?>">Prev</a>
	</li>
	<li class="<?php if ($pagenum >= $totalPages) { echo 'disabled'; } ?>">
		<a href="<?php if ($pagenum >= $totalPages) { echo '#'; } else { echo "?pagenum=" . ($pagenum + 1); } ?>">Next</a>
	</li>
	<li>
		<a href="?pagenum=<?php echo $totalPages; ?>">Last</a>
	</li>
</ul>

You can make changes to the code above and extend it as far as your imagination and skills can take you.

Found this article valuable? Want to show your appreciation? Here are some options:

  1. Spread the word! Use these buttons to share this link on your favorite social media sites.
  2. Help me share this on . . .

    • Facebook
    • Twitter
    • LinkedIn
    • Reddit
    • Tumblr
    • Pinterest
    • Pocket
    • Telegram
    • WhatsApp
    • Skype
  3. Sign up to join my audience and receive email notifications when I publish new content.
  4. Contribute by adding a comment using the comments section below.
  5. Follow me on Twitter, LinkedIn, and Facebook.

Related

Filed Under: Backend (Server-Side), Database, PHP, Programming, Web Development Tagged With: MySQL, Pagination, PHP, Web Development

Reader Interactions

Comments

  1. Ehi Kioya says

    April 4, 2020 at 6:50 pm

    Great stuff Chinomnso!

    For anyone testing this out and wondering why it may not be nicely styled for them, I just want to point out that I removed the html, head, and body wrappers from the code in Chinomnso’s original submission. This is for security reasons – certain types of html tags could inadvertently open up security holes and cause a bunch of issues.

    The head section in Chinomnso’s original code contained a link reference to bootstrap CSS which should make the styling look nice. There were also a couple of script tags in the head section of the original code with JavaScript references (one of the script references was for jQuery if I remember correctly).

    I don’t think jQuery is used anywhere in the code though. So it should probably be fine to leave that out.

    Chinomnso, it would be nice if you could share a screenshot of how the final output of the code looks like in real life – with bootstrap styles applied and everything.

    Thanks.

    Reply
    • Chinomnso says

      April 4, 2020 at 6:57 pm

      Here’s what the output looks like:

      PHP Pagination (Styled With Bootstrap CSS)

      Maybe I’ll write another topic soon on how to implement numbered pagination.

      Reply
      • Ehi Kioya says

        April 4, 2020 at 6:58 pm

        The results looks cool. Thanks.

        And yeah, a topic about numbered pagination sounds like a good idea. When you write it, do link back to this one so that it will be like some sort of follow-up or series.

        Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Primary Sidebar

25,496
Followers
Follow
30,000
Connections
Connect
14,568
Page Fans
Like

POPULAR   FORUM   TOPICS

  • How to find the title of a song without knowing the lyrics
  • The Art of Exploratory Data Analysis (Part 1)
  • Welcome Message
  • How To Change Or Remove The WordPress Login Error Message
  • Getting Started with SQL: A Beginners Guide to Databases
  • Replacing The Default SQLite Database With PostgreSQL In Django
  • How to Implement Local SEO On Your Business Website And Drive Traffic
  • What Is Arduino, And Why Should You Use It?
  • How To View And Manage Saved Passwords In Google Chrome
  • Tips to Secure Your Windows 10 PC Against Hackers and Viruses
  • Recently   Popular   Posts   &   Pages
  • Actual Size Online Ruler Actual Size Online Ruler
    I created this page to measure your screen resolution and produce an online ruler of actual size. It's powered with JavaScript and HTML5.
  • Allowing Multiple RDP Sessions In Windows 10 Using The RDP Wrapper Library Allowing Multiple RDP Sessions In Windows 10 Using The RDP Wrapper Library
    This article explains how to bypass the single user remote desktop connection restriction on Windows 10 by using the RDP wrapper library.
  • WordPress Password Hash Generator WordPress Password Hash Generator
    With this WordPress Password Hash Generator, you can convert a password to its hash, and then set a new password directly in the database.
  • Forums
  • About
  • Contact

© 2021   ·   Ehi Kioya   ·   All Rights Reserved
Privacy Policy