[SOLVED] mysql db order by forum threads

[ted_chou12]

I asked similar question yesterday, however, this case is more complicated:

I store forum threads in mysql db, all of which are in one table, there are two types of thread, so I made a column called threadattribute, one kind is starter threads, starting each topic, and other threads just continue the same topic started by starter threads. Therefore, in other words, the total number of continue threads is the replies. One starter thread and several continue threads are grouped together using threadid, therefore, they can be easily recognized as one topic, so the table looks something like this:

threadid

threadattribute

1stater

1continue

1continue

2stater

1continue

2continue

In logic, there will always be one starter thread for each threadid, however, the continue threads can be infinite.

What I wish to do, is to order these threadids according to the frequency of the continue threads, in other words, the no. of replies.

However, this is really confusing even to myself, can anyone help me out please?

Thanks

Ted

[ted_chou12]

sorry, I forgot to say, what I want to echo out are the information of the starter threads, however, I only want to use the frequency of the reply threads to organize the starters.

Thanks

[ldsmike88]

Why can't you have another column in the table that has a unique name or ID for each thread. Then when you get the information from the database you could simply count how many had any certain ID?

[ted_chou12]

sorry, i dont quite understand you, but i do have another column purely called id, i didnt put it up because i dont think it helps in organizing the query, and i need to organize by the number of rows anyways, so I didnt bother about that.

Ted

[ted_chou12]

maybe starting with COUNT in the ORDER BY statement would start me off in some way, can anyone help me please?

[shoz]

SELECT
t1.*, COUNT(t2.threadid) AS num_replies
FROM
tablename AS t1
LEFT JOIN
tablename AS t2
ON
t1.threadid = t2.threadid AND t2.threadattribute = 'continue'
WHERE
t1.threadattribute='starter'
GROUP BY
t1.threadid
ORDER BY
num_replies DESC

 

 

 

[ted_chou12]

thanks a lot, for this:

tablename AS t1

LEFT JOIN

tablename AS t2

what if all of them are in the same table? would i just reenter forum AS t1 and forum AS t2?

Thanks

Ted

[shoz]

thanks a lot, for this:

tablename AS t1

LEFT JOIN

tablename AS t2

what if all of them are in the same table? would i just reenter forum AS t1 and forum AS t2?

 

If "forum" is the name of the table, then yes.

[ted_chou12]

thanks, that worked out perfectly!!!