Instant Search With Pagination in PHP, MySQL, jQuery and Ajax

Posted by & filed under Ajax, JQUERY, MYSQL, PHP.

This tutorial cotinuation of my previous tutorial on jQGrid, in this tutorial i had added advanced searching functionality with pagination using jQGrid. Before continuing this tutorial please refer following tutorial jQGrid.

1. Pagination, Previous, Next, First and Last button using jQGrid, PHP, MySQL, jQuery and Ajax

2. jQGrid PHP inline Editing



Instant Search With Pagination in PHP, MySQL, jQuery and Ajax

Instant Search With Pagination in PHP, MySQL, jQuery and Ajax


This tutorial is Contiuation of my previous tutorial on jQGrid, so please refer that tutorial. I am going to explain only searching functionality in this tutorial.

This index.php file, I added two textboxes to search country and last name respectively. Every time when user types something in either of the textboxes I am calling dosearch() function through onkeydown() event. The dosearch() inturns calls gridReload() that sends ajax request search.php file. Finally search.php receives user entered through GET method, with that data we query in the database. Finally it sends back response to the index.php file.



Note: I haded edited only index.php and search.php, all files are same that i had in my previous tutorial.

<title>jQGrid PHP inline Editing With Search</title>
<link rel='stylesheet' href='css/jquery-ui-custom.css'/>
<link rel='stylesheet' href='css/ui.jqgrid.css'/>

<script src='js/jquery-1.9.0.min.js'></script>
<script src='js/grid.locale-en.js'></script>
<script src='js/jquery.jqGrid.min.js'></script>

@font-face{font-family: Lobster;src: url('css/Lobster.otf');}
.wrapper{ margin: 20px 0 0 250px;}
h1{text-align:center;font-family: Lobster;}

.ui-widget{font-family:Arial; color:#fff;}
.ui-jqgrid .ui-jqgrid-hdiv {height:25px;}
.ui-jqgrid tr.jqgrow td{height:40px;}
.ui-jqgrid .ui-jqgrid-pager {height:40px;}
.ui-state-default, .ui-widget-content .ui-state-default, .ui-widget-header .ui-state-default {
  background: #fff;font-weight: bold;color:#000;font-size:13px;border:1px solid #00BB64;}
.ui-widget-content{border: 1px solid #00BB64;}
.txt{width:250px;height:30px;border-radius:5px;border:1px solid #00BB64;}

  <h1>jQGrid PHP inline Editing Example with Search</h1>

  <div style='padding:20px 0 0 370px;'> Search : 
  <input type="text" id="item" onkeydown="doSearch(arguments[0]||event)" placeholder='Country' class='txt'/>
  <input type="text" id="empid" onkeydown="doSearch(arguments[0]||event)" placeholder='Last Name' class='txt'/>


  <div class='wrapper'>
	<table id="rowed2"></table> 
	<div id="prowed2"></div>


	datatype: "json",
	   {name:'EmployeeID',index:'EmployeeID', width:30,classes: 'cvteste'}, 
	   {name:'LastName',index:'LastName', width:90,classes: 'cvteste',editable:true}, 
	   {name:'FirstName',index:'FirstName', width:80,classes: 'cvteste',editable:true},
	   {name:'BirthDate',index:'BirthDate', width:150,align:"center",classes: 'cvteste',editable:true},
       {name:'Address',index:'Address', width:140, sortable:false,classes: 'cvteste',editable:true},
	   {name:'City',index:'City', width:80, sortable:false,classes: 'cvteste',editable:true},
	   {name:'Region',index:'Region', width:70, sortable:false,classes: 'cvteste',editable:true},
	   {name:'Country',index:'Country', width:70, sortable:false,classes: 'cvteste',editable:true},
       {name:'act',index:'act', width:130,sortable:false}	   
   	pager: '#prowed2',
   	sortname: 'EmployeeID',
    viewrecords: true,
    sortorder: "asc",
	gridComplete: function(){
		var ids = jQuery("#rowed2").jqGrid('getDataIDs');
		for(var i=0;i<ids.length;i++){
			var cl = ids[i];
			be = "<input style='height:22px;width:40px;' type='button' value='Edit' onclick=\"jQuery('#rowed2').jqGrid('editRow','"+cl+"');\"  />"; 
			se = "<input style='height:22px;width:40px;' type='button' value='Save' onclick=\"jQuery('#rowed2').jqGrid('saveRow','"+cl+"');\"  />"; 
			ce = "<input style='height:22px;width:50px;' type='button' value='Cancel' onclick=\"jQuery('#rowed2').jqGrid('restoreRow','"+cl+"');\" />"; 
	editurl: "update.php"

var timeoutHnd; 
var flAuto = true;
 function doSearch(ev){ 
 if(timeoutHnd) clearTimeout(timeoutHnd);
  timeoutHnd = setTimeout(gridReload,500);
function gridReload(){
 var nm_mask = jQuery("#item").val();
 var cd_mask = jQuery("#empid").val();

Step 2:

Here is search.php file and have a look how I am getting user entered data’s and forming the SQL queries.

author : muni 
 ini_set('max_execution_time', 600);
 require_once 'config.php';

     $nm_mask = $_GET['nm_mask'];
     $nm_mask = "";
     $cd_mask = $_GET['cd_mask'];
     $cd_mask = "";

 $where = "WHERE 1=1";
     $where.= " AND Country LIKE '$nm_mask%'";
     $where.= " AND LastName LIKE '$cd_mask%'";

 $page = $_GET['page']; // get the requested page
 $limit = $_GET['rows']; // get how many rows we want to have into the grid
 $sidx = $_GET['sidx']; // get index row - i.e. user click to sort
 $sord = $_GET['sord']; // get the direction
 if(!$sidx) $sidx =1; // connect to the database
 $result = mysql_query("SELECT COUNT(*) AS count FROM employees ".$where);
 $row = mysql_fetch_array($result,MYSQL_ASSOC);
 $count = $row['count'];

 if( $count >0 ) { 
 $total_pages = ceil($count/$limit);
 } else { 
 $total_pages = 0; 
 if ($page > $total_pages) $page=$total_pages;
 if ($limit<0) $limit = 0; 
 $start = $limit*$page - $limit; // do not put $limit*($page - 1)
 if ($start<0) $start = 0;
 $SQL = "SELECT * from employees ". $where ." ORDER BY $sidx $sord LIMIT $start , $limit"; 
 $result = mysql_query( $SQL ) or die("Couldn?t execute query.".mysql_error());
 $responce->page = $page;
 $responce->total = $total_pages;
 $responce->records = $count; 
 while($row = mysql_fetch_array($result,MYSQL_ASSOC)) { 
 $responce->rows[$i]['cell']=array($row['EmployeeID'],$row['LastName'],$row[FirstName],$row[BirthDate],$row[Address],$row['City'],$row['Region'],$row['Country'],""); $i++;
 echo json_encode($responce);




Download Premium Only Scripts & 80+ Demo scripts Instantly at just 1.95 USD per month + 10% discount to all Exclusive Scripts

If you want any of my script need to be customized according to your business requirement,

Please feel free to contact me [at] muni2explore[at]

Note: But it will be charged based on your customization requirement

Get Updates, Scripts & Other Useful Resources to your Email

Join 10,000+ Happy Subscribers on feedburner. Click to Subscribe (We don't send spam)
Every Email Subsciber could have access to download 100+ demo scripts & all future scripts.

Get Instant Script Download Access!