当前位置： 文江博客话题详情

在父子表单无序列表中打印分层数据php？

发布于 2024-10-05 15:20:03 字数 601 浏览 4 评论 0原文

我在父子层次结构中的 mysql 表中有数据，例如；

|---------+-----------+-------------|
| 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 |
|---------+-----------+-------------|

我需要以父子无序列表格式显示它，例如

 -msg1 
   -msg1-child1
   -msg2-child2
 -msg2
 -msg3
   -msg3-child1

我该怎么做？我需要帮助，特别是如何在表单的层次结构中显示它。

原文

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.

如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论， 你可以免费 注册 一个本站的账号。

评论（2）

最佳男配角 2024-10-12 15:20:03

好的，从后端到前端工作...

您可以从 php 脚本调用单个非递归存储过程 (sproc)，它会为您生成消息层次结构。这种方法的优点是你只需要从 php 到数据库进行SINGLE调用，而如果你使用内联 SQL，那么你将进行与级别一样多的调用（至少） 。另一个优点是，由于它是一个非递归存储过程，因此性能非常好，并且还可以使您的 php 代码保持美观和干净。最后，我必须郑重声明，调用存储过程比任何其他方法更安全、更高效，因为您只需向应用程序用户授予执行权限，并且存储过程需要的数据库往返次数比任何其他方法都要少。其他方法包括参数化查询，单个查询需要至少 2 次调用（其中 1 个用于在数据库中设置查询模板，另一个用于填充参数）

因此，以下是从 MySQL 命令行调用存储过程的方法。

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

好的，现在我们有能力通过简单地使用我们需要的任何起始节点调用我们的存储过程来获取完整或部分消息树，但是我们将如何处理结果集？

在这个例子中，我决定用它生成一个 XML DOM，然后我需要做的就是转换 (XSLT) XML，我们将拥有一个嵌套消息网页。

PHP 脚本

PHP 脚本相当简单，它只是连接到数据库、调用存储过程并循环结果集来构建 XML DOM。请记住，我们只调用数据库一次。

<?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 保存在文件中并在浏览器中打开它，您将能够展开和折叠级别。

<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 呈现网页，也许只是循环结果集并直接呈现消息。我选择这种方法只是为了使我的示例尽可能全面且信息丰富。

MySQL脚本

这是一个完整的脚本，包括表、存储过程和测试数据。

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 路线，如果您这样做，网络上也会有大量示例。

希望这对您有所帮助:)

编辑：

添加了更多数据，以便您拥有多个根消息 (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。

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：

call message_hier(6); -- returns all messages belonging to msg_id = 6

但是，如果您想要所有根的所有消息，那么您可以调用我创建的这个新存储过程，如下所示：

call message_hier_all(); -- returns all messages for all roots.

主要问题是，随着消息表的增长，它将返回大量数据，这就是为什么我专注于一个更具体的存储过程，它只为给定的根获取消息节点或起始 msg_id。

我不会发布新的存储过程代码，因为它实际上与原始代码相同，但您可以在这里找到所有修改：http:// Pastie.org/1339618

您需要进行的最后更改是在 php 脚本中，该脚本现在将调用新的存储过程，如下所示：

//$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();

OK working from the backend towards the front-end...

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)

So here's how you'd call the stored procedure from the MySQL command line.

call message_hier(1);

and here's the resultset it creates.

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

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 ??

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 script

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 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>

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.

MySQL script

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);

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 :)

EDIT:

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

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

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.

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.

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

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

Hope this helps :)

call message_hier_all();
回复 原文
过期以后 2024-10-12 15:20:03
function get_list($parent='NULL', $counter=0, $spaces=""){

    $sql = "SELECT * FROM t1 WHERE parent_id = ".parent;
    $rs[$counter] = mysql_query($sql) or die(mysql_error());
    while($row[$counter] = mysql_fetch_array($rs[$counter])){
        echo $spaces.$row[$counter]['msg']."<br />";
        get_list($row[$counter]['parent_id'], $counter+1, "  ".$spaces);
    }
    mysql_free_result($rs[$counter]);
}

或者，接近那个。

function get_list($parent='NULL', $counter=0, $spaces=""){

    $sql = "SELECT * FROM t1 WHERE parent_id = ".parent;
    $rs[$counter] = mysql_query($sql) or die(mysql_error());
    while($row[$counter] = mysql_fetch_array($rs[$counter])){
        echo $spaces.$row[$counter]['msg']."<br />";
        get_list($row[$counter]['parent_id'], $counter+1, "  ".$spaces);
    }
    mysql_free_result($rs[$counter]);
}

Or, close to that.

回复 原文
~没有更多了~

关于作者

一绘本一梦想

暂无简介

0 文章
0 评论
24 人气
发私信

相关话题

更多

热门标签

操作系统 程序设计 IT运维 Linux系统管理 JavaScript 服务器应用 solaris C/C++ PHP Shell BSD Vue.js aix Oracle Python HTML 系统管理 HTML5 CSS 前端
更多

推荐作者

lorenzathorton8

文章 0 评论 0

Zero

文章 0 评论 0

萧瑟寒风

文章 0 评论 0

mylayout

文章 0 评论 0

tkewei

文章 0 评论 0

17818769742

文章 0 评论 0

更多

友情链接

文江博客
    我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站，即表示您同意使用 Cookies 和您的相关数据。
    原文