Recusive loop? Or another solution

[braedon]

I have made a simple discusion forum.
The fields are id, poster, email, parent_id, title, desc, approved, last_modified.

I would like to display the amount of times the a message has been replied to, not just the main message. I will give an example.

message 1
reply 1
reply to reply 1
reply 2

I would like the number of replies to be 3. I currently just using a simple query to see how many messages have the parent_id of message 1 which gives me 2 of course. I have tryed a recursive query, but it does wierd things.

If someone could help me out I would greatly appreciate it.

Thanks
Shawn

 

[KevinFSI]

Once you get the two messages which have a parent ID equal to the original post, loop through your db looking for messages whose parent ID equals either of the two messages returned in the first query. Kevin
slanek@ssd.fsi.com

 

[CFHub]

Have you tried COUNT()?

SELECT
Count(ID) AS MessageCount
FROM
Messages
WHERE
TopicID=#TopicID#

 

[braedon]

I could do it the way KevinFSI has discribed, but then I would only go one layer deep. Many of the threads already have three or four layers. Like this....

Message 1
reply 1
reply 2
reply 3
reply 4
reply 5

So to get around this I tryed building a template that called it self unless the record count was zero. Like this...

<!--- Parent id of the first messagee is always zero --->
<cfparam name=&quot;PostID&quot; default=&quot;0&quot;>
<cfparam name=&quot;theCount&quot; default=&quot;0&quot;>

<CFQUERY name=&quot;repliesQuery&quot; .....>
SELECT id
FROM messages
WHERE parent_id = #postID#
</CFQUERY>

<cfset theCount = theCount + repliesQuery.RecordCount>

<CFOUTPUT QUERY=&quot;repliesQuery&quot;>

<cfset postID = repliesQuery.id>
<clinclude template=&quot;this_template.cfm&quot;>

</CFOUTPUT>

I got a error doing this. The error was that it could not find repliesQuery.id after going through a couple of threads. I figure this is because i am using the same query name all the way through so when it get back to the original output it doesn't have an id anymore because the last query also had the same name and it had no results.

I also tryed dynamicly defining the query names but had no luck with that.

So if you could give me a little more help that would be great

Thanks