0

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!

Improve this question

2 Answers 2

Reset to default
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)
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

He wants the comment_id COUNT.
Excuse my ignorance, but wouldn't this be two SELECT statements then?
0 
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
Improve this answer

5 Comments

The left join will make sure you still return posts without any comments. The group by will allow you to get the posts data and the comment count (an aggregation) in one query.
tehMick, Thank you so much for your quick reply. This seems to work, however, when using the following code, I am not getting a count, or anything for that matter: <? echo $row->comment_id; ?> As you probably can tell, I'm a rookie at this and am learning as I go. Thanks again for your assistance.
You'll need to alias the count in order to reference it in php: SELECT ...` Count(c.comment_id) AS comment_count` Then you can use $row->comment_count.
TehMick, If I wanted to add a WHERE statement to this, where would I put it? I've used WHERE statements in the past, but am a bit confused with the JOIN. Thanks again for your help! I'm so appreciative!
@Nick: I always confuse myself about that as well. The WHERE clause would go just before the GROUP BY - it always follows any join. It allows you to filter your results before they are aggregated. If you want to filter the comments you count rather than the posts, you could just add another condition to the ON clause of the join with AND: ...ON p.post_id = c.comment_id AND c.comment_by = 'Nick'

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.