Categories > Your tips & tricks >

display data with a tree or hierarchical structure

The forum is closed. Please use Stack Overflow for submitting new questions. Use tags: tinybutstrong , opentbs
By: Skrol29
Date: 2007-06-24
Time: 01:04

display data with a tree or hierarchical structure

Hello,

They are many questions on how to display data with a tree or hierarchical structure using TBS 3.2.0.

First of all I have to recall that hierarchical data can be something quite complicated and only few database systems give you features to manage and display data such a way.
That's why they are several ways of managing it with common database system.
The more common way we met around is to save all items in table and have a Parent field which refers to the id of the parent item in the same table.
The example given under in this post is for that way of saving data.
This way is smart but it makes difficult or expensive to retrieve the whole tree structure.
If you could also save the level of the item, it will be very faster to retrieve the tree and it won't be so complicated to do it with TBS.
In the same spirit, if you could retrieve the tree yourself into a PHP array, then it would be faster and easier to display it with TBS.

Just be inspired by this example to adapt it to your precise case.
In this example, we have a simple table [t_test_tree] with fields (m_id, m_parent, m_title).
The SQL source to build and feed the table is given at the end of this post.

Some technical explanations:
They are two blocks, one for the root items and another one for all subitems. We will use the subblocks feature of TBS to merge child items for each and every subitems. That can be expensive because this means there will be as many SQL queries as they are items in the tree. In order to make the loop, we save the subitem block before the merge, and we re-insert it for each items using an "ondata" function.


HTML:
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title>Test</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>
<body>
<br>
<br>
<table border="1" cellspacing="0" cellpadding="2">
  <tr>
    <td bgcolor="#CCCCCC">Id</td>
    <td bgcolor="#CCCCCC">Title</td>
  </tr>
  <tr>
    <td>[m.m_id;block=tr]</td>
    <td>[m.m_title]
      <table border="1" cellspacing="0" cellpadding="2">
        <tr>
          <td bgcolor="#FFCCFF">Id [sparent;block=table]</td>
          <td bgcolor="#FFCCFF">Title</td>
        </tr>
        <tr>
          <td>[s.m_id;block=tr;bmagnet=table;p1=[m.m_id];ondata=f_ondata_tree]</td>
          <td>[s.m_title][s.subitems;protect=no;htmlconv=no]</td>
        </tr>
      </table>
    </td>
  </tr>
</table>
</body>
</html>

PHP:
<?php

// Connection to the database
$cnx_id = mysql_connect('localhost','root','') ;
if ($cnx_id===False) exit(mysql_error());
mysql_select_db('test',$cnx_id);

include_once("tbs_class_php5.php");

$TBS =& new clsTinyButStrong;
$TBS->LoadTemplate("test.htm");

// Save the source of the Subitem table
$SrcSub = $TBS->GetBlockSource('sparent',false,false);
// Delete tags in the template
$TBS->MergeField('sparent','');

// Merge the root items
$TBS->MergeBlock('m','mysql','SELECT * FROM t_test_tree WHERE m_parent=0 ORDER BY m_id');
// Merge the subitems
$TBS->MergeBlock('s','mysql','SELECT * FROM t_test_tree WHERE m_parent=%p1% ORDER BY m_id');

$TBS->Show();

function f_ondata_tree($BlockName,&$CurrRec,$RecNum) {
    global $SrcSub;
    // Add the source of the Subitem tables as a new field of the current row.
    $CurrRec['subitems'] = str_replace('[m.m_id]',$CurrRec['m_id'],$SrcSub);
}

?>

SQL:
CREATE TABLE t_test_tree (
  m_id int(11) NOT NULL auto_increment,
  m_parent int(11) NOT NULL,
  m_title varchar(50) NOT NULL,
  PRIMARY KEY  (m_id)
);

INSERT INTO t_test_tree (m_id,m_parent,m_title) VALUES ( 1, 0,'Menu 1');
INSERT INTO t_test_tree (m_id,m_parent,m_title) VALUES ( 2, 0,'Menu 2');
INSERT INTO t_test_tree (m_id,m_parent,m_title) VALUES ( 3, 0,'Menu 3');
INSERT INTO t_test_tree (m_id,m_parent,m_title) VALUES ( 4, 0,'Menu 4');

INSERT INTO t_test_tree (m_id,m_parent,m_title) VALUES ( 5, 1,'Item 1.1');
INSERT INTO t_test_tree (m_id,m_parent,m_title) VALUES ( 6, 1,'Item 1.2');
INSERT INTO t_test_tree (m_id,m_parent,m_title) VALUES ( 7, 1,'Item 1.3');
INSERT INTO t_test_tree (m_id,m_parent,m_title) VALUES ( 8, 2,'Item 2.1');
INSERT INTO t_test_tree (m_id,m_parent,m_title) VALUES ( 9, 2,'Item 2.2');
INSERT INTO t_test_tree (m_id,m_parent,m_title) VALUES (10, 2,'Item 2.3');
INSERT INTO t_test_tree (m_id,m_parent,m_title) VALUES (11, 3,'Item 3.1');
INSERT INTO t_test_tree (m_id,m_parent,m_title) VALUES (12, 3,'Item 3.2');
INSERT INTO t_test_tree (m_id,m_parent,m_title) VALUES (13, 3,'Item 3.3');
INSERT INTO t_test_tree (m_id,m_parent,m_title) VALUES (14, 4,'Item 4.1');
INSERT INTO t_test_tree (m_id,m_parent,m_title) VALUES (15, 4,'Item 4.2');
INSERT INTO t_test_tree (m_id,m_parent,m_title) VALUES (16, 4,'Item 4.3');

INSERT INTO t_test_tree (m_id,m_parent,m_title) VALUES (17, 5,'Item 1.1.1');
INSERT INTO t_test_tree (m_id,m_parent,m_title) VALUES (18, 5,'Item 1.1.2');
INSERT INTO t_test_tree (m_id,m_parent,m_title) VALUES (19, 6,'Item 1.2.1');
INSERT INTO t_test_tree (m_id,m_parent,m_title) VALUES (20, 6,'Item 1.2.2');
INSERT INTO t_test_tree (m_id,m_parent,m_title) VALUES (21, 8,'Item 2.1.1');
INSERT INTO t_test_tree (m_id,m_parent,m_title) VALUES (22, 8,'Item 2.1.2');
INSERT INTO t_test_tree (m_id,m_parent,m_title) VALUES (23, 9,'Item 2.2.1');
INSERT INTO t_test_tree (m_id,m_parent,m_title) VALUES (24,11,'Item 3.3.1');
INSERT INTO t_test_tree (m_id,m_parent,m_title) VALUES (25,15,'Item 4.2.1');

INSERT INTO t_test_tree (m_id,m_parent,m_title) VALUES (26,21,'Item 2.1.1.1')
By: Martin
Date: 2007-06-24
Time: 05:33

Re: display data with a tree or hierarchical structure

Thanks a lot Skrol !!!! I test it, and run perfect. ;)
By: Coyote
Date: 2007-06-26
Time: 10:09

Re: display data with a tree or hierarchical structure

Thanks a lot...

works great with <table>...
can you add an example with <ul><li></li></ul>
By: andy
Date: 2007-10-06
Time: 06:48

Re: display data with a tree or hierarchical structure

Could you give an example on <ul><li></li><ul>? Thanks
By: Skrol29
Date: 2007-10-19
Time: 23:20

Re: display data with a tree or hierarchical structure

Hi,

Here is the HTML template for <ul><li> hierachical display:
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title>Test</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>
<body>

  <ul>
    <li>[m.m_title;block=li]
      <ul>
        <li>[sparent;block=ul][s.m_title;block=li;bmagnet=ul;p1=[m.m_id];ondata=f_ondata_tree]
          [s.subitems;protect=no;htmlconv=no]
        </li>
      </ul>
    </li>
  </ul>

</body>
</html>
By: Rromulo
Date: 2008-01-23
Time: 14:20

Re: display data with a tree or hierarchical structure

IS PERFECT !!!!!

I am using a base sql with 474 columns, he was half slow to generate my menu - tree , exists some better form?
By: MBt
Date: 2008-05-17
Time: 01:50

Re: display data with a tree or hierarchical structure

Hello all.
I found my way to do the same using an array instead of multiple queries (I guess it's faster with an array) but when I use this script throughout 'subtpl' the function passed to "ondata" is never called so only fist tree level is displayed.

I tried :
1/ to declare the function in main script
2/ to declare the function in sub script
3/ to use ObjectRef and ~functionName
4/ to use a dummy name for the function (I don't even get an error)

Do you guys have the 5th possible try?
Thanks,
MBt
By: Skrol29
Date: 2008-05-17
Time: 01:52

Re: display data with a tree or hierarchical structure

Hi,
What is the error message you've got ?
By: MBt
Date: 2008-05-17
Time: 11:17

Re: display data with a tree or hierarchical structure

Hi,
I do not get any error message.
did anyone tried to run the templte you gave in this thread as a subtpl?

I'll try to give you my files but I need to clean them up a bit to make it more readable...
MBt
By: MBt
Date: 2008-05-17
Time: 13:04

Re: display data with a tree or hierarchical structure

Hi Skrol29,
I started a new thread on this issue to treat this out of this Tips&Tricks post.
Please reply here : http://www.tinybutstrong.com/forum.php?msg_id=8926
MBt