I have a database that contains payment amounts and dates for the payments.
example:
date = '2016-12-15'
transaction_amount = '250.00'
I then have a dashboard that calculates the sum of the payments for the current month, previous month and following month.
For the current years calculations my select works fine. It looks for any dates with that month and year. The issue is when the year changes, the results are not for the intended month. For example, for Dec 2016, I want Jan 2017 but instead I am getting Jan 2016.
How do I continuously calculate the current month, previous month and next month data and take into consideration the change from year to year.
Here is my code:
$getlastmonth = mysqli_query("SELECT sum(transaction_amount) as TOTAL
from customer_payment
WHERE YEAR(date)=YEAR(CURDATE())
and MONTH(date)=MONTH(CURDATE()- INTERVAL 1 MONTH)
and status='completed'");
$getthismonth = mysqli_query("SELECT sum(transaction_amount) as TOTAL
from customer_payment
WHERE YEAR(date)=YEAR(CURDATE())
and MONTH(date)=MONTH(CURDATE())
and status='completed'");
$getnextmonth = mysqli_query("SELECT sum(transaction_amount) as TOTAL
from customer_payment
WHERE YEAR(date)=YEAR(CURDATE())
and MONTH(date)=MONTH(CURDATE()+ INTERVAL 1 MONTH)
and status='completed'");
I've tried using answers found on S.O. but they all seem to just get the exact day of the next month instead of the date range (start to end of the month).