· 8 years ago · Jun 06, 2018, 10:02 AM
1<table cellpadding="1" cellspacing="1" id="users" class="display" width="100%">
2 <thead>
3 <tr>
4 <th>ID</th>
5 <th>simno</th>
6 <th>city</th>
7 <th>newno</th>
8 <th>time</th>
9
10 </tr>
11 </thead>
12 <tfoot>
13 <tr>
14 <th>ID</th>
15 <th>simno</th>
16 <th>city</th>
17 <th>newno</th>
18 <th>time</th>
19
20 </tr>
21 </tfoot>
22</table>
23
24<script type="text/javascript">
25 $(document).ready(function () {
26 $('#users').DataTable({
27 "columns": [
28 {"data": "id"},
29 {"data": "simno"},
30 {"data": "city"},
31 {"data": "newno"},
32 {"data": "time"}
33
34 ],
35 "processing": true,
36 "serverSide": true,
37 "ajax": {
38 url: 'demo2.php',
39 type: 'POST',
40 dataType: "json",
41 contentType: "application/json; charset=utf-8"
42 }
43 });
44 });
45</script>
46
47<?php
48ini_set('memory_limit', '8G');
49ini_set('max_execution_time', 3000000);
50?>
51<?php
52
53if (!empty($_POST) ) {
54
55
56
57 define("HOST", "localhost");
58 define("USER", "root");
59 define("PASSWORD", "root");
60 define("DB", "archive");
61 define("MyTable", "bond");
62
63 $connection = mysqli_connect(HOST, USER, PASSWORD, DB) OR DIE("Impossible to access to DB : " . mysqli_connect_error());
64
65
66 function getData($sql){
67 global $connection ;//we use connection already opened
68 $query = mysqli_query($connection, $sql) OR DIE ("Can't get Data from DB , check your SQL Query " );
69 $data = array();
70 foreach ($query as $row ) {
71 $data[] = $row ;
72 }
73 return $data;
74 }
75
76 /* Useful $_POST Variables coming from the plugin */
77 $draw = $_POST["draw"];//counter used by DataTables to ensure that the Ajax returns from server-side processing requests are drawn in sequence by DataTables
78 $orderByColumnIndex = $_POST['order'][0]['column'];// index of the sorting column (0 index based - i.e. 0 is the first record)
79 $orderBy = $_POST['columns'][$orderByColumnIndex]['data'];//Get name of the sorting column from its index
80 $orderType = $_POST['order'][0]['dir']; // ASC or DESC
81 $start = $_POST["start"];//Paging first record indicator.
82 $length = $_POST['length'];//Number of records that the table can display in the current draw
83 /* END of POST variables */
84
85 $recordsTotal = count(getData("SELECT * FROM ".MyTable));
86
87 /* SEARCH CASE : Filtered data */
88 if(!empty($_POST['search']['value'])){
89
90 /* WHERE Clause for searching */
91 for($i=0 ; $i<count($_POST['columns']);$i++){
92 $column = $_POST['columns'][$i]['data'];//we get the name of each column using its index from POST request
93 $where[]="$column like '%".$_POST['search']['value']."%'";
94 }
95 $where = "WHERE ".implode(" OR " , $where);// id like '%searchValue%' or name like '%searchValue%' ....
96 /* End WHERE */
97
98 $sql = sprintf("SELECT * FROM %s %s", MyTable , $where);//Search query without limit clause (No pagination)
99
100 $recordsFiltered = count(getData($sql));//Count of search result
101
102 /* SQL Query for search with limit and orderBy clauses*/
103 $sql = sprintf("SELECT * FROM %s %s ORDER BY %s %s limit %d , %d ", MyTable , $where ,$orderBy, $orderType ,$start,$length );
104 $data = getData($sql);
105 }
106 /* END SEARCH */
107 else {
108 $sql = sprintf("SELECT * FROM %s ORDER BY %s %s limit %d , %d ", MyTable ,$orderBy,$orderType ,$start , $length);
109 $data = getData($sql);
110
111 $recordsFiltered = $recordsTotal;
112 }
113
114 /* Response to client before JSON encoding */
115 $response = array(
116 "draw" => intval($draw),
117 "recordsTotal" => $recordsTotal,
118 "recordsFiltered" => $recordsFiltered,
119 "data" => $data
120 );
121
122 echo json_encode($response);
123
124} else {
125 echo "NO POST Query from DataTable";
126}
127?>
128
129CREATE TABLE IF NOT EXISTS `bond` (
130`id` bigint(255) AUTO_INCREMENT ,
131`simno` varchar(255) NOT NULL,
132 `city` varchar(255) NOT NULL,
133`newno` varchar(255) NOT NULL,
134`time` varchar(255) NOT NULL
135) ENGINE=MyISAM DEFAULT CHARSET=latin1;