php - Using Date_Sub to subtract year with Aliases column -

php - Using Date_Sub to subtract year with Aliases column -

i have 2 column brand , date_time , during select assign them aliases. query below:

select brand, year(date_time) 'year', sum( brand = 'kfc' ) kfc sum( brand = 'pzh' ) pzh, sum( brand = 'sub' ) sub scan_report grouping year(date_time)

result this:

now here's confuse part. how can utilize date_sub function display info lastly 5 years. mean user come in 2014 , display/select record lastly 5 year means 2010 2014.

using where such gave me empty records:

select year(date_time) 'year', sum( brand = 'kfc' ) kfc sum( brand = 'pzh' ) pzh, sum( brand = 'sub' ) sub scan_report date_sub(2014, interval 5 year) grouping year(date_time)

or

from scan_report date_sub(year = 2014, interval 5 year)

any thought how can retrieve record lastly 5 years of user input? prefer solve problem mysql, if no solution found i'll go php. in advance.

do simple math in php:

class="lang-php prettyprint-override">$year = 2014; # $_post or anywhere else $from = ($year - 4) . '-01-01 00:00:00'; $to = $year . '-12-31 23:59:59';

and utilize 2 variables in query.

class="lang-php prettyprint-override">$sql = " ... date_time between '$from' , '$to' ... ";

demo

update (calculation in sql):

if don't want calculate in php, can calculate in mysql:

class="lang-php prettyprint-override">$sql = " ... date_time between concat({$year}-4, '-01-01 00:00:00') , concat({$year}, '-12-31 23:59:59') ... "; update (don't care speed or indexes): $sql = " ... year(date_time) , {$year}-4 , {$year} ... ";

php mysql datetime mysqli alias