PHP Pagination

PHP is mostly used to store and display data from a database. Pagination can be done with ajax, but here this is done with non-ajax. In this tutorial, we will learn the pagination in PHP with MySQL. Let's take a brief review about pagination with an example -

It is possible that a SQL SELECT query may returns millions of records back. It is not a good idea to display all records on a single page. A large list of records on a single page may take so much time to load the page and also consume time to find specific data. This may cause (leads to) the confusion in user's mind. Therefore, divide these records over several pages according to the user requirement.

So, what can we do to distribute these large number of records in number of pages? The method of distributing a single list into multiple pages is known as Pagination. Paging refers to showing your query result on multiple pages instead of a single page.

What is Pagination?

Pagination is a way of showing the data on multiple pages rather than putting them to a single page. Pagination helps to divide the records onto several pages, which makes the data more readable and understandable.

Pagination is a common task for PHP developers. MySQL helps the developer to create pagination by using LIMIT clause, which takes two arguments. The first argument as OFFSET and the second argument is number of records that will return from database.

Let's look at some advantages and disadvantages of using pagination concept in PHP -

Advantages of Pagination

• Pagination is very useful in large scale projects because it makes the webwork more professional. Not only more professional, but it also makes the webpage work much faster, precise, and efficient.
• With the help of pagination, we can save the loading time of a page by dividing the data on various pages. It saves us from loading a lot of information at once.
  For Example - A webpage with 1000 images will take more time to load images than the 50 images on each webpage.
  This means that thousands of images need thousands of HTTP requests, which would make the page unresponsive. This problem is resolved by limiting the amount of data with the help of pagination using LIMIT clause.
• The use of pagination improves the user experience, advertising revenue, and decrease the loading time of the page.

Disadvantages of Pagination

While there are some powerful advantages of pagination, but still many developers avoid to use it. Along with some powerful advantages, there are few disadvantages of pagination as well, which are as follows:

• Pagination itself is a big overhead in PHP, which is one of the disadvantages of pagination. It is completely an overhead as it is an external feature that can be implemented to the cost of extraneous Markup, Styling, and logic. A small dataset often ignored to use pagination.
• Pagination may cause of low page rank on search engine because when a page is away from the home page and requires several clicks, it usually does not get a high page rank.
• It also limits the number links, social shares, total number of results to visible on the webpage, and anchor text that a page receives when the information is split over several pages.

You can avoid the use of pagination technique by modifying the structure of your navigation setup.

Implementation of Pagination with PHP and MySQL

In order to implement the pagination, we require a large dataset to apply pagination to it. Therefore, first we need to create a database and table. After that, provide the records in the table and start coding to create pagination. So that the data fetched from the database can be split over several pages.

Here we will introduce two examples of pagination. First example is a simple and basic example of pagination creation with no CSS, whereas in second example, we will create pagination in attractive way using CSS and bootstrap. You can see the output for both. Below are the steps given for pagination creation;

Simple steps to create pagination -

1. Create a database and table. Provide a list of records into the table.
2. Connect with the MySQL database.
3. Create the pagination link to split the data on multiple pages and add them to bottom of the table.
4. Fetch data from the database and display it to the multiple pages.

Follow the below step one by one and create simple pagination.

Example 1

The below code is a simple example of pagination, which is done in PHP with the help of MySQL database. It splits the data fetched from the database on several pages. In this example, we will create the pagination for alphabets to be displayed on several pages.

Database creation

First of all, create a database named by pagination as we created and a table inside it named alphabet. Create attribute by name id and alphabet, and provide the data in the table.

Database Connectivity

Connecting the PHP file to the database is a required task. So that you can display the data stored in database to the webpage. Therefore, connect the database in your PHP file for showing data on webpage.

You can write database connectivity code in the same file as well as also keep it separate into another file and include it to your required PHP file. Code for database connection-

In this tutorial, we are using mysqli extension. So, all the queries are written according to mysqli format.

Get the current page number

The below code determines the page number the user is currently visiting. In case if it is not present, by default it is set page number to 1.

Formula for pagination

For pagination, you need to set the limit of number of records to be displayed per page. Here, we set the limit for result per page to 10, so it will display on each page as given below -

Page1 - A to J (1-10)

Page 2 - K to T (11-20)

Page3 - U to Z (21-26)

Get total number of pages

Retrieve data and display on webpage

The below code is used to retrieve the data from database and display on the webpages that are divided accordingly.

Display the link of the pages in URL

Using this code URL of the webpage will change for each page.

Final Code

Now, put all the codes together in a single file to done pagination.

File: Index2.php

Output:

See the below output for above pagination example -

Example 2

The below example is another example of pagination in which we used CSS along with HTML to make webpage view more attractive. CSS makes the webpage more creative and attractive. On the other hand, MySQL stores the data in database. So, you can learn pagination much better.

We have written whole code in a single file except database connectivity. Therefore, we will create two files, i.e., connection.php and index1.php. Save both the files in .php extension. In the example below, you will learn to create pagination more creative and attractive.

• php: Created for database connectivity
• php: Created for pagination

File: connection.php

File: index1.php

Output:

See the below output for above pagination example -

Note that code written inside the <style> tag is used for styling and to give an attractive look to the table and pagination present inside the webpage. We also used bootstrap for this.

Explanation of code

Now, we will explain the code used for creating pagination.

Database creation

First of all, create a database named by pagination as we created and a table inside it named student. Create attribute by name Rank, Name, College, and Score, and provide at least 25 records in the table.

Database Connectivity

Connecting the PHP file to the database is a required task. In this tutorial, we are using mysqli extension. So, all the queries are written according to mysqli format.

Database connectivity code can be written in the same file or you can also keep it separate into another file and include it to your required PHP file. Code for database connection-

Fetch data and display on webpage

As we have created dataset, now we need to fetch and display it to various webpages. The below code is used to retrieve the data from database and display on the webpages that are divided accordingly.

Fetch data

After establishing the database connection in "connection.php" file, we just need to import it into our code using require_once keyword. We will explicitly define the number of records per page to show.

Display data

This section is very simple. In this section, we iterate the loop over the records that we fetched and display each record stored in columns of the table.

Pagination Link creation

Now the most important code is pagination link creation. So, we will create the Previous, Next, and numeric links for pagination and add them to bottom of the table.

Without using CSS, the pagination will be created same as the screenshot below, which is similar to the example 1.

After including CSS code into the index1.php file, the pagination will be looked like the screenshot below. In example 1, we have created simple pagination that was logically correct, but visually it is not so good.

Code for Random Moment

In case when the number of pages is too much, this code helps us for random moment. By entering the page number in the input field, a user can directly move to that page. This code is written in JavaScript.

Apart from all these codes, we have also included bootstrap into index1.php to make the table view good. This code is written inside the </link> tag.