在Zend Framework中选择上个月的所有记录?

发布于 2024-10-20 17:20:17 字数 823 浏览 3 评论 0原文

我的项目中有一个小需求:

我想从数据库中获取上个月的所有记录。

表的结构如下:

id   clientid     task   date     
1    1            1      01.Feb.2011 12:13
2    1            1      05.Feb.2011 15:22
3    1            0      09.Feb.2011 14:17
4    2            1      11.Feb.2011 19:53
5    1            0      19.Feb.2011 14:27
6    2            1      23.Feb.2011 09:53
7    1            0      01.Mar.2011 14:17
8    2            1      01.Mar.2011 19:53
9    1            0      03.Mar.2011 14:67
10   2            1      03.Mar.2011 09:53
.....................

这里我想要在 Zend Framework获取特定客户端上个月的所有记录。例如

:如果我想要 client 1 记录,那么它应该显示记录:1,2,3 和 5。

请建议一些可以帮助我的代码或链接......

提前致谢

I have a small requirement in my project:

I want to fetch all the records of the previous month from the database.

The structure of the table is as follows:

id   clientid     task   date     
1    1            1      01.Feb.2011 12:13
2    1            1      05.Feb.2011 15:22
3    1            0      09.Feb.2011 14:17
4    2            1      11.Feb.2011 19:53
5    1            0      19.Feb.2011 14:27
6    2            1      23.Feb.2011 09:53
7    1            0      01.Mar.2011 14:17
8    2            1      01.Mar.2011 19:53
9    1            0      03.Mar.2011 14:67
10   2            1      03.Mar.2011 09:53
.....................

Here I want to fetch all the records of the previous month of a particular client in Zend Framework.

For Example : If I want client 1 records then It should show me records : 1,2,3 and 5.

Please Suggest some code, or link that helps me......

Thanks in advance

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

扫码二维码加入Web技术交流群

发布评论

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

评论(5

只是偏爱你 2024-10-27 17:20:17

假设日期列是 DateTime 列,我会尝试类似于

$select->from('tablename')
       ->where('MONTH(date) = ?', date('n', strtotime('last month')))
       ->where('YEAR(date) = ?', date('Y'))
       ->where('clientid = ?', $clientId)
;

注意:未经测试,可能需要调整,但这是总体方向

这将从表名中获取所有行,其中月份是上个月,年份是当前年份,您的 clientId 是选定的 clientId。因此,查询应该变成类似的内容

SELECT * from tablename 
    WHERE MONTH(date) = 2 
      AND YEAR(date) = 2011
      AND clientid = 1;

您也可以将上个月和当年的计算直接放入查询中,例如使用 适当的 MySql 函数 为此,而不是使用 PHP 计算它们。这个可能比较靠谱。

Assuming the date column is a DateTime column, I'd try with something like

$select->from('tablename')
       ->where('MONTH(date) = ?', date('n', strtotime('last month')))
       ->where('YEAR(date) = ?', date('Y'))
       ->where('clientid = ?', $clientId)
;

Note: untested and likely needs tweaking but it's the general direction

This would fetch all rows from tablename where the month is the last month and year is the current year and your clientId is the selected clientId. So the query should become something like

SELECT * from tablename 
    WHERE MONTH(date) = 2 
      AND YEAR(date) = 2011
      AND clientid = 1;

You could also put the calculation for last month and current year directly into the query, e.g. using the appropriate MySql functions for this instead of calculating them with PHP. This might be more reliable.

灯角 2024-10-27 17:20:17

您可以使用 PHP 获取当月的第一天:

$this_month = mktime(0, 0, 0, date("m"), 1, date("Y"));
$previous_month = mktime(0, 0, 0, date("m")-1, 1, date("Y"));

然后您只需将此日期作为查询的参数传递即可:

SELECT * FROM mytable WHERE date >= ? AND date < ? and client_id = 1

在哪里替换 ?分别按 '$previous_month' 和 '$this_month'

You can get the first day of the current month using PHP:

$this_month = mktime(0, 0, 0, date("m"), 1, date("Y"));
$previous_month = mktime(0, 0, 0, date("m")-1, 1, date("Y"));

Then you simply pass this date as a parameter of your query:

SELECT * FROM mytable WHERE date >= ? AND date < ? and client_id = 1

where you replace the ? respectively by '$previous_month' and '$this_month'

卖梦商人 2024-10-27 17:20:17

如果您的date字段的类型为Datetime,您可以使用MySQL中的日期特定函数来执行此操作。使用数据库函数时,只需使用 Zend_Db_Expr 构建语句即可。

If your date field is of the type Datetime you can use the date specific functions in MySQL to do this. Simply construct your statement with Zend_Db_Expr when using database functions.

櫻之舞 2024-10-27 17:20:17

我的 Zend_Db 技能有点生疏,但我认为以下内容可以满足您的要求:

$select->from('tablename')
           ->where(new Zend_Db_Expr('MONTH(`date`) =  MONTH(DATE_SUB(NOW(), INTERVAL 1 MONTH))'))
           ->where(new Zend_Db_Expr('YEAR(`date`) = IF(MONTH(NOW()) = 1, YEAR(NOW()) - 1, YEAR(NOW()))'))
           ->where('clientid = ?', $clientId)

My Zend_Db skill is a bit rusty, but I think the following does what you want:

$select->from('tablename')
           ->where(new Zend_Db_Expr('MONTH(`date`) =  MONTH(DATE_SUB(NOW(), INTERVAL 1 MONTH))'))
           ->where(new Zend_Db_Expr('YEAR(`date`) = IF(MONTH(NOW()) = 1, YEAR(NOW()) - 1, YEAR(NOW()))'))
           ->where('clientid = ?', $clientId)
泪是无色的血 2024-10-27 17:20:17

您可以使用 SQL DATE_SUB 和 INTERVAL 函数,例如:

select * from table where `date` >=  DATE_SUB(NOW(), INTERVAL 1 month)

在 ZF1 中您可以编写如下内容:

$select->from('tablename')
 ->where( 'date >=  DATE_SUB(NOW(), INTERVAL 1 month)');

you can use SQL DATE_SUB and INTERVAL function like:

select * from table where `date` >=  DATE_SUB(NOW(), INTERVAL 1 month)

In ZF1 you can write something like:

$select->from('tablename')
 ->where( 'date >=  DATE_SUB(NOW(), INTERVAL 1 month)');
~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文