Le Blog de Pierre

This article is about displaying a set of nested XHTML unordered lists starting from an Oracle database hierarchy made of a parent/id relationship.

I’ve had to browse an infinite quantity of articles to gather the necessary information to be able to code this. It is in no way the result of my own work but rather a compilation of some others ideas and scripts.
I take the opportunity to thank them all for their advices and remarks.

I haven’t taken the time to adapt this to MySQL because at the time of writing this MySQL doesn’t handle the “CONNECT BY” hierarchical queries but I’m sure some of you will easily hack that script and adapt it.

I’ll assume that:

• You have an Oracle database up and running (this example has been implemented on Oracle XE the free database edition from Oracle)
• You have a web server (mine is Apache on Fedora Linux) running with PHP and everything set up correctly to talk to the database.
• You know what I’m talking about when you read the title.

1.  Creating the table:

CREATE TABLE hierarchy (
 id           NUMBER
 , parent       NUMBER NULL
 , name         VARCHAR2(255)
 , child_order  NUMBER NOT NULL
 , CONSTRAINT hierarchy_pk_id PRIMARY KEY (id)
 , CONSTRAINT hierarchy_uq_child UNIQUE (parent,child_order)
);
/
CREATE INDEX hierarchy_ix_parent ON hierarchy(parent);
/

The field “id” is the primary key.
The field “parent” contains the id of the node to which this item belongs to.
The field “name” is the name of the node (doh!).
The field “child_order” is used to be able to order the children in the hierarchy (alphabetical order is not always convenient).

We then create the CONSTRAINT to define the primary key the we create a unique index to avoid any duplicate “child_order” under the same “parent”.

Finally an index is created on the “parent” column. Take a look at the explain plan and you’ll understand

2. Inserting some data:

INSERT INTO hierarchy (id,parent,name,child_order) VALUES (1,NULL,'Sciences',1);
INSERT INTO hierarchy (id,parent,name,child_order) VALUES (2,1,'Mathematics',1);
INSERT INTO hierarchy (id,parent,name,child_order) VALUES (3,1,'Chemistry',2);
INSERT INTO hierarchy (id,parent,name,child_order) VALUES (4,1,'Biology',3);
INSERT INTO hierarchy (id,parent,name,child_order) VALUES (5,1,'Physics',4);
INSERT INTO hierarchy (id,parent,name,child_order) VALUES (6,2,'Algebra',1);
INSERT INTO hierarchy (id,parent,name,child_order) VALUES (7,2,'Geometry',2);
INSERT INTO hierarchy (id,parent,name,child_order) VALUES (8,2,'Analysis',3);
INSERT INTO hierarchy (id,parent,name,child_order) VALUES (9,3,'Analytical Chemistry',1);
INSERT INTO hierarchy (id,parent,name,child_order) VALUES (10,3,'Inorganic Chemistry',2);
INSERT INTO hierarchy (id,parent,name,child_order) VALUES (11,3,'Organic Chemistry',3);
INSERT INTO hierarchy (id,parent,name,child_order) VALUES (12,4,'Botany',1);
INSERT INTO hierarchy (id,parent,name,child_order) VALUES (13,4,'Zoology',2);
INSERT INTO hierarchy (id,parent,name,child_order) VALUES (14,4,'Virology',3);
INSERT INTO hierarchy (id,parent,name,child_order) VALUES (15,5,'Mechanics',1);
INSERT INTO hierarchy (id,parent,name,child_order) VALUES (16,5,'Thermodynamics',2);
INSERT INTO hierarchy (id,parent,name,child_order) VALUES (17,5,'Optics',3);
INSERT INTO hierarchy (id,parent,name,child_order) VALUES (18,5,'Electricity and Magnetism',4);
/
COMMIT;
/

Of course, you might want to create a sequence and a trigger on the table but you’ll loose the visibility and/or the control of your hierarchy when performing bulk inserts.
I have done the inserts without the sequence and the trigger but I have created them later.

3. Testing the structure of the hierarchy

SELECT LPAD('   ',12*(level-1)) || TO_CHAR(child_order) || ' - ' || name s
FROM hierarchy
START WITH parent IS NULL
CONNECT BY PRIOR id = parent
ORDER SIBLINGS BY child_order;
/

This query outputs sort of a “tree” that will allow you to check if the data inserted is correct.

I personally use Oracle SQL developer which is a great tool if (like me) you can’t afford a license for Quest Software’s TOAD.
Toad is a great tool and I’ve been using it for many years professionally but SQL developer does more or less the same job at no cost.

Nothing special here but more info in the documentation from Oracle.

4. Deleting a node and at the same time dropping all its siblings.

For maintenance purpose, you might be interested in deleting a node and all its siblings.
Here is the solution… A radical solution because it will delete anything below the dropped node without a warning and without being able to rollback (once committed) unless you have a valid export of your hierarchy table.

So the solution is to alter the table and to add a self referring FOREIGN KEY with ON DELETE CASCADE.

ALTER TABLE hierarchy ADD CONSTRAINT hierarchy_fk_parent FOREIGN KEY (parent) REFERENCES hierarchy(id) ON DELETE CASCADE;

So, be aware that with this technique if you delete the row with the ID=1… You’ll empty your table!  To be forewarned is to be forearmed.

5. The PHP function to get and format the data

function ul_hierarchy_list(){
$tree = array();
global $connect_db;
$query_tree = oci_parse($connect_db,'SELECT level
 , id
 , parent
 , name
 , child_order
 FROM hierarchy
 START WITH PARENT IS NULL
 CONNECT BY PRIOR id = parent
 ORDER SIBLINGS BY child_order');
oci_execute($query_tree) or die ('ERROR GETTING TREE DATA : ' . oci_error());

while ($row_hierarchy = oci_fetch_array($query_tree)) {
   $tree[] = array(htmlspecialchars($row_hierarchy['NAME']), $row_hierarchy['LEVEL'], $row_hierarchy['ID'], $row_hierarchy['PARENT']);
}

$depth = 0;
$flag = false;
foreach ($tree as $row) {
   while ($row[1] > $depth) {
      echo "<ul>", "<li id=\"id_".$row[2]."\">";
      $flag = false;
      $depth++;
   }
   while ($row[1] < $depth) {
      echo "</li>", "</ul>";
      $depth--;
   }
   if ($flag) {
      echo "</li>", "<li id=\"id_".$row[2]."\">";
      $flag = false;
   }
   echo '<ins> </ins><a href="#">'.$row[0].'</a>';
   $flag = true;
}
while ($depth-- > 0) {
   echo "</li>", "</ul>";
}

}

6. The XHTML stuff

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en">
<head>
	<title>Branch Tree</title>
	<meta http-equiv="Content-type" content="text/html;charset=UTF-8" />

	<style type="text/css">
		ul {font-family: Verdana, Arial, Helvetica; font-size: 10px;}
	</style>
</head>
<body>

<?php
   ul_hierarchy_list();
?>
</body>
</html>

7. The result.

8. What’s next?

Great! You’ve been through this quick and dirty tutorial and you might want to go further and create some nice desktop like folder trees.

Using the javascript jQuery framework with the fantastic jsTree plugin you’d be able to achieve something like this:

function ul_hierarchy_list(){
$tree = array();
$connect_db = oci_connect('dbuser', 'dbpassword', 'localhost/XE');
$query_tree = oci_parse($connect_db,'SELECT level
 , id
 , parent
 , name
 , child_order
 FROM hierarchy
 START WITH PARENT IS NULL
 CONNECT BY PRIOR id = parent
 ORDER SIBLINGS BY child_order');
oci_execute($query_tree) or die ('ERROR GETTING TREE DATA : ' . oci_error());

while ($row_hierarchy = oci_fetch_array($query_tree)) {
   $tree[] = array(htmlspecialchars($row_hierarchy['NAME']), $row_hierarchy['LEVEL'], $row_hierarchy['ID'], $row_hierarchy['PARENT']);
}

$depth = 0;
$flag = false;
foreach ($tree as $row) {
   while ($row[1] > $depth) {
      echo "<ul>", "<li id="id_".$row[2]."_".$row[3]."">";
      $flag = false;
      $depth++;
   }
   while ($row[1] < $depth) {
      echo "</li>", "</ul>";
      $depth--;
   }
   if ($flag) {
      echo "</li>", "<li id="id_".$row[2]."_".$row[3]."">";
      $flag = false;
   }
   echo '<ins> </ins><a href="#">'.$row[0].'</a>';
   $flag = true;
}
while ($depth-- > 0) {
   echo "</li>", "</ul>";
}

}