I want to display the latest 10 comments and its replies.

It's only one level deep. (i.e. you cannot reply to replies. You can only reply to comments)

What is the best way to do it?

Right now, my method seems very retarded. Hope somebody can give me a better solution.

My method

1) Find the latest 10 comments.
2) Find replies where comment_id IN (the comment ids found in 1)

I can't do it with joins as i do not know how many replies each comment has. So if the first comment has 20 replies and i do a join LIMIT 10, i will only display the comment and 9 replies.

Anyone has a better idea?

Then LIMIT it to something huge bu still sane say 300, there aren't going to be 300 replys, but there might be 11.

As for my forum, each comment has a parent field in its row. When displaying each comment I check the db to see if this comment has any where its KEY/ID is the PARENT of any others.

Let me try to think of some code ...

$result=mysql_query("SELECT * from comments ORDER by ID desc LIMIT 10;");
while ($row = mysql_fetch_assoc($result))
{
echo $row["title"]; //or any other thing you want displayed here

$result2=mysql_query("SELECT * from replies where comment_id= $row["id"] ;");
while ($row2 = mysql_fetch_assoc($result2))
{
echo $row2["title"]; //or any other thing you want displayed here
}
}

I am in a hurry right now and I'm not sure if this'll work from the first time but that's all I can think of right now. Good luck.

Let me try to think of some code ...

$result=mysql_query("SELECT * from comments ORDER by ID desc LIMIT 10;");
while ($row = mysql_fetch_assoc($result))
{
echo $row["title"]; //or any other thing you want displayed here

$result2=mysql_query("SELECT * from replies where comment_id= $row["id"] ;");
while ($row2 = mysql_fetch_assoc($result2))
{
echo $row2["title"]; //or any other thing you want displayed here
}
}

I am in a hurry right now and I'm not sure if this'll work from the first time but that's all I can think of right now. Good luck.

that's pure nuts. 11 queries. my current queries only take 2 queries.

grabmail, what are your two SQL querys?

1) SELECT * from comments ORDER by ID desc LIMIT 10;
2) SELECT * from replies where comment_id IN (1,2,3,4, ......)

Replace 1,2,3,4,.... with the comment ids obtained in 1

Is this the most efficient way so far?

I was actually wondering if there was some fancy way to do it with mysql views or something.

grabmail.. you'd still have just as many querys as maiahost's method. Infact.. they're both the same method

You get a list of the comments and for each comment you select for the the relys. So if C is the number of comments you'd both have C+1 querys.

you could do

SELECT Comments.text, Comments.id, Replys.id, Replys.Text FROM Comments, Replys WHERE Replys.InReplyTo=Comments.id GROUP BY Comments.id

This would result in being ONE query, but you'd see teh same comment listed in the result set for every reply that was assosiated with it.

that's pure nuts. 11 queries. my current queries only take 2 queries.

Well sometimes it's more effective to use 11 rather than one complicated one. I'll try to think of something else :P

SELECT Comments.*, Replys.*
FROM Comments
LEFT JOIN Replys
ON Comments.id=Replys.id
That should return all the results but you'll have to think of a way to display them.

So the same as my suggestion, but with a JOIN instead of a WHERE?

OK got me here, as I said I don't have much time :) sorry and you may consider Xeentech the winner