Query between MySQL dates showing all days in the range

Question:

I have an e-commerce system in which N products are sold and I'm making a comparison between sales of a certain product by period of time, that is, the admin will have an area in which he chooses a product and a date range ( ex: 01/04/2018 – 26/04/2018) to plot this on a line graph allowing you to see the evolution of sales of this product over time.

Is there any way, in MySQL itself, to make even dates that have no sales appear as 0? I know I could generate this range with PHP and get 1 to 1 and generate an array that, but I was wondering if there is any direct way to MySQL.

Example:

Produto A
Data Inicial: 01/04/2018
Data Final: 05/04/2018

01/04 - 01 vendidos
02/04 - 04 vendidos
03/04 - 00 vendidos
04/04 - 06 vendidos
05/04 - 00 vendidos

The result of this query would only be

01/04 - 01 vendidos
02/04 - 04 vendidos
04/04 - 06 vendidos

I wanted the zero days to appear too.

Answer:

What you need to do is query the intended range with MySQL and then with PHP you do the interaction and show every day and conditions so that the days that are not returned are displayed as 0.

WHERE DATE(data) > DATE(:inicial) AND DATE(data) < DATE(:final)

Scroll to Top