turkman Posted October 23, 2010 Share Posted October 23, 2010 ok have a table. like the following. ID // title // ReplyToo // message ok, so its for a forum... the above are the only rows we need. ID is each posts unique identifyer. title is the tile of the topic. It will only have a title if its a new thread otherwise it has the value of blah ReplyToo is for replys to topic, it will equal the ID of the topic its replying too, if its a new topic its value will be 0 Message is the message of the topic So we might have the following ID//////title//////ReplyToo//////message 1 //////Hi there/ 0 /////////// This is a new thread 2 //////blah ///// 1 //////////// im replying to a new thread ID 1 is starting a new thread of title Hi there, notice its replytoo value is 0 ID 2 is replying to ID1 thread and because it doesn't have a title its set to blah ReplyToo is 1 because its replying to ID1 Ok now i have explained..Here is what i want. I want a query that will count the replys to each thread then order them by most replies. This means the query must find all threads with a ReplyToo value of 0 Get its ID, find and count all threads with a ReplyToo value of ID Then return them in order Thread title // num of replies. Quote Link to comment https://forums.phpfreaks.com/topic/216656-need-help-with-a-very-tricky-mysql-query-one-table/ Share on other sites More sharing options...
eran Posted October 23, 2010 Share Posted October 23, 2010 You'll find it much easier to achieve if you normalize your database. Have a table for topics and a table for posts Something like: topics - id - title posts - id - topic_id - message Quote Link to comment https://forums.phpfreaks.com/topic/216656-need-help-with-a-very-tricky-mysql-query-one-table/#findComment-1125651 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.