-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathbigset.html
159 lines (148 loc) · 4.78 KB
/
bigset.html
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
<p style="font-size:12px;">
This sample demonstrates how jqGrid works with large amount of data.<br>
We have put into a table in a Mysql database about 12.000 records filled with random words. jqGrid, using Ajax, loads only those records, that are visible.<br>
If you want to make a search (just enter some word into input fields), grid sends search criteria to server and loads only data that correspond to entered criteria.<br>
Speed is increased about 2 times if we index the column. In this case this is column Code<br>
<i>Important: sample is working with real data - <b>12.000</b> records! Enjoy it's performance</i>
<br />
<div class="h">Search By:</div>
<div>
Code<br />
<input type="text" id="search_cd" onkeydown="doSearch(arguments[0]||event)" />
<input type="checkbox" id="autosearch" onclick="enableAutosubmit(this.checked)"> Enable Autosearch <br/>
</div>
<div>
Name<br>
<input type="text" id="item_nm" onkeydown="doSearch(arguments[0]||event)" />
<button onclick="gridReload()" id="submitButton" style="margin-left:30px;">Search</button>
</div>
<br />
<table id="bigset" ></table>
<div id="pagerb"></div>
<script src="bigset.js" type="text/javascript"> </script>
<br />
<div style="font-size:12px;">
<b> HTML </b>
<XMP>
...
<div class="h">Search By:</div>
<div>
<input type="checkbox" id="autosearch" onclick="enableAutosubmit(this.checked)"> Enable Autosearch <br/>
Code<br />
<input type="text" id="search_cd" onkeydown="doSearch(arguments[0]||event)" />
</div>
<div>
Name<br>
<input type="text" id="item" onkeydown="doSearch(arguments[0]||event)" />
<button onclick="gridReload()" id="submitButton" style="margin-left:30px;">Search</button>
</div>
<br />
<table id="bigset"></table>
<div id="pagerb"></div>
<script src="bigset.js" type="text/javascript"> </script>
</XMP>
<b>Java Scrpt code</b>
<XMP>
jQuery("#bigset").jqGrid({
url:'bigset.php',
datatype: "json",
height: 255,
colNames:['Index','Name', 'Code'],
colModel:[
{name:'item_id',index:'item_id', width:65},
{name:'item',index:'item', width:150},
{name:'item_cd',index:'item_cd', width:100}
],
rowNum:12,
// rowList:[10,20,30],
mtype: "POST",
pager: jQuery('#pagerb'),
pgbuttons: false,
pgtext: false,
pginput:false,
sortname: 'item_id',
viewrecords: true,
sortorder: "asc"
});
var timeoutHnd;
var flAuto = false;
function doSearch(ev){
if(!flAuto)
return;
// var elem = ev.target||ev.srcElement;
if(timeoutHnd)
clearTimeout(timeoutHnd)
timeoutHnd = setTimeout(gridReload,500)
}
function gridReload(){
var nm_mask = jQuery("#item_nm").val();
var cd_mask = jQuery("#search_cd").val();
jQuery("#bigset").jqGrid('setGridParam',{url:"bigset.php?nm_mask="+nm_mask+"&cd_mask="+cd_mask,page:1}).trigger("reloadGrid");
}
function enableAutosubmit(state){
flAuto = state;
jQuery("#submitButton").attr("disabled",state);
}
</XMP>
<b>PHP with MySQL (bigset.php)</b>
<XMP>
<?php
ini_set('max_execution_time', 600);
include("dbconfig.php");
// coment the above lines if php 5
include("JSON.php");
$json = new Services_JSON();
// end comment
$examp = $_GET["q"]; //query number
$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;
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 = "";
//construct where clause
$where = "WHERE 1=1";
if($nm_mask!='')
$where.= " AND item LIKE '$nm_mask%'";
if($cd_mask!='')
$where.= " AND item_cd LIKE '$cd_mask%'";
// connect to the database
$db = mysql_pconnect($dbhost, $dbuser, $dbpassword)
or die("Connection Error: " . mysql_error());
mysql_select_db($database) or die("Error conecting to db.");
$result = mysql_query("SELECT COUNT(*) AS count FROM items ".$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 item_id, item, item_cd FROM items ".$where." ORDER BY $sidx $sord LIMIT $start , $limit";
$result = mysql_query( $SQL ) or die("Couldnt 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[item_id];
$responce->rows[$i]['cell']=array($row[item_id],$row[item],$row[item_cd]);
$i++;
}
echo $json->encode($responce); // coment if php 5
//echo json_encode($responce);
mysql_close($db);
?>
</XMP>
</div>