TyssZyDY

1<?php
2
3header("Content-type: text/xml");
4
5$conn = new mysqli("localhost", "foo_dbo", "pass", "foo_db", 3306);
6
7// one non-recursive db call to get the tree
8
9$result = $conn->query(sprintf("call department_hier(%d,%d)", 2,1));
10
11$xml = new DomDocument;
12$xpath = new DOMXpath($xml);
13
14$dept = $xml->createElement("department");
15$xml->appendChild($dept);
16
17// loop and build the DOM
18
19while($row = $result->fetch_assoc()){
20
21    $staff = $xml->createElement("staff");
22    // foreach($row as $col => $val) $staff->setAttribute($col, $val); 
23
24    $staff->setAttribute("staff_id", $row["staff_id"]); 
25    $staff->setAttribute("name", $row["name"]); 
26    $staff->setAttribute("parent_staff_id", $row["parent_staff_id"]); 
27
28    if(is_null($row["parent_staff_id"])){
29        $dept->setAttribute("dept_id", $row["dept_id"]); 
30        $dept->setAttribute("department_name", $row["department_name"]); 
31        $dept->appendChild($staff);
32    }
33    else{
34        $qry = sprintf("//*[@staff_id = '%d']", $row["parent_staff_id"]);
35        $parent = $xpath->query($qry)->item(0);
36        if(!is_null($parent)) $parent->appendChild($staff);
37    }
38}
39$result->close();
40$conn->close();
41
42echo $xml->saveXML();
43?>
44	
45<?php
46
47header("Content-type: text/xml");
48
49$conn = new mysqli("localhost", "foo_dbo", "pass", "foo_db", 3306);
50
51// one non-recursive db call to get the tree
52
53$result = $conn->query(sprintf("call department_hier(%d,%d)", 2,1));
54
55$xml = new DomDocument;
56$xpath = new DOMXpath($xml);
57
58$dept = $xml->createElement("department");
59$xml->appendChild($dept);
60
61// loop and build the DOM
62
63while($row = $result->fetch_assoc()){
64
65    $staff = $xml->createElement("staff");
66    // foreach($row as $col => $val) $staff->setAttribute($col, $val); 
67
68    $staff->setAttribute("staff_id", $row["staff_id"]); 
69    $staff->setAttribute("name", $row["name"]); 
70    $staff->setAttribute("parent_staff_id", $row["parent_staff_id"]); 
71
72    if(is_null($row["parent_staff_id"])){
73        $dept->setAttribute("dept_id", $row["dept_id"]); 
74        $dept->setAttribute("department_name", $row["department_name"]); 
75        $dept->appendChild($staff);
76    }
77    else{
78        $qry = sprintf("//*[@staff_id = '%d']", $row["parent_staff_id"]);
79        $parent = $xpath->query($qry)->item(0);
80        if(!is_null($parent)) $parent->appendChild($staff);
81    }
82}
83$result->close();
84$conn->close();
85
86echo $xml->saveXML();
87?>
88	
89<?php
90
91header("Content-type: text/xml");
92
93$conn = new mysqli("localhost", "foo_dbo", "pass", "foo_db", 3306);
94
95// one non-recursive db call to get the tree
96
97$result = $conn->query(sprintf("call department_hier(%d,%d)", 2,1));
98
99$xml = new DomDocument;
100$xpath = new DOMXpath($xml);
101
102$dept = $xml->createElement("department");
103$xml->appendChild($dept);
104
105// loop and build the DOM
106
107while($row = $result->fetch_assoc()){
108
109    $staff = $xml->createElement("staff");
110    // foreach($row as $col => $val) $staff->setAttribute($col, $val); 
111
112    $staff->setAttribute("staff_id", $row["staff_id"]); 
113    $staff->setAttribute("name", $row["name"]); 
114    $staff->setAttribute("parent_staff_id", $row["parent_staff_id"]); 
115
116    if(is_null($row["parent_staff_id"])){
117        $dept->setAttribute("dept_id", $row["dept_id"]); 
118        $dept->setAttribute("department_name", $row["department_name"]); 
119        $dept->appendChild($staff);
120    }
121    else{
122        $qry = sprintf("//*[@staff_id = '%d']", $row["parent_staff_id"]);
123        $parent = $xpath->query($qry)->item(0);
124        if(!is_null($parent)) $parent->appendChild($staff);
125    }
126}
127$result->close();
128$conn->close();
129
130echo $xml->saveXML();
131?>
132	
133<?php
134
135header("Content-type: text/xml");
136
137$conn = new mysqli("localhost", "foo_dbo", "pass", "foo_db", 3306);
138
139// one non-recursive db call to get the tree
140
141$result = $conn->query(sprintf("call department_hier(%d,%d)", 2,1));
142
143$xml = new DomDocument;
144$xpath = new DOMXpath($xml);
145
146$dept = $xml->createElement("department");
147$xml->appendChild($dept);
148
149// loop and build the DOM
150
151while($row = $result->fetch_assoc()){
152
153    $staff = $xml->createElement("staff");
154    // foreach($row as $col => $val) $staff->setAttribute($col, $val); 
155
156    $staff->setAttribute("staff_id", $row["staff_id"]); 
157    $staff->setAttribute("name", $row["name"]); 
158    $staff->setAttribute("parent_staff_id", $row["parent_staff_id"]); 
159
160    if(is_null($row["parent_staff_id"])){
161        $dept->setAttribute("dept_id", $row["dept_id"]); 
162        $dept->setAttribute("department_name", $row["department_name"]); 
163        $dept->appendChild($staff);
164    }
165    else{
166        $qry = sprintf("//*[@staff_id = '%d']", $row["parent_staff_id"]);
167        $parent = $xpath->query($qry)->item(0);
168        if(!is_null($parent)) $parent->appendChild($staff);
169    }
170}
171$result->close();
172$conn->close();
173
174echo $xml->saveXML();
175?>
176	
177<?php
178
179header("Content-type: text/xml");
180
181$conn = new mysqli("localhost", "foo_dbo", "pass", "foo_db", 3306);
182
183// one non-recursive db call to get the tree
184
185$result = $conn->query(sprintf("call department_hier(%d,%d)", 2,1));
186
187$xml = new DomDocument;
188$xpath = new DOMXpath($xml);
189
190$dept = $xml->createElement("department");
191$xml->appendChild($dept);
192
193// loop and build the DOM
194
195while($row = $result->fetch_assoc()){
196
197    $staff = $xml->createElement("staff");
198    // foreach($row as $col => $val) $staff->setAttribute($col, $val); 
199
200    $staff->setAttribute("staff_id", $row["staff_id"]); 
201    $staff->setAttribute("name", $row["name"]); 
202    $staff->setAttribute("parent_staff_id", $row["parent_staff_id"]); 
203
204    if(is_null($row["parent_staff_id"])){
205        $dept->setAttribute("dept_id", $row["dept_id"]); 
206        $dept->setAttribute("department_name", $row["department_name"]); 
207        $dept->appendChild($staff);
208    }
209    else{
210        $qry = sprintf("//*[@staff_id = '%d']", $row["parent_staff_id"]);
211        $parent = $xpath->query($qry)->item(0);
212        if(!is_null($parent)) $parent->appendChild($staff);
213    }
214}
215$result->close();
216$conn->close();
217
218echo $xml->saveXML();
219?>
220	
221<?php
222
223header("Content-type: text/xml");
224
225$conn = new mysqli("localhost", "foo_dbo", "pass", "foo_db", 3306);
226
227// one non-recursive db call to get the tree
228
229$result = $conn->query(sprintf("call department_hier(%d,%d)", 2,1));
230
231$xml = new DomDocument;
232$xpath = new DOMXpath($xml);
233
234$dept = $xml->createElement("department");
235$xml->appendChild($dept);
236
237// loop and build the DOM
238
239while($row = $result->fetch_assoc()){
240
241    $staff = $xml->createElement("staff");
242    // foreach($row as $col => $val) $staff->setAttribute($col, $val); 
243
244    $staff->setAttribute("staff_id", $row["staff_id"]); 
245    $staff->setAttribute("name", $row["name"]); 
246    $staff->setAttribute("parent_staff_id", $row["parent_staff_id"]); 
247
248    if(is_null($row["parent_staff_id"])){
249        $dept->setAttribute("dept_id", $row["dept_id"]); 
250        $dept->setAttribute("department_name", $row["department_name"]); 
251        $dept->appendChild($staff);
252    }
253    else{
254        $qry = sprintf("//*[@staff_id = '%d']", $row["parent_staff_id"]);
255        $parent = $xpath->query($qry)->item(0);
256        if(!is_null($parent)) $parent->appendChild($staff);
257    }
258}
259$result->close();
260$conn->close();
261
262echo $xml->saveXML();
263?>
264	
265<department dept_id="2" department_name="Mathematics">
266    <staff staff_id="1" name="f00" parent_staff_id="">
267        <staff staff_id="5" name="gamma" parent_staff_id="1"/>
268        <staff staff_id="6" name="delta" parent_staff_id="1">
269            <staff staff_id="7" name="zeta" parent_staff_id="6">
270                <staff staff_id="2" name="bar" parent_staff_id="7"/>
271                <staff staff_id="8" name="theta" parent_staff_id="7"/>
272            </staff>
273        </staff>
274    </staff>
275</department>
276	
277-- TABLES
278
279drop table if exists staff;
280create table staff
281(
282staff_id smallint unsigned not null auto_increment primary key,
283name varchar(255) not null
284)
285engine = innodb;
286
287drop table if exists departments;
288create table departments
289(
290dept_id tinyint unsigned not null auto_increment primary key,
291name varchar(255) unique not null
292)
293engine = innodb;
294
295drop table if exists department_staff;
296create table department_staff
297(
298dept_id tinyint unsigned not null,
299staff_id smallint unsigned not null,
300parent_staff_id smallint unsigned null,
301primary key (dept_id, staff_id),
302key (staff_id),
303key (parent_staff_id)
304)
305engine = innodb;
306
307-- STORED PROCEDURES
308
309drop procedure if exists department_hier;
310
311delimiter #
312
313create procedure department_hier
314(
315in p_dept_id tinyint unsigned,
316in p_staff_id smallint unsigned
317)
318begin
319
320declare v_done tinyint unsigned default 0;
321declare v_dpth smallint unsigned default 0;
322
323create temporary table hier(
324 dept_id tinyint unsigned,
325 parent_staff_id smallint unsigned, 
326 staff_id smallint unsigned, 
327 depth smallint unsigned
328)engine = memory;
329
330insert into hier select dept_id, parent_staff_id, staff_id, v_dpth from department_staff 
331    where dept_id = p_dept_id and staff_id = p_staff_id;
332
333/* http://dev.mysql.com/doc/refman/5.0/en/temporary-table-problems.html */
334
335create temporary table tmp engine=memory select * from hier;
336
337while not v_done do
338
339    if exists( select 1 from department_staff e 
340            inner join hier on e.dept_id = hier.dept_id and e.parent_staff_id = hier.staff_id and hier.depth = v_dpth) then
341
342        insert into hier select e.dept_id, e.parent_staff_id, e.staff_id, v_dpth + 1 from department_staff e 
343            inner join tmp on e.dept_id = tmp.dept_id and e.parent_staff_id = tmp.staff_id and tmp.depth = v_dpth;
344
345        set v_dpth = v_dpth + 1;            
346
347        truncate table tmp;
348        insert into tmp select * from hier where depth = v_dpth;
349
350    else
351        set v_done = 1;
352    end if;
353
354end while;
355
356select 
357 hier.dept_id,
358 d.name as department_name,
359 s.staff_id,
360 s.name,
361 p.staff_id as parent_staff_id,
362 p.name as parent_name,
363 hier.depth
364from 
365 hier
366inner join departments d on hier.dept_id = d.dept_id
367inner join staff s on hier.staff_id = s.staff_id
368left outer join staff p on hier.parent_staff_id = p.staff_id;
369
370drop temporary table if exists hier;
371drop temporary table if exists tmp;
372
373end #
374
375delimiter ;
376
377-- TEST DATA
378
379insert into staff (name) values 
380    ('f00'),('bar'),('alpha'),('beta'),('gamma'),('delta'),('zeta'),('theta');
381
382insert into departments (name) values
383 ('Computing'),('Mathematics'),('English'),('Engineering'),('Law'),('Music');
384
385insert into department_staff (dept_id, staff_id, parent_staff_id) values
386(1,1,null), 
387    (1,2,1), 
388    (1,3,1), 
389        (1,4,3),
390            (1,7,4),
391(2,1,null), 
392    (2,5,1), 
393    (2,6,1), 
394        (2,7,6),
395            (2,8,7),
396            (2,2,7);
397
398-- TESTING (call this sproc from your php)
399
400call department_hier(1,1);
401
402call department_hier(2,1);