PhP 按今天、每周、每月排序...不起作用

发布于 2024-12-22 17:20:38 字数 1435 浏览 0 评论 0原文

我试图按“今天”、“本周”、“本月”、“所有时间”对文章进行排序。问题是我的查询总结了一个表中的投票并从另一个表中获取文章，这使得查询对我来说非常复杂，而且我不确定我是否做得正确。

不管怎样，我有一个像 http://web.com/top.php?time=“这里有时” 我使用以下代码根据用户选择的时间来选择文章，而不是 “这里有时”

$time = preg_replace('#[^a-z]#i', '', $_GET["time"]);

    if (!$time) { //Today
    $date = "WHERE TO_DAYS(NOW()) - TO_DAYS(stories.st_date) = 1";
    }
    else if ($time == "Week") { //This Week
    $date = "WHERE TO_DAYS(NOW()) - TO_DAYS(stories.st_date) <= 7";
    }
    else if ($time == "Month") { //This Month
    $date = "WHERE TO_DAYS(NOW()) - TO_DAYS(stories.st_date) <= 31";
    }
    else if ($time == "All") { //All Time
    $date = "";
    }
    else {
    header("Location: http://www.web.com/");
    exit;
    }

$sql = ("SELECT stories.*, SUM(votes.vote_value) as 'total_votes' FROM stories JOIN votes ON stories.id = votes.item_name ".$date." GROUP BY stories.id ORDER BY total_votes DESC LIMIT 10") or die (mysql_error("There was an error in connection"));

这似乎都不起作用； DI不明白为什么，在我看来应该是这样，但我又是对 php 和 mysql 来说相对较新。那么有人可以建议这里的问题是什么吗？

我的数据库结构，让您更好地了解系统。

故事表

在此处输入图像描述

投票表

在此处输入图像描述

原文

I am trying to sort articles by "Today", "This Week", "This Month" , "All Time". The thing is that my query sums up votes from one table and gets articles from another table, this make query really complicated to me, And I am not sure if I am doing it right.

Anyway, I have a page like http://web.com/top.php?time="some time here" And I use the following code to select articles basing on what time user chooses instead of "some time here"

$time = preg_replace('#[^a-z]#i', '', $_GET["time"]);

    if (!$time) { //Today
    $date = "WHERE TO_DAYS(NOW()) - TO_DAYS(stories.st_date) = 1";
    }
    else if ($time == "Week") { //This Week
    $date = "WHERE TO_DAYS(NOW()) - TO_DAYS(stories.st_date) <= 7";
    }
    else if ($time == "Month") { //This Month
    $date = "WHERE TO_DAYS(NOW()) - TO_DAYS(stories.st_date) <= 31";
    }
    else if ($time == "All") { //All Time
    $date = "";
    }
    else {
    header("Location: http://www.web.com/");
    exit;
    }

$sql = ("SELECT stories.*, SUM(votes.vote_value) as 'total_votes' FROM stories JOIN votes ON stories.id = votes.item_name ".$date." GROUP BY stories.id ORDER BY total_votes DESC LIMIT 10") or die (mysql_error("There was an error in connection"));

None of this seems to work ;D I don't see why, In my opinion it should, but than again I am relatively new to php and mysql. So could anyone suggest what the problem is here?

My Database Structure to give you better understanding of the system.

Stories table

enter image description here

Votes table

enter image description here

分享到QQ

分享到微博

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

发布评论

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

混浊又暗下来 2024-12-29 17:20:38

尝试

    SELECT stories.*, SUM(votes.vote_value) as 'total_votes' 
    FROM stories JOIN votes ON stories.id = votes.item_name 
    WHERE votes.item_name=$date 
    GROUP BY stories.id ORDER BY total_votes DESC LIMIT 10

或类似，思考你想要实现的目标和逻辑，然后重写你的sql查询
在 phpmyadmin 中，直到获得正确的输出，然后再将其放入 php 中。

try

    SELECT stories.*, SUM(votes.vote_value) as 'total_votes' 
    FROM stories JOIN votes ON stories.id = votes.item_name 
    WHERE votes.item_name=$date 
    GROUP BY stories.id ORDER BY total_votes DESC LIMIT 10

or similar, think about what you want to achieve and the logic then rewrite your sql query
in phpmyadmin till you get the right output before putting it into php.

回复收藏 0 原文