Print hierachical data in a parent child form unor

Question

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.

Answer 1

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, "&nbsp;&nbsp;".$spaces);
    }
    mysql_free_result($rs[$counter]);
}

Or, close to that.

Answer 2

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