HELP! going crazy trying to figure this out!

[mattsutton1457]

Hi, thanks for looking.

 

I have a database table "content" with which i'm trying to create a sitemap. The content table has a field "content_parent" which contains the ID of the row that it relates to ("content_id") (i.e. a related page in the navigation). ("content_parent=0" means the item is top level navigation)

 

Basically, I want the sitemap to display as follows, if someone can make sense of this and help me out i would REALLY appreciate it. (the items in brackets don't need to be displayed but should make it easier to understand)

 

- Home (content_parent=0, content_id=1)

  //check if there are any pages where content_parent=1) if so...

  -  Home - sub page 1 (content_parent=1, content_id=2)

    //check if there are any pages where content_parent=2) if so...

    - Home - sub page 2 (content_parent=2, content_id=3)

        //check if there are any pages where content_parent=3) there aren't so move on...

 

- About us (content_parent=0, content_id=4)

  //check if there are any pages where content_parent=4) if so...

  -  About us - sub page 1 (content_parent=4, content_id=5)

 

etc

etc

etc

 

I hope this makes even the slightest of sense

 

If anyone can shed ANY light on this, thank you!

[de.monkeyz]

Using recursion would be the best way I think, so I'm just gonna show you a pseudo function, hopefully you can figure out the sql yourself:

 

function showMap($parent_id = 0)
{
     $query = "SELECT * FROM table WHERE parent_id = '$parent_id'";
     $result = query($query);
     if(num_rows($result) == 0) return;
     
    while($row = fetch_assoc($result))
    {
          echo $row['title']; //Do any sort of formatting here
          showMap($row['id']); //Find child elements
    }

}

 

That function using recursion will go as deep into the structure as neccessary

[mattsutton1457]

Thanks very much for the reply, the solution you provided seems to be exactly what im looking for. I've modified the code you supplied to match my table fields, however when i call the page, there is no error, just nothing at all is being displayed.

 

Table fields are need for this are:

content_id - id of the content

content_title - the page title to be displayed in the nav

content_parent - the content_id of the parent page

 

code:

function showMap($content_parent = 0)
{
     $query = "SELECT * FROM content WHERE content_parent = '$content_parent'";
     $result = query($query);
     if(num_rows($result) == 0) return;
     
    while($row = fetch_assoc($result))
    {
          echo $row['content_title']; //Do any sort of formatting here
          showMap($row['content_id']); //Find child elements
    }

}

 

I'm sure i'm doing something really stupid which is stopping it from displaying.. can anyone point me in the right direction??

[ignace]

There's an article on MySQL in how to handle such data (called Adjacency List Model)

 

http://dev.mysql.com/tech-resources/articles/hierarchical-data.html