Storing hierarchical data in a database

Question

As an extension to this post, I've created this class/script to handle multi-dimensional Menus whose data is stored in a DB. I need some feedback and new ideas.

Pastebin

<?php
/*
 * Author: Carlos Arturo Alaniz
 * Last Modified Date: 06/19/2013
 * Languages: PHP/HTML/HTML
 *
 * Description:
 * This class and script creates a multi dimentional menu
 * using a SQL database and hierarchical structure
 */
class menu {
 /* Constructor */
 function __construct($content, $label = NULL) {
 $this->ul = array('<ul>', '</ul>');
 $this->li = array('<li>', '</li>');
 $this->content = $content;
 $this->label = $label;
 }
 /* Private */
 private $label;
 private $ul;
 private $li;
 private $menu_str;
 /* Public */
 public $content;
 public function add_level($content, $id) {
 $lenght = count($this->content);
 for ($i = 0; $i < $lenght; $i++) {
 if ($this->content[$i][1] == $id) {
 $pos = $i;
 break;
 }
 }
 if (isset($pos)) {
 $label = $this->content[$pos][0];
 unset($this->content[$pos][0]);
 $this->content[$pos][0] = $content;
 $this->content[$pos][0]->label = $label;
 }
 }
 public function print_con() {
 print_r($this->content);
 }
 public function generateMenu() {
 $this->menu_str = NULL;
 $this->menu_str.= $this->ul[0];
 $size = count($this->content);
 for ($i = 0; $i < $size; $i++) {
 $this->menu_str.= $this->li[0];
 if (!is_object($this->content[$i][0]))
 $this->menu_str.= $this->content[$i][0];
 else {
 $this->menu_str.= $this->content[$i][0]->label;
 $this->menu_str.= $this->content[$i][0]->generateMenu();
 }
 $this->menu_str .= $this->li[1];
 }
 $this->menu_str .= $this->ul[1];
 return $this->menu_str;
 }
 public function writeMenu($filepath) {
 file_put_contents($filepath, $this->menu_str);
 }
}
/*Connection info*/
$db_host = "localhost";
$db_user = "root";
$db_password = "EHA";
$db_database = "page_system";
$pdo = new PDO("mysql:host=$db_host;dbname=$db_database;charset=utf8", $db_user, $db_password);
/*Connection info*/
$qry = "SELECT MAX(parent) FROM menu";
$max_parent = $pdo->query($qry)->fetch();
$content = NULL; //Variable used to store the results of the qery
$menus_objs = array(); //Array to hold the menus and its parent id.
$obj_count = 0;
$level = NULL; // this variable its going to hold
//the last level in the tree
$parent = NULL; //This variable its going to be modified
//in each loop cycle to be later stord in the menu_objs
//array at index 1 as the parent id for that group
$level_element_count = array(); //This array uses the
//$level variable as an index and store how many objects
//exists in each level
//
 //Traverse array $max_parent times and group each elements
//in menu objects according to its parent
//
 for ($i = 0; $i <= $max_parent[0]; $i++) {
 $qry = "SELECT * FROM menu WHERE parent = $i ORDER BY 'order' ASC";
 $stmt = $pdo->query($qry);
 $content_index = 0; //index for array of elements to be ineserted into a
 //Create elements array to later create a menu obj
 //Clear existing array before continuing
 if (isset($content)) {
 unset($content);
 }
 while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
 if (!isset($content)) {
 $level = $row['level'];
 $parent = $row['parent'];
 }
 $content[$content_index] = array($row['label'], $row['id']);
 $content_index++;
 }
 //if content exists Create a menu object using the content array
 if (isset($content)) {
 //if the level index array is not created yet initiazlize first
 //value to one otherwise increse that value this array counts
 //how many objects exist in each level.
 if (!isset($level_element_count[$level])) {
 $level_element_count[$level] = 1;
 } else {
 $level_element_count[$level]++;
 }
 //store the object in the correct index and pair it with its
 //corresponding parent id and increment the objs count by one
 $menus_objs[$obj_count][0] = new menu($content);
 $menus_objs[$obj_count][1] = $parent;
 $obj_count++;
 }
}
//add a zero and the end of level element count
$level_element_count[] = 0;
$i = $obj_count;
//Goinf backwards through the levels of the
//array of objects
for ($l = $level; $l > 0; $l--) {
 $c = 0; //counter variable
 for ($q = 0; $q < $level_element_count[$l]; $q++) {
 $i--;
 for ($upl = 0; $upl < $level_element_count[$l - 1]; $upl++) {
 $k = $i + $c - $upl - $level_element_count[$l];
 //
 // $k is index of the corresponding upper level object
 // index of object - qty of objets per level - qty of elements
 // in upper level
 //
 echo $k;
 $menus_objs[$k][0]->add_level($menus_objs[$i][0], $menus_objs[$i][1]);
 }
 $c++;
 }
}
//At the end we should have all the data from the DB organized
//as a multi-dimentional menu we just have to call the generate menu
//method on the last level object witch its always one, obj [0][0]
$menus_objs[0][0]->generateMenu();
$menus_objs[0][0]->writeMenu("../menu.html");
?>

• Paul: lenght should be length
• user28514: The word in square brackets is the alt text, which gets displayed if the browser can't show the image. Be sure to include meaningful alt text for screen-reading software.

Answer 1

This code is too complex. It is going to be difficult to maintain or reuse.

Current Issues

I see the following problems with the code as it is:

• Method names are not consistent: lower_pascal_case is mixed with lowerCamelCase
• Hard-coded values of 0 and 1 make the code difficult to read.
• Public property $content takes away from the information hiding.
• One letter variables take away from readability. In a few lines of code I see $k$i$c$q (it looks like "Kick You" to me).
• Not reusable. You are going to have to copy and paste to reuse this.
• Mixed paradigms. Object Oriented for the view, Procedural for the Model/Persistence. Why not use the same paradigm for both?

Suggestions

I can't actually understand your implementation. If it works then I guess it is doing what you want already.

There are a few ways of storing hierarchical data in a flat structure. Read hierarchical data in a database especially page 2 and 3 which talk about Modified Preorder Tree Traversal (MPTT). Using MPTT provides you with interesting ways of accessing the menu tree or subtree.

• Carlos Arturo Alaniz: Thanks for the suggestions, After reading that I think I had the correct idea (Kind of) so Im going to try to re write the code in a cleaner way.. thanks for that warning for the $content variable. And my basic idea was group the elements by their parent id and create menu objs out of them, using the 'level' column in my db I know where each opbj goes (level wise) after that I just traverse the array of objs backwards trying to add each obj to a upper level obj creating at the end a menu obj that contains everything.