PHP CRUD with Search and Pagination

Last modified on October 19th, 2018 by Vincy.

Making a CRUD system is one of the prime and regular requirements in most of the software. It is nothing but the collection functionalities that are used to create and manage data.

In this tutorial, we are going to learn how to create a PHP CRUD system with search and pagination feature. This will be explained with a simple example of creating and managing toy database data with the create, read, update and delete (CRUD) actions.

Earlier, we saw about CRUD with AJAX and CRUD without AJAX (both did not have search and pagination). As we have learned enough about PHP CRUD, let’s see the code for implementing the search and pagination in PHP CRUD. 

Search by Column using PHP and MySQL

In this section, we are going to see the code for displaying the search option in the UI and also the PHP code to handle the search request.

Below HTML form contains two fields name and code to get the keyword from the users. Once the user enters the keyword in these fields and submits, then the search request is received in the PHP endpoint. This form also contains the Reset button to clear the searched keyword by resetting the search form.

<form name="frmSearch" method="post" action="index.php">
	<div class="search-box">
	<p>
	<input type="text" placeholder="Name" name="search[name]" class="demoInputBox" value="<?php echo $name; ?>"	/>
	<input type="text" placeholder="Code" name="search[code]" class="demoInputBox" value="<?php echo $code; ?>"	/>
	<input type="submit" name="go" class="btnSearch" value="Search">
	<input type="reset" class="btnSearch" value="Reset" onclick="window.location='index.php'">
	</p>
	</div>
</form>

In PHP, the Name and Code field data are received and used in a MySQL select query. In this query, the LIKE clause is used to filter the toy database table result based on the search request.

Below PHP code shows the search request handling which will happen after form submit. In this code, the switch case constructs the query condition based on which column the user wants to filter the database result.

<?php
	require_once("perpage.php");	
	require_once("dbcontroller.php");
	$db_handle = new DBController();
	
	$name = "";
	$code = "";	
	$queryCondition = "";
	if(!empty($_POST["search"])) {
		foreach($_POST["search"] as $k=>$v){
			if(!empty($v)) {
				$queryCases = array("name","code");
				if(in_array($k,$queryCases)) {
					if(!empty($queryCondition)) {
						$queryCondition .= " AND ";
					} else {
						$queryCondition .= " WHERE ";
					}
				}
				switch($k) {
					case "name":
						$name = $v;
						$queryCondition .= "name LIKE '" . $v . "%'";
						break;
					case "code":
						$code = $v;
						$queryCondition .= "code LIKE '" . $v . "%'";
						break;
				}
			}
		}
	}
	$orderby = " ORDER BY id desc"; 
	$sql = "SELECT * FROM toy " . $queryCondition;
	$href = 'index.php';					
		
	$perPage = 2; 
	$page = 1;
	if(isset($_POST['page']))	$page = $_POST['page'];
	$start = ($page-1)*$perPage;
	if($start < 0) $start = 0;
		
	$query =  $sql . $orderby .  " limit " . $start . "," . $perPage; 
	$result = $db_handle->runQuery($query);
	
	if(!empty($result)) $result["perpage"] = showperpage($sql, $perPage, $href);
?>

PHP Pagination by Preserving Search Results

This section contains the PHP code to display the pagination markup below the recordset of the CRUD system. This perpage function receives the total record count, per page count parameters. Using these parameters it computes the total page count. For each page, it creates the HTML markup to navigate among the pages.

The page navigations are displayed as submit buttons to preserve the searched column data. If we display the pagination as links then it will redirect the user and the user will lose the searched keyword after the redirect.

function perpage($count, $per_page = '10',$href) {
	$output = '';
	$paging_id = "link_perpage_box";
	if(!isset($_POST["page"])) $_POST["page"] = 1;
	if($per_page != 0)
	$pages  = ceil($count/$per_page);
	if($pages>1) {		
		if(($_POST["page"]-3)>0) {
			if($_POST["page"] == 1)	$output = $output . '<span id=1 class="current-page">1</span>';
			else	$output = $output . '<input type="submit" name="page" class="perpage-link" value="1" />';
		}
		if(($_POST["page"]-3)>1) $output = $output . '...';
		for($i=($_POST["page"]-2); $i<=($_POST["page"]+2); $i++)	{
			if($i<1) continue;
			if($i>$pages) break;
			if($_POST["page"] == $i)	$output = $output . '<span id='.$i.' class="current-page" >'.$i.'</span>';
			else	$output = $output . '<input type="submit" name="page" class="perpage-link" value="' . $i . '" />';
		}		
		if(($pages-($_POST["page"]+2))>1) $output = $output . '...';
		if(($pages-($_POST["page"]+2))>0) {
			if($_POST["page"] == $pages)
				$output = $output . '<span id=' . ($pages) .' class="current-page">' . ($pages) .'</span>';
			else				
				$output = $output . '<input type="submit" name="page" class="perpage-link" value="' . $pages . '" />';
		}
		
	}
	return $output;
}

dbcontroller.php

This PHP class is a DAO to handle the database connection establishment and query execution. The runQuery function receives the SELECT query statement and executes it to get the database resource ident. With the reference if the resource id, it retrieves the recordset and iterates it to construct the resultant array to be returned to the CRUD system.

<?php
class DBController {
	private $host = "localhost";
	private $user = "root";
	private $password = "test";
	private $database = "phpsamples";
    private $conn;
	
	function __construct() {
		$this->conn = $this->connectDB();
	}
	
	function connectDB() {
		$conn = mysqli_connect($this->host,$this->user,$this->password,$this->database);
		return $conn;
	}
	
	function runQuery($query) {
		$result = mysqli_query($this->conn,$query);
		while($row=mysqli_fetch_assoc($result)) {
			$resultset[] = $row;
		}		
		if(!empty($resultset))
			return $resultset;
	}
	
	function numRows($query) {
		$result  = mysqli_query($this->conn, $query);
		$rowcount = mysqli_num_rows($result);
		return $rowcount;	
	}
	
	function executeQuery($query) {
	    $result  = mysqli_query($this->conn, $query);
	    return $result;	
	}
}
?>

MySQL Database Script

The following SQL script contains the toy database table’s create statement. By using this you can create the required database structure in your local environment to run this example.

CREATE TABLE IF NOT EXISTS `toy` (
  `id` int(8) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `code` varchar(20) NOT NULL,
  `category` varchar(255) NOT NULL,
  `price` double NOT NULL,
  `stock_count` bigint(16) NOT NULL,
  PRIMARY KEY (`id`)
)

PHP CRUD with Search and Pagination Output

This screenshot shows the output of the PHP CRUD with search and pagination. 

php-crud-with-search-pagination

Download

↑ Back to Top

Share this Article