· 8 years ago · Nov 22, 2017, 05:34 PM
1CREATE TABLE IF NOT EXISTS `detalleinventarioinicial` (
2 `id` int(10) NOT NULL,
3 `idingreso` varchar(80) COLLATE utf8_spanish2_ci NOT NULL,
4 `nro` varchar(10) CHARACTER SET latin1 NOT NULL,
5 `codigo` int(10) NOT NULL,
6 `descripcion` varchar(300) COLLATE utf8_spanish2_ci NOT NULL,
7 `unidad` varchar(20) CHARACTER SET latin1 NOT NULL,
8 `cantidad` int(10) NOT NULL,
9 `marca` varchar(100) CHARACTER SET latin1 NOT NULL,
10 `serie` varchar(100) CHARACTER SET latin1 NOT NULL,
11 `color` varchar(100) CHARACTER SET latin1 NOT NULL,
12 `tipo` varchar(100) CHARACTER SET latin1 NOT NULL,
13 `procedencia` varchar(100) CHARACTER SET latin1 NOT NULL,
14 `docingreso` varchar(30) CHARACTER SET latin1 NOT NULL,
15 `numdocingreso` int(10) NOT NULL,
16 `estado` varchar(10) CHARACTER SET latin1 NOT NULL,
17 `observacion` text CHARACTER SET latin1 NOT NULL,
18 `idcolegio` int(5) NOT NULL,
19 `idusuario` varchar(50) CHARACTER SET latin1 NOT NULL,
20 `modificacion` varchar(50) CHARACTER SET latin1 NOT NULL,
21 `bien` varchar(200) CHARACTER SET latin1 NOT NULL,
22 `patrimonial` int(12) NOT NULL,
23 `ano` int(5) NOT NULL,
24 `idlaboratorio` varchar(20) COLLATE utf8_spanish2_ci NOT NULL,
25 `canti` int(5) NOT NULL,
26 `foto` varchar(20) COLLATE utf8_spanish2_ci NOT NULL,
27 `modelo` varchar(80) COLLATE utf8_spanish2_ci NOT NULL,
28 `dimension` varchar(80) COLLATE utf8_spanish2_ci NOT NULL
29) ENGINE=InnoDB AUTO_INCREMENT=16814 DEFAULT CHARSET=utf8 COLLATE=utf8_spanish2_ci;
30
31--
32-- Volcado de datos para la tabla `detalleinventarioinicial`
33--
34
35INSERT INTO `detalleinventarioinicial` (`id`, `idingreso`, `nro`, `codigo`, `descripcion`, `unidad`, `cantidad`, `marca`, `serie`, `color`, `tipo`, `procedencia`, `docingreso`, `numdocingreso`, `estado`, `observacion`, `idcolegio`, `idusuario`, `modificacion`, `bien`, `patrimonial`, `ano`, `idlaboratorio`, `canti`, `foto`, `modelo`, `dimension`) VALUES
36(10708, 'VIDRIO', 'MATERIAL', 11109001, 'TUBO  DE DESCARGA A BAJA PRESIÓN', 'unidad', 1, '', '', ' ', '', '', '', 0, 'BUENO', '', 2, 'marcos@gmail.com', '2017-10-12 20:40:39', 'BIEN MENOR', 0, 2011, '1', 0, '', '', ''),
37(10709, 'VIDRIO', 'MATERIAL', 11109002, 'TUBO  DE DESCARGA A BAJA PRESIÓN', 'unidad', 1, '', '', ' ', '', '', '', 0, 'BUENO', '', 2, 'marcos@gmail.com', '2017-10-12 20:40:15', 'BIEN MENOR', 0, 2011, '1', 0, '', '', ''),
38
39<html>
40 <head>
41 <title>Display Last N Days Data into Datatables using PHP Ajax</title>
42 <script src="https://ajax.googleapis.com/ajax/libs/jquery/2.2.0/jquery.min.js"></script>
43 <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" />
44 <script src="https://cdn.datatables.net/1.10.12/js/jquery.dataTables.min.js"></script>
45 <script src="https://cdn.datatables.net/1.10.12/js/dataTables.bootstrap.min.js"></script>
46 <link rel="stylesheet" href="https://cdn.datatables.net/1.10.12/css/dataTables.bootstrap.min.css" />
47 <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/js/bootstrap.min.js"></script>
48
49 </head>
50 <body>
51 <div class="container">
52 <h2 align="center">Display Last N Days Data into Datatables using PHP Ajax</h2>
53 <br />
54 <div class="row">
55 <div class="col-md-2">
56
57 <select name="laboratorio" id="laboratorio" class="form-control">
58 <option value="">Select Days</option>
59 <option value="1">General</option>
60 <option value="2">Fisica</option>
61 <option value="3">Quimica</option>
62 <option value="4">Biologia</option>
63 </select>
64
65 </div>
66 <div style="clear:both"></div>
67 <br />
68 <div class="table-responsive">
69 <table id="order_data" class="table table-bordered table-striped">
70 <thead>
71 <tr>
72 <th>Customer Name</th>
73 <th>Customer Name</th>
74 <th>Product Name</th>
75 <th>Order Value</th>
76 <th>Order Date</th>
77
78 </tr>
79 </thead>
80 </table>
81 </div>
82 </div>
83 </div>
84 </body>
85</html>
86
87
88
89<script type="text/javascript" language="javascript" >
90$(document).ready(function(){
91
92 load_data();
93
94 function load_data(is_days)
95 {
96 var dataTable = $('#order_data').DataTable({
97 "processing":true,
98 "serverSide":true,
99 "order":[],
100 "ajax":{
101 url:"fetch.php",
102 type:"POST",
103 data:{is_days:is_days}
104 }
105 });
106 }
107
108 $(document).on('change', '#laboratorio', function(){
109 var no_of_days = $(this).val();
110 $('#order_data').DataTable().destroy();
111 if(no_of_days != '')
112 {
113 load_data(no_of_days);
114 }
115 else
116 {
117 load_data();
118 }
119 });
120
121});
122</script>
123
124<?php
125//fetch.php
126$connect = mysqli_connect("localhost", "root", "", "xxx");
127$column = array("id","descripcion", "nro", "idingreso", "idlaboratorio");
128$query = " SELECT * FROM detalleinventarioinicial WHERE ";
129
130if(isset($_POST["is_days"]))
131{
132 $query .= "idlaboratorio = ".$_POST["is_days"]." AND ";
133}
134
135if(isset($_POST["search"]["value"]))
136{
137 $query .= '(id LIKE "%'.$_POST["search"]["value"].'%" ';
138 $query .= 'OR descripcion LIKE "%'.$_POST["search"]["value"].'%" ';
139 $query .= 'OR nro LIKE "%'.$_POST["search"]["value"].'%" ';
140 $query .= 'OR idingreso LIKE "%'.$_POST["search"]["value"].'%" ';
141 $query .= 'OR idlaboratorio LIKE "%'.$_POST["search"]["value"].'%") ';
142}
143
144if(isset($_POST["order"]))
145{
146 $query .= 'ORDER BY '.$column[$_POST['order']['0']['column']].' '.$_POST['order']['0']['dir'].' ';
147}
148else
149{
150 $query .= 'ORDER BY id DESC ';
151}
152
153$query1 = '';
154
155if($_POST["length"] != -1)
156{
157 $query1 .= 'LIMIT ' . $_POST['start'] . ', ' . $_POST['length'];
158}
159
160$number_filter_row = mysqli_num_rows(mysqli_query($connect, $query));
161
162$result = mysqli_query($connect, $query . $query1);
163
164$data = array();
165
166while($row = mysqli_fetch_array($result))
167{
168 $sub_array = array();
169 $sub_array[] = $row["id"];
170 $sub_array[] = $row["descripcion"];
171 $sub_array[] = $row["nro"];
172 $sub_array[] = $row["idingreso"];
173 $sub_array[] = $row["idlaboratorio"];
174 $data[] = $sub_array;
175}
176
177function get_all_data($connect)
178{
179 $query = "SELECT * FROM detalleinventarioinicial";
180 $result = mysqli_query($connect, $query);
181 return mysqli_num_rows($result);
182}
183
184$output = array(
185 "draw" => intval($_POST["draw"]),
186 "recordsTotal" => get_all_data($connect),
187 "recordsFiltered" => $number_filter_row,
188 "data" => $data
189);
190
191echo json_encode($output);
192
193
194
195?>