IG

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.

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 |

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.

add a comment
0

17 Answers

  • Votes
  • Oldest
  • Latest
NI

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:

$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";
.
.
.

With JavaScript, you can then use an event handler for the checkboxes:

//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;
add a comment
0
GI

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.

add a comment
0
MA

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

$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);

?>

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

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

add a comment
0
TS

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

add a comment
0
AK

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

add a comment
0
KN

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

$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 πŸ˜› 

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);

?>

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

add a comment
0
AK

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

add a comment
0
GI

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 ..........

add a comment
0
IG

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:

// 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);
	}
}

Thanks again. 😁

add a comment
0
IG

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.

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.

add a comment
0
GI

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 :

$q = "SELECT ID, productName FROM Products";
$r = mysql_query($q) or die(mysql_error()."<br /><br />".$q);

and for each item you rebuild is genealogy calling the recursive function :

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
}

what will the recursive function look like?
you have to define this, somewhere before the above :

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); 
	} 
} 

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

echo "<p style=\"text-indent : ".($level * ident)."\">".$d[1]."</p>";//displaying  current  node  :  $d[1]

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 ? πŸ™‚

add a comment
0
IG

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.

add a comment
0
IG

Thanks very much for the link gisele - I'm out most of today I'm afraid, but will read it when I get back.

add a comment
0
GI

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 :
https://www.ozzu.com/forum/337876/creating-a-category-system-with-multiple-sub-categories#post-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.

add a comment
0
IG

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):

Category 1
    Category 9
        Category 15
        Category 16
        Category 17
        Category ...
        Category 24
    Category 10
        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.

add a comment
0
NI

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.

add a comment
0
EX

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.

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

$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);

?>

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

add a comment
0