To expand on RabidDog's idea using a recursive solution, I am in agreement with that.
The first solution I provided was just the SQL solution, which can be taken in a global applicative solution, depending on what he needs as an approach vertical/horizontal/both.
Horizontal:
SELECT c.ID_CATEGORY, c.NAME CATEGORY, ID_SUB_CATEGORY, c2.NAME_CATEGORY FROM CATEGORY c JOIN RELATION r ON (c.ID_CATEGORY=r.ID_CATEGORY) JOIN CATEGORY c2 ON(ID_SUB_CATEGORY=c2.ID_CATEGORY) ORDER BY c.ID_CATEGORY
Vertical, something looking like that:
function single_genealogy($category, $level = 0)
{
$q = "SELECT ID_SUB_CATERORY, NAME_CATEGORY FROM RELATION JOIN CATEGORY c USING(ID_CATEGORY) WHERE c.ID_CATEGORY=".$category;
$r = mysql_query($q); //or die/mail/echo mysql_error()
if(mysql_num_rows($r))
{
$level++;
while($d = mysql_fetch_row($q))
{
//displaying current node : $d[1]...
//recursive call :
single_genealogy($d[0], $level);
}
}
}
for a given category, that'll show :
level(<=given one)
level
level
level
level
level
Very importantly, a category mustn't be twice in a single genealogical tree otherwise it'll turn the recursive solution into a perpetual one! 🙂