Solved

Print database tree from a selected leaf into a UL list

Posted on 2011-02-23
9
227 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 explains how to prepare an HTML email signature template file containing dynamic placeholders for users' Azure AD data. Furthermore, it explains how to use this file to remotely set up a department-wide email signature policy in Office …
Finding original email is quite difficult due to their duplicates. From this article, you will come to know why multiple duplicates of same emails appear and how to delete duplicate emails from Outlook securely and instantly while vital emails remai…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
The viewer will learn the basics of jQuery including how to code hide show and toggles. 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…

770 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