mysql, simple table join - how to loop through records

[batfastad]

Hi

I've been gradually learning and using PHP for about a year and I've now decided to start learning MySQL

I've got my first database up and running, and I can display records, edit/delete/search and sort records from that database.
But I want to make things a bit more complicated and start making related tables.

I've decided to re-invent the wheel and make a very simple content management system for myself, just to see if I can.

I have a table called posts, which is what I enter my table content into, consisting of headline, creation date, post text and an id field.

I've also set up a table called comments which will allow users to add comments to my posts.
In the comments table there's a field called id_post - which is the id of the post that the comment relates to in the posts table

I've already got my loop set up to loop through my posts records, but I'm confused about how I go through looping to get the comments records.

How do I do the loop?

I thought about doing an SQL query to the comments table nested inside my loop through my posts results


SELECT * FROM comments WHERE id_post='$id' ORDER BY stampcreate


But I assume this isn't the most efficient way of doing things.
Having a nested SQL query inside my main loop.
Would mean an additional SQL query for each posts record.

I've been reading up on joins from
http://www.crazygrrl.com/weav/mysql3.php3

And some kind of table join linking id from posts to id_post from comments seems to be what I need.

But I don't know how to restructure my initial query, and loop through the subset of records from the comments table, inside my posts loop.

Any ideas?

Thanks

Ben

[nikkiH]

Not sure what you're going for here.

Give these a look and see if they help.

1. (join with null)
SELECT * FROM posts
left outer join comments on posts.id_post=comments.id_post and posts.id_post='$id'
ORDER BY stampcreate

2. (no null join)
SELECT * FROM posts
inner join comments on posts.id_post=comments.id_post and posts.id_post='$id'
ORDER BY stampcreate

3. (grouping)
SELECT * FROM posts
left outer join comments on posts.id_post=comments.id_post and posts.id_post='$id'
group by posts.post_id, stampcreate ORDER BY posts.post_id, stampcreate

[raf]

i think we need more info.
because the way i see it, you don't even need a join between the two tables. By the time that you need to select the comments, you alreasy have the postID so you can simply ise that in the condition of your select on the comments-table.
I also don't quite see why you want to select the comments from more then one post ...