PHP CRUD with Search and Pagination

In this tutorial we will see about a simple PHP application for database create, read, update and delete (CRUD) operations. With these CRUD operations we will also have search and pagination features integrated to it.

Earlier, we saw about CRUD with AJAX and CRUD without AJAX (both did not have search and pagination). Now in this tutorial we will directly jump into the search and pagination part. The download code contains the complete CRUD operations, search and pagination features.

php-crud-with-search-pagination

Download

Search Data by Keyword using PHP and MySQL

We have search options for searching the Name and Code columns by the given keywords posted via the search form. The search keyword is used to find match with the values of corresponding columns by using MySQL LIKE clause. If match found then the results will be read and displayed in the list page with pagination.

The code for the search form is,

<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>

After submitting the search form, we are accessing the keywords from a PHP script to create search conditions for the select query. We have switch case to create query conditions with LIKE clause. The code is,

<?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

If you have paginated search results by Name or Code and want to navigate through the pages, the search query condition should be preserved. If we have the pagination links for page navigation then it will redirect to different URL and the search conditions will be reset. So, we are using submit buttons for the page navigation.

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;
}

MySQL Database Script

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`)
)

Download

This PHP code tutorial was published on November 2, 2014.

↑ Back to Top