Displaying a hierachy with php

[meghatronic]

I have a mysql table that contains parent|child. Just two columns and 800 rows.

I'd like to extrapolate a familial relation from this list. And wrote something in PHP to do this, however it is VERY inefficient. There can be up to 8 or 9 levels on the hierachy in places, in others just 2. I was writing it in a case-by-case way... but proved too long and inefficient!

There must be an easy way to iterate up and down the list to use:

food|cheese
drink|wine
drink|beer
drink|soda
wine|red
wine|white
beer|lager
beer|bitter
beer|ale

to produce, for example if I searched for lager:

drink

beer

lager​

bitter​

ale​

soda​

wine

red​

white​

note food and cheese wasn't called.

 

[meghatronic]

could you give me an example of how this data is actually returned? I'm having a hard time picturing it based on your explanation. Could you var_dump() the array returned from a query to this table and I'll take a look for you? It'll need a recursive function, but I'll have a better idea of which one with some data

The data is just stored in the database at the moment as parent/child list.

I was recurisvely searching upwards. For example:

1) User searches for ale.
2) Db queries for parents to ale.
3) Does parents have parents? and so on up the tree.
4) Do each of these parents have parents and children? and so on up and down the tree.

I'm sorry I'm not expalining it well

 

[meghatronic]

Haven't got the time to write a search algorithm atm but if you haven't got an index on each column then it may speed things up a bit by adding them.

PS - it's an interesting problem though, i'd probably approach it as follows:

A findParent() function that takes a search param, queries the db for a parent and recursively calls itself until it finds the root node, and then returns each node it found on the way.

A findChildren() function that does the reverse of findParent() until it finds the leaf nodes.

Bolt the results from the two together and you should have the full hierachy.

This is what I started doing this morning, only it doesn't work!

The findparent bit:

//process hierarchy
function get_parent($child)
  {
	
	$query = "SELECT `parent` FROM `hierarchy` WHERE `child` = '" . $child . "'";
	$result = mysql_query($query) or die('Error, query failed');
	$num_results =  mysql_num_rows($result);

	if ($num_results == 1) {
		while ($rows = mysql_fetch_array($result)) {
			$parent = trim($rows['parent']);
			
			if ($parent != null){
				get_parent($parent);
			}
		}
	}
	return $parent;
	}

echo get_parent($gc);

It calls only the $gc parent, not the overall top parent. If I echo out $parent on the if not null line, it does echo them out.... though.

 

[meghatronic]

Thank to LazyManc's help, I've now got the following:

function findParent($child) {
    $query = "SELECT * FROM hierarchy WHERE child = '$child'";
    $result = mysql_query($query) or die('Error, query failed: ' . mysql_error());
    
    if(mysql_num_rows($result) == 0) {
        return $child;
    } else {
        $row = mysql_fetch_assoc($result);
        return findParent($row['parent']);
    }
}


function findChildren($parent) {
$children[$parent] = array();
    $query = "SELECT child FROM hierarchy WHERE parent = '$parent'";
    $result = mysql_query($query) or die('Error, query failed: ' . mysql_error());
		while ($row = mysql_fetch_array($result)) {
		array_push($children[$parent], $row['child']);		
		}		
	print_r($children);
}

$top_parent = findParent($gc);
findChildren($top_parent);

Naturally it doesn't return children's children and so on as I haven't figured out how to do that yet!

EDIT: Got the below to work (although it isn't recursive!) to check all the information is there. It only goes a few levels deep though as it isn't recursive. Any pointers?

foreach ($arr as $key => $value) {
   echo $key . "<br>";

   foreach ($value as $split) {
   $new = findChildren($split);
   foreach ($new as $keya => $valuea) {
		echo "&nbsp;&nbsp;&nbsp;" . $keya . "<br>";
   foreach ($valuea as $bits) {
		echo "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;" . $bits . "<br>";
			$newa = findChildren($bits);
				foreach ($newa as $valueb) {
									foreach ($valueb as $valuec) {
					echo "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;" . $valuec . "<br>";
					$newb = findChildren($valuec);
					foreach ($newb as $valued) {
									foreach ($valued as $valuee) {
					echo "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;" . $valuee . "<br>";
					}
					}
					}
					}
					
			}
		}	
   }
}

 

[meghatronic]

How hard it would be to piece the two together I have no idea!

Cheers dude, I'll try that later.

I'm pleased to say I've finished it... half of it in an intelligent recurvise fashion. Half of it in a very naughty way. As I know how many levels there are, I've merely nested foreach loops I must fix this to be honest!

EDIT - What I mean is:

$top_parent = findParent($gc);

$children = findChildren($top_parent);

echo boldit($top_parent, $gc) . "<br>";
foreach ($children as $key => $value) {
   echo "&nbsp;&nbsp;&nbsp;" . boldit($key, $gc) . "<br>";
      if ($value!=null){
		foreach ($value as $keya => $valuea) {
			echo "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;" . boldit($keya, $gc) . "<br>";
			if ($valuea!=null){
			foreach ($valuea as $keyb => $valueb) {
			echo "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;" . boldit($keyb, $gc) . "<br>";
			if ($valueb!=null){
			foreach ($valueb as $keyc => $valuec) {
			
			echo "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;" . boldit($keyc, $gc) . "<br>";
		}
		}
		}
		}
		}
		}
		}

(please forgive indentation, my tab key doesn't work)

 

[meghatronic]

Any ideas how to make the above recursive?

It's pretty lazy/bad of me to do it like that... some pointers would be great!