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. 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 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
Archived
This topic is now archived and is closed to further replies.