mysql/php:显示帖子，并为每个帖子提供所有注释 [英] mysql/php: show posts and for each post all comments
问题描述
我知道这个问题已被问过多次(但是，我仍然找不到解决方案):
基本问题:拥有表格帖子，评论，用户 ...可以用一个选择语句选择并显示所有帖子和所有评论(带有comment.user，comment.text，comment.timestamp)?这样的选择语句会是什么样子?如果没有，最简单的解决方案是什么?
我还尝试使用posts表将JOIN表与comments表一起使用并使用GROUP BY，但是我在每一行或每一条注释中都只有一个注释，而且这些帖子也被多次！? >
我尝试了第一个链接(嵌套mysql_query然后取回)以及第二个链接(带有数组)的解决方案.但是，第一个导致了很多错误(该文章中的语法似乎不正确，我无法弄清楚如何解决它)，第二个导致数组出现问题.
到目前为止，我的查询是这样的:
SELECT p.id, p.title, p.text, u.username, c.country_name, (SELECT SUM(vote_type) FROM votes v WHERE v.post_id = p.id) AS sum_vote_type FROM posts p LEFT JOIN user u ON ( p.user_id = u.id ) LEFT JOIN countries c ON ( c.country_id = u.country_id ) ORDER BY $orderby DESC
我想知道是否这个问题不是很普遍，有帖子和评论来显示...?
谢谢您的每一次帮助！
不知道您的数据库结构，它看起来应该像这样.请注意，您应将*字符替换为实际需要的更明确的列列表.
SELECT p.*, c.*, u.* FROM posts p
LEFT JOIN comments c ON c.post_id = p.id
LEFT JOIN users u ON u.id = p.author_id
请注意，如果您只是想获取计数，总计和类似信息，则最好缓存一些信息.例如，您可能希望将评论计数缓存在发布表中，而不是对每个查询都进行计数.添加/删除评论时，仅计算和更新评论数.
意识到您还希望将用户数据附加到每个注释.您可以多次加入同一张表，但是它很难看.这可能会变成一个非常昂贵的查询.我还将提供一个有关如何为列添加别名的示例，以减少混乱:
SELECT p.*, c.*, u.name as post_author, u2.name as comment_author FROM posts p
LEFT JOIN comments c ON c.post_id = p.id
LEFT JOIN users u ON u.id = p.author_id
LEFT JOIN users u2 ON u2.id = c.author_id
I know this question has been asked multiple times (however, I could still not find a solution):
- PHP MYSQL showing posts with comments
- mysql query - blog posts and comments with limit
- mysql structure for posts and comments ...
Basic question: having tables posts, comments, user... can you with one single select statement select and show all posts and all comments (with comment.user, comment.text, comment.timestamp)? How would such a select statement look like? If not, what is the easiest solution?
I also tried to JOIN the comments table with the posts table and use GROUP BY, but I got either only one comment in each row or each comment but also those posts multiple times!?
I tried the solution of the first link (nested mysql_query and then fetch) as well as the second link (with arrays). However, the first caused a bunch of errors (the syntax in that post seems to be not correct and I could not figure out how to solve it) and in the second I had problems with the arrays.
My query looks like this till now:
SELECT p.id, p.title, p.text, u.username, c.country_name, (SELECT SUM(vote_type) FROM votes v WHERE v.post_id = p.id) AS sum_vote_type FROM posts p LEFT JOIN user u ON ( p.user_id = u.id ) LEFT JOIN countries c ON ( c.country_id = u.country_id ) ORDER BY $orderby DESC
I was wondering if this issue was not very common, having posts and comments to show...?
Thank you for every help in advance!
Not knowing your database structure, it should look something like this. Note that you should replace the * characters with more explicit lists of columns you actually need.
SELECT p.*, c.*, u.* FROM posts p
LEFT JOIN comments c ON c.post_id = p.id
LEFT JOIN users u ON u.id = p.author_id
Note that if you're just trying to get counts, sums and things like that it's a good idea to cache some of that information. For instance, you may want to cache the comment count in the post table instead of counting them every query. Only count and update the comment count when adding/removing a comment.
EDIT: Realized that you also wanted to attach user data to each comment. You can JOIN the same table more than once but it gets ugly. This could turn into a really expensive query. I also am including an example of how to alias columns so it's less confusing:
SELECT p.*, c.*, u.name as post_author, u2.name as comment_author FROM posts p
LEFT JOIN comments c ON c.post_id = p.id
LEFT JOIN users u ON u.id = p.author_id
LEFT JOIN users u2 ON u2.id = c.author_id
这篇关于mysql/php:显示帖子，并为每个帖子提供所有注释的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
