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>";
}

}

My girl, my girl, don’t lie to me,
Tell me where did you sleep last night.

In the pines, in the pines,
Where the sun don’t ever shine.
I would shiver the whole night through.

My girl, my girl, where will you go?
I’m going where the cold wind blows.

Her husband, was a hard working man,
Just about a mile from here.
His head was found in a driving wheel,
But his body never was found.

In the pines, …the pines,
The sun,
The shine.

These lyrics are taken from an old blues song made famous by Lead Belly and coverd by Nirvana on their Unplugged Album. The picture is dedicated to Stephen Thomas, a Canadian guy who taught me this song when I was living in Zürich.

The picture was taken by the Lake Sebastien, North of St-David de Falardeau, QC

En rentrant à la maison, j’ai profité de la présence de quelques graines de tournesol pour prendre une ou deux photos des oiseaux qui nous font l’hospitalité de leur coin de fôret.

J’ai suivi les baleines
Et elles m’ont raconté
L’histoire d’un enfant paisible
Que j’avais oublié

Laissez-moi avec les baleines
Que je puisse inventer
Un futur tant possible
Qu’il n’y aurait plus qu’à le rêver.

L’après-midi fut à la découverte de Chicoutimi et le soir a célébrer la mort du soleil dans les eaux du lac Sébastien.

Les distances ici sont en impressionantes. Rolling take off sur une piste de plus de 6000ft… Que du bonheur! Ca me change des pistes suisses

Allez hop! Vous avez été sages. Je vous ajoute quelques photos.

Un chausson avec ça?

Je pars donc au Québec dans 10 jours l’esprit libre. Les jours passent et me rapprochent de la date du départ. Chaque matin je sens un petit picotement supplémentaire. J’ai la même sentiment innocent et étonné que ma fille d’un an qui boit de l’eau gazeuse. Ce mélange de surprise et d’émerveillement. Le parfum de la chose nouvelle suffit à me faire sourire. Mon âme respire à pleins poumons.

Devenir pilote était mon plus lointain mais aussi mon plus incertain rêve d’enfant. Aller au Canada est depuis toujours la source de mille rêves mystérieux. Le fantasme des indiens et les songes de grands espaces qui défilent à toute vitesse à quelques centimètres sous mes pieds. Je ne réalise pas encore vraiment que tout ça sera là dans quelques jours. Chaque soir avant de fermer les yeux je me fais des films de survol de la forêt de sang et d’or. Je me vois assis sur une berge avec le seul bruit d’une rivière pour douce mélodie. Mais ce qui par dessus tout me rend heureux c’est de savoir que je ne serai pas seul. Mona est là à 110% pour suivre son mari volant un peu fou. Il y a aussi nos filles, plus merveilleuses chaque jour. Je ne serais pas un homme entier sans mes petites femmes. Je me régale déjà de pouvoir leur conter des histoires autour du feu que je ferai avec elles devant notre chalet au bord du lac Clair.

Je pars avec mille désirs et cent fois plus de rêves. Je n’ai pas l’intention d’être déçu et je guette comme un premier bourgeon de mars le moment où je n’aurai plus envie de revenir. J’ai préparé cet instant comme on préparerait un repas de Noël. J’ai fait de la place pour ceux que j’aime et j’ai dressé un couvert de plus pour l’inconnu improviste qui viendrait nous conter son pays.

Je ne vois pas ce que je pourrais demander de plus. Accomplir un rêve en communion avec le bonheur intense de se savoir entouré de son clan.

Je rêve donc je suis.