A Forum Without Recursive SQL Queries...

Anon

I am creating a forum for a site I am coding from the ground up. I was just wondering if anyone had any architecture recommendations. This forum will have threaded, nested and flat views like slashdot and will be GPLed. Thus far I have tables for message bodies and topics seperate.

Any ideas would be appreciated as to how to do a nested view without recursive queries.

Anon

I've thought for a while that you could do a threaded site without the parent-child relationship setup that I think would require a recursive programming approach.
My thought would be to setup the discussion message table to have a field for the order. The first message would be '00001'.
The first reply to the message would be '00001.00001'.
The second reply to the first message would be '00001.00002'.
The first reply to the second message would be '00001.00002.00001'. etc.etc.etc.
When you get a result set with from MySQL and sort it on this field it lists them in the corrent hierarchical order, so displaying what you need would be fairly easy.

Brian

kevin wrote:

Any ideas would be appreciated as to how to do a nested view without recursive queries.

Anon

Good idea, but i dont think you'll need a zero fill for that. you could just do a
$start_thread = "1.5.4.100000000";
$query = "SELECT * FROM forums WHERE thread_id LIKE '".$start_thread."%' ORDER BY date";
it would pull all the data out you needed to sort in the order you needed it to be placed in. Then it would just be a question of sorting the data. I'm in the process of writing a forum as well and I'm considering using this method. I'm assuming the only thing you'd have to watch for would be to make sure when you reached the end of the varchar(255) column you'd store that id in that you'd not start losing inserts due to activity. At that level it would be an issue of performance which you wouldnt have dealing with a child parent set of INT fields. at least thats my assumption. I have no practical understanding of varchar fields vs int fields in sql.

again, great idea. email me with any comments you might have. recursion, arrays, and multithread forums. what a head ache.

i'll post some example code on here when i get the parser worked out for that string.

Anon

after further consideration you would have no valid way to auto_incriment. a varchar(50) would be all you needed sizewise for a good amount of traffic but you cant get around the autoincrimenting that sql servers use to make sure data doesnt overlap. if you can think of a way around this without using seperate sql statements that would be great.