PHP function to echo out a nested grouped list from MYSQL

[magnetbox]

Hello all..

 

I've done some PHP in my day, but I'm no guru, and I've been stuck trying to get one function to work properly.

What I want in a nutshell is an nested list with "Clusters" then SubClusters" listed under the appropriate cluster.

I have a database full of rows with cluster / subcluster declaration.

 

Here's the thing.

A row may have a cluster only.. subcluster is totally optional.

 

So, I need to be able to pull all the cluster and subcluster names, and nest the subclusters properly underneath their parent cluster.

 

It's probably something stupid I'm just overlooking....

 

Here's what I have working, except for the fact that its showing duplicate clusters ( I know why I have dups, but this gives me all the info I want to display)

echo'<ul>';

$result = mysql_query("SELECT cluster, subcluster FROM TABLE GROUP BY cluster, subcluster");
	while ($row = mysql_fetch_array($result)) 
		{

			if($row["subcluster"] != null)
			{
			echo '<li><a href="index.php?sort='.$row["cluster"].'">'.$row["cluster"].'</a></li>';
			echo '<ul>';
			echo '<li><a href="index.php?sort='.$row["subcluster"].'">- '.$row["subcluster"].'</a></li>';		
			echo '</ul>';
			}
			else 
			{
			echo '<li><a href="index.php?sort='.$row["cluster"].'">'.$row["cluster"].'</a></li>';
			}		
		}
echo '</ul>';

 

 

 

[dptr1988]

I don't know if that's possible to do in one SQL query. The results of an SQL statement is a one dimensional array. Somebody else may have a better solution for you, but here is how I have done it:

 

<?php
echo'<ul>';

$result = mysql_query("SELECT cluster, cluster_id FROM TABLE");
	while ($row = mysql_fetch_array($result)) 
		{


			$result2 = mysql_query("SELECT subcluster FROM TABLE WHERE parent_cluster_id = {$row['cluster_id']}");
			if (mysql_num_rows($result) > 0)
			{
				echo '<ul>';
				 while($row2 = mysql_fetch_array($result2)
				{	
					echo '<li><a href="index.php?sort='.$row2["subcluster"].'">- '.$row2["subcluster"].'</a></li>';			

				}
				echo '</ul>';
			}
			else 
			{
			  echo '<li><a href="index.php?sort='.$row["cluster"].'">'.$row["cluster"].'</a></li>';
			}		
		}
echo '</ul>';
?>

 

The problem with this is that you only can go to the first subcluster. If one subcluster can be the parent to another, this wouldn't work.

 

I think that SQL does not allow you to select data from infinitely nested rows in one query and also the fact that mysql_query() returns an 1 dimensional array also limits you.

 

This example is just what I've used to get around this problem. I hope that someone else can point out a more elegant solution.

[moselkady]

 

I think this may work for you:

 

<?
echo'<ul>';
$this_cluster = '';	
$result = mysql_query("SELECT cluster, subcluster FROM TABLE GROUP BY cluster, subcluster");
	while ($row = mysql_fetch_array($result)) 
		{
			if ($row['cluster'] != $this_cluster)
			{
				if ($this_cluster != '') echo '</ul>';
				$this_cluster = $row['cluster'];
				echo '<li><a href="index.php?sort='.$row["cluster"].'">'.$row["cluster"].'</a></li>';
				echo '<ul>';
			}
			if($row["subcluster"] != null)
			{
				echo '<li><a href="index.php?sort='.$row["subcluster"].'">- '.$row["subcluster"].'</a></li>';		
			}		
		}
 	 	 if ($this_cluster != '') echo '</ul>';
 echo '</ul>';
?>