CODEWITHSUNDEEP
phpmysql
Nick
Nick

Reputation: 425

Combining Data from two MySQL tables

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!

Upvotes: 0

Views: 866

Answers (2)

Eric Mickelsen
Eric Mickelsen

Reputation: 10377

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

Upvotes: 0

Wadih M.
Wadih M.

Reputation: 13462

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)

Upvotes: 1

Related Questions