CODEWITHSUNDEEP
phpmysql
tmartin314
tmartin314

Reputation: 4171

How go get entry from last year MySQL

This part of the query doesn't seem to be working

$iThisYear = date('Y');
$iLastYear = $iThisYear-1;

SELECT * FROM `{$this->_sPrefix}clicks` 
WHERE `affiliate_id` = '{$iAid}' 
AND `raw` = '1' 
AND YEAR(`date`) = '{$iLastYear}'

It grabs from this year still. Does YEAR work?

Upvotes: 1

Views: 10461

Answers (4)

Yevgeniy Afanasyev
Yevgeniy Afanasyev

Reputation: 41390

Use DATE_SUB

.... where yourdate_column > DATE_SUB(now(), INTERVAL 12 MONTH)

Upvotes: 2

angelcool.net
angelcool.net

Reputation: 2546

The following worked for me:

AND year = YEAR( NOW() ) - 1

Answering this for future reference.

Good luck!!

Upvotes: 0

Nick Rolando
Nick Rolando

Reputation: 26177

date('Y') returns a string. You might be running into a problem trying to subtract 1 from a string. You should try subtracting a year's worth of seconds from your timestamp and get that Year. I could be wrong because I know php is loosely typed, and I can't get my xampp working atm to test it. Good luck bud

Upvotes: 0

Matt Lowden
Matt Lowden

Reputation: 2616

Could you use DATE_SUB instead

SELECT * FROM `{$this->_sPrefix}clicks` 
WHERE `affiliate_id` = '{$iAid}' 
AND `raw` = '1' 
AND YEAR(`date`) = YEAR(DATE_SUB(CURDATE(), INTERVAL 1 YEAR))

Upvotes: 8

Related Questions