Combining Data from two MySQL tables.

Question

I'm trying to combine data from two tables in MySQL with PHP.

I want to select all the data (id, title, post_by, content and created_at) from the "posts" table.

Then I would like to select the comment_id COUNT from the "comments" table IF the comment_id equals the posts id.

Finally, I would like to echo/print something on this order:

<? echo $row->title; ?>
Posted by <? echo $row->post_by; ?> on <? echo $row->created_at; ?> CST
<? echo $row->content; ?>
<? echo $row->comment_id; ?> comments | <a href="comment.php?id=<? echo $row->id; ?>">view/post comments</a>

I'm uncertain as to how to "combine" the data from two tables. I have tried numerous things and have spent several evenings and have had no luck.

Any help would be greatly appreciated!

Answer 1

What you're looking for is a join

select * from posts p
inner join comments c on p.post_id = c.comment_id

To get the count of how many comment rows you have for all posts, you can use a nested select statement:

select count(comment_id) from comments c 
where comment_id in (select id from posts)

Answer 2

SELECT p.id, p.title, p.post_by, p.content, p.created_at, Count(c.comment_id)
FROM posts p
LEFT JOIN comments c on p.post_id = c.comment_id
GROUP BY p.id, p.title, p.post_by, p.content, p.created_at