PHP Pagination | Set 2

In the previous article on PHP Pagination, we had seen why pagination is needed and also developed a basic structure for implementing a pagination system using PHP. In continuation, let us start with our development process and first create a script to connect to our Database.

Connecting to Database

Connecting to Database is often a required task for showing data in any PHP file, thus writing the same code repeatedly is not the best approach so following the convention we will create a PHP file to be known as “connection.php”, and will write the following code.

In this article, we will be using mysql extension, in case you want to work your way with the mysqli extension please refer the following article. The above code snippet just defines the credentials such as Host, Username, Password and Database name as PHP constants and uses them to establish the connection. Now we have to fetch the data and distribute it in pages.

Data Fetch and Representation

So far, we have created a Demo Markup, we have created the Database table to hold fake data and we have also connected to the database. Now all that is left is to fetch the rows of information and show it on various pages. Now, what could be the general logic behind it. Pagination generally limits the number of entries in a page and also maintains the sequence of all the entries throughout the pages. So, if we could have a variable that would denote us the Max number of entries that can be shown on a page and given a page number we could find exactly which entries are to be shown. For example, if the current page number is 5 and the limit is 10 then we can see manually that the first page will contain 1-10, the second page will contain 11-20, and similarly, the fifth page will contain 41-50 entries.
So we could write that if the page number is denoted using $pn and limit is denoted using $limit then the starting index of the entry can be written as: (($pn-1)*$limit)+1. As we have calculated the relation we just now need to implement the following in our “index.php” file. The following is what we should finally get.

As you can see we have added three sections (highlighted parts) in the previously written code to fetch and show the data as well as links to next pages. Let us go through all these sections separately to get an in-depth understanding of the code.

Fetching from Database

After we have created the connection to the Database in “connection.php” we will just import the file in with the require_once keyword. Next, we are explicitly defining the maximum number of records to be shown in a single page. Subsequently, we are checking if there is any “page” attribute in the URL i.e. we are looking for the “page” attribute in “GET” method, which if found is set to be the page number to be shown, otherwise it’s the first page or the value is 1. Following this we are calculating the index to start our fetch, it is calculated using the formulae we derived before but as SQL uses 0-indexing we are not adding the additional 1. Lastly, we are just fetching every field of the records starting from index $start_from and limiting up to $limit records and storing in the $rs_result variable.

Showing the Records

This section is fairly simple, we are just iterating over the records we fetched and fetching each row as an Associative array, we are echoing each field as table data(td tags) in each table rows(tr tags). After every iteration we are creating a table row consisting of four columns namely Rank, Name, College and Score.

Showing the Page Navigation

This is the final section where we need to develop the navigation links to help the user go to different pages. To do the same we first need to know in how many pages is our content distributed. In above code, we r first fetching the count of total records which in this example is 1500. Nextly we are calculating the ceiling of the division of total records by the limit which gives us the number of pages required. Finally, we are simply iterating for the same number of times as the total page numbers each time creating a link having the page number in its URL’s “page” attribute and if the current page link is to be added we just add a class=”active” denoting the page is active. The figure below is our final result.

We have created a simple Pagination system using PHP in a procedural way, but now the problem is we have a total of 150 pages and in spite of being functionally correct it really looks weird to have all those pages and that’s why in the next article we will look into adding more functionalities and give a cosmetic makeup to our pagination system.