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
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:
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 -
Follow the below step one by one and create simple pagination.
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.
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.
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.
Now, put all the codes together in a single file to done pagination.
See the below output for above pagination example -
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.
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.
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.
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.
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.
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
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.