Solved

Print database tree from a selected leaf into a UL list

Posted on 2011-02-23
9
231 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
Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

 
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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
The viewer will receive an overview of the basics of CSS showing inline styles. In the head tags set up your style tags: (CODE) Reference the nav tag and set your properties.: (CODE) Set the reference for the UL element and styles for it to ensu…
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 …

679 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