如何在YII上个月的两个日期之间获取数据？

I want to get data of the last month, i am using the below code to do it, but it is not working correctly for me:

我想获取上个月的数据,我使用下面的代码来做,但它对我不起作用:

Here is my code:

这是我的代码:

public function getLastMonth($user_id)
{       
    $criteria=new CDbCriteria;

    $criteria->condition = 'leave_from_date >= DATE_SUB(CURDATE(), INTERVAL 1 MONTH)';
    $criteria->condition = 'leave_to_date <= DATE_SUB(CURDATE(), INTERVAL 1 DAY)';
    $criteria->condition = "user_id = $user_id";
    $data = Leaves::model()->findAll($criteria);
    $lastMonthR = 0;
    foreach($data as $lastMonth)
     {
         $lastMonthR += $lastMonth->total_leaves_hours;
     }      
    //return lastMonthR CHtml::encode($lastMonthR);
    return $lastMonthR;
}

I want to get the result equivalent to the results of following query that is in MYSQL:

我想得到的结果等同于MYSQL中的以下查询的结果:

SELECT *,sum(total_leaves_hours) FROM tbl_leaves
WHERE leave_from_date >= DATE_SUB(CURDATE(), INTERVAL 1 MONTH) AND 
leave_to_date <= DATE_SUB(CURDATE(), INTERVAL 1 DAY) ;

When i run the code(shown above) of YII, it shows me all the data of the table, please help me i am new to YII. thanks in advance.

当我运行YII的代码(如上所示)时,它会显示该表的所有数据,请帮助我,我是YII的新手。提前致谢。

2 个解决方案

#1

When you assign a value for condition for the second time you write over the first condition. Combine the two into a single condition. You should also use prepared statements instead of string interpolation:

当您第二次为条件赋值时,您将在第一个条件上进行书写。将两者合并为一个条件。您还应该使用预准备语句而不是字符串插值:

$criteria->condition = 'leave_from_date >= DATE_SUB(CURDATE(), INTERVAL 1 MONTH)
      AND leave_to_date <= DATE_SUB(CURDATE(), INTERVAL 1 DAY
      AND user_id = :id';

$data = Leaves::model()->findAll($criteria, array (':id'=>$user_id));

#2

You wrote $criteria->condition = ... three times. That assigns the last value and discards the rest. Joni's answer gives a simple solution.

你写了$ criteria-> condition = ...三次。分配最后一个值并丢弃其余值。 Joni的回答提供了一个简单的解决方案。

However, after looking closer at your logic, it can actually be replaced with a simpler SUM query, if you do not use any other data from the query. The sample query you gave selects more data, but the function you wrote does not use it - it's not clear if you actually need the records themselves.

但是,在仔细查看逻辑之后,如果不使用查询中的任何其他数据,它实际上可以用更简单的SUM查询替换。您提供的示例查询会选择更多数据,但您编写的函数不会使用它 - 不清楚您是否确实需要记录本身。

public function getLastMonth($user_id) {
    $cmd = Yii::app()->db->createCommand()
      ->select('SUM(total_leaves_hours)')
      ->from(Leaves::model()->tableName())
      ->andWhere('leave_from_date >= DATE_SUB(CURDATE(), INTERVAL 1 MONTH)')
      ->andWhere('leave_to_date <= DATE_SUB(CURDATE(), INTERVAL 1 DAY)')
      ->andWhere('user_id = :user_id', array(
        ':user_id' => $user_id,
      ))
    ;

    return $cmd->queryScalar();
}