[RESOLVED] Get last 6 months data

zzz

I need to be able to get the data for the last 6 month. Not sure how to do that and be able to order them in the appropriate order like:

JAN FEB MAR APR JUN JUL AUG

Now, the problem, I see when the current month is not January and the months will be in the following order:

FEB MAR APR JUN JUL AUG

Currently I have a data from Jan 05 till Feb 2006. When I do the following:

$6months = $db->get_results ("
SELECT COUNT(t1.a1) AS coCOUNT,
t1.a2, t1.a3, t2.b1, t2.b2, 
YEAR( t2.date ) as yr,
MONTH( t2.date ) as mo
FROM t1
LEFT JOIN t2 ON ( t1.a2 = t2.b1 )
WHERE t2.b2 = 'ON'
AND t1.a1 = ".$VAR."
GROUP BY yr, mo
ORDER BY mo ASC 
LIMIT 0,6");

I get:

JAN(05) JAN(06) FEB MAR APR JUN

Clearly I what i need is the last 6 month, which should be:

AUG SEP OCT NOV DEC JAN(06)

Where's my mistake?

devinemke

are you using mySQL? if so look at the Date and Time Functions (specifically DATE_SU😎

zzz

Yes, I do use MySQL. I'll check these functions.

zzz

It appears that I could add a conditions into my query like:

 AND DATE_SUB(CURDATE(),INTERVAL 6 MONTH)

but I'm still not sure if this is what I need to do. Supposedly it'll give me a start point of 6 months prior to NOW, right? What do I do with it than? Confused...

😕

devinemke

SELECT * FROM table WHERE date BETWEEN DATE_SUB(CURDATE(), INTERVAL 6 MONTH) AND CURDATE()

zzz

Perfect. Now trimming my own code. This is an overkill:

ORDER BY mo ASC 
LIMIT 0,6