Invoice Script PHP jQuery

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

 

Demo jQGrid With Search Using PHP

 

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.

 

Download jQGrid PHP Script

 

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

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

?>
JavaScript
Copy

 

 .

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

Get Access Now Instantly

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]gmail.com

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!

no thanks