Solved

Print database tree from a selected leaf into a UL list

Posted on 2011-02-23
9
230 Views
Last Modified: 2013-10-09
Hello all,

I have a tree structure in my data base as desicribed:-

-- -----------------------------------------------------
-- Table `filter`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `filter` (
  `id` BIGINT(8) UNSIGNED NOT NULL AUTO_INCREMENT ,
  `name` VARCHAR(255) NOT NULL ,
  `parent` BIGINT(8) UNSIGNED NULL DEFAULT NULL ,
  PRIMARY KEY (`id`) ,
  INDEX `fk_filter_filter` (`parent` ASC) ,
  CONSTRAINT `fk_filter_filter`
    FOREIGN KEY (`parent` )
    REFERENCES `filter` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

Open in new window



I want to be able to from any leaf in the tree recusivly get all it's siblings, then it's parent and all the parent's siblings untill I get back to the root. And then print out in order from Root to selected Leaf in a nested UL list.

Thanks in advance
0
Comment
Question by:codevomit
  • 4
  • 3
9 Comments
 
LVL 15

Expert Comment

by:dirknibleck
ID: 34962594
Here is some psueod-php that will get you there, you'll just need to test it...
echo print_list($leaf, null);

function print_list($id, $html){
     $output = null;
     $db = database_connection
     $query = database_query("SELECT * FROM filter WHERE parent IN (SELECT parent FROM filter WHERE id = $id");

     if($id){
          $output = "<ul>";
          while ($row = fetch_array($query)){
                $parent = $row['parent'];
                $output .= "<li>" . $row['name'] . ($id == $row['id'] ? $html : null) . "</li>";
          }
          $output .= "</ul">;
          $output = print_list($parent, $output);
     }

     return $output;

}

Open in new window

0
 
LVL 1

Author Comment

by:codevomit
ID: 34979360
Thanks for getting back to me, it's almost there. This is what I currently have:-

INSERT INTO `filter` (`id`, `name`, `parent`) VALUES
(4, 'C', NULL),
(5, 'D', NULL),
(11, 'G', NULL),
(2, 'A', 11),
(3, 'B', 2),
(6, 'E', 4),
(8, 'F', 5),
(12, 'H', 3),
(14, 'I', 3),
(15, 'J', 14),
(16, 'K', 15),
(17, 'L', 2);

Open in new window


<?php

//error_reporting(E_ALL);

mysql_connect("localhost", "root", "");
mysql_select_db("shop");

function print_list($id, $html) {
    $output = null;
    $query = "SELECT * FROM filter WHERE parent IN (SELECT parent FROM filter WHERE id " . (($id > 0) ? ("='$id'") : ("IS NULL")) . ") ORDER BY `name` ASC";
    $results = mysql_query($query);

    if ($id) { {
            $output = "<ul>";
            $parent = null;
            while ($row = mysql_fetch_array($results)) {
                $parent = $row['parent'];
                $output .= "<li>" . $row['name'] . ($id == $row['id'] ? $html : null) . "</li>";
            }
            $output .= "</ul>";
            $output .= print_list($parent, $output);
        }

        return $output;
    }
}

echo print_list(16, null);
?>

Open in new window


Expected output:-

C
D
G
--A
---- B
------ H
------ I
-------- J
---------- K
---- L

Open in new window


What I'm actually getting:-

K
J
-- K
H
-- I
---- J
------- K
B
-- H
-- I
---- J
----- K
L
A
-- B
---- H
---- I
------ J
-------- K
-- L

Open in new window

0
 
LVL 15

Expert Comment

by:dirknibleck
ID: 34979727
I think the issue is that you mis-copied this line:

$output .= print_list($parent, $output);

Open in new window


You want to completely replace $output at this point, not add to it, so:

$output = print_list($parent, $output);

Open in new window

0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 1

Author Comment

by:codevomit
ID: 34979899
Yeah, I did that on purpose because it results in $output being blank.
0
 
LVL 1

Author Comment

by:codevomit
ID: 34980218
You can see that $output will always result as null without having to execute the code.
0
 
LVL 15

Accepted Solution

by:
dirknibleck earned 500 total points
ID: 34981107
Right, I see that now.

Try this then?

<?php

//error_reporting(E_ALL);

mysql_connect("localhost", "root", "");
mysql_select_db("shop");

function print_list($id, $html) {
    $output = null;
    $query = "SELECT * FROM filter WHERE parent IN (SELECT parent FROM filter WHERE id " . (($id > 0) ? ("='$id'") : ("IS NULL")) . ") ORDER BY `name` ASC";
    $results = mysql_query($query);

    if ($id) { {
            $output = "<ul>";
            $parent = null;
            while ($row = mysql_fetch_array($results)) {
                $parent = $row['parent'];
                $output .= "<li>" . $row['name'] . ($id == $row['id'] ? $html : null) . "</li>";
            }
            $output .= "</ul>";
            if ($parent) $output .= print_list($parent, $output);
        }

        return $output;
    }
}

echo print_list(16, null);
?>

Open in new window

0
 
LVL 1

Author Comment

by:codevomit
ID: 35117069
Thanks, I will try that out.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article discusses how to create an extensible mechanism for linked drop downs.
I have a large data set and a SSIS package. How can I load this file in multi threading?
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
The viewer will learn the basics of jQuery, including how to invoke it on a web page. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery.: (CODE)

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question