PHP/MySQL: Multi-level tree of categories

  • Impel GD
  • Professor
  • Professor
  • Impel GD
  • Posts: 834
  • Loc: Cologne, Germany

Post 3+ Months Ago

Hi all,

I need to create a 'tree' of a multi-level category structure, complete with a checkbox for each category, based on data from one MySQL table.

Code: [ Select ]
mysql> SELECT ID, ParentID FROM ProductCategories;
+-----+----------+
| ID | ParentID |
+-----+----------+
|  1 |    0 |
|  2 |    0 |
|  3 |    0 |
|  4 |    0 |
|  5 |    0 |
|  6 |    0 |
|  7 |    0 |
|  8 |    0 |
|  9 |    1 |
| 10 |    1 |
| 11 |    1 |
| 12 |    1 |
| 13 |    1 |
| 14 |    1 |
| 15 |    9 |
| 16 |    9 |
| 17 |    9 |
| 18 |    9 |
| 19 |    9 |
| 20 |    9 |
| 21 |    9 |
| 22 |    9 |
| 23 |    9 |
| 24 |    9 |
| 25 |    2 |
| 26 |    2 |
| 27 |    2 |
  1. mysql> SELECT ID, ParentID FROM ProductCategories;
  2. +-----+----------+
  3. | ID | ParentID |
  4. +-----+----------+
  5. |  1 |    0 |
  6. |  2 |    0 |
  7. |  3 |    0 |
  8. |  4 |    0 |
  9. |  5 |    0 |
  10. |  6 |    0 |
  11. |  7 |    0 |
  12. |  8 |    0 |
  13. |  9 |    1 |
  14. | 10 |    1 |
  15. | 11 |    1 |
  16. | 12 |    1 |
  17. | 13 |    1 |
  18. | 14 |    1 |
  19. | 15 |    9 |
  20. | 16 |    9 |
  21. | 17 |    9 |
  22. | 18 |    9 |
  23. | 19 |    9 |
  24. | 20 |    9 |
  25. | 21 |    9 |
  26. | 22 |    9 |
  27. | 23 |    9 |
  28. | 24 |    9 |
  29. | 25 |    2 |
  30. | 26 |    2 |
  31. | 27 |    2 |


For instance, all categories with a ParentID of '1' would appear indented under the category with an ID of '1'.

I suppose I'll need to create a multi-dimensional array, and then loop through it to create the list, but I'm a bit lost as to how to go about it.

Many thanks.
  • Anonymous
  • Bot
  • No Avatar
  • Posts: ?
  • Loc: Ozzuland
  • Status: Online

Post 3+ Months Ago

  • Nightslyr
  • Proficient
  • Proficient
  • Nightslyr
  • Posts: 283

Post 3+ Months Ago

I'm at a bit of a loss with what you're trying to do. Are the checkboxes something the user will access (I'm guessing it is, as your table suggests a store)? When the checkboxes are checked, you want the proper items in that category to be displayed, correct? If so, why not use a mix of PHP and JavaScript?

PHP would be something like:
Code: [ Select ]
$query = "SELECT * FROM ProductCategories WHERE ParentID=1";
$result = mysql_query($query);

if(mysql_num_rows($result) > 0){
  echo "<div id="parentCat1">
  while($row = mysql_fetch_assoc($result)){
   //build your items
  }
}

$query = "SELECT * FROM ProductCategories WHERE ParentID=2";
.
.
.
  1. $query = "SELECT * FROM ProductCategories WHERE ParentID=1";
  2. $result = mysql_query($query);
  3. if(mysql_num_rows($result) > 0){
  4.   echo "<div id="parentCat1">
  5.   while($row = mysql_fetch_assoc($result)){
  6.    //build your items
  7.   }
  8. }
  9. $query = "SELECT * FROM ProductCategories WHERE ParentID=2";
  10. .
  11. .
  12. .


With JavaScript, you can then use an event handler for the checkboxes:
Code: [ Select ]
//assuming the checkboxes have a name that follows the ParentID (parent1, parent2, etc)

function init(){

var boxes = new Array();

getBoxes();
attachBoxEvents();
}

function getBoxes(){
  var inputs = document.getElementsByTagName('input');
  for(var i = 0; i < inputs.length; i++){
   if(inputs[i].type == "checkbox" && inputs[i].name.search(/parent/) >= 0){
     boxes[boxes.length] = inputs[i];
   }
  }
}

function attachBoxEvents(){
   for(var i = 0; i < boxes.length; i++){
     boxes[i].onclick = showAndHide();
   }
}

function showAndHide(evt){
  evt = (evt) ? evt : ((event) ? event : NULL);
  if(evt){
   var elem = (evt.target) ? evt.target : ((evt.srcElement) ? evt.srcElement : NULL);
   if(elem){
     var index = elem.name.match([0-9]/g);
     var div = document.getElementById('parentCat' + index);
     if(div.style.display == 'block'){
      elem.style.display = '';
     }

     else{
      div.style.display = 'block';
     }
   }
  }
}

window.onload = init;
  1. //assuming the checkboxes have a name that follows the ParentID (parent1, parent2, etc)
  2. function init(){
  3. var boxes = new Array();
  4. getBoxes();
  5. attachBoxEvents();
  6. }
  7. function getBoxes(){
  8.   var inputs = document.getElementsByTagName('input');
  9.   for(var i = 0; i < inputs.length; i++){
  10.    if(inputs[i].type == "checkbox" && inputs[i].name.search(/parent/) >= 0){
  11.      boxes[boxes.length] = inputs[i];
  12.    }
  13.   }
  14. }
  15. function attachBoxEvents(){
  16.    for(var i = 0; i < boxes.length; i++){
  17.      boxes[i].onclick = showAndHide();
  18.    }
  19. }
  20. function showAndHide(evt){
  21.   evt = (evt) ? evt : ((event) ? event : NULL);
  22.   if(evt){
  23.    var elem = (evt.target) ? evt.target : ((evt.srcElement) ? evt.srcElement : NULL);
  24.    if(elem){
  25.      var index = elem.name.match([0-9]/g);
  26.      var div = document.getElementById('parentCat' + index);
  27.      if(div.style.display == 'block'){
  28.       elem.style.display = '';
  29.      }
  30.      else{
  31.       div.style.display = 'block';
  32.      }
  33.    }
  34.   }
  35. }
  36. window.onload = init;
  • Impel GD
  • Professor
  • Professor
  • Impel GD
  • Posts: 834
  • Loc: Cologne, Germany

Post 3+ Months Ago

Thanks for the reply - sorry I didn't make things clearer.

The page in question is part of a backend admin system. The checkboxes I mentioned will be there in order for the various categories to be assigned to a product (more than one category may be assigned to one product) and each category listing will have a checkbox visible permanently. However, I'll (hopefully) be able to deal with those myself...

The problem is outputting the various categories in the right order and in the correct heirachy. I'd like to do this in a way which will not be limited to a certain depth, and will naturally accommodate whatever is in the ProductCategories table. I'm expecting to use nested ULs.

Edit: I've been looking at this trying to work out how I could use it to do the job, but my brain's suffering from overheating. I have a basic grasp of arrays and recordsets, but am stuck on how to use them in this case.
  • Nightslyr
  • Proficient
  • Proficient
  • Nightslyr
  • Posts: 283

Post 3+ Months Ago

Yeah, multi-dimensional arrays can be a pain.

Question: what you're doing is dependent on the hierarchy, correct? In other words (to use playing cards as an example), one cannot have a jack without a queen first, correct? If that's the case, looping through the array(s) is easy.

The problem is making this extensible so depth isn't an issue. If this was C++, I'd suggest making an array of lists, with each element in the list having its own array with the proper products for that level of the hierarchy, but I'm not sure if PHP has the functionality to create such a data structure.
  • Impel GD
  • Professor
  • Professor
  • Impel GD
  • Posts: 834
  • Loc: Cologne, Germany

Post 3+ Months Ago

Yes, the order of the list is heirarchy-dependent in as much as I want to display it as follows (based on table posted in original post):

Code: [ Select ]
Category 1
  Category 9
    Category 15
    Category 16
    Category 17
    Category ...
    Category 24
  Category 10
    etc...
  1. Category 1
  2.   Category 9
  3.     Category 15
  4.     Category 16
  5.     Category 17
  6.     Category ...
  7.     Category 24
  8.   Category 10
  9.     etc...


Each entry should be accompanied by a checkbox, as discussed.

I have actually succeeded in doing a similar thing in order to create a two-level dropdown menu for the actual site, but, as you say, doing it irrespective of the number of levels involved is trickier.

PHP can create arrays within arrays if that's what you mean. So an element within an array can be an array, or not.
  • gisele
  • Expert
  • Expert
  • User avatar
  • Posts: 585
  • Loc: Nimes (France)

Post 3+ Months Ago

Hi,

You should look around a recursive way, it's easy to build a tree view with a recursive function.

You just have to make sure that your SQL table will be optimized with indexes on sub cat.

Take a look at this :
http://www.ozzu.com/sutra339655.html#339655

particualry when Rabid Dog and I talk about a recursive function and feel free to ask questions if you are intersed in this kind of method.

We can go further and I can give you a way to make up hierachical view (tree view) refleting the relations between the categories in your table.
  • Impel GD
  • Professor
  • Professor
  • Impel GD
  • Posts: 834
  • Loc: Cologne, Germany

Post 3+ Months Ago

Thanks very much for the link gisele - I'm out most of today I'm afraid, but will read it when I get back.
  • Impel GD
  • Professor
  • Professor
  • Impel GD
  • Posts: 834
  • Loc: Cologne, Germany

Post 3+ Months Ago

Right, I've read it now and see that the category table(s) is/are queried for every list of categories. Is there a way to hit the database once, create a multi-dimensional array from my table and then recursively loop through the array to create the heirarchecal list?

Thanks.
  • gisele
  • Expert
  • Expert
  • User avatar
  • Posts: 585
  • Loc: Nimes (France)

Post 3+ Months Ago

No need in fact, you just just have to build up a distinct list on the main table of products(where ID's are generated and uniques).

Sommething like :

built up the list :
PHP Code: [ Select ]
 
$q = "SELECT ID, productName FROM Products";
 
$r = mysql_query($q) or die(mysql_error()."<br /><br />".$q);
 
 
  1.  
  2. $q = "SELECT ID, productName FROM Products";
  3.  
  4. $r = mysql_query($q) or die(mysql_error()."<br /><br />".$q);
  5.  
  6.  

and for each item you rebuild is genealogy calling the recursive function :
PHP Code: [ Select ]
 
while($d = mysql_fetch_array($r))
 
{
 
   echo "<p class=\"node0\">".$d[1]."</p>";//display node 0
 
   single_genealogy($d[0], 1);//get and display the filiation following with node 1
 
}
 
 
  1.  
  2. while($d = mysql_fetch_array($r))
  3.  
  4. {
  5.  
  6.    echo "<p class=\"node0\">".$d[1]."</p>";//display node 0
  7.  
  8.    single_genealogy($d[0], 1);//get and display the filiation following with node 1
  9.  
  10. }
  11.  
  12.  


what will the recursive function look like?
you have to define this, somewhere before the above :
PHP Code: [ Select ]
 
function  single_genealogy($category,  $level  =  0)
 
{
 
   // the sons of the current node = the IDs that are matched with this parentid
 
        $q  =  "SELECT  p.ID, productName FROM Products join ProductCategories USING(ID) WHERE ParentID = ".$category;
 
        $r  =  mysql_query($q);  //or  die/mail/echo  mysql_error()
 
   $level++;
 
   while($d  =  mysql_fetch_row($q))
 
   {
 
      echo "<p class=\"node".$level."\">".$d[1]."</p>";//displaying  current  node  :  $d[1]
 
      //recursive  call  :
 
      single_genealogy($d[0],  $level);
 
   }
 
}
 
 
  1.  
  2. function  single_genealogy($category,  $level  =  0)
  3.  
  4. {
  5.  
  6.    // the sons of the current node = the IDs that are matched with this parentid
  7.  
  8.         $q  =  "SELECT  p.ID, productName FROM Products join ProductCategories USING(ID) WHERE ParentID = ".$category;
  9.  
  10.         $r  =  mysql_query($q);  //or  die/mail/echo  mysql_error()
  11.  
  12.    $level++;
  13.  
  14.    while($d  =  mysql_fetch_row($q))
  15.  
  16.    {
  17.  
  18.       echo "<p class=\"node".$level."\">".$d[1]."</p>";//displaying  current  node  :  $d[1]
  19.  
  20.       //recursive  call  :
  21.  
  22.       single_genealogy($d[0],  $level);
  23.  
  24.    }
  25.  
  26. }
  27.  
  28.  


In fact what I call "node".$level (node1 node2 node3 etc....) are CSS classes for <p> (for example with spécifics indent, size)
depending on the level in the tree.
If you hate CSS and its classes, You could also imagine a simple cumulative indent :
with a #define indent = value;

and then
PHP Code: [ Select ]
 
echo "<p style=\"text-indent : ".($level * ident)."\">".$d[1]."</p>";//displaying  current  node  :  $d[1]
 
 
  1.  
  2. echo "<p style=\"text-indent : ".($level * ident)."\">".$d[1]."</p>";//displaying  current  node  :  $d[1]
  3.  
  4.  


Well, that's all, I think all will just look like that, no more, , define the function, and then a a first query that list products and their names, and foreach one, we built and display is filiation.

Does that make sense ? :-)
  • Impel GD
  • Professor
  • Professor
  • Impel GD
  • Posts: 834
  • Loc: Cologne, Germany

Post 3+ Months Ago

Many thanks gisele - the category list is now in place!

Obviously this method performs a SELECT query each time the function is recursively called - I was wondering if there was another way. But it's not really an issue anyway - this is part of an admin system and so won't be used all the time.

Here's the code I ended up with as per your excellent guidance:

PHP Code: [ Select ]
// Builds category list
 
function categories($category=0,$level=0) {
 
   $query = "SELECT ID, ParentID, Name FROM ProductCategories WHERE ParentID = $category";
 
   $result = mysql_query($query) or die (mysql_error());
 
   $level++;
 
   while ($row = mysql_fetch_assoc($result)) {
 
      echo "<p class=\"catnode$level\"><input type=\"checkbox\" id=\"category" . $row['ID'] . "\" name=\"category[" . $row['ID'] . "]\" value=\"1\"><label for=\"category" . $row['ID'] . "\">" . $row['Name'] . "</label></p>\n";
 
      categories($row['ID'],$level);
 
   }
 
}
  1. // Builds category list
  2.  
  3. function categories($category=0,$level=0) {
  4.  
  5.    $query = "SELECT ID, ParentID, Name FROM ProductCategories WHERE ParentID = $category";
  6.  
  7.    $result = mysql_query($query) or die (mysql_error());
  8.  
  9.    $level++;
  10.  
  11.    while ($row = mysql_fetch_assoc($result)) {
  12.  
  13.       echo "<p class=\"catnode$level\"><input type=\"checkbox\" id=\"category" . $row['ID'] . "\" name=\"category[" . $row['ID'] . "]\" value=\"1\"><label for=\"category" . $row['ID'] . "\">" . $row['Name'] . "</label></p>\n";
  14.  
  15.       categories($row['ID'],$level);
  16.  
  17.    }
  18.  
  19. }

Thanks again. :D
  • gisele
  • Expert
  • Expert
  • User avatar
  • Posts: 585
  • Loc: Nimes (France)

Post 3+ Months Ago

Well,

Your query is right if you want :
All the childs (ID + Name) for a given ID and not current name + id all the childs IDs.

If this is what you want it's OK.

I realized than you use a single table.
Do you mean that each cat has one single parent max?
if so, no problem.

Or have you got to double a category that has more than one parent (if possible)?
in that case, with a something like that (n child <=> n parents) it would be better to split to 2 tables :
categories table and relations table, and a query like mine.


For the performance :

in fact, you can optimize this stuff just puting an index on ParentId, that will optimzed the where clause.
If you want to add an order by clause, for example to sort the childs by name, then you'll have to put an index on this field too.

For the load on the MySQL server,
If you tell me that you'll have more than a couple of hundred thousands entries for a public traffic I will tell you to seriously check the server ressources unless you make a slick pagination (with a LIMIT on the first query (the one before the call to categories() )

But nevermind, using this recursive function for admin tasks no problem, just make sure to put the right indexes so that optimize the query no matter how many categories are in the table.
  • gisele
  • Expert
  • Expert
  • User avatar
  • Posts: 585
  • Loc: Nimes (France)

Post 3+ Months Ago

Last thing very important.

An Id musn't be repeated in a filiation othewise the recursive function will get into loop for eternity.

example

cat 1=>cat 9=>cat17=>cat9 oups!cat17=>cat9 at17=>cat9 at17=>cat9 at17=>cat9 at17=>cat9 at17=>cat9 at17=>cat9 at17=>cat9 ..........
  • akazdenko
  • Born
  • Born
  • akazdenko
  • Posts: 2
  • Loc: Croatia

Post 3+ Months Ago

hi to all...

i've found this very usefull topic and i've got one question: i have one table with id, name, id_parent and i tried to create a nested tree just like Imple Gd said:

Topic 1
---Topic 2
------Topic 3
------Topic 4
---------Topic 5
Topic 6
Topic 7
---Topic 8
------Topic 9
------Topic 10
---------Topic 11
------------Topic 12
---------------Topic 13
---------------------Topic 14
---------------------Topic 15
---------Topic 16
Topic 17

etc...

i've tried to use your code but with no success :(

any help please?

thanks in advance

Zdenko, Croatia
  • knexor2
  • Proficient
  • Proficient
  • User avatar
  • Posts: 445
  • Loc: US

Post 3+ Months Ago

I did something similar to this once. Basically, you have a single table with auto incremented IDs, a name (for example's sake), and a corresponding parent ID, where the parent ID refers to another item in the same table, a parent of 0 referring to a top-level item. First off, is it necessary that the resulting hierarchy be inside a data structure, or can it be printed straight to the page?

Anyway, the thing I did took all the items and parents and printed it hierarchy-fashion into a <select> (stretched to prevent drop-down action). It's a recursive solution, BTW...

Table is formatted like

+-------------------------+
| ID | Name | Parent |

PHP Code: [ Select ]
 
<?PHP
 
 
 
$db_host = "localhost";
 
$db_un = "username";
 
$db_pass = "password";
 
$Item_DB = "Database containing item table";
 
$table = "Table containing items";
 
 
 
$link = mysql_connect($db_host, $db_un, $db_pass);
 
 
 
$tab = "        "; // this is 8 spaces, which works as a pseudo-tab character inside the <option>s
 
$tablvl = 1;
 
function print_kids($pos) { // $pos is the current position inside the hierarchy (curr item's ID)
 
   global $link;
 
   global $tab;
 
   global $tablvl;
 
   $pos = ($pos?$pos:null);
 
   $query = "SELECT * from $table WHERE parent".($pos == null ? " IS NULL" : "=".$pos);
 
         // NULL parent == top level item.  For 0-parents, replace " IS NULL" with "=0"
 
   $res = mysql_db_query($Item_DB, $query, $link);
 
   if (!$res) print(mysql_error());
 
   while($row = mysql_fetch_array($res)) {
 
      $has_kids =
 
      mysql_fetch_array(mysql_db_query($Item_DB, "SELECT * from $table where parent=$row[0]", $link)) != null;
 
      print("<option value=\"$row[0]\">");
 
      for ($i=0; $i<$tablvl; $i++) print($tab);
 
      print("$row[1]</option>\n");
 
      if ($has_kids) {
 
         $tablvl++;
 
         print_kids($row[0]); // recursive call
 
         $tablvl--;
 
      }
 
   }
 
}
 
 
 
$numrows = 1;
 
$res = mysql_db_query($Item_DB, "SELECT * FROM $table", $link);
 
while (mysql_fetch_array($res)) $numrows++;
 
// Yes, I'm sure there's a more efficient way to do this <img src="./images/smilies/icon_razz.gif" alt=":P" title="Razz" />
 
 
 
print("<select name=\"hierarchy\" size=\"$numrows\">\n");
 
print("<option value=\"null\" selected=\"selected\">Root of all items</option>\n");
 
print_kids();
 
print("</select>");
 
 
 
mysql_close($link);
 
 
 
?>
 
 
  1.  
  2. <?PHP
  3.  
  4.  
  5.  
  6. $db_host = "localhost";
  7.  
  8. $db_un = "username";
  9.  
  10. $db_pass = "password";
  11.  
  12. $Item_DB = "Database containing item table";
  13.  
  14. $table = "Table containing items";
  15.  
  16.  
  17.  
  18. $link = mysql_connect($db_host, $db_un, $db_pass);
  19.  
  20.  
  21.  
  22. $tab = "        "; // this is 8 spaces, which works as a pseudo-tab character inside the <option>s
  23.  
  24. $tablvl = 1;
  25.  
  26. function print_kids($pos) { // $pos is the current position inside the hierarchy (curr item's ID)
  27.  
  28.    global $link;
  29.  
  30.    global $tab;
  31.  
  32.    global $tablvl;
  33.  
  34.    $pos = ($pos?$pos:null);
  35.  
  36.    $query = "SELECT * from $table WHERE parent".($pos == null ? " IS NULL" : "=".$pos);
  37.  
  38.          // NULL parent == top level item.  For 0-parents, replace " IS NULL" with "=0"
  39.  
  40.    $res = mysql_db_query($Item_DB, $query, $link);
  41.  
  42.    if (!$res) print(mysql_error());
  43.  
  44.    while($row = mysql_fetch_array($res)) {
  45.  
  46.       $has_kids =
  47.  
  48.       mysql_fetch_array(mysql_db_query($Item_DB, "SELECT * from $table where parent=$row[0]", $link)) != null;
  49.  
  50.       print("<option value=\"$row[0]\">");
  51.  
  52.       for ($i=0; $i<$tablvl; $i++) print($tab);
  53.  
  54.       print("$row[1]</option>\n");
  55.  
  56.       if ($has_kids) {
  57.  
  58.          $tablvl++;
  59.  
  60.          print_kids($row[0]); // recursive call
  61.  
  62.          $tablvl--;
  63.  
  64.       }
  65.  
  66.    }
  67.  
  68. }
  69.  
  70.  
  71.  
  72. $numrows = 1;
  73.  
  74. $res = mysql_db_query($Item_DB, "SELECT * FROM $table", $link);
  75.  
  76. while (mysql_fetch_array($res)) $numrows++;
  77.  
  78. // Yes, I'm sure there's a more efficient way to do this <img src="./images/smilies/icon_razz.gif" alt=":P" title="Razz" />
  79.  
  80.  
  81.  
  82. print("<select name=\"hierarchy\" size=\"$numrows\">\n");
  83.  
  84. print("<option value=\"null\" selected=\"selected\">Root of all items</option>\n");
  85.  
  86. print_kids();
  87.  
  88. print("</select>");
  89.  
  90.  
  91.  
  92. mysql_close($link);
  93.  
  94.  
  95.  
  96. ?>
  97.  
  98.  


This is (obviously?) a slightly modified version from what I had, but it should do the same basic thing. Hope it helps. :D
  • akazdenko
  • Born
  • Born
  • akazdenko
  • Posts: 2
  • Loc: Croatia

Post 3+ Months Ago

with a slight changes i've got what i need :)

thanks a lot to u forum people (special thanx to knexor2 and gisele)

c'ya
  • tsanthoshk
  • Born
  • Born
  • tsanthoshk
  • Posts: 1

Post 3+ Months Ago

akazdenko wrote:
with a slight changes i've got what i need :)

thanks to every one who providing this code so that my task become very simple.
i am looking for this kind of code. finally i got it.

But i am looking to change this code with populating the array rather than making a recursive call
to DB. Once we got the whole data in an array. After than populate the array make them as per our needs.
So that process become very fast. If there are 1000 of categories. then it is going to be a problem for the
system as performance issue.



c'ya
  • maliwik
  • Born
  • Born
  • maliwik
  • Posts: 1

Post 3+ Months Ago

knexor2 wrote:
I did something similar to this once. Basically, you have a single table with auto incremented IDs, a name (for example's sake), and a corresponding parent ID, where the parent ID refers to another item in the same table, a parent of 0 referring to a top-level item. First off, is it necessary that the resulting hierarchy be inside a data structure, or can it be printed straight to the page?

Anyway, the thing I did took all the items and parents and printed it hierarchy-fashion into a <select> (stretched to prevent drop-down action). It's a recursive solution, BTW...

Table is formatted like

+-------------------------+
| ID | Name | Parent |

PHP Code: [ Select ]
<?PHP
 
$db_host = "localhost";
$db_un = "username";
$db_pass = "password";
$Item_DB = "Database containing item table";
$table = "Table containing items";
 
$link = mysql_connect($db_host, $db_un, $db_pass);
 
$tab = "        "; // this is 8 spaces, which works as a pseudo-tab character inside the <option>s
$tablvl = 1;
function print_kids($pos) { // $pos is the current position inside the hierarchy (curr item's ID)
   global $link;
   global $tab;
   global $tablvl;
   $pos = ($pos?$pos:null);
   $query = "SELECT * from $table WHERE parent".($pos == null ? " IS NULL" : "=".$pos);
         // NULL parent == top level item.  For 0-parents, replace " IS NULL" with "=0"
   $res = mysql_db_query($Item_DB, $query, $link);
   if (!$res) print(mysql_error());
   while($row = mysql_fetch_array($res)) {
      $has_kids =
      mysql_fetch_array(mysql_db_query($Item_DB, "SELECT * from $table where parent=$row[0]", $link)) != null;
      print("<option value=\"$row[0]\">");
      for ($i=0; $i<$tablvl; $i++) print($tab);
      print("$row[1]</option>\n");
      if ($has_kids) {
         $tablvl++;
         print_kids($row[0]); // recursive call
         $tablvl--;
      }
   }
}
 
$numrows = 1;
$res = mysql_db_query($Item_DB, "SELECT * FROM $table", $link);
while (mysql_fetch_array($res)) $numrows++;
// Yes, I'm sure there's a more efficient way to do this <!-- s:P --><img src=\"{SMILIES_PATH}/icon_razz.gif\" alt=\":P\" title=\"Razz\"><!-- s:P -->
 
print("<select name=\"hierarchy\" size=\"$numrows\">\n");
print("<option value=\"null\" selected=\"selected\">Root of all items</option>\n");
print_kids();
print("</select>");
 
mysql_close($link);
 
?>
 
  1. <?PHP
  2.  
  3. $db_host = "localhost";
  4. $db_un = "username";
  5. $db_pass = "password";
  6. $Item_DB = "Database containing item table";
  7. $table = "Table containing items";
  8.  
  9. $link = mysql_connect($db_host, $db_un, $db_pass);
  10.  
  11. $tab = "        "; // this is 8 spaces, which works as a pseudo-tab character inside the <option>s
  12. $tablvl = 1;
  13. function print_kids($pos) { // $pos is the current position inside the hierarchy (curr item's ID)
  14.    global $link;
  15.    global $tab;
  16.    global $tablvl;
  17.    $pos = ($pos?$pos:null);
  18.    $query = "SELECT * from $table WHERE parent".($pos == null ? " IS NULL" : "=".$pos);
  19.          // NULL parent == top level item.  For 0-parents, replace " IS NULL" with "=0"
  20.    $res = mysql_db_query($Item_DB, $query, $link);
  21.    if (!$res) print(mysql_error());
  22.    while($row = mysql_fetch_array($res)) {
  23.       $has_kids =
  24.       mysql_fetch_array(mysql_db_query($Item_DB, "SELECT * from $table where parent=$row[0]", $link)) != null;
  25.       print("<option value=\"$row[0]\">");
  26.       for ($i=0; $i<$tablvl; $i++) print($tab);
  27.       print("$row[1]</option>\n");
  28.       if ($has_kids) {
  29.          $tablvl++;
  30.          print_kids($row[0]); // recursive call
  31.          $tablvl--;
  32.       }
  33.    }
  34. }
  35.  
  36. $numrows = 1;
  37. $res = mysql_db_query($Item_DB, "SELECT * FROM $table", $link);
  38. while (mysql_fetch_array($res)) $numrows++;
  39. // Yes, I'm sure there's a more efficient way to do this <!-- s:P --><img src=\"{SMILIES_PATH}/icon_razz.gif\" alt=\":P\" title=\"Razz\"><!-- s:P -->
  40.  
  41. print("<select name=\"hierarchy\" size=\"$numrows\">\n");
  42. print("<option value=\"null\" selected=\"selected\">Root of all items</option>\n");
  43. print_kids();
  44. print("</select>");
  45.  
  46. mysql_close($link);
  47.  
  48. ?>
  49.  


This is (obviously?) a slightly modified version from what I had, but it should do the same basic thing. Hope it helps. :D



Oh.. My.. God... You have just brought an end to WEEKS of me fiddling and stomping. You make something so complicated into a simple recursive function that has literally brought tears to my eyes. Something so flawless and perfect (well, almost) that it will be used in EVERY one of my projects that requires capability like this. Thank you SO much for this solution! This will probably be my only post, as I was so excited to get it working I just HAD to register on here to thank you!

May your programming travels be bug free and smooth as can be!

-- Mike
  • exceil
  • Born
  • Born
  • exceil
  • Posts: 1

Post 3+ Months Ago

Hi!
I try to use this code below and I have a problem because it don't works and I don't know why :(
I changed connection settings on the beginning, my db looks like knexor2 wrote and nothing, I have just "Root of all items" in place where categories should be.

knexor2 wrote:
I did something similar to this once. Basically, you have a single table with auto incremented IDs, a name (for example's sake), and a corresponding parent ID, where the parent ID refers to another item in the same table, a parent of 0 referring to a top-level item. First off, is it necessary that the resulting hierarchy be inside a data structure, or can it be printed straight to the page?

Anyway, the thing I did took all the items and parents and printed it hierarchy-fashion into a <select> (stretched to prevent drop-down action). It's a recursive solution, BTW...

Table is formatted like

+-------------------------+
| ID | Name | Parent |

PHP Code: [ Select ]
<?PHP
 
$db_host = "localhost";
$db_un = "username";
$db_pass = "password";
$Item_DB = "Database containing item table";
$table = "Table containing items";
 
$link = mysql_connect($db_host, $db_un, $db_pass);
 
$tab = "        "; // this is 8 spaces, which works as a pseudo-tab character inside the <option>s
$tablvl = 1;
function print_kids($pos) { // $pos is the current position inside the hierarchy (curr item's ID)
   global $link;
   global $tab;
   global $tablvl;
   $pos = ($pos?$pos:null);
   $query = "SELECT * from $table WHERE parent".($pos == null ? " IS NULL" : "=".$pos);
         // NULL parent == top level item.  For 0-parents, replace " IS NULL" with "=0"
   $res = mysql_db_query($Item_DB, $query, $link);
   if (!$res) print(mysql_error());
   while($row = mysql_fetch_array($res)) {
      $has_kids =
      mysql_fetch_array(mysql_db_query($Item_DB, "SELECT * from $table where parent=$row[0]", $link)) != null;
      print("<option value=\"$row[0]\">");
      for ($i=0; $i<$tablvl; $i++) print($tab);
      print("$row[1]</option>\n");
      if ($has_kids) {
         $tablvl++;
         print_kids($row[0]); // recursive call
         $tablvl--;
      }
   }
}
 
$numrows = 1;
$res = mysql_db_query($Item_DB, "SELECT * FROM $table", $link);
while (mysql_fetch_array($res)) $numrows++;
// Yes, I'm sure there's a more efficient way to do this <!-- s:P --><img src=\"{SMILIES_PATH}/icon_razz.gif\" alt=\":P\" title=\"Razz\"><!-- s:P -->
 
print("<select name=\"hierarchy\" size=\"$numrows\">\n");
print("<option value=\"null\" selected=\"selected\">Root of all items</option>\n");
print_kids();
print("</select>");
 
mysql_close($link);
 
?>
 
  1. <?PHP
  2.  
  3. $db_host = "localhost";
  4. $db_un = "username";
  5. $db_pass = "password";
  6. $Item_DB = "Database containing item table";
  7. $table = "Table containing items";
  8.  
  9. $link = mysql_connect($db_host, $db_un, $db_pass);
  10.  
  11. $tab = "        "; // this is 8 spaces, which works as a pseudo-tab character inside the <option>s
  12. $tablvl = 1;
  13. function print_kids($pos) { // $pos is the current position inside the hierarchy (curr item's ID)
  14.    global $link;
  15.    global $tab;
  16.    global $tablvl;
  17.    $pos = ($pos?$pos:null);
  18.    $query = "SELECT * from $table WHERE parent".($pos == null ? " IS NULL" : "=".$pos);
  19.          // NULL parent == top level item.  For 0-parents, replace " IS NULL" with "=0"
  20.    $res = mysql_db_query($Item_DB, $query, $link);
  21.    if (!$res) print(mysql_error());
  22.    while($row = mysql_fetch_array($res)) {
  23.       $has_kids =
  24.       mysql_fetch_array(mysql_db_query($Item_DB, "SELECT * from $table where parent=$row[0]", $link)) != null;
  25.       print("<option value=\"$row[0]\">");
  26.       for ($i=0; $i<$tablvl; $i++) print($tab);
  27.       print("$row[1]</option>\n");
  28.       if ($has_kids) {
  29.          $tablvl++;
  30.          print_kids($row[0]); // recursive call
  31.          $tablvl--;
  32.       }
  33.    }
  34. }
  35.  
  36. $numrows = 1;
  37. $res = mysql_db_query($Item_DB, "SELECT * FROM $table", $link);
  38. while (mysql_fetch_array($res)) $numrows++;
  39. // Yes, I'm sure there's a more efficient way to do this <!-- s:P --><img src=\"{SMILIES_PATH}/icon_razz.gif\" alt=\":P\" title=\"Razz\"><!-- s:P -->
  40.  
  41. print("<select name=\"hierarchy\" size=\"$numrows\">\n");
  42. print("<option value=\"null\" selected=\"selected\">Root of all items</option>\n");
  43. print_kids();
  44. print("</select>");
  45.  
  46. mysql_close($link);
  47.  
  48. ?>
  49.  


This is (obviously?) a slightly modified version from what I had, but it should do the same basic thing. Hope it helps. :D

Post Information

  • Total Posts in this topic: 18 posts
  • Users browsing this forum: No registered users and 109 guests
  • You cannot post new topics in this forum
  • You cannot reply to topics in this forum
  • You cannot edit your posts in this forum
  • You cannot delete your posts in this forum
  • You cannot post attachments in this forum
 
 

© 1998-2014. Ozzu® is a registered trademark of Unmelted, LLC.