CODEWITHSUNDEEP
phpmysql
The Oracle
The Oracle

Reputation: 13

php and mysql query to get date of the last 12 months

I have a php function to get dates for a morris pie chart and it was working fine.

But now that I have more data (date for the last year and the first 3 months of this year). It's now displaying duplicate months. In this case february of last year and this years data is now showing on the same pie chart.

I'd like to write a some mysql code in php that will only display the last 12 months. I have the following code:

function writesql($rec) {
$year = date('Y') -1;
$month = date('m');
$lastyear = $year - $month; // I know this is a problem.It's subtracting the two variables

$sql = "";
$sql = $sql . " SELECT";
$sql = $sql . " YEAR(`value`)as 'Year',";
$sql = $sql . " MONTH(`value`)as 'Month',";
$sql = $sql . " `value` , ";
$sql = $sql . " COUNT(`value`) as 'Calls' ,";
$sql = $sql . " ROUND(SUM( `value` ),2) as 'Value'";
$sql = $sql . " FROM `table`";
$sql = $sql . " GROUP BY";
$sql = $sql . " YEAR(`value`),";
$sql = $sql . " MONTH(`value`)" ;
$sql = $sql . " ORDER BY";
$sql = $sql . " YEAR(`value`),";
$sql = $sql . " MONTH(`value`)";
$sql = $sql." WHERE (`value`)='".$lastyear."'";// I also know this is wrong too but im lost as to how to fix it.

return $sql;
}

Then the rest follows, I only want to get the data for the last 12 months how should I be executing this.

Upvotes: 0

Views: 976

Answers (3)

Louis
Louis

Reputation: 116

You could do WHERE value >= DATE_SUB(CURDATE(),INTERVAL 1 YEAR);

This would show 12 months up to the current date.

Upvotes: 3

Strawberry
Strawberry

Reputation: 33945

First off, I find this easier to read:

    $sql = "
    SELECT YEAR(value) Year
         , MONTH(value) Month 
         , value
         , COUNT(value) Calls
         , ROUND(SUM(value),2) Value
      FROM `table`
     GROUP 
        BY YEAR(value)
         , MONTH(value)
     ORDER 
        BY YEAR(value)
         , MONTH(value)
     WHERE (value) = '".$lastyear."'
     ";

But this query is syntactically incorrect. So here's a syntactically correct version:

$sql = "
SELECT YEAR(`value`) Year
     , MONTH(`value`) Month 
     , COUNT(value) Calls
     , ROUND(SUM(value),2) Total_Value
  FROM `table`
 WHERE value = '".$lastyear."'
 GROUP 
    BY YEAR(value)
     , MONTH(value)
 ORDER 
    BY YEAR(value)
     , MONTH(value);
 ";

Now see about prepared and bound queries

Upvotes: 0

Samir Selia
Samir Selia

Reputation: 7065

You are close. Make these changes

$year = date('Y') -1; // This will give last year

$sql." WHERE YEAR(`value`)='".$year."'"

Updated Query

$sql = "
SELECT YEAR(`value`) as 'Year', MONTH(`value`) as 'Month', `value`,
COUNT(`value`) as 'Calls', ROUND(SUM( `value` ),2) as 'Value'
FROM `table`
WHERE YEAR(`value`) = '$year'
GROUP BY YEAR(`value`), MONTH(`value`)
ORDER BY YEAR(`value`), MONTH(`value`)";

Upvotes: 0

Related Questions