· 8 years ago · Aug 18, 2018, 02:52 PM
1CREATE TABLE IF NOT EXISTS `mobile_phones` (
2 `id` int(11) NOT NULL AUTO_INCREMENT,
3 `name` varchar(255) DEFAULT NULL,
4 `price` int(11) DEFAULT NULL,
5 `samsung` tinyint(1) DEFAULT NULL,
6 `iphone` tinyint(1) DEFAULT NULL,
7 `htc` tinyint(1) DEFAULT NULL,
8 `lg` tinyint(1) DEFAULT NULL,
9 `nokia` tinyint(1) DEFAULT NULL,
10 PRIMARY KEY (`id`)
11 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
12
13INSERT INTO `mobile_phones` (`id`, `name`, `price`, `samsung`, `iphone`,
14`htc`, `lg`, `nokia`) VALUES
15(1, 'Samsung Galaxy S 1', 180, 1, 0, 0, 0, 0),
16(2, 'Samsung Galaxy S 2', 220, 1, 0, 0, 0, 0),
17(3, 'Samsung Galaxy S 3', 300, 1, 0, 0, 0, 0),
18(4, 'Samsung Galaxy S 4', 450, 1, 0, 0, 0, 0),
19(5, 'Samsung Galaxy S 4 mini', 400, 1, 0, 0, 0, 0),
20(6, 'Iphone 3GS', 150, 0, 1, 0, 0, 0),
21(7, 'Iphone 4', 200, 0, 1, 0, 0, 0),
22(8, 'Iphone 4S', 250, 0, 1, 0, 0, 0),
23(9, 'Iphone 5', 300, 0, 1, 0, 0, 0),
24(10, 'Iphone 5S', 350, 0, 1, 0, 0, 0),
25(11, 'Htc Desire', 150, 0, 0, 1, 0, 0),
26(12, 'Htc Desire200', 200, 0, 0, 1, 0, 0),
27(13, 'Htc Desire500', 250, 0, 0, 1, 0, 0),
28(14, 'Htc One', 400, 0, 0, 1, 0, 0),
29(15, 'Htc One mini', 250, 0, 0, 1, 0, 0),
30(16, 'Lg Optimus L3', 150, 0, 0, 0, 1, 0),
31(17, 'Lg Optimus L5', 250, 0, 0, 0, 1, 0),
32(18, 'Lg Optimus L7', 350, 0, 0, 0, 1, 0),
33(19, 'Lg Optimus L9', 400, 0, 0, 0, 1, 0),
34(20, 'Lg Optimus G2', 450, 0, 0, 0, 1, 0),
35(21, 'Nokia 100', 50, 0, 0, 0, 0, 1),
36(22, 'Nokia E72', 100, 0, 0, 0, 0, 1),
37(23, 'Nokia E6', 150, 0, 0, 0, 0, 1),
38(24, 'Nokia Lumia 520', 200, 0, 0, 0, 0, 1),
39(25, 'Nokia Lumia 620', 250, 0, 0, 0, 0, 1);
40
41<?php
42 $pdo = new PDO('mysql:host=localhost;dbname=sitepoint', 'root', '');
43 $select = 'SELECT *';
44 $from = ' FROM mobile_phones';
45 $where = ' WHERE ';
46 $opts = $_POST['filterOpts'];
47
48 if (empty($opts)){
49 // 0 checkboxes checked
50 $where .= 'TRUE';
51 } else {
52 if(count($opts) == 1){
53 // 1 checkbox checked
54 $where .= $opts[0] . ' = 1';
55 } else {
56 // 2+ checkboxes checked
57 $where .= implode(' = 1 OR ', $opts) . ' = 1';
58 }
59 }
60
61 $sql = $select . $from . $where;
62 $statement = $pdo->prepare($sql);
63 $statement->execute();
64 $results = $statement->fetchAll(PDO::FETCH_ASSOC);
65 $json = json_encode($results);
66 echo($json);
67?>
68
69<html>
70 <head>
71 <meta charset="utf-8">
72 <title>AJAX filter demo</title>
73 <style>
74 body {
75 padding: 10px;
76 }
77
78 h1 {
79 margin: 0 0 0.5em 0;
80 color: #343434;
81 font-weight: normal;
82 font-family: 'Ultra', sans-serif;
83 font-size: 36px;
84 line-height: 42px;
85 text-transform: uppercase;
86 text-shadow: 0 2px white, 0 3px #777;
87 }
88
89 h2 {
90 margin: 1em 0 0.3em 0;
91 color: #343434;
92 font-weight: normal;
93 font-size: 30px;
94 line-height: 40px;
95 font-family: 'Orienta', sans-serif;
96 }
97
98 #phones {
99 font-family: "Lucida Sans Unicode", "Lucida Grande", Sans-Serif;
100 font-size: 12px;
101 background: #fff;
102 margin: 15px 25px 0 0;
103 border-collapse: collapse;
104 text-align: center;
105 float: left;
106 width: 700px;
107 }
108
109 #phones th {
110 font-size: 14px;
111 font-weight: normal;
112 color: #039;
113 padding: 10px 8px;
114 border-bottom: 2px solid #6678b1;
115 }
116
117 #phones td {
118 border-bottom: 1px solid #ccc;
119 color: #669;
120 padding: 8px 10px;
121 }
122
123 #phones tbody tr:hover td {
124 color: #009;
125 }
126
127 #filter {
128 float:left;
129 }
130 </style>
131 </head>
132 <body>
133 <h1>Phones database</h1>
134
135 <table id="phones">
136 <thead>
137 <tr>
138 <th>ID</th>
139 <th>Name</th>
140 <th>Price</th>
141 <th>Samsung</th>
142 <th>iPhone</th>
143 <th>HTC</th>
144 <th>LG</th>
145 <th>Nokia</th>
146 </tr>
147 </thead>
148 <tbody>
149 </tbody>
150 </table>
151
152 <div id="filter">
153 <h2>Filter options</h2>
154 <div>
155 <input type="checkbox" id="samsung">
156 <label for="samsung">Samsung</label>
157 </div>
158 <div>
159 <input type="checkbox" id="iphone">
160 <label for="iphone">iPhone</label>
161 </div>
162 <div>
163 <input type="checkbox" id="htc">
164 <label for="htc">HTC</label>
165 </div>
166 <div>
167 <input type="checkbox" id="lg">
168 <label for="lg">LG</label>
169 </div>
170 <div>
171 <input type="checkbox" id="nokia">
172 <label for="nokia">Nokia</label>
173 </div>
174 </div>
175
176 <script src="http://code.jquery.com/jquery-latest.js"></script>
177 <script>
178 function makeTable(data){
179 var tbl_body = "";
180 $.each(data, function() {
181 var tbl_row = "";
182 $.each(this, function(k , v) {
183 tbl_row += "<td>"+v+"</td>";
184 })
185 tbl_body += "<tr>"+tbl_row+"</tr>";
186 })
187
188 return tbl_body;
189 }
190
191 function getPhoneFilterOptions(){
192 var opts = [];
193 $checkboxes.each(function(){
194 if(this.checked){
195 opts.push(this.id);
196 }
197 });
198
199 return opts;
200 }
201
202 function updatePhones(opts){
203 $.ajax({
204 type: "POST",
205 url: "submit.php",
206 dataType : 'json',
207 cache: false,
208 data: {filterOpts: opts},
209 success: function(records){
210 $('#phones tbody').html(makeTable(records));
211 }
212 });
213 }
214
215 var $checkboxes = $("input:checkbox");
216 $checkboxes.on("change", function(){
217 var opts = getPhoneFilterOptions();
218 updatePhones(opts);
219 });
220
221 updatePhones();
222 </script>
223 </body>
224</html>