如何从PHP中的数据库中获取上个月的数据? [英] How to fetch previous month's data from database in php?
问题描述
我正在开发一个php网站，需要从上个月获取一些数据.不仅是前一个月的数据，而且是最近五个月的数据.我感到困惑，我该怎么做?在数据库中，我创建了两列.一个是月"，另一个是年".所以行看起来就像
I am developing a php website and need to fetch some data from previous month. Not only first previous month but last five months data. And I am confused that how can I do this? In my database I've created two columns. One is "Month" and another is "Year". So row will look like
id ----文本----月----年
id ---- text ---- month ---- year
1 ----你好----五月----- 2014
1 ---- hello ---- May ----- 2014
2 ----嗨----五月----- 2014
2 ---- hi ---- May ----- 2014
现在，我可以使用类似date()的函数来获取数据.
Now I can fetch data by using date() function like this.
$first_month = date('F', strtotime('-1 month'));
$second_month = date('F', strtotime('-2 month'));
$third_month = date('F', strtotime('-3 month'));
五个月以来，我可以创建五个变量.但是一年?我的意思是，如果当前月份为2014年1月，则我需要从2013年12月开始获取数据，那么它将如何工作?有人可以建议我吗?
for five months, I can create five variables. But for year? I mean if current month is january and year 2014, then I will need to fetch data from December 2013, so how it will work? Can anyone suggest me?
我很困惑.请告诉我以前是否有人做过这样的事情.我需要想法或概念.
I am so confused. Please tell me if anyone has done something like this before. I need idea or concept.
推荐答案
您可以重组表以使用Unix时间戳或MySQL date，然后使用比较器进行选择.或者，您可以创建临时表并执行相同的操作.
You could either restructure your tables to use a Unix timestamp or MySQL date, and select using comparator's. Or you could create temporary tables and do the same.
您要前进的方向对于维护和调试将是不平凡的.另外，正确的查询将比我认为的要复杂的多.例如，跨年将是上述的一行.尽管许多线路都按您的方式行事.
The direction you are heading will be nontrivial to maintain and debug. Also, the correct queries will be more complex than I believe you expect them to be. For instance, spanning years will be one line with the above. While many many lines going the way you are.
编辑:要保持当前的逻辑，SQL Select语句必须为一系列的WHERE.例如:
To maintain your current logic, the SQL Select statement would have to be a series of WHEREs. For instance:
SELECT * FROM table
WHERE
(month IN ('April', 'May') AND year = 2014)
将选择2014年的4月和5月.
Would select the months of April and May in the year 2014.
以下内容将持续一年:
SELECT * FROM table
WHERE
(month IN ('December') AND year = 2013)
OR
(month IN ('January') AND year = 2014)
从程序化的角度来看，您需要生成一系列的年份和月份，包括一年中要选择的所有月份.
From a programmatic standpoint, you would need to generate an array of years and months, including all months of the year you would like to select from.
这种数组的结构如下:
// For the first example
$where1 = array(
2014 => array(
'April',
'May'
)
);
// For the second example
$where2 = array(
2013 => array(
'December'
),
2014 => array(
'January'
)
);
并且您将必须从这些数组创建一个WHERE子句.如:
And you would have to create a WHERE clause from those arrays. Such as:
/**
* Outputs
* [where1] (month IN ('April', 'May') AND year = 2014)
*
* [where2] (month IN ('December') AND year = 2013) OR (month IN ('January') AND year = 2014)
*/
foreach(array('where1','where1') as $where_name) {
$clause = '';
$where = $$where_name;
$wheres = array();
foreach($where as $year => $months) {
$wheres[] = "month IN ('" . implode("', '", $months) . "') AND year = $year";
}
$clause = '('.implode (') OR (', $wheres).')';
echo "[$where_name] $clause\n\n";
}
这应该可以解决问题. 此处有一个有效的示例.收集月份的清单几乎与您一直在做的一样，但是更像是这样:
That should do the trick. There is a working example here. Gathering the list of months is almost the same as you have been doing, but moreso like so:
list($month, $year) = explode(" ", date('F Y', strtotime('-3 month')));
var_dump($month); // February
var_dump($year); // 2014
$array[$year][] = $month; // Will create the array structure as above
有更有效的方法，但这是一般的想法.我相信我已经为您提供了一些有趣的工具来解决您的问题.
There are more efficient ways, but this is the general idea. I believe I have left you with some interesting tools to solve your problem.
要使其达到某个范围，您需要执行以下操作:
To make this hit a range you would need to do something along the lines of:
$where3 = array();
for ($prev_months = 1; $prev_months <= 5; $prev_months++) {
list($month, $year) = explode(" ", date('F Y', strtotime("-$prev_months month")));
$where3[$year][] = $month;
}
您可以在此处查看工作示例: http://ideone.com/UWI5wI
You can view the working example here: http://ideone.com/UWI5wI
作为参考，以下是使用您的方法与公认准则的示例:
For reference, here are the examples of using your methodology vs the accepted norm:
// your last and 5
(month IN ('April', 'March', 'February', 'January') AND year = 2014) OR (month IN ('December') AND year = 2013)
// native 1 and 5
date <= 2014-05-01 01:00:00 AND date > 2013-12-01 01:00:00
我使用了第一个(这不是约定)，因为每个月都有一个.您可能希望使用目标月份的最后一天，而不是下个月的第一天，但是您可以使用.
I used the first (which is not convention) because every month has a first. You would want to use the last day of the target month, instead of the first day of the following month, but you get that.
跨越很长一段时间后，差异更加明显:
The difference is much more apparent when spanning LONG periods of time:
// 2 years, yours
(month IN ('April', 'March', 'February', 'January') AND year = 2014) OR (month IN ('December', 'November', 'October', 'September', 'August', 'July', 'June', 'May', 'April', 'March', 'February', 'January') AND year = 2013) OR (month IN ('December', 'November', 'October', 'September', 'August', 'July', 'June', 'May') AND year = 2012)
// The conventional way is still just two comparators...
这篇关于如何从PHP中的数据库中获取上个月的数据?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
