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

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

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.

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'

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.

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)

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)');