Forum parent_id idea

[bobnew32]

In my travels of learning php and owning my own site, I wanted to complement it with its own custom made forum. It took a while for me to think upon this forum parent id system, and I wanted some input tips on how to work it.

My concept is to have each forum take advantage of a parent_id in their mysql database. Thus, I could implement sub-forums without going the extra mile.

Majorly, I have two sections on my forum, television and movies. Their parent_id would be NULL because they are the highest you can go. If a forum has a parent_id, then it must be connected with another forum.

This is theoretically a pretty good system imo, but i'm not too sure how to work a couple things. I want to create a "bread crumb" type of navigation on each page in the forum, but how the heck would I do that? I'm thinking I should do a for each or while until the parent_id in the sql returns null, but it would compile somthing like this:

Forum Index :: Television :: Drama :: Boston Public :: Fan Discussion

Obviously Fan Discussion would be a subforum of Boston Public, but really I could have a subforum for that and another for that, so how would I create such a breadcrumb?

And oh yeah comment on this type of forum id arrangement. Thx

[dcrombie]

Sounds like a job for ... recursion!

function getMenu ($id) { 
 $retval = array($id); 
 if ($parentid = <get parentid of forum $id>) { 
 return array_merge ($retval, getMenu ($parentid)); 
 } 
 return $retval; 
}

(I _really_ haven't tested this but the principle is there)

The final return value should be an array of 'id's starting from the top parent and ending with $id.

[bobnew32]

I 100% thankyou for your help with that reply, but I really can't decipher the function you just posted. Where would I put the sql statement to even grab the menu? But thankyou so much for your input.

[dmorison]

I have written such a forum system; but rather than going down the parent ID route I used a path style system, similar to usenet; but using standard directory separators "/" rather than full stops ".".

When posting a message, one of the input fields is the "path" into which you wish to post your message. This defaults to the current path, but can be changed to anything. Users can create "sub forums" simply by posting a message into a path that does not currently exist.

When browsing the forum; the default view is by recent activity (like the "recent posts" view here at WebmasterWorld); but the real cool thing is that you can browse the forum system at any level of the tree you like; and you get to see all activity at your position and lower.

So, for example, the system could support a tree of discussion boards for Football in the UK. The path might start out at something like:

/uk/sport/football

and have sub-directories splitting it down into divisions and then teams; for example:

/uk/sport/football/division1

and

/uk/sport/football/division1/reading

If you were interested in UK football in general then you would browse the forum at the /uk/sport/football level; whereas fans could browse down at their individual club level.

The self-organisation feature would then come into play if a team were to be promoted. When Reading are promoted to the Premiership next season; their fans simply start posting in:

/uk/sport/football/premiership/reading

.. and the old "board" simply dissapears quietly.

Messages are retrieved by way of a database index on the "path"; there is no searching for parent or child relationships (which can be slow). For example, when generating the recent activity view for the UK football board above; the query is along the lines of:

SELECT * FROM threads WHERE path LIKE '/uk/sport/football/%' order by last_message DESC

The percent sign in the LIKE clause is the wildcard, so everything at and below that point in the forum is selected.

Single table query, indexes on path and last_messages - as fast as it gets.