Solved

Unordered List Hierarchy from MySQL Table that Has Columns for Category, Subcategory, Sub-subcategory

Posted on 2007-03-22
20
855 Views
Last Modified: 2013-12-13
I know this would be easier with XML, etc., but for various reasons I want to do it with php & MySQL: convert a flat MySQL table into a hierarchical unordered list in html.
     The table is set up like this, where the Topic field always contains data, but the Subtopic and SubSubtopic fields may be blank:

ID     Topic        Subtopic         SubSubTopic     Title          Summary
-----------------------------------------------------------------------------------
1   Computers   Programming  C                 C Made Easy     [text]
2   Computers   Programming  Java             Java Is Fun       [text]
3   Computers   Programming  Basic            Basic Tips         [text]
4   Computers   Hardware       CPU              AMD: Good       [text]
5   Computers   Hardware       Mouse          Optical Reigns  [text]
6   Computers   Hardware       Mouse          USB Is Good     [text]
7   Computers   Hardware                            New Equip.      [text]
8   Computers                                              Why Computers [text]
9   Vehicles        Cars               Sedans         Chevy is Nice    [text]
10 Vehicles        Cars                                    We Drive          [text]
11 Vehicles        Trucks                                 Trucks R Us       [text]
12 Vehicles                                                   Vehicles Today [text]
etc.

I'm trying to devise a query and php handler that can enable echoing the data into the format below, which includes sorting the nodes alphabetically and inserting the proper <ul>, </ul> and <li>, </li>, and also making the Title fields bold with <b>, </b>:

<ul class="mktree" id="tree1">

      <li>Computers
            <ul>
                  <li>Hardware
                        <ul>
                              <li><b>New Equip.</b>
                                    <ul>
                                          <dir>[Summary Text]</dir>
                                    </ul>
                              </li>
                        </ul>
                         <ul>
                              <li>CPU
                                    <ul>
                                          <li><b>AMD: Good</b>
                                                <ul>
                                                      <dir>[Summary Text]</dir>
                                                </ul>
                                          </li>
                                    </ul>
                              </li>
                              <li>Mouse
                                    <ul>
                                          <li><b>New Mice</b>
                                                <ul>
                                                      <dir>[Summary Text]</dir>
                                                </ul>
                                          </li>
                                          <li><b>Optical Reigns</b>
                                                <ul>
                                                      <dir>[Summary Text]</dir>
                                                </ul>
                                          </li>
                                          <li><b>USB Is Good</b>
                                                <ul>
                                                      <dir>[Summary Text]</dir>
                                                </ul>
                                          </li>                                          
                                    </ul>
                              </li>
                        </ul>
                  </li>
                  <li>Programming
                        <ul>
                              <li>C
                                    <ul>
                                          <li><b>C Made Easy</b>
                                                <ul>
                                                      <dir>[Summary Text]</dir>
                                                </ul>
                                          </li>
                                          <li><b>C Is Good</b>
                                                <ul>
                                                      <dir>[Summary Text]</dir>
                                                </ul>
                                          <li><b>Work with C</b>
                                                <ul>
                                                      <dir>[Summary Text]</dir>
                                                </ul>
                                          </li>
                                    </ul>
                              </li>
                              <li>Basic
                                    <ul>
                                          <li><b>Basic Tips</b>
                                                <ul>
                                                  <dir>[Summary Text]</dir>
                                                </ul>
                                          </li>
                                    </ul>
                              </li>
                              <li>Java
                                    <ul>
                                          <li><b>Java Is Fun</b>
                                                <ul>
                                                      <dir>[Summary Text]</dir>
                                                </ul>
                                          </li>
                                    </ul>
                              </li>
                        </ul>
                  </li>                          
            </ul>
            <ul>
                  <li><b>Why Computers?</b>
                        <ul>
                              <dir>[Summary Text]</dir>
                        </ul>
                  </li>
            </ul>
      <li>Vehicles
            <ul>
                  <li>Cars
                        <ul>
                              <li><b>Cars Rock</b>
                                    <ul>
                                          <dir>[Summary Text]</dir>
                                    </ul>
                              </li>
                        </ul>
                        <ul>
                              <li><b>Like Cars</b>
                                    <ul>
                                          <dir>[Summary Text]</dir>
                                    </ul>
                              </li>
                        </ul>
                        <ul>
                              <li><b>We Drive</b>
                                    <ul>
                                          <dir>[Summary Text]</dir>
                                    </ul>
                              </li>
                        </ul>
                        <ul>              
                              <li>Sedans
                                    <ul>
                                          <li><b>Chevy Is Nice</b>
                                                <ul>
                                                       <dir>[Summary Text]</dir>
                                                </ul>
                                          </li>
                                    </ul>
                              </li>
                        </ul>
                  </li>
            </ul>
            <ul>
                  <li>Trucks
                        <ul>
                              <li><b>Trucks R Us</b>
                                    <ul>
                                          <dir>[Summary Text]</dir>
                                    </ul>
                              </li>
                        </ul>
                  </li>                                                                        
            </ul>
            <ul>
                  <li><b>Vehicles Today</b>
                        <ul>
                              <dir>[Summary Text]</dir>
                        </ul>
                  </li>
            </ul>
      </li>
</ul>

The goal is to dynamically generate a dhtml tree menu from Matt Kruse's Javascript Toolbox.  See the example and html source of this sample page, which uses the data shown above:  http://216.92.61.99/tree.htm.
     I know Matt has a perl script designed to do something remotely similar (build the tree from a NN bookmarks file); and I'm aware of somewhat similar issues at http:Q_21208114.html and http:Q_21892373.html (dynamically building an unordered list from a flat file that uses dots to indicate the level of indentation); and http:Q_22116927.html (recursive algorithm for categories using php / MySQL), but I haven't quite been able to adapt it to my MySQL table and required html unordered list structure.  
     By the way, due to space limitations I did not show that the MySQL table also has a "keyword" field that should be used to sort the Titles within the nodes (e.g., the Titles could all start with "The," so it doesn't make sense to just sort by the title name. So the table also has a "keyword" in each row to allow for sorting by the most significant word of the title.)   Maybe it would be best to bring in the whole table into an array and then sort and perform if-then statements on the array?
     Please help me figure this out so I can generate the HTML unordered list from this MySQL table.  Thanks.
0
Comment
Question by:Randall-B
  • 11
  • 9
20 Comments
 
LVL 20

Expert Comment

by:steelseth12
Comment Utility
Randall-B do you have to use this table structure .... it would be alot easier if you used a parent-child relationship would be more efficient and much easier to populate.
0
 

Author Comment

by:Randall-B
Comment Utility
   I'm aware some other table structure or even use of XML (instead of MySQL) would be more efficient overall, but I'm pretty much locked into this table structure for various reasons.  So I hope there is a way to make it output the form of unordered list shown above.  One good thing is that the input form is very efficient for input and editing of the records.
    Seems like some kind of recursive function should be possible, something like this:  Query for all distinct rows of Topic, then use those distinct values in a for-each query based on all distinct rows of Subtopics, and then use those distinct values in a for-each query for SubSubTopics (and order by Keyword, in order to sort the Topics by the most significant word--as designated by the user in the input form).  Maybe that's a general idea for how to start, but I need expert guidance to put it together and account for need to display the nodes in alphabetical order and with the right amount of <ul>, </ul>, <li>, </li>, etc.  Thanks.
0
 
LVL 20

Expert Comment

by:steelseth12
Comment Utility
I named the table category ... rename it to your table name.

$query= mysql_query("SELECT * FROM category WHERE Subtopic='' AND SubSubTopic='' GROUP BY Topic ORDER BY keyword");
      
      while($row = mysql_fetch_object($query)) {
      
            print "<ul><li>".$row->Topic;
      
            $get_child = mysql_query("SELECT * FROM category WHERE Topic='".$row->Topic."' GROUP BY Subtopic ORDER BY keyword");
      
            while($row_sub = mysql_fetch_object($get_child)) {
                  
                  if($row_sub->Subtopic) {
                  
                        print "<ul><li>".$row_sub->Subtopic;
                        
                        $get_sub_child = mysql_query("SELECT * FROM category WHERE Subtopic='".$row_sub->Subtopic."' GROUP BY SubSubTopic ORDER BY keyword");
                        
                        while($row_sub_sub = mysql_fetch_object($get_sub_child)) {
                        
                        
                              if($row_sub_sub->SubSubTopic) {
                              
                                    print "<ul><li>".$row_sub_sub->SubSubTopic;
                                    
                                    $get_final = mysql_query("SELECT * FROM category WHERE SubSubTopic='".$row_sub_sub->SubSubTopic."' ORDER BY keyword");
                                    
                                    
                                    while($row_final = mysql_fetch_object($get_final)) {
                                    
                                          print "<ul>
                                      <li><b>".$row_final->Title."</b>
                                                <ul>
                                                        <dir>".$row_final->Summary."</dir>
                                                </ul>
                                      </li>
                                    </ul>";
                                    
                                    }
                              
                                    print "</li></ul>";
                              
                              }else{
                              
                                    print "<ul>
                                      <li><b>".$row_sub_sub->Title."</b>
                                                <ul>
                                                        <dir>".$row_sub_sub->Summary."</dir>
                                                </ul>
                                      </li>
                                    </ul>";
                              
                              }
                        }
                        
                        print "</li></ul>";
      
                  }else{
                                    
                        print "<ul>
                                      <li><b>".$row_sub->Title."</b>
                                                <ul>
                                                        <dir>".$row_sub->Summary."</dir>
                                                </ul>
                                      </li>
                                    </ul>";
                        
                  }                  
            
            }
            print "</li></ul>";
            
      }
0
 

Author Comment

by:Randall-B
Comment Utility
steelseth12,
   Thanks. That looks great. However, in testing, I get no ouput.  I even renamed my table as "category" just in case.  I double-checked all the field names, and verified that the table has the data.   Because my table shows blank cells as 'NULL" in Navicat, I even tried changing '' to NULL. But still no results. So I switched it back to '' .  Still no results. Ironically, there are no error messages, either.  I even tried $row = mysql_fetch_array instead of fetch_object.  But no results.
   (However, I verified that I could select and display things from that table using a simple select query.) So what could be wrong here?
0
 
LVL 20

Expert Comment

by:steelseth12
Comment Utility
Could you do a dumb of your table structure and some test data so i can test it. I replicated the table you show above but there may be some differences.
0
 

Author Comment

by:Randall-B
Comment Utility
OK, thanks. Here is the entire dump file:


/*
MySQL Data Transfer
Source Host: localhost
Source Database: testbase
Target Host: localhost
Target Database: time
Date: 3/22/2007 5:59:58 PM
*/

SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for category
-- ----------------------------
CREATE TABLE `category` (
  `ID` mediumint(8) NOT NULL auto_increment,
  `Reviewer` varchar(20) default NULL,
  `Topic` varchar(25) default NULL,
  `Subtopic` varchar(30) default NULL,
  `SubSubTopic` varchar(30) default NULL,
  `keyword` varchar(30) default NULL,
  `Title` varchar(255) default NULL,
  `Summary` text,
  `Updated` datetime default '0000-00-00 00:00:00',
  PRIMARY KEY  (`ID`),
  KEY `Subject` (`Topic`),
  KEY `Keyword` (`keyword`),
  KEY `Reviewer` (`Reviewer`),
  FULLTEXT KEY `Title` (`Title`),
  FULLTEXT KEY `Summary` (`Summary`)
) ENGINE=MyISAM AUTO_INCREMENT=32 DEFAULT CHARSET=latin1;

-- ----------------------------
-- Records
-- ----------------------------
INSERT INTO `category` VALUES ('1', 'Jones', 'Computers', 'Programming', 'C', 'A', 'C Made Easy', 'Summary of this one.', '2007-03-16 16:10:05');
INSERT INTO `category` VALUES ('2', 'Smith', 'Computers', 'Programming', 'Java', 'B', 'Java Is Fun', 'Summary of this one.', '0000-00-00 00:00:00');
INSERT INTO `category` VALUES ('3', 'Jones', 'Computers', 'Programming', 'Basic', 'C', 'Basic Tips', 'Summary of this one.', '0000-00-00 00:00:00');
INSERT INTO `category` VALUES ('4', 'Smith', 'Computers', 'Hardware', 'Mouse', 'D', 'AMD: Good', 'Summary of this one.', '2007-03-22 14:10:05');
INSERT INTO `category` VALUES ('5', 'Sanchez', 'Computers', 'Hardware', 'Mouse', 'E', 'Optical Reigns', 'Summary of this one.', '2007-03-22 14:00:23');
INSERT INTO `category` VALUES ('6', 'Jones', 'Computers', 'Hardware', null, 'F', 'USB Is Good', 'Summary of this one.', '2007-03-22 15:06:50');
INSERT INTO `category` VALUES ('7', 'Smith', 'Computers', 'Hardware', null, 'G', 'New Equip.', 'Summary of this one.', '2007-03-22 15:11:53');
INSERT INTO `category` VALUES ('8', 'Sanchez', 'Vehicles', '', null, 'H', 'Why Computers?', 'Summary of this one.', '2007-03-22 15:16:40');
INSERT INTO `category` VALUES ('9', 'Jones', 'Vehicles', 'Cars', 'Sedans', 'I', 'Chevy is Nice', 'Summary of this one.', '2007-03-22 15:30:13');
INSERT INTO `category` VALUES ('10', 'Smith', 'Vehicles', 'Cars', null, 'J', 'We Drive', 'Summary of this one.', '2007-03-22 15:33:30');
INSERT INTO `category` VALUES ('11', 'Sanchez', 'Vehicles', 'Trucks', null, 'K', 'Trucks R Us', 'Summary of this one.', '2007-03-22 15:38:45');
INSERT INTO `category` VALUES ('12', 'Smith', 'Vehicles', '', null, 'L', 'Vehicles Today ', 'Summary of this one.', '2007-03-22 00:00:00');
0
 
LVL 20

Expert Comment

by:steelseth12
Comment Utility
it seems like subtopic is empty where as subsubtopic is null ...


      $query= mysql_query("SELECT * FROM category WHERE Subtopic='' AND SubSubTopic IS NULL GROUP BY Topic ORDER BY keyword");
      
      
      
      while($row = mysql_fetch_object($query)) {
      
            print "<ul><li>".$row->Topic;
      
            $get_child = mysql_query("SELECT * FROM category WHERE Topic='".$row->Topic."' GROUP BY Subtopic ORDER BY keyword");
      
            while($row_sub = mysql_fetch_object($get_child)) {
                  
                  if($row_sub->Subtopic) {
                  
                        print "<ul><li>".$row_sub->Subtopic;
                        
                        $get_sub_child = mysql_query("SELECT * FROM category WHERE Subtopic='".$row_sub->Subtopic."' GROUP BY SubSubTopic ORDER BY keyword");
                        
                        while($row_sub_sub = mysql_fetch_object($get_sub_child)) {
                        
                        
                              if($row_sub_sub->SubSubTopic) {
                              
                                    print "<ul><li>".$row_sub_sub->SubSubTopic;
                                    
                                    $get_final = mysql_query("SELECT * FROM category WHERE SubSubTopic='".$row_sub_sub->SubSubTopic."' ORDER BY keyword");
                                    
                                    
                                    while($row_final = mysql_fetch_object($get_final)) {
                                    
                                          print "<ul>
                                      <li><b>".$row_final->Title."</b>
                                                <ul>
                                                        <dir>".$row_final->Summary."</dir>
                                                </ul>
                                      </li>
                                    </ul>";
                                    
                                    }
                              
                                    print "</li></ul>";
                              
                              }else{
                              
                                    print "<ul>
                                      <li><b>".$row_sub_sub->Title."</b>
                                                <ul>
                                                        <dir>".$row_sub_sub->Summary."</dir>
                                                </ul>
                                      </li>
                                    </ul>";
                              
                              }
                        }
                        
                        print "</li></ul>";
      
                  }else{
                                    
                        print "<ul>
                                      <li><b>".$row_sub->Title."</b>
                                                <ul>
                                                        <dir>".$row_sub->Summary."</dir>
                                                </ul>
                                      </li>
                                    </ul>";
                        
                  }                  
            
            }
            print "</li></ul>";
            
      }
0
 
LVL 20

Expert Comment

by:steelseth12
Comment Utility
actually you only need to change the first query :)

$query= mysql_query("SELECT * FROM category WHERE Subtopic='' AND SubSubTopic IS NULL GROUP BY Topic ORDER BY keyword");
0
 
LVL 20

Expert Comment

by:steelseth12
Comment Utility
On a closer look thats not even needed as it groups by topic ...

$query= mysql_query("SELECT * FROM category GROUP BY Topic ORDER BY keyword");

sorry :)
0
 

Author Comment

by:Randall-B
Comment Utility
Thanks!  It displays results now.  But the nodes are not in alphabetical order.  Probably because it is sorting by keyword.  But the keyword sort should affect only the Titles within other nodes.  Like this simplified format:

Vehicles
     Cars
           Title:Summary Keyword=A
           Title:Summary Keyword=B
           Title:Summary Keyword=C
    Trucks
     Vehicles
I'm not sure if this makes sense.  Maybe the best way to explain what I'm looking for is by looking at :  http://216.92.61.99/tree.htm , as far as how all the nodes should look.  Thanks.
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 20

Expert Comment

by:steelseth12
Comment Utility
I think this i what you mean

      $query= mysql_query("SELECT * FROM category GROUP BY Topic");
      
      while($row = mysql_fetch_object($query)) {
      
            print "<ul><li>".$row->Topic;
      
            $get_child = mysql_query("SELECT * FROM category WHERE Topic='".$row->Topic."' GROUP BY Subtopic");
      
            while($row_sub = mysql_fetch_object($get_child)) {
                  
                  if($row_sub->Subtopic) {
                  
                        print "<ul><li>".$row_sub->Subtopic;
                        
                        $get_sub_child = mysql_query("SELECT * FROM category WHERE Subtopic='".$row_sub->Subtopic."' GROUP BY SubSubTopic");
                        
                        while($row_sub_sub = mysql_fetch_object($get_sub_child)) {
                        
                        
                              if($row_sub_sub->SubSubTopic) {
                              
                                    print "<ul><li>".$row_sub_sub->SubSubTopic;
                                    
                                    $get_final = mysql_query("SELECT * FROM category WHERE SubSubTopic='".$row_sub_sub->SubSubTopic."' ORDER BY keyword");
                                    
                                    
                                    while($row_final = mysql_fetch_object($get_final)) {
                                    
                                          print "<ul>
                                      <li><b>".$row_final->Title."</b>
                                                <ul>
                                                        <dir>".$row_final->Summary."</dir>
                                                </ul>
                                      </li>
                                    </ul>";
                                    
                                    }
                              
                                    print "</li></ul>";
                              
                              }else{
                              
                                    print "<ul>
                                      <li><b>".$row_sub_sub->Title."</b>
                                                <ul>
                                                        <dir>".$row_sub_sub->Summary."</dir>
                                                </ul>
                                      </li>
                                    </ul>";
                              
                              }
                        }
                        
                        print "</li></ul>";
      
                  }else{
                                    
                        print "<ul>
                                      <li><b>".$row_sub->Title."</b>
                                                <ul>
                                                        <dir>".$row_sub->Summary."</dir>
                                                </ul>
                                      </li>
                                    </ul>";
                        
                  }                  
            
            }
            print "</li></ul>";
            
      }

I a liitle confused thought why in the example C is above Basic
0
 

Author Comment

by:Randall-B
Comment Utility
Thanks!  That's close to what I was trying to do.  (By the way, the C before Basic issue was a typo. You're right, B was supposed to come before C.  Sorry about the confusion.)
 
   I've been testing it extensively, and I'm having two problems:
 
   1) When I add new rows (or modify old rows), it does not display the data from some of the new or modified rows.  My tests lead me to think it may be caused by the "null" vs. "empty" phenomenon (perhaps in the SubSubTopic field), but I couldn't see where that issue could be involved in the latest script.  If it is an issue, is there a way to adjust the query so that it does not distinguish between those two kinds?

 2)  When a nonbranching <b>Title</b> or some nonbranching Titles come directly off of a node, and also one or more branching nodes co-exist with the Title at that level, is it possible to make the Title go under the branching nodes, rather than above?  For example, in the latest script, it has it above:
    Vehicles      
           <b>Why Vehicles</b> [Title, non-branching]
           Cars [child of Vehicles & branches further out]
           Trucks [child of Vehicles & branches out]

but I hope it's possible to put them under:

  Vehicles
           Cars [child of Vehicles & branches further out]
           Trucks [child of Vehicles & branches out]
           <b>Good Vehicles</b> [Title, nonbranching]
           <b>Great Vehicles</b> [Title, nonbranching]
           <b>More Vehicles</b> [Title, nonbranching]
           <b>New Vehicles</b> [Title, nonbranching]
           <b>Nice Vehicles</b> [Title, nonbranching]
           <b>Old Vehicles</b> [Title, nonbrancing]
           <b>Other Vehicles</b> [Title, nonbrancing]
           <b>Some Vehicles</b> [Title, nonbrancing]
           <b>Why Vehicles</b> [Title, nonbranching]

The reason is because eventually, when there may be a lot of non-branching Titles at that level, it may be hard to notice the branching subtopic nodes way down under them.
    To me as a non-expert, it looks like it would be impossible to make that change without ruining the logic of the algorithm. But maybe you can see a way.
    I really appreciate your expertise on this. Thanks.
0
 
LVL 20

Expert Comment

by:steelseth12
Comment Utility
Randall-B can you give me an example of a record that is not showing, also can you tell me what version of mysql you are running
0
 

Author Comment

by:Randall-B
Comment Utility
I'm developing and testing this on WAMP with MySQL version 5.0.27 , but the actual ISP server will only have MySQL 4.1.20; so it will need to be compatible with v. 4.1.20 .

From an sql dump, here is an example of two rows that *are* showing as nonbranching Titles under the Vehicle node:

    '8', 'Sanchez', 'Vehicles', '', null, 'H', 'Why Vehicles?', 'Summary of this one.'

   '13', 'Smith', 'Vehicles', null, null, 'M', 'Old Vehicles', 'Summary of this one.'
   ( in the 1st, it was Subtopic = '' and SubSubTopic IS NULL ;  and in the 2nd, both are null;  but both rows are showing OK)

But here is two rows that are *not* showing:

  '11', 'Sanchez', 'Vehicles', '', null, 'K', 'New Vehicles', 'Summary of this one.'

   '12', 'Smith', 'Vehicles', '', null, 'L', 'Vehicles Today ', 'Summary of this one.'
    (They both have Subtopic = '' and SubSubTopic IS NULL , just like one of the rows above; but these two are not showing.)
     The complete sql dump is posted here if you want to test it:    http://216.92.61.99/category.sql  .


By the way, referring to my earlier question about showing the nonbranching Titles under the branching nodes (in cases where they co-exist at the same level), is it possible to let those be sorted by keyword--but while still sorting the branching nodes by first-word?  
      In the illustration below, I've placed a " / " at the end of branching nodes to clarify, and I'm showing the keyword at the start of each Title, just to emphasize the sorting.  Currently it is doing this:

Vehicles
      B: Old Vehicles     [It's sorting by 1st word of Title]
      A: Why Vehicles?
     Cars/
     Trucks/

But can it do this:

Vehicles
     Cars/
     Trucks/
      A: Why Vehicles?    [It's sorting by Keywords A, B
      B: Old Vehicles        and putting nonbranching
                                     Titles under branching Subtopics
                                     The branching SubTopics are
                                     still sorted alphabetically.]

By the way, I modified the script to add the " / " at the end of each branching node, put the keyword at the beginning of each Title; and also to make one big tree instead of starting a new tree at the end of each main node.  The reason for this is to all the "expand all nodes" and "collapse all nodes" buttons to work on the whole thing.  
      You may want to see the result at: http://216.92.61.99/categorytest.htm  and can download this modified script from:  http://216.92.61.99/categoryphp.txt .  
     It would be nice if you use this modified script for further development in this question.
    Thanks so much.
0
 
LVL 20

Assisted Solution

by:steelseth12
steelseth12 earned 500 total points
Comment Utility
Try this ...
<xmp>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
<html>
<head>
<title>DHTML Expandable and Collapsible Tree from JavascriptToolbox.com</title>
<link rel="stylesheet" type="text/css" href="mkcss.css" media="all">
<link rel="stylesheet" type="text/css" href="mktree.css">
<script type="text/javascript" src="mktree.js"></script>
</head>
<body>
<p> <a href="#" class="button" onClick="expandTree('tree1');return false;">Expand
  All Nodes</a> &nbsp; <A class="button" href="#" onClick="collapseTree('tree1'); return false;">Collapse
  All Nodes</A></p>

<?php

$db = @mysql_connect('localhost', 'root', '') or die(mysql_error());
mysql_select_db('mydatabase', $db) or die(mysql_error());

$query= mysql_query("SELECT * FROM category GROUP BY Topic");

$iteration = 0;
     
      while($row = mysql_fetch_object($query)) {
     
             if ($iteration == 0) {
                print "<ul class=\"mktree\" id=\"tree1\">";
                        } else {
                        print "";
                        }
                  print "<li>".$row->Topic."/";
     
            $get_child = mysql_query("SELECT * FROM category WHERE Topic='".$row->Topic."' GROUP BY Subtopic");
     
           
                  $print_a="";
                  while($row_sub = mysql_fetch_object($get_child)) {
                  
                                         
                  if($row_sub->Subtopic) {
                 
                        $print_a .= "<ul><li>".$row_sub->Subtopic."/";
                       
                        $get_sub_child = mysql_query("SELECT * FROM category WHERE Subtopic='".$row_sub->Subtopic."' GROUP BY SubSubTopic ");
                       
                       
                                  $print_a_a = "";
                                  while($row_sub_sub = mysql_fetch_object($get_sub_child)) {
                       
                       
                              if($row_sub_sub->SubSubTopic) {
                             
                                    $print_a_a .= "<ul><li>".$row_sub_sub->SubSubTopic."/";
                                   
                                    $get_final = mysql_query("SELECT * FROM category WHERE SubSubTopic='".$row_sub_sub->SubSubTopic."' ORDER BY Keyword ASC");
                                   
                                   
                                    while($row_final = mysql_fetch_object($get_final)) {
                                   
                                          $print_a_a .= "<ul>
                                      <li>".$row_final->Keyword.": <b>".$row_final->Title."</b>
                                                <ul>
                                                        <dir>".$row_final->Summary."</dir>
                                                </ul>
                                      </li>
                                    </ul>";
                                   
                                    }
                                                      
                                                      
                             
                                    $print_a_a .= "</li></ul>";
                             
                              }else{
                                            
                                                  $get_sub_child1 = mysql_query("SELECT * FROM category WHERE Subtopic='".$row_sub->Subtopic."' AND  (SubSubTopic IS NULL OR SubSubTopic='')");
                                                $print_a_b ="";
                                                while($row_sub_sub1 = mysql_fetch_object($get_sub_child1)) {
                             
                                    $print_a_b .= "<ul>
                                      <li>".$row_sub_sub1->Keyword.": <b>".$row_sub_sub1->Title."</b>
                                                <ul>
                                                        <dir>".$row_sub_sub1->Summary."</dir>
                                                </ul>
                                      </li>
                                    </ul>";
                                                      
                                                      }
                             
                              }
                        }
                       
                                    
                                    $print_a .= $print_a_a;
                                    $print_a .= $print_a_b;
                        $print_a .= "</li></ul>";
     
                  }else{
                              $get_child1 = mysql_query("SELECT * FROM category WHERE Topic='".$row->Topic."'  AND  (Subtopic IS NULL OR Subtopic='')");
     
                                          while($row_sub1 = mysql_fetch_object($get_child1)) {    
                        $print_b .= "<ul>
                                      <li>".$row_sub1->Keyword.": <b>".$row_sub1->Title."</b>
                                                <ul>
                                                        <dir>".$row_sub1->Summary."</dir>
                                                </ul>
                                      </li>
                                    </ul>";
                                                      
                                                      
                                          }
                       
                  }                  
           
            }
           
                    print $print_a;
                  print $print_b;
               // print "</li></ul>";
          print "</li>";

       $iteration++;          
      }
?>

</ul>

<br>
<br>
</body>
</html>
</xmp>

Let me know if this is how you want it to work or if i understood wrong
0
 

Author Comment

by:Randall-B
Comment Utility
steelseth12,
   I figured out how to solve those problems, and it all seems to be working now.  
    The non-printing rows seems to have been caused by a "group by" clause in the query that selects the non-branching Titles under the main nodes.
   To get the non-branching Titles to print underneath the branching subnodes, I made a new query outside of the previous while loop.
   Thanks for providing the initial framework, which allowed me to modify and get the working results.

A sample of the working results is at:  
     http://216.92.61.99/category-working.htm .  

The working modified script at:
     http://216.92.61.99/category-working-php.txt .  

The table dump is at:
      http://216.92.61.99/category.sql
   
    It now has 6 select queries.  With just a few records in my table, it seems fast enough on my WAMP localhost.  But I hope it won't be noticeably slower, with all those separate queries, when it has hundreds of records and is querying from an Internet server.

    Any advice on improving this script or ensuring efficiency?  Thanks.
0
 
LVL 20

Expert Comment

by:steelseth12
Comment Utility
The script i post abouve is very similar to my last post .... i a similiar script i wrote im using a recursive function and the number of queries executed are alot more so i wouldnt worry about it ... mysql seems to handle them fine.
0
 

Author Comment

by:Randall-B
Comment Utility
Sorry, I didn't see you latest post before I posted my latest.  (I already had this page open already while I was modifying the script.)
    Your latest is close to what my latest does, but you would not have been able to see a problem that shows up with the bigger database (which I posted above): some duplicates are showing up under Vehicles, and some Computer Titles are showing up under Vehicles.
    If those bugs are fixed, I'm sure your script would be more efficient than my latest (posted above).  Thanks.
0
 
LVL 20

Accepted Solution

by:
steelseth12 earned 500 total points
Comment Utility
add $print_b=""; above   while($row_sub1 = mysql_fetch_object($get_child1)) {  
0
 

Author Comment

by:Randall-B
Comment Utility
Great.  That does it.  I'm delighted to have this working.
    Please leave a comment at my pointer question ( http:Q_22466378.html ) to mention that you answered this question, so you can get the points for both.  Thanks so much for your expert assistance.
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Generating table dynamically is the most common issue faced by php developers.... So it seems there is a need of an article that explains the basic concept of generating tables dynamically. It just requires a basic knowledge of html and little maths…
Developers of all skill levels should learn to use current best practices when developing websites. However many developers, new and old, fall into the trap of using deprecated features because this is what so many tutorials and books tell them to u…
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 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 …

772 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now