Back To Programming / Scripting / Coding Forum

PHP/MySQL: Multi-level tree of categories

  • tsanthoshk
  • Born
  • Born
  • No Avatar
  • Joined: Feb 20, 2007
  • Posts: 1
  • Status: Offline

Post February 20th, 2007, 11:44 pm

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
  • Anonymous
  • Bot
  • No Avatar
  • Joined: 25 Feb 2008
  • Posts: ?
  • Loc: Ozzuland
  • Status: Online

Post February 20th, 2007, 11:44 pm

  • maliwik
  • Born
  • Born
  • No Avatar
  • Joined: Jun 17, 2011
  • Posts: 1
  • Status: Offline

Post June 17th, 2011, 11:51 am

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
  • No Avatar
  • Joined: Dec 26, 2011
  • Posts: 1
  • Status: Offline

Post December 26th, 2011, 4:31 pm

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

1, 2

Post Information

  • Total Posts in this topic: 18 posts
  • Users browsing this forum: No registered users and 167 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
 
cron
 

© 2011 Unmelted, LLC. Ozzu® is a registered trademark of Unmelted, LLC.