Best storage and retrieval practices for comments in PHP/MYSQL -

i'm having bit of problem when comes database , query design. here i'd have (honestly, lot stack overflow).:

• an item has many posts.
• a post has many comments.
• comments can flagged, liked, , disliked.
• comments cannot have subcomments.

the table structure follows:

items ----- iid desc ...  posts ----- pid iid uid date desc ...  comments ----- cid pid uid date desc ... 

the logic of is: posts item -> each post, comments. best in 1 query? best 1 query posts separate query each set of comments on each post?

if 1 query, i'll potentially have 100 row behemoth i'll have ton of duplicate data. if separate call each post, i'll have far many queries. adivce?

i imagine system going have 3 distinctive views , structure follows:

1. a list of items - showing item description , number of posts attached item. e.g. select i.desc, ...., count(p.pid) items left join posts p on i.iid = p.iid group i.iid order i.date desc
2. a list of posts selected item - showing posts , number of comments made each post e.g select p.desc, ...., count(c.cid) posts p left join comments c on p.pid = c.pid p.iid = $iid group p.pid order p.date desc
3. a post page shows post , comments underneath. page require 2 queries. query collect post information, returning 1 database row. , query collect each individual comment attached post. e.g. select p.desc, ... posts p p.pid = $pid limit 1 , select c.desc, ... comments c c.pid = $pid order c.date desc. alter ordering here mimic how stackoverflow orders data oldest, votes, etc.

i scalable model , advise writing lean mysql queries retrieve information needed each displayed entity.

to mimic page use following code ...

itemrs = mysql_query("select i,iid, i.desc, ... items i.iid = $iid limit 1");  // posts relating item postsrs = mysql_query("select p.pid, p.desc, .... posts p left join comments c on p.pid = c.pid p.iid = $iid order p.date desc");  // comments posts relating item commentsrs = mysql_query("select c.pid, c.cid, c.desc, .... comments c inner join posts p on p.pid = c.pid inner join items on p.iid = i.iid p.iid = $iid order p.date desc"); 

you need create method display data. perhaps encapsulate functionality dao (data access object) called item retrieves required data before calling display() method show data in format want.