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