mysql数据抽取_MySQL层次结构数据抽取

我更喜欢邻接表的方式。下面的示例使用一个非递归存储过程返回一个树/子树,然后我将其转换为一个XML DOM,但是您可以对resultset执行任何您喜欢的操作。记住,这是从PHP到MySQL的一次调用,而且邻接列表更易于管理。

菲律宾比索

header("Content-type: text/xml");

$conn = new mysqli("localhost", "foo_dbo", "pass", "foo_db", 3306);

// one non-recursive db call to get the tree

$result = $conn->query(sprintf("call department_hier(%d,%d)", 2,1));

$xml = new DomDocument;

$xpath = new DOMXpath($xml);

$dept = $xml->createElement("department");

$xml->appendChild($dept);

// loop and build the DOM

while($row = $result->fetch_assoc()){

$staff = $xml->createElement("staff");

// foreach($row as $col => $val) $staff->setAttribute($col, $val);

$staff->setAttribute("staff_id", $row["staff_id"]);

$staff->setAttribute("name", $row["name"]);

$staff->setAttribute("parent_staff_id", $row["parent_staff_id"]);

if(is_null($row["parent_staff_id"])){

$dept->setAttribute("dept_id", $row["dept_id"]);

$dept->setAttribute("department_name", $row["department_name"]);

$dept->appendChild($staff);

}

else{

$qry = sprintf("//*[@staff_id = '%d']", $row["parent_staff_id"]);

$parent = $xpath->query($qry)->item(0);

if(!is_null($parent)) $parent->appendChild($staff);

}

}

$result->close();

$conn->close();

echo $xml->saveXML();

?>

XML输出

SQL资料

-- TABLES

drop table if exists staff;

create table staff

(

staff_id smallint unsigned not null auto_increment primary key,

name varchar(255) not null

)

engine = innodb;

drop table if exists departments;

create table departments

(

dept_id tinyint unsigned not null auto_increment primary key,

name varchar(255) unique not null

)

engine = innodb;

drop table if exists department_staff;

create table department_staff

(

dept_id tinyint unsigned not null,

staff_id smallint unsigned not null,

parent_staff_id smallint unsigned null,

primary key (dept_id, staff_id),

key (staff_id),

key (parent_staff_id)

)

engine = innodb;

-- STORED PROCEDURES

drop procedure if exists department_hier;

delimiter #

create procedure department_hier

(

in p_dept_id tinyint unsigned,

in p_staff_id smallint unsigned

)

begin

declare v_done tinyint unsigned default 0;

declare v_dpth smallint unsigned default 0;

create temporary table hier(

dept_id tinyint unsigned,

parent_staff_id smallint unsigned,

staff_id smallint unsigned,

depth smallint unsigned

)engine = memory;

insert into hier select dept_id, parent_staff_id, staff_id, v_dpth from department_staff

where dept_id = p_dept_id and staff_id = p_staff_id;

/* http://dev.mysql.com/doc/refman/5.0/en/temporary-table-problems.html */

create temporary table tmp engine=memory select * from hier;

while not v_done do

if exists( select 1 from department_staff e

inner join hier on e.dept_id = hier.dept_id and e.parent_staff_id = hier.staff_id and hier.depth = v_dpth) then

insert into hier select e.dept_id, e.parent_staff_id, e.staff_id, v_dpth + 1 from department_staff e

inner join tmp on e.dept_id = tmp.dept_id and e.parent_staff_id = tmp.staff_id and tmp.depth = v_dpth;

set v_dpth = v_dpth + 1;

truncate table tmp;

insert into tmp select * from hier where depth = v_dpth;

else

set v_done = 1;

end if;

end while;

select

hier.dept_id,

d.name as department_name,

s.staff_id,

s.name,

p.staff_id as parent_staff_id,

p.name as parent_name,

hier.depth

from

hier

inner join departments d on hier.dept_id = d.dept_id

inner join staff s on hier.staff_id = s.staff_id

left outer join staff p on hier.parent_staff_id = p.staff_id;

drop temporary table if exists hier;

drop temporary table if exists tmp;

end #

delimiter ;

-- TEST DATA

insert into staff (name) values

('f00'),('bar'),('alpha'),('beta'),('gamma'),('delta'),('zeta'),('theta');

insert into departments (name) values

('Computing'),('Mathematics'),('English'),('Engineering'),('Law'),('Music');

insert into department_staff (dept_id, staff_id, parent_staff_id) values

(1,1,null),

(1,2,1),

(1,3,1),

(1,4,3),

(1,7,4),

(2,1,null),

(2,5,1),

(2,6,1),

(2,7,6),

(2,8,7),

(2,2,7);

-- TESTING (call this sproc from your php)

call department_hier(1,1);

call department_hier(2,1);