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.
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.
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)