php - 如何显示用户信誉

Question

我想在我的项目中获得像徽章一样的声誉，称为高级作家和初级作家。基于获得的总数量的用户声誉。

我有 3 个表:

表格帖子:

    id_post | news      | id_user
    3       | IT news   | 1 
    4       | game news | 2

表用户:

id_user | username
   1    | dora
   2    | boots
   3    | swipper

表投票:

id_vote | id_post | id_user | LIKE
10      | 3       | 2       | 1
11      | 3       | 1       | 1
12      | 4       | 3       | 1

这是我的查询:

SELECT p.*, SUM(like) AS like_post, 
(SELECT SUM(like) //this is subquery start
FROM user u 
LEFT JOIN post p ON p.id_user= u.id_user 
LEFT JOIN vote v ON  v.id_post=p.id_post GROUP BY u.id) AS reputation // end subquery 
FROM post p 
LEFT join user u ON p.id_user=u.id_user 
LEFT JOIN vote v ON p.id_post=v.id_post 
GROUP BY p.id_post 
ORDER BY p.id_post DESC
LIMIT 10;

这是我的观点:

    <?php foreach $news as $data:?>
    <?php echo $data['id_post'];?>
    <?php echo $data['title'];?>
    LIKE: <?php echo $data['like_post']; ?>
    post by <?php echo $data['username'];?>
    reputation: <?php if ($data['reputation']==1)
    {echo "JUNIOR writer";} 
      else 
    {echo "SENIOR Writer"; }
    ?>
    <?php endforeach;?>

我期望用户 Dora 会因为收到 2 个赞而获得“高级作家”的声誉。 Boots 将获得“初级作家”的声誉，因为只收到 1 个赞。

问题是返回查询是这样的错误:子查询返回多于 1 行

有答案吗？

非常感谢...

Answer 1

使用此查询。

SELECT u.id_user, u.username, SUM(v.like) AS reputation
FROM user u 
LEFT JOIN post p ON u.id_user=p.id_user 
LEFT JOIN vote v ON v.id_post=p.id_post 
GROUP BY u.id_user

使用此 PHP 代码。

LIKE: <?php echo $data['reputation']; ?>
    post by <?php echo $data['username'];?>
    reputation: <?php if ($data['reputation']==1)
    {echo "JUNIOR writer";} 
      else 
    {echo "SENIOR Writer"; }
    ?>
    <?php endforeach;?>

编辑:**

查询like_post

SELECT v.id_user,v.id_post,SUM(v.like1) AS like_post
FROM vote v  
LEFT JOIN USER u ON v.id_user=u.id_user 
GROUP BY u.id_user

查询声誉

SELECT u.id_user, u.username, SUM(v.like1) AS reputation
FROM USER u 
LEFT JOIN post n ON u.id_user=n.id_user 
LEFT JOIN vote v ON v.id_post=n.id_post 
GROUP BY u.id_user