Pagination & Search in PHP

Introduction

One of the most common things that we do on any application in php is to display records from database. When these records are large we show them in pages and also add a numbering at the bottom to add navigation.

Now along with navigation we might also need search. Building both of these from scratch can be a complex and time consuming process and to simplify these I wrote a small library that helps me implement pagination and Search in the easiest possible way.

We will use Brave Framework which is part of a Open Source framework that I created to avoid hassles of frameworks which sometimes slowers the performance and add undue complexity in our project.

Installation of required Libraries.

We would need to include a couple of library files from our github account. These are very simple libraries and can be used along any PHP framework or just plain simple PHP. These are well documented and quite easy to understand once you see the code.

The link to the Brave Framework is as follows.
https://github.com/shishirraven/Brave-CMS-Library

If you do not want the complete framework. Please download just the following files.

pagination.php
pagination_extended.php
pagination_with_search.php

Once you have downloaded these files you can keep these files inside a library folder. But if you want to keep it elsewhere it is completely ok. Just remember to update the paths once you include them in your project.

Building SQL queries

Since the records that we want to display are going to come from a MySQL Database. It’s important that we figure out the query through which we are going to get results. Some examples of select queries are as below.

select * from users
select * from photo_albums where user_id=7

Depending upon your situation please write your own sql query that will return the results that you want. Also I recommend that you test the query on your sql client to ensure everything is ok before we proceed any further.

Including the Libraries into your PHP page

One the query is ready. We will begin including the dependent libraries into our php file. It’s very simple and involves just including the libraries into our page.

See the example below.

<?php 
include("connection.php"); 
include("library/pagination_with_search.php");
?>

Here you see that we have included two files. 1st is connection.php and the 2nd is pagination_with_search.php

Setting up Connection File

If you are wondering about what the connection file should look like, I am pasting it below. Please feel free to make adjustments according to your code and you might also use your own connection. But for the sake of example I am pasting what I usually use.

<?php 
// connection.php

$con= mysqli_connect("localhost","root","db_password","your_database");
// Check connection
if (mysqli_connect_errno())
{
    echo "Failed to connect to MySQL: " . mysqli_connect_error();
}
?>

Well, you will find the connection quite easy to understand. If you are using this please remember to replace your mysql database credentials.

Setup Pagination Configuration

Well once you have the the connection file in place and have included the library on your page. The next thing is we will setup the config array. Config array simply tell the parameters for our pagination. Like it tells how many records should be displayed per page and what is the sql query that will get results from the database.

The following is and example of our Config array.

<?php 
include("connection.php"); 
include("library/pagination_with_search.php");
include("library/notifications.php");
include("authenticate.php");

$config = array();
$config['query'] = "select * from users";
$config['enable_field_search'] = "true";
$config['con'] = $con;
$config['rows_per_page'] = 20;
$rec_page = new pagination_with_search($config);
$row_array  = $rec_page->get_array();
?>

Understanding the code is quite simple. The following is a code explaination.

$config[‘query’]
Here you pass the SQL query for the results you want to be paginated. You can use where clause here if you want and you can also pass in complex queries which have joins.

$config[‘enable_field_search’]
If you are using Search feature then you can enable this field. If not then you an pass false. Passing false will make the pagination faster as it would not execute any search related code.

$config[‘con’]
You need to pass the object representing the SQL connection. you get this in return when you use mysqli_connect. See the code above for connection file to understand more. In our case we store this in $con.

$config[‘rows_per_page’]
As the name suggests, this tells the code how many records you want to keep per page. Usually people keep 10,15 or 20 records per page. The default is 10 per page. Adjust this according to your needs.

$rec_page = new pagination_with_search($config);
Here we simply pass the $config array to the pagination_with_search class and we get an object in return. This object we are saving in $rec_page. This object has many functions and will use them in the entire page.

$row_array = $rec_page->get_array();
We use get_array() function to get the records for the current page from the object. And the results we are saving in $row_array.
The results array that we have in $row_array can be looped to display the records on our page.

<table class="table">
		<thead>
			<tr>
				<th><?php echo $rec_page->sortable_label('id',"ID"); ?></th>
				<th><?php echo $rec_page->sortable_label('name',"Name"); ?></th>
			</tr>
		</thead>
			<?php 
			foreach ($row_array as $user_row) 
			{
			 	?>
			 	<tr>
				 	<td><?php echo  $user_row['id']; ?></td>
				 	<td><?php echo  $user_row['name']; ?></td>
			 	</tr>
			 	<?php
			 }
			  ?>
	</table>

Sortable Headers

In the above example we have created sortable_labels. Like

<?php echo $rec_page->sortable_label('name',"Name"); ?>

Sortables labels are nothing but clickable text headings which toggle sorting. Simply it means that once you click the heading it will sort the results in ascending order and when you click them again it will reverse the order to descending. If you want a similar function on the website then you can use sortable labels in your website and it is completely optional.

The second thing that you need see in the above example is how the array is looped to display the record. There is nothing fancy here. We are simply using a foreach loop.

<?php 
foreach ($row_array as $user_row) 
{
?>
   <tr>
	<td><?php echo  $user_row['id']; ?></td>
	<td><?php echo  $user_row['name']; ?></td>
   </tr>
<?php
}
?>

Here each row creates a <tr> row with two <td> inside it. And we are displaying id and name inside them. In this example we want to display the records in a table. But in your case you can use your own design which necessarily might not be a table.

Till here if you have followed well , you will see that you will have the records displayed on your page with sortable headers but you will not be able to navigate to the next page. For this we would have to use the following function.

<?php $rec_page->show_links_new();; ?>

Wherever you place this code in your design. You will get a paginated Navigation similar to the following.

Once you have placed the above function and if you will run the code. Then you would be able to navigate from one page to another.

There are a few more things which you might want to add to your pagination as well.

Records Per page controller.

If you want the users to choose how many records they want to be displayed on a single page then you can use the following function.

<?php $rec_page->perpage_selector_new(); ?>

Wherever you place the above function you will get a selector displayed as following.

Records Status.

Showing the records status. If you want user to know more information about which records are being displayed then you can use the following function.

<?php echo $rec_page->record_statement(); ?>

The result of this function is as following.

Adding Search to our Pagination.

We might want to add a mechanism so that we can filter the records on our page. The following are a few types of searches which we can introduce on our page.

To being searching we would need a form where we can create inputs for our search filters. All the inputs will be created inside this form.

<form method="GET">

<form>

1. Simple Text Search / Like Search

Simple search is the one where we give a user a text input to fill in.
Once the user fills that input we compare it with the particular field and get results.

To use this we simply just create a input and we name it as follows.

<input name="s__table_name--fieldname"/>

Here you can replace table_name with your table name and fieldname with your table field name. Let’s say that your table name is user and your field name is name The example becomes as following.

<input name="s__user--name"/>

Once you place this along with a submit button as following you will find that the results are sorted automatically.

<input name="s__user--name"/>
<input type="submit" value="search">

The above example will use like in the database to compare “%keyword%”.

2. Exact Match Search

When you want the input that you have filled to match exactly from the database then you are going to use exact match. The following is the example of how it works.

<input name="es__table_name--fieldname"/>