如何统计 UP 票数，然后从数据库中按 UP 票数排序进行选择

发布于 2024-12-05 11:49:25 字数 681 浏览 0 评论 0原文

我的网站上有投票系统，它将每个用户的投票存储在名为 skmp_voting 的表中，该数据库中的每条记录都有 id、item_id （这是用户投票的文章 ID）和 vote_value 。 vote_value 取决于用户所做的事情，如果他们投票赞成，则值为“1”；如果他们投票反对，值为“-1”。

我有 toarticles.php 页面，我不想在其中显示热门文章，因此具有更多赞成票的文章。这是我的 mysql 查询，用于获取我现在拥有的热门文章：

SELECT stories.*, skmp_votes.vote_value FROM stories 
JOIN skmp_votes ON stories.id = skmp_votes.item_id 
ORDER BY skmp_votes.vote_value DESC

它从名为 stories 的其他表中选择文章信息，并将其与 skmp_votes 表中的 vote_value 进行比较。

我非常确信这是不对的，因为它选择的 vote_value 等于 1 或其他值，所以我需要以某种方式将所有 vote_values 一起计数，然后使用 mysql 查询来获取热门文章。

原文

I have voting system on my website, it stores each users vote in table called skmp_voting each record in this database has id, item_id (this is id of article user voted on), and vote_value . vote_value depend's on what users did, if they voted up value is "1" if they voted down value is "-1".

I have toparticles.php page where I wan't to display top articles, so articles that have more up votes. Here is my mysql query to get top articles I have now:

SELECT stories.*, skmp_votes.vote_value FROM stories 
JOIN skmp_votes ON stories.id = skmp_votes.item_id 
ORDER BY skmp_votes.vote_value DESC

It select's article information from other table called stories and put's it against vote_value from skmp_votes table.

I'm pretty confident that this isn't right, as it selects vote_value that is equal to 1 or something, so I need to somehow count all vote_values together and then use mysql query to get top articles.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

阳光下慵懒的猫 2024-12-12 11:49:25

这取决于您的投票表。正如我想象的那样，每票都有一行。在这种情况下，您必须对与文章匹配的投票进行求和。即

SELECT SUM(vote_value) as 'total_votes' FROM skmp_voting WHERE item_id='$article_id';

您使用 SUM 而不是 COUNT 因为您想从反对票中减去该值。

编辑：补充答案

下面的查询将为您提供每篇文章及其总票数，按总票数排序（投票最多的文章位于顶部）

SELECT stories.*, SUM(skmp_votes.vote_value) as 'total_votes' FROM stories 
  JOIN skmp_votes ON stories.id = skmp_votes.item_id 
  ORDER BY skmp_votes.total_votes DESC

要获取投票最多的 5 篇文章，您只需在末尾添加 限制5

It depends on your voting table. As I imagined, it has a row for each vote. In this case, you'll have to do a sum for the votes wich match an article. i.e.

SELECT SUM(vote_value) as 'total_votes' FROM skmp_voting WHERE item_id='$article_id';

You use SUM instead of COUNT becouse you want to substract the value from the negative votes.

EDIT: complementing the answer

This following Query will get you every article and its total votes, ordered by the total votes (the most voted articles at the top)

SELECT stories.*, SUM(skmp_votes.vote_value) as 'total_votes' FROM stories 
  JOIN skmp_votes ON stories.id = skmp_votes.item_id 
  ORDER BY skmp_votes.total_votes DESC

To get, say the 5 most voted articles, you just add at the end LIMIT 5

回复收藏 0 原文

~没有更多了~