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, and a parent of 0 refers 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.

The MySQL table is formatted like:

This is (obviously?) a slightly modified version of what I had, but it should do the same basic thing:

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

?>

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;

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.

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.

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.

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/questions/337876/how-to-create-infinitely-nested-categories-using-php-and-mysql#post-339655

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

We can go further and I can give you a way to make up a hierarchical view (tree view) reflecting the relations between the categories in your table.

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 ? 🙂

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

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.

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

I've found this a very useful 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. For example:

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

I've tried to use your code but with no success 😟