Displaying recursive database relationships using a Tree View

[eluser]Daniel Peraza[/eluser]
Sometimes we face recursive relationships in the databases that our applications use and we want to display the data stored in those relationships in a hierarchical manner. Consider the case of a series of products or articles grouped by categories. The categories may have other sub-categories, thus generating a recursive relationship for which we want to display a kind of tree view like those that desktop applications use.

When you design your database, you will have to include an E-R entity called "CATEGORY", which, when translated to plain SQL, probably will look like:

Code:

CREATE TABLE `CATEGORY`(
   id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
   name VARCHAR(45) NOT NULL,
   PARENT_id INT NULL DEFAULT NULL,
   INDEX(PARENT_id),

   CONSTRAINT FOREIGN KEY(PARENT_id) REFERENCES `CATEGORY`
       ON UPDATE CASCADE # Depends on your implementation
       ON DELETE CASCADE  # Depends on your implementation
);

For this case, I have developed a couple of classes (or libraries in the CI's jargon) which can be uses in conjunction with the Yahoo! User Interface Library (YUI) and its Tree View widget, but it's general enough to serve to other purposes as well (e.g. generating XML markup).

The first class is just called Tree.php. It's a container class which implements an N-ary Tree by means of an array of child nodes. Its task is to store in memory the data retrieved from the database and provide a method for recursiveley walk through the nodes and process them.

The second class is called Tree_menu.php and is the main responsible of doing the hard work. It retrieves the data from the database using a configuration file named tree_menu.php, with the options needed to do that:

Code:

$config['table'] = 'category'; // The name of the DB table to use.
$config['pk'] = 'id'; // The name of the primary key column for the DB table
$config['fk'] = 'PARENT_id'; // The name of the foreign key column for the DB table
$config['text_column'] = 'text'; // The name of the column which data will be used as
                                 // labels for the tree nodes.
$config['toggler_open_class'] = 'toggler_open'; // The CSS class that will be assigned to
                                                // parent nodes (nodes with children)
$config['submenu_class'] = 'sub_menu'; // The CSS class that will be assigned to the UL
                                       // sub-lists elements containing the child nodes.
$config['menu_list_class'] = 'cat_menu'; // The CSS class that will be assigned to the
                                         // whole category menu.

Put the config file in your application/config folder and the two class files in your application/libraries folder. Then, within your controller's constructor load the Tree_Menu class and call

Code:

$this->load->library('tree_menu');
$this->tree_menu->build();

for building the tree; and then use

Code:

$this->tree_menu->print_menu()

to get the HTML markup for displaying the category tree as unordered nested lists. Easy right?

Refer to the YUI page for documentation about the Tree View widget. I'll post that information later when I have time enough to do it.

The Tree_Menu class is designed to work as other CI's libraries like pagination, form validation, etc. It is supposed to be able to receive an array with the configuration settings instead of using a config file, but this feature is currently not working properly. Use the config file method instead, thus, you won't have to call the

Code:

initialize()

method in your controllers constructor (as other CI libraries work as well).

I have used this class in some projects and it suites my needs. I would like to receive some feedback, bug fixes, suggestions, and improvements from the community if possible.

Thanks!

[eluser]TheFuzzy0ne[/eluser]
Sounds fantastic! Would I be able to use this to manage forum categories?

[eluser]Daniel Peraza[/eluser]
Well, I think you may use it to navigate through your forum categories more easily. You can even integrate it with other Javascript/AJAX frameworks and dynamically retrieve topics within a selected category.

[eluser]johnwbaxter[/eluser]
Hmm i think i'll give this a test tomorrow. Nice work.

[eluser]Daniel Peraza[/eluser]
Thanks. That would be great because I am very busy right now and I have not had time enough to test that code thoroughly. I just tested it until worked well enough for me. This is in fact, the idea behind posting my code to the forum: to be able to get some feedback and to integrate other people's ideas into that code.

[eluser]harimada[/eluser]
This is great!...

I have tried it and but have modified it a bit to connect to my treeview oracle database
implimented into CI and it works. Recursive function works well.

Static Output HTML when run example like below:

Customer Lists
Staff List
Students Lists
Vendors Lists
Vendor
My Staf Reports
Total Staf by State
Staf by PTJ
My e-Salary
View Projects
My TASK
SPAN Project
Administration
Wordlist Bilingual Administration


Only problem, i dont know how to make treeview. Already tried YUI but ended puzzled.
Please Guide Me. I am a newbie.
Thanks CI Gurus.

[eluser]harimada[/eluser]
Hi,

Just come back to say thank you all...
Already solve the problem using jQuery plugin library by these guys.
If may i share :

http://bassistance.de/jquery-plugins/jqu...-treeview/

Thanks CI Gurus.

[eluser]Önder ÖZCAN[/eluser]
The attachment does not work, I think CI just blocks working class libraries