Pushpendra Pushpendra - 4 months ago 14
SQL Question

Select all the records of the previous month in 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

Answer

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.

Comments