在父子窗体中无序列表php打印层次数据? [英] Print hierachical data in a parent child form unordered list php?
问题描述
我在mysql表中的父子层次结构中有数据；
I have data in mysql table in a parent child hierarchy like;
|---------+-----------+-------------|
| msg_id | parent_id | msg |
|---------+-----------+-------------|
| 1 | NULL | msg1 |
| 2 | NULL | msg2 |
| 3 | NULL | msg3 |
| 4 | 1 | msg1_child1 |
| 5 | 1 | msg1_child2 |
| 6 | 3 | msg3_child1 |
|---------+-----------+-------------|
我需要以父子无序列表格式显示它，例如
I need to display it in a parent-child unordered list format like
-msg1
-msg1-child1
-msg2-child2
-msg2
-msg3
-msg3-child1
我该怎么做?我需要帮助，尤其是如何在表单的层次结构中显示它.
How do I do it? I need help especially how could I display it in a hierarchy on a form.
推荐答案
从后端到前端都可以正常工作...
OK working from the backend towards the front-end...
您可以从php脚本调用一个非递归存储过程(sproc)，该过程为您生成消息层次结构.这种方法的优点是您只需要从php到数据库进行 SINGLE 调用，而如果使用内联SQL，则将进行尽可能多的调用(至少要有多个级别) .另一个优点是，由于它是一种非递归的存储程序，因此性能极佳，并且还可以使您的php代码保持整洁.最后，为了记录起见，我要说的是，调用存储过程比任何其他方法都更加安全和高效，因为您只需要授予应用用户的执行权限，并且存储过程到数据库的往返行程比任何其他方法都要少其他方法包括参数化查询，该查询至少需要2个调用才能进行单个查询(一种用于在db中设置查询模板，另一种用于填充参数)
You could call a single non recursive stored procedure (sproc) from your php script which generates the message hierarchy for you. The advantage of this approach is you only need to make a SINGLE call from php to your database whereas if you use inline SQL then you'll be making as many calls as there are levels (at a minimum). Another advatange is that as it's a non recursive sproc it's extremely performant and it also keeps your php code nice and clean. Finally, and I have to say this for the record, that calling stored procedures is more secure and more efficient than any other method because you only need to GRANT execute permissions to your app user and stored procedures require less round trips to the database than any other methods including parameterised queries which require at least 2 calls for a single query (1 to setup the query template in the db, the other to populate the params)
这就是从MySQL命令行调用存储过程的方式.
So here's how you'd call the stored procedure from the MySQL command line.
call message_hier(1);
这是它创建的结果集.
msg_id emp_msg parent_msg_id parent_msg depth
====== ======= ============= ========== =====
1 msg 1 NULL NULL 0
2 msg 1-1 1 msg 1 1
3 msg 1-2 1 msg 1 1
4 msg 1-2-1 3 msg 1-2 2
5 msg 1-2-2 3 msg 1-2 2
6 msg 1-2-2-1 5 msg 1-2-2 3
7 msg 1-2-2-1-1 6 msg 1-2-2-1 4
8 msg 1-2-2-1-2 6 msg 1-2-2-1 4
好吧，现在我们可以通过简单地用所需的任何起始节点调用sproc来获取完整或部分消息树的功能，但是我们将如何处理结果集??
Ok, so now we have a the ability to fetch a full or partial message tree by simply calling our sproc with whatever starting node we require but what are we going to do with the resultset ??
在这个示例中，我已经决定要使用它生成一个XML DOM，然后要做的就是对XML进行转换(XSLT)，我们将拥有一个嵌套的消息网页.
Well in this example I've decided we're going to generate an XML DOM with it, then all I need to do is transform (XSLT) the XML and we'll have a nested messages web page.
php脚本非常简单，它仅连接到数据库，调用sproc并循环结果集以构建XML DOM.请记住，我们只调用一次数据库.
The php script is fairly simple, it just connects to the database, calls the sproc and loops the resultset to build the XML DOM. Remember we're only calling into the db once.
<?php
// i am using the resultset to build an XML DOM but you can do whatever you like with it !
header("Content-type: text/xml");
$conn = new mysqli("localhost", "foo_dbo", "pass", "foo_db", 3306);
// one non-recursive db call to get the message tree !
$result = $conn->query(sprintf("call message_hier(%d)", 1));
$xml = new DomDocument;
$xpath = new DOMXpath($xml);
$msgs = $xml->createElement("messages");
$xml->appendChild($msgs);
// loop and build the DOM
while($row = $result->fetch_assoc()){
$msg = $xml->createElement("message");
foreach($row as $col => $val) $msg->setAttribute($col, $val);
if(is_null($row["parent_msg_id"])){
$msgs->appendChild($msg);
}
else{
$qry = sprintf("//*[@msg_id = '%d']", $row["parent_msg_id"]);
$parent = $xpath->query($qry)->item(0);
if(!is_null($parent)) $parent->appendChild($msg);
}
}
$result->close();
$conn->close();
echo $xml->saveXML();
?>
XML输出
这是php脚本生成的XML.如果将此XML保存在文件中并在浏览器中打开，则可以展开和折叠级别.
XML output
This is the XML that the php script generates. If you save this XML in a file and open it in your browser you'll be able to expand and collapse the levels.
<messages>
<message msg_id="1" emp_msg="msg 1" parent_msg_id="" parent_msg="" depth="0">
<message msg_id="2" emp_msg="msg 1-1" parent_msg_id="1" parent_msg="msg 1" depth="1"/>
<message msg_id="3" emp_msg="msg 1-2" parent_msg_id="1" parent_msg="msg 1" depth="1">
<message msg_id="4" emp_msg="msg 1-2-1" parent_msg_id="3" parent_msg="msg 1-2" depth="2"/>
<message msg_id="5" emp_msg="msg 1-2-2" parent_msg_id="3" parent_msg="msg 1-2" depth="2">
<message msg_id="6" emp_msg="msg 1-2-2-1" parent_msg_id="5" parent_msg="msg 1-2-2" depth="3">
<message msg_id="7" emp_msg="msg 1-2-2-1-1" parent_msg_id="6" parent_msg="msg 1-2-2-1" depth="4"/>
<message msg_id="8" emp_msg="msg 1-2-2-1-2" parent_msg_id="6" parent_msg="msg 1-2-2-1" depth="4"/>
</message>
</message>
</message>
</message>
</messages>
现在，如果您愿意，您可以放弃构建XML DOM并使用XSL呈现网页，并且可能只是循环结果集并直接呈现消息.我只是选择了这种方法，以使我的示例尽可能全面而有用.
Now you could forego building the XML DOM and using XSL to render a web page if you wish and perhaps just loop the resultset and render the messages directly. I've simply chosen this method to make my example as comprehensive and informative as possible.
这是一个完整的脚本，包括表，存储过程和测试数据.
This is a complete script including tables, sprocs and test data.
drop table if exists messages;
create table messages
(
msg_id smallint unsigned not null auto_increment primary key,
msg varchar(255) not null,
parent_msg_id smallint unsigned null,
key (parent_msg_id)
)
engine = innodb;
insert into messages (msg, parent_msg_id) values
('msg 1',null),
('msg 1-1',1),
('msg 1-2',1),
('msg 1-2-1',3),
('msg 1-2-2',3),
('msg 1-2-2-1',5),
('msg 1-2-2-1-1',6),
('msg 1-2-2-1-2',6);
drop procedure if exists message_hier;
delimiter #
create procedure message_hier
(
in p_msg_id smallint unsigned
)
begin
declare v_done tinyint unsigned default(0);
declare v_dpth smallint unsigned default(0);
create temporary table hier(
parent_msg_id smallint unsigned,
msg_id smallint unsigned,
depth smallint unsigned
)engine = memory;
insert into hier select parent_msg_id, msg_id, v_dpth from messages where msg_id = p_msg_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 messages e inner join hier on e.parent_msg_id = hier.msg_id and hier.depth = v_dpth) then
insert into hier select e.parent_msg_id, e.msg_id, v_dpth + 1
from messages e inner join tmp on e.parent_msg_id = tmp.msg_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
m.msg_id,
m.msg as emp_msg,
p.msg_id as parent_msg_id,
p.msg as parent_msg,
hier.depth
from
hier
inner join messages m on hier.msg_id = m.msg_id
left outer join messages p on hier.parent_msg_id = p.msg_id;
drop temporary table if exists hier;
drop temporary table if exists tmp;
end #
delimiter ;
-- call this sproc from your php
call message_hier(1);
此答案的完整来源可以在这里找到: http://pastie.org/1336407 .正如您已经注意到的那样，我已经省略了XSLT，但是您可能不会采用XML路由，而且如果这样做的话，网络上还会有很多示例.
The full source for this answer can be found here : http://pastie.org/1336407. As you'll have noted already I've omitted the XSLT but you probably wont go the XML route and if you do there are heaps of examples on the web.
希望这对您有所帮助:)
Hope you find this helpful :)
添加了更多数据，因此您拥有多个根消息(msg_ids 1,9,14).
Added a little more data so you have more than one root message (msg_ids 1,9,14).
truncate table messages;
insert into messages (msg, parent_msg_id) values
('msg 1',null), -- msg_id = 1
('msg 1-1',1),
('msg 1-2',1),
('msg 1-2-1',3),
('msg 1-2-2',3),
('msg 1-2-2-1',5),
('msg 1-2-2-1-1',6),
('msg 1-2-2-1-2',6),
('msg 2',null), -- msg_id = 9
('msg 2-1',9),
('msg 2-2',9),
('msg 2-3',9),
('msg 2-3-1',12),
('msg 3',null); -- msg_id = 14
现在，如果只想获取特定于根节点的消息(起始消息)，则可以调用原始存储过程，并传入所需根的起始msg_id.使用上面的新数据将是msg_ids 1,9,14.
Now if you want to just get the messages that are specific to a root node (starting message) you can call the original stored procedure passing in the starting msg_id of the root you require. Using the new data above that would be msg_ids 1,9,14.
call message_hier(1); -- returns all messages belonging to msg_id = 1
call message_hier(9); -- returns all messages belonging to msg_id = 9
call message_hier(14); -- returns all messages belonging to msg_id = 14
您可以传递任何您喜欢的msg_id，因此，如果我希望所有消息在msg 1-2-2-1以下，那么您将传递msg_id = 6:
you can pass in any msg_id you like so if I want all of the messages below msg 1-2-2-1 then you would pass in msg_id = 6:
call message_hier(6); -- returns all messages belonging to msg_id = 6
但是，如果您希望所有消息都来自所有根，那么可以调用我创建的这个新存储过程，如下所示:
However, if you want all of the messages for all of the roots then you can call this new sproc I've created as follows:
call message_hier_all(); -- returns all messages for all roots.
主要问题是随着您的消息表的增长，它将返回大量数据，这就是为什么我专注于一个更特定的sproc的原因，该过程仅获取给定根节点的消息或启动msg_id.
The main problem with this is as your message table grows it's going to be returning lots of data which is why I was focusing on a more specific sproc that only fetched messages for a given root node or starting msg_id.
我不会发布新的sproc代码，因为它实际上与原始代码相同，但是您可以在此处找到所有的修订: http ://pastie.org/1339618
I wont post the new sproc code as it is virtually the same as the original but you can find all the amendments here : http://pastie.org/1339618
您最后需要做的更改是在php脚本中，该脚本现在将按如下所示调用新的sproc:
The final change you'll need to make is in the php script which will now call the new sproc as follows:
//$result = $conn->query(sprintf("call message_hier(%d)", 1)); // recommended call
$result = $conn->query("call message_hier_all()"); // new sproc call
希望这会有所帮助:)
call message_hier_all();
这篇关于在父子窗体中无序列表php打印层次数据?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
