· 8 years ago · Nov 16, 2017, 02:20 PM
1<?php
2
3/*
4
5 To recreate the various database tables and data used for this demo...
6
7 CREATE TABLE IF NOT EXISTS `irn_instructions` (
8 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
9 `instruction` varchar(50) NOT NULL DEFAULT '0',
10 PRIMARY KEY (`id`)
11 ) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=latin1;
12
13 INSERT INTO `irn_instructions` (`id`, `instruction`) VALUES
14 (1, 'Iron'),
15 (2, 'Dry Clean Only'),
16 (3, 'Hi-Pressure dry clean'),
17 (4, 'Steam Clean'),
18 (5, 'Decontaminate'),
19 (6, 'Waterproof'),
20 (7, 'Dry Clean'),
21 (8, 'Stach & Fold'),
22 (9, 'Burn'),
23 (10, 'Fumagate'),
24 (11, 'Shred & dispose'),
25 (12, 'Wash, Iron & fold'),
26 (13, 'Steam clean, Iron, fold & pack'),
27 (14, 'Gentle clean'),
28 (15, 'Wipe clean, press and pack'),
29 (16, 'Stroke and fumble');
30
31 CREATE TABLE IF NOT EXISTS `irn_item` (
32 `irn` int(10) unsigned NOT NULL AUTO_INCREMENT,
33 `name` varchar(50) DEFAULT NULL,
34 PRIMARY KEY (`irn`)
35 ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;
36
37 INSERT INTO `irn_item` (`irn`, `name`) VALUES
38 (1, 'Shirt'),
39 (2, 'Trousers'),
40 (3, 'Jacket'),
41 (4, 'Socks'),
42 (5, 'Underpants'),
43 (6, 'Hat'),
44 (7, 'Brassiere'),
45 (8, 'Suspenders'),
46 (9, 'G-string'),
47 (10, 'Thong');
48
49 CREATE TABLE IF NOT EXISTS `irn_service` (
50 `srn` int(10) unsigned NOT NULL AUTO_INCREMENT,
51 `irn` int(10) unsigned NOT NULL DEFAULT '1',
52 `instruction` int(10) unsigned NOT NULL DEFAULT '1',
53 PRIMARY KEY (`srn`),
54 KEY `irn` (`irn`),
55 KEY `instruction` (`instruction`),
56 CONSTRAINT `fk_irn__item` FOREIGN KEY (`irn`) REFERENCES `irn_item` (`irn`) ON DELETE CASCADE ON UPDATE CASCADE,
57 CONSTRAINT `fk_irn__srn_instruction` FOREIGN KEY (`instruction`) REFERENCES `irn_instructions` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
58 ) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=latin1;
59
60
61 INSERT INTO `irn_service` (`srn`, `irn`, `instruction`) VALUES
62 (1, 2, 1),
63 (2, 1, 2),
64 (3, 3, 3),
65 (4, 4, 4),
66 (5, 5, 5),
67 (6, 6, 6),
68 (7, 3, 7),
69 (8, 1, 8),
70 (9, 8, 13),
71 (10, 7, 14),
72 (11, 7, 16);
73
74
75*/
76
77
78 $dbhost = 'localhost';
79 $dbuser = 'root';
80 $dbpwd = 'xxx';
81 $dbname = 'xxx';
82 $db = new mysqli( $dbhost, $dbuser, $dbpwd, $dbname );
83
84
85
86 if( $_SERVER['REQUEST_METHOD']=='POST' && isset( $_POST['action'], $_POST['id'] ) && $_POST['action']=='get_dependant_menu' ){
87
88 ob_clean();
89
90 try{
91 $id=filter_input( INPUT_POST, 'id', FILTER_SANITIZE_STRING );
92
93 if( $id && !empty( $id ) ){
94
95 $html=array();
96
97 $sql='select s.`srn`,i.`instruction` from `irn_service` s
98 join `irn_item` m on m.`irn`=s.irn
99 join `irn_instructions` i on i.`id`=s.`instruction` where s.`irn` = ? order by s.`srn` asc';
100
101 $stmt=$db->prepare( $sql );
102
103 if( $stmt ){
104 $stmt->bind_param( 's', $id );
105 $stmt->execute();
106 $stmt->store_result();
107 $stmt->bind_result( $srn, $instruction );
108
109
110 while( $stmt->fetch() ){
111 $html[]="<option value='{$srn}'>{$instruction}";
112 }
113 $stmt->close();
114 }
115
116 header('Content-Type: text/html');
117 echo implode( PHP_EOL, $html );
118 }
119 }catch( Exception $e ){
120 echo $e->getMessage();
121 }
122 exit();
123 }
124?>
125<!doctype html>
126<html>
127 <head>
128 <title>Dependent / Chained SELECT menus</title>
129 <script>
130 /* AJAX FUNCTION */
131 function ajax(m,u,p,c,o){
132 var xhr=new XMLHttpRequest();
133 xhr.onreadystatechange=function(){
134 if( xhr.readyState==4 && xhr.status==200 )c.call( this, xhr.response, o, xhr.getAllResponseHeaders() );
135 };
136
137 var params=[];
138 for( var n in p )params.push(n+'='+p[n]);
139
140 switch( m.toLowerCase() ){
141 case 'post': p=params.join('&'); break;
142 case 'get': u+='?'+params.join('&'); p=null; break;
143 }
144
145 xhr.open( m.toUpperCase(), u, true );
146 xhr.setRequestHeader('Content-Type','application/x-www-form-urlencoded');
147 xhr.send( p );
148 }
149
150
151 /* AJAX CALLBACK */
152 function createmenu(r,o,h){
153 o.menu.innerHTML=r;
154 }
155
156 function get_nextsibling(n){
157 x=n.nextSibling;
158 while ( x.nodeType!==1 ) x=x.nextSibling;
159 return x;
160 }
161 function get_previoussibling(n){
162 x=n.previousSibling;
163 while ( x.nodeType!==1 ) x=x.previousSibling;
164 return x;
165 }
166
167 /* INLINE EVENT HANDLER */
168 function evtselect(e){
169 try{
170 var el=e.target;
171 if( el.value=='null' || el.value==null )return false;
172
173 var method='post';
174 var url=location.href;
175 var params={
176 'action':'get_dependant_menu',
177 'id':el.value
178 };
179 var td=get_nextsibling( el.parentNode );
180 var oSelect=td.querySelector('select');
181 var opts={
182 menu:oSelect
183 };
184 ajax.call( this, method, url, params, createmenu, opts );
185 }catch( err ){
186 console.log( err );
187 }
188 }
189
190 /* ADD NEW ROW EVENT HANDLER */
191 function bindEvents(){
192
193 var bttn=document.querySelector('input[name="add"]');
194 var tbl=document.querySelector('form#item_sel > table');
195
196 if( bttn && tbl ){
197 bttn.addEventListener('click',function(e){
198 /* get a reference to the first & last row, of class "item", in table */
199 var tr=tbl.querySelectorAll( 'tr.items' )[0];
200 var ref=tbl.querySelector( 'tr.save' );
201
202 /* Create a clone of the entire row - which includes the inline event handlers */
203 var clone=tr.cloneNode( true );
204
205 /* Insert the new row after the last row */
206 tr.parentNode.insertBefore( clone, ref );
207
208 /* Ensure that newly added "service" select menu is empty */
209 clone.querySelector('select[name="item[]"]').value='null';
210 clone.querySelector('select[name="service[]"]').innerHTML='';
211 clone.querySelector('input[name="qty[]"]').value='';
212
213 },{ capture:false, passive:true, once:false } );
214 }
215 }
216 document.addEventListener( 'DOMContentLoaded', bindEvents, false );
217 </script>
218 <style type='text/css' charset='utf-8'>
219 select {padding:1rem;width:300px;}
220 input[type='number'],
221 input[type='submit'],
222 input[type='button']{
223 padding:1rem;
224 margin:0 1rem 0 3px;
225 }
226 button{padding:1rem;}
227 </style>
228 </head>
229 <body>
230 <h1>Chained select menus using basic ajax</h1>
231 <?php
232 if( $_SERVER['REQUEST_METHOD']=='POST' && isset( $_POST['item'], $_POST['service'], $_POST['qty'] ) ){
233
234 /* PROCESS POST REQUEST - FORM SUBMISSION!!! */
235 function pre($s){
236 echo '<pre>',print_r($s,true),'</pre>';
237 }
238 $keys=array_keys( $_POST );
239 $values=array_values( $_POST );
240
241 pre($keys);
242 pre($values);
243
244 }
245 ?>
246 <form method='post' id='item_sel'>
247 <table>
248 <tr class='headers'>
249 <th scope='col'>Item</th>
250 <th scope='col'>Service</th>
251 <th scope='col'>Qty</th>
252 </tr>
253 <tr class='items'>
254 <td>
255 <select name='item[]' class='country' onchange='evtselect(event)'>
256 <option value=null>Please Select
257 <?php
258 $sql='select * from `irn_item` where `irn` in ( select `irn` from `irn_service` ) order by `name`;';
259 $result=$db->query( $sql );
260 $html=array();
261 if( $result ){
262 while( $rs=$result->fetch_object() ){
263 $html[]="<option value='{$rs->irn}'>{$rs->name}";
264 }
265 echo implode( PHP_EOL, $html );
266 }
267 ?>
268 </select>
269 </td>
270 <td><select name='service[]' class='country'></select></td>
271 <td><input type='number' name='qty[]' min=0 max=1000 /></td>
272 </tr>
273 <tr class='save'>
274 <td colspan='3'>
275 <button type='submit' name='btnsave' class='btn btn-default'>
276 <span class='glyphicon glyphicon-save'></span> Save
277 </button>
278 </td>
279 </tr>
280 </table>
281 <input name='add' type='button' class='add-row' value='Add Row' />
282 <input type='submit' value='Submit requirements for processing' />
283 </form>
284 </body>
285</html>