so i'm working with this loop, but it isn't working with me...

[Monkee_Of_Evil]

what i'm trying to do is make a function to display categories from a db in a List or Drop-Down menu. I want the categories to display with subcategories, and so on. with multipule subcategories allowed. I'm working with 2 tables:

link_cats and link_ChildCats

Link_cats contains category information such as ID number, the ID number of it's parent Category ( if any ), name, and description and is the first table any queries are run on.

link_cats structure:
lcatid INT auto_inc
lcParentId INT
lcDesc TEXT
lcName TEXT

lcatid = primary key, id number of each category.
lcParentId = lcatid of the parent category; if 1 or 0 then the category has no parent.
lcDesc and lcName are name and description.

link_ChildCats is a simple 2 field table. Every time a category is added to the db it's parent category information is sent here along with the ID #

link_ChildCats structure:
cCatId INT
ParentCatId INT

cCatID = child category ID
ParentCatID = Parent Category ID # of the cCatID , should be the same as the lcParentID of lcatID when comparing.

My code is below, I think I'm not using the right type of loop to do this but i'm not sure.

The first part of the while loop checks link_cats for "master categories" meaning, that the category has no Parent, and is displayed by itself - it outputs the category ID into the value field, and displays the name.

Second nested while checks ChildCats for categories that belong to the parent which was just displayed.

third part of the script displays subcategories in the same fashion.

EDIT: Ok so I fixed something with my insert code and it started working sorta. It'll display the subcategories for each Main category, but it WON'T display the subcategories of subcategories at all.

Here's a link to see it in action right now to see what i'm talking about:
http://www.napjunk.net/test/acp.php?menu=links&submen=linkCategoryManager

function listLinkCatBox()
	{
	global $njDB;
	$query = "SELECT  * FROM link_cats WHERE `lcParentId`=0 ORDER BY lcName";
	$q = mysql_query($query,$njDB);
    	while($row = mysql_fetch_array($q))
		{
          echo('<option value=\"'.$row['lcatId'].'\">'. $row['lcName'].'</option>');
		  $query2 = "SELECT * FROM link_ChildCats WHERE `ParentCatId`=". $row['lcatid'];
		  $q2 = mysql_query($query2,$njDB);
          while ($row2 = mysql_fetch_array($q2))
		  	{
			$query3 = "SELECT  * FROM link_cats WHERE `lcatid`=".$row2['cCatId']." ORDER BY lcName";
			$q3 = mysql_query($query3,$njDB);
			while($row3 = mysql_fetch_array($q3))
				{
				echo('<option value=\"'.$row3['lcatId'].'\">--'. $row3['lcName'].'</option>');
				}

		}
	}
}

[Monkee_Of_Evil]

seriously though, can i get some help with this? do i need to re-word what the problem is or something?

[Weedpacket]

Not entirely sure, but in the third query is the reference to lcatid correct? Or should that be lcParentId?

Incidentally, your query may run faster if you only select the fields you want to use, rather than selecting *. I'm also a bit nervous about nesting queries inside loops (five subcategories with three subsubcategories each would make for twenty-one hits on the database), but doing something about that would take a bit more work.

[robert_w_ark]

Originally posted by Monkee Of Evil
what i'm trying to do is make a function to display categories from a db in a List or Drop-Down menu. I want the categories to display with subcategories, and so on. with multipule subcategories allowed. I'm working with 2 tables:

link_cats and link_ChildCats

Link_cats contains category information such as ID number, the ID number of it's parent Category ( if any ), name, and description and is the first table any queries are run on.

link_cats structure:
lcatid INT auto_inc
lcParentId INT
lcDesc TEXT
lcName TEXT

lcatid = primary key, id number of each category.
lcParentId = lcatid of the parent category; if 1 or 0 then the category has no parent.
lcDesc and lcName are name and description.

link_ChildCats is a simple 2 field table. Every time a category is added to the db it's parent category information is sent here along with the ID #

link_ChildCats structure:
cCatId INT
ParentCatId INT

cCatID = child category ID
ParentCatID = Parent Category ID # of the cCatID , should be the same as the lcParentID of lcatID when comparing.

My code is below, I think I'm not using the right type of loop to do this but i'm not sure.

The first part of the while loop checks link_cats for "master categories" meaning, that the category has no Parent, and is displayed by itself - it outputs the category ID into the value field, and displays the name.

Second nested while checks ChildCats for categories that belong to the parent which was just displayed.

third part of the script displays subcategories in the same fashion.

EDIT: Ok so I fixed something with my insert code and it started working sorta. It'll display the subcategories for each Main category, but it WON'T display the subcategories of subcategories at all.

Here's a link to see it in action right now to see what i'm talking about:
http://www.napjunk.net/test/acp.php?menu=links&submen=linkCategoryManager

function listLinkCatBox()
	{
	global $njDB;
	$query = "SELECT  * FROM link_cats WHERE `lcParentId`=0 ORDER BY lcName";
	$q = mysql_query($query,$njDB);
    	while($row = mysql_fetch_array($q))
		{
          echo('<option value=\"'.$row['lcatId'].'\">'. $row['lcName'].'</option>');
		  $query2 = "SELECT * FROM link_ChildCats WHERE `ParentCatId`=". $row['lcatid'];
		  $q2 = mysql_query($query2,$njDB);
          while ($row2 = mysql_fetch_array($q2))
		  	{
			$query3 = "SELECT  * FROM link_cats WHERE `lcatid`=".$row2['cCatId']." ORDER BY lcName";
			$q3 = mysql_query($query3,$njDB);
			while($row3 = mysql_fetch_array($q3))
				{
				echo('<option value=\"'.$row3['lcatId'].'\">--'. $row3['lcName'].'</option>');
				}

		}
	}
}

[/B]

That really didnt look right - So try this code if you will..

function listLinkCatBox()
{
  $query = "SELECT  * FROM link_cats WHERE `lcParentId`=0 ORDER BY lcName";
  $query = mysql_query(SELECT * FROM link_cats WHERE lcParentId=0 ORDER BY lcName ASC); // or DESC
  while($row = mysql_fetch_array($query))
  {
    echo "<option value=\"".$row['lcatId']."\">".$row['lcName']."</option>";
    $query2 = mysql_query("SELECT * FROM link_ChildCats WHERE ParentCatID=".$row['lcatid']."");
    while ($row2 = mysql_fetch_array($query2))
    {
      $query3 = mysql_query("SELECT * FROM link_cats WHERE lcatid=".$row2['cCatId']." ORDER BY lcName ASC"); // or DESC
      while($row3 = mysql_fetch_array($query3))
      {
        echo "<option value=\"".$row3['lcatId']."\">--". $row3['lcName']."</option>";
      }
    }
  }
}

I'm sure this will work, -Rob

[Monkee_Of_Evil]

Not entirely sure, but in the third query is the reference to lcatid correct? Or should that be lcParentId?

your right, it should. I cought that but I don't have the current copy of the code on this computer.

Incidentally, your query may run faster if you only select the fields you want to use, rather than selecting *. I'm also a bit nervous about nesting queries inside loops (five subcategories with three subsubcategories each would make for twenty-one hits on the database), but doing something about that would take a bit more work.

I don't mind re-coding it if it'll make it work properly. I'm writing this stuff so I can learn the language and such. If you have any suggestions as to how i should go about re-doing this then i'm all ears.

[robert_w_ark]

they queryies shouldnt lag 'too' bad.. I doubt it will even be noticable.