MySQL:从表中选择日期为当前星期和当前月的数据 [英] MySQL: Select data from a table where the date falls in the current Week and current Month

问题描述

我正在创建一个Web应用程序，如果用户单击链接"WEEK"，该页面将显示该周提交的所有帖子.还有一个选项可以查看本月提交的所有帖子.在帖子表中有一个名为post_date的列，其中的日期以YYYY-MM-DD格式存储.

I am creating a web app where if a user clicks on Link called "WEEK", the page shows up all the posts which were submitted in that week. There's also an option to view all the posts submitted this month. There's a column in the posts table named post_date in which the date is stored in the format : YYYY-MM-DD.

我的问题:我不知道如何生成本周和本月提交的帖子.

我可以像这样成功地选择当前日期提交的帖子:

I can successfully select the posts submitted on the current date like this:

$today = date("Y-m-d"); // This is the format of the DATE column named post_date in the posts table.
if($_GET['when'] == "today")
{
$when = $today;
}
else if ($_GET['when'] == "week")
{
$when = // I don't know what goes here if I want to generate posts submitted this week
}
else if ($_GET['when'] == "month")
{
$when = // I don't know what goes here if I want to generate posts submitted this month
}
// I have already connected to the database and selected the table.
$query = "SELECT * FROM posts WHERE post_date = '$when'";
$result = mysql_query($query);
// After this I Generate the Posts Using WHILE loop and mysql_fetch_array

如果URL中的$ _GET ['when']值为"today"，则SCRIPT可以正常工作并生成"today"提交的帖子.但是我不知道如何选择当前星期和当前月份.我已经完成了作业:我已经搜索并搜索了StackOverflow以查找匹配的问题，但是仍然存在诸如上周"中的数据之类的问题，并且代码简单明了，无法适应我的情况.例如，有一个选项可以使用WEEK($today,1)选择第一，第二，第三和第四周(我不知道它是否有效，但我想生成当前一周和一个月中提交的帖子)

If the $_GET['when']'s value in the URL is "today", the SCRIPT works fine and generates the posts submitted 'today'. But I don't know how to select the current week and the current month. I have already done my homework: I have googled and also searched StackOverflow for matching questions but there were questions like data from Last Week and the code was straight forward and couldn't fit in my situation. For example, there was an option to select the 1st, 2nd, 3rd and 4th week by using WEEK($today,1) ( I don't know if it works or not, but I want to generate the posts submitted in the current week and month ).

谢谢.

推荐答案

有

There are several MySQL functions that do things like this for you

SELECT * FROM posts
WHERE
   WEEK(post_date) = WEEK(CURDATE())
   AND
   MONTH(post_date) = MONTH(CURDATE())

这篇关于MySQL:从表中选择日期为当前星期和当前月的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！