I have a question that seems easy but i failed to solve it by myself
i have this mysql table with the statistics of a website visitors
+-----------------------------+
+ date | visits +
+-----------------------------+
+ 2014-03-01 | 198 +
+ 2014-03-02 | 259 +
+ 2014-03-03 | 94 +
+-----------------------------+
i have this query
SELECT * FROM statistics WHERE date >= '2014-03-01' and date <= '2014-03-30';
My question is if i can complete the whole month with one sql query so i can get this output:
+-----------------------------+
+ date | visits +
+-----------------------------+
+ 2014-03-01 | 198 +
+ 2014-03-02 | 259 +
+ 2014-03-03 | 94 +
+ 2014-03-04 | 0 +
+ 2014-03-05 | 0 +
+ 2014-03-06 | 0 +
...
+ 2014-03-30 | 0 +
+-----------------------------+
Thank you very much in advance.
What do you mean by one sql query? Here you are also using one single query.
"SELECT * FROM statistics WHERE date >= '2014-03-01' and date <= '2014-03-30'"
I think you can use Between clause. You can write that one like
"SELECT * FROM statistics WHERE date BETWEEN '2014-03-01' AND '2014-03-30'".
Two things.
SQL can't (easily) create rows where there is no data. That means if there are no rows for 2014-03-05, then you won't get a row, let alone one where the count is 0. The only real way to "create" such empty rows is to have another table with a list of the dates that you LEFT JOIN to. But this is awfully naff.
Secondly, you can fetch a whole months' worth easily with the following:
WHERE date BETWEEN '2014-03-01' AND DATE_ADD('2014-03-01', INTERVAL 1 MONTH)