Solved

Print database tree from a selected leaf into a UL list

Posted on 2011-02-23
9
235 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Is your Office 365 signature not working the way you want it to? Are signature updates taking up too much of your time? Let's run through the most common problems that an IT administrator can encounter when dealing with Office 365 email signatures.
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
HTML5 has deprecated a few of the older ways of showing media as well as offering up a new way to create games and animations. Audio, video, and canvas are just a few of the adjustments made between XHTML and HTML5. As we learned in our last micr…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

734 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