I have a simple system to monitor and plot statistical data from a MySQL server. The services are written in PHP and return JSON. The issue am stuck with is that as today is July 1, 2014 ... values from the last seven days graph and table became zero. The date calculation logic is giving correct dates in the table output. I tried to manually call the service and check, and indeed the values in JSON are all zero. Most of the queries in this module utilise the following:
$query_string = SELECT new_user_count AS new_user_count
FROM kpi_summaries
WHERE DATE(date) = CURDATE()-".$n."
AND data_type=1";
In a simple loop, I am using this query string to get values for 7 last days from every day. This worked perfect till yesterday (since I guess, dates after subtraction from CURDATE were all in the same month).
Another way to phrase my doubt is: Is it not correct to use CURDATE()-3 if 3 days ago was a date in the last month ? If this is wrong, what is the correct way to go about it ? Will I need to use DATE_SUB() or something like that ..?