Help needed with a recursive function to display valid unordered list

rollerbob

I have a MySQL table that stores a hierarchy of categories using a parent_category field and I'd like to display this information as a nested list. I have a recursive function that does does this, but the problem is the unordered list that it generates isn't valid, therefore, I need some help with the php logic to make the list well formed.

Here's an example of how the data structure:

id | category | parent_category

1 | Winter Olympics | 0
2 | Summer Olympics | 0
3 | Skiiing | 1
4 | Track & Field | 2
5 | Javelin | 4
6 | Slalom | 3
7 | Downlhill | 3

... and here's how I want to display it:

<ul>
   <li>Winter Olympics
     <ul>
         <li>Skiing
             <ul>
                 <li>Slalom</li>
                 <li>Downhill</li>
             </ul>
         </li>
     </ul>
   </li>
   <li>Summer Olympics
      <ul>
         <li>Track &amp; Field
             <ul>
                 <li>Javelin</li>
             </ul>
         </li>
      </ul>
    </li>
</ul>

... and here's the code I'd like to improve:

function display_categories($parent, $level) {

 $result = mysql_query("SELECT id, category, parent_category FROM table WHERE parent_category='$parent'");

 static $str = "";
 $str .= "<ul>\n";
 while ($row = mysql_fetch_array($result)) {

	  $str .= "<li><a href=\"\">".$row['category']."</a>";

	  display_categories($row['id'], $level+1);

	  $str .= "</li>\n";

 }
 $str .= "</ul>\n";
 return $str;
}

Anyone help would be greatly appreciated.

cbj4074

You just need to add one more column to your table, and perhaps call it "level" (TINYINT, 1). I think you will have an easier time managing the category level that way.

For example, the "level" would be 0 for Winter Olympics, 1 for Skiing, 2 for Slalom, and 2 for Downhill. With those two pieces of information, it is possible to print the well-formed list, as in your example.

I have done something almost exactly like this and as I recall, I used a muti-dimensional array to sort out the result set. You could loop through the mysql_fetch_array() resultant array and create a new array that has your categories stored hierarchically.

If you use a "for" loop, and inside it nest a "foreach" loop (foreach of your MySQL result set rows), you can do something like this:

"Check the category level; if it's equal to $i, and $i is 0, create a new sub-array that is zero levels deep."

You could continue this pattern, looping through your query result and assigning each category to a "tier" of your new array. You will, of course, need to be familiar with arrays, and more specifically, keys, indeces, and manipulating them.

rollerbob

Thanks for your help.

I have a form whereby people add a category name and select the parent category from a drop-down list. So that's how the hierarchy is created. It would be difficult for the user to know what level the category is at when they enter it, although I suppose it could be calculated when the user submits the form. I don't really want to make any changes to the database, so a PHP solution would be preferable.

niroshan

try this,

$sql="SELECT * FROM categories WHERE mainId=0";
$row=mysql_query($sql) or die ("error in the query"); 

if(mysql_num_rows($row)<>0)
{
	print "<ul>";
	while($result=mysql_fetch_array($row))
	{
		print "<li>".$result['category'];
		subItems($result['id']);
		print "</li>";
	}
	print "</ul>";
}

function subItems($mainId)
{
	$sql="SELECT * FROM categories WHERE mainId=".$mainId;
	$tempRow=mysql_query($sql) or die ("Error in temp query");

if(mysql_num_rows($tempRow)<>0)
{
	print "<ul>";
	while($tempResult=mysql_fetch_array($tempRow))
	{
		print "<li>".$tempResult['category'];
		subItems($tempResult['id']);
		print "</li>";
	}
	print "</ul>";
}
}

🙂