sitemap with sql

[1internet]

So I have a site with product categories, sub-cats, sub-sub, sub-sub-sub, and even sub-sub-sub-sub. So each category could be up to 5 levels deep.

 

I want to be able to go to a category page and pull all the categories, and sub-cats this category belongs to, to create a sitemap.

 

The database is structured like:

category_id   |   category_name   |   parent_id   |   category_url
1   |   Clothes   |   null   |   clothes
2   |   Shirts   |   1   |   shirts
3   |   Designer Shirts   |   2   |   designer-shirts

So if I was to create a sitemap for Designer Shirts, I would want

<a href="index.php">Home</a> >> <a href="clothes">Clothes</a> >> <a href="clothes/shirts">Shirts</a> >> <a href="clothes/shirts/designer-shirts">Design Shirts</a>

So I want to do a mysql join that will find me all the categories that belong to the current page, and pull the urls (urls also have the parents urls contained too). And the query has to be useable for any hierachy of the category page.

 

 

[Barand]

You need a recursive function. What have you tried so far?

[1internet]

Play around with some table joins, but got nowhere, I think this is out of my depth.

[Barand]

try something like this

$sql = "SELECT category_id, name, parent
        FROM category";
$res = mysql_query($sql);
while (list($id, $name, $parent) = mysql_fetch_row($res)) {
    $data[$parent][] = array('id'=>$id, 'name'=>$name);
}
    

// call the recursive function
displayHierarchy($data, 0);

// function to print a category then its child categories 
function displayHierarchy(&$arr, $parent, $indent=0)
{
    $ind = $indent * 50;
    if (isset($arr[$parent]))
    foreach($arr[$parent] as $rec)
    {
        echo "<div style='width:300px; margin-top:5px; margin-left: {$ind}px; padding:5px; border:1px solid gray;'>
        {$rec['name']}
        </div>" ;
        displayHierarchy($arr, $rec['id'], $indent+1);
    }
    
}