Please support us by disabling ad blocker for smarttutorials.net ...

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

Step1:

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.

<!DOCTYPE HTML>
<html>
<head>
<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>

<style>
@font-face{font-family: Lobster;src: url('css/Lobster.otf');}
body{width:100%;padding:0px;margin:0px;}
.wrapper{ margin: 20px 0 0 250px;}
.cvteste{color:#000;font-size:12px;font-family:verdana}
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;}
</style>

</head>
<body>
  <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>

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

  </div>

	<script>
	jQuery("#rowed2").jqGrid({
   	url:'server.php',
	datatype: "json",
   	 colNames:['EMPLOYEE ID','LAST NAME', 'FIRST NAME', 'BIRTH DATE','ADDRESS','CITY','REGION','COUNTRY','ACTIONS'], 
	   colModel:[ 
	   {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}	   
	   ],
   	rowNum:10,
   	rowList:[10,20,30],
   	pager: '#prowed2',
   	sortname: 'EmployeeID',
    viewrecords: true,
	height:'100%',
    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+"');\" />"; 
			jQuery("#rowed2").jqGrid('setRowData',ids[i],{act:be+se+ce});
		}	
	},
	editurl: "update.php"
});

var timeoutHnd; 
var flAuto = true;
 function doSearch(ev){ 
 if(!flAuto)return; 
 if(timeoutHnd) clearTimeout(timeoutHnd);
  timeoutHnd = setTimeout(gridReload,500);
 }
function gridReload(){
 var nm_mask = jQuery("#item").val();
 var cd_mask = jQuery("#empid").val();
jQuery("#rowed2").jqGrid('setGridParam',{url:"server.php?nm_mask="+nm_mask+"&cd_mask="+cd_mask,page:1}).trigger("reloadGrid");   
}	   	   
	</script>
</body>
</html>

Step 2:

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

<?php
/*
www.smarttutorials.net
author : muni 
*/
 error_reporting(0);
 ini_set('max_execution_time', 600);
 require_once 'config.php';

 if(isset($_GET["nm_mask"]))
     $nm_mask = $_GET['nm_mask'];
 else 
     $nm_mask = "";
 if(isset($_GET["cd_mask"])) 
     $cd_mask = $_GET['cd_mask'];
 else
     $cd_mask = "";

 $where = "WHERE 1=1";
 if($nm_mask!='')
     $where.= " AND Country LIKE '$nm_mask%'";
 if($cd_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; 
 $i=0;
 while($row = mysql_fetch_array($result,MYSQL_ASSOC)) { 
 $responce->rows[$i]['id']=$row[EmployeeID];
 $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] muni[at]smarttutorials.net

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.

  • jack li

    Your register/subcribed system look silly, I try 5 times to get a download link and fail after download “Grid_inline”, don’t you have a remember_me or register system?

  • salman

    i am not able to download it even after 12 hours.. can you please help me out?

  • fatin

    how to create many row from table database in one text for search?please help me

  • fatin

    i want to create this code n1 until n10 how?

    if($nt_mask!=”)
    $where.= ” AND n1 LIKE ‘$nt_mask%'”;

  • agustri

    nice tutorial… copy it.

Get Instant Script Download Access!