Need help with thread script

[IamStang]

Hello all,

Been awhile since I posted anything here, but here goes nothing.

I am trying to create a message board that uses threads suach as:

Main Subject line
.....1rst Reply here
.....2nd Reply here
.........Reply to 2nd here
.....3rd reply

etc.

I am curious to your thoughts on how best to implement this. I am not asking for long coding examples. Just a breif synopsis on the steps in creating it is all. I learn best by doing for myself. So, I will take care of finding any other answers to questions I might have regarding coding it.

[dmorison]

Just thinking out loud, one straight forward hack might be to have a text based name for each post using an SQL type VARCHAR(255) or something like that.

Then, you "name" each post in the thread using a dotted decimal notation rather like an IP address.

First post is "1", second post is "2". If 2 people then reply to the first post they get named "1.1" and "1.2" respectively. Replies to replies follow the same pattern, so you'd have 1.1.1, 1.1.2.

With this scheme, you can then:

1) Order by "name" to retreive posts in the correct order for display

2) <blockqutoe> your output based on the number of "dots" in the name compared to the previous post. More dots = one more level of <blockquote>, same number of dots = no change, less dots = </blockquote>

Further inspiration may come from studying Slashcode [slashcode.com] which is the open source back-end to Slashdot. I think that may be in Perl though...

[ergophobe]

Hmm... I like the above solution. The alternative is to implement parent-child relationships.

So yoru table would look like

id
parent
text

parent = 0 means that it's the first post

parent = id means that it's a child of that post.

The one bad thing about this, is you end up doing a lot of queries, because every post needs to query to see if it has children, whereas a linear threading system (like the one here) is going to be quite a bit more efficient.

You could cache the results upon update and serve up the cache straight away for those who are viewing the thread.

[IamStang]

Thanks dmorison and ergophobe,

Sounds like a pretty good line of attack. I sort of started laying out the idea on paper and one part has me kinda stumped for ideas.

Having a hard time seeing how to get replies to fall under their respective post.

My first thought was to give each main post an ID number, say 10000 would be the first's ID. Then each 2nd level reply would go up by 100 (10100,10200, etc.). And each 3rd level go up by 1. But that limits the whole thing to no more than a 3rd level of replies and no more than 99 per level. Of course I could use more digits, but this just doesn't seem feasible.

I also tossed around the idea of having multiple IDs. something like

10005 10003 2nd level post
10004 10002 3rd level post
10003 0 main level post
10002 10000 2nd level post
10001 10000 2nd level post
10000 0 main level post

where the second ID is referencing the post that it is replying to. But then I ran into the question of how to get them to show up on the page in the proper order. I guess they could be sorted, but would have to be sorted by groups in some way or another. (Sorting is still not one of my specialties by any means).

So, following along with the original idea proposed by dmorison, any more thoughts on my predicament? LOL

Keep it simple
IamStang

[ergophobe]

In either solution, you simply need to have the ID of the current post in the "reply" link or otherwise pass it to the next page (i.e. you may not want it to be part of a URL, so you would put it in a hidden field and each reply button would be a mini form that would pass the ID of the post you're replying to).

Then, for the dmorison solution, you would need to create some sort of ID in a process like this.

ID = ID of post you're replying to, let's say its 2345-2-1 meaning that in thread 2345, you are replying to the first reply to the second reply in thread 2345 (BTW, I don't recommend using . as you'll have to escape it in all regular expressions).

1. query for posts where ID LIKE '2345-2-1-\d+'

You add the last - there so that it doesn't return the original post, but only it's other children, and you say that after that - it can only have digits and must have at least one, so that you get only children and not more distant descendants. Get a count for the results returned from that query and add 1 and you have the id of your reply. In other words if

"SELECT COUNT(*) FROM posts WHERE id LIKE '" . $id . "-\d*'";

gives a count of 7, your new post would be ID = 2345-2-1-8

2. For displaying, the sort order is a little complicated because you don't want to zero-pad for the max possible number of replies, but you do need a natural sort. You have the id, though, so you can figure out the depth of current threading by counting delimiters (i.e. hyphens in my example)

$sql = "SELECT (length(id) - length(replace(id, '-', '')) + 1) as depth
FROM IdSort
WHERE id LIKE '2345-%'
ORDER BY LENGTH(id) DESC
LIMIT 1";

Grab the result from that and it gives you the max depth for that thread.

Now for your select, you would create an ORDER BY that will sort by natural order so that you get

2345-1-1
2345-1-2
2345-1-10

rather than

2345-1-1
2345-1-10
2345-1-2

And we have to do that for every level of the thread, so we need as many ORDER BY conditions as there are depth levels.

$order = 'SUBSTRING_INDEX(id, '-', 1)+0,' ; 
for ($i=2; $i<=$depth; $i++) 
{ 
 $order .= "SUBSTRING( 
     REPLACE( 
SUBSTRING_INDEX(id, '-', ".$id."),  
SUBSTRING_INDEX(id, '-', ".($id-1)."), 
'') 
     ,2) 
    +0," ;  
} 
$order = substr($order,1) ; 
$sql = "SELECT id, post  
  FROM posts  
  WHERE id LIKE '" . $thread_id . "%'  
  ORDER BY . $order; 

That code hasn't been tested, but the queries have. The code should generate a query that looks like

ORDER BY  
SUBSTRING_INDEX(id, '-', 1)+0,  
SUBSTRING(REPLACE( 
SUBSTRING_INDEX(id, '-', 2),  
SUBSTRING_INDEX(id, '-', 1), 
''),2) +0,  
SUBSTRING(REPLACE( 
SUBSTRING_INDEX(id, '-', 3),  
SUBSTRING_INDEX(id, '-', 2), 
''),2) +0, 
SUBSTRING(REPLACE( 
SUBSTRING_INDEX(id, '-', 4),  
SUBSTRING_INDEX(id, '-', 3), 
''), 2) +0 ; 

Basically, what you're doing is this.

1. SUBSTRING_INDEX gives you the string from the beginning of the string to the Nth occurrence of the substring (here our delimiter '-').

2. in our first ORDER BY condition, we add 0 to implicitly cast it as an integer and get a numeric sort.

3. For the subsequent levels, we need to pull out just the part that pertains to the level in question so we

A. Take the current level's ID and subtract out the id for the previous level.

B. This leaves us with the delimiter in front, so we use SUBSTRING to get that (note that MySQL SUBSTRING does not work like PHP substr() - it is 1 indexed instead of zero indexed, so SUBSTRING('-23', 2) gives us '23'.

C. Once again, we implicitly cast the string as an integer using +0.

This will work for any number of levels, any number of posts. It makes the SQL server do all the work on that end, so it should be fast.

Well, that was a lot longer than planned, so I'll let someone else chime in about the parent-child version. That version is a fair bit easier, but like I said before, if you have a lot of depths and a lot of posts, you will get a huge number of queries. It is best done with recursion... but I have to save that for another day.

[IamStang]

Thanks fellas for the input! Will tryto make some sense of it all and put it to good use. Hopefully! LOL