October 24th, 2000, 11:30 AM
here's a tricky one: I am writing a forum.
I save all the messages of one forum into one MySQL table.
The important fields of every record are:
id: unique id of message
parent_id: the id of the parent message (it's a tree style forum)
thread_id: the id of the thread
Now I want to find out which thread is the most popular one. I can easily find the number of replies for a single thread, that would be
SELECT * FROM msg_table WHERE thread_id=[thread id]
and then substract 1 from the number of result rows.
But if I would do this for every single thread, basically loop through all of them, save the number of result rows in an array, and then bubblesort it........ man that would really be a pain in the neck.
Did i forget to put in a num_replies field or am I just too stupid to find a convenient SQL query to do this task for me?
thanks for any ideas
October 24th, 2000, 12:10 PM
Not debugged, but try this out
October 24th, 2000, 12:16 PM
that works like a dream!
October 24th, 2000, 04:06 PM
I was wondering, the query outputs two columns and is ordered by thread_id. But how can I order it by count(id), so that the thread with the most messages appears on top...? Otherwise I'll have to write the whole thing in an array and bubblesort it.
Sorry for the last message I was just euphoric because the output looked cool
- Alex Suzuki
October 24th, 2000, 07:10 PM
You'll have to alias the count() and order by the alias.
count(id) as cnt
October 25th, 2000, 04:55 AM
Yes, sorry. I said I didn't check it for err's.
You got your help though
October 25th, 2000, 06:28 AM
Thanks a lot for your help. Does anybody know where I can find a good SQL-Tutorial on the web, so I don't have to post questions like that one ?