Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Print database tree from a selected leaf into a UL list

Posted on 2011-02-23
9
Medium Priority
?
238 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
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 2000 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Q&A with Course Creator, Mark Lassoff, on the importance of HTML5 in the career of a modern-day developer.
The viewer will learn the benefit of using external CSS files and the relationship between class and ID selectors. Create your external css file by saving it as style.css then set up your style tags: (CODE) Reference the nav tag and set your prop…
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…

721 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