Query between Mysql dates showing every day of the range

2

Hello,

I would like help with logic. I have an ecommerce system in which N products are sold and I am comparing the sales of a certain product per period of time, ie the admin will have an area in which he chooses a product and a date range (Ex: 01/04/2018 - 04/26/2018) and I'll plot this in a line graph so he can see the sales evolution of this product over time.

My question is, 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 fetch 1 to 1 and generate an array of it, but I wanted to know if it has any direct form in mysql.

Example:

Product A
Start date 01/04/2018
End date 05/04/2018

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

The result of this query would be only 01/04 - 01 sold
02/04 - 04 sold
04/04 - 06 sold

I wanted the zero days to come too.

I hope to be clear and thank you right away.

    
asked by anonymous 27.04.2018 / 03:37

1 answer

0

What you need to do is to query the desired interval with MySQL and then with PHP you do the interaction and it shows every day and conditions that the days that are not returned are displayed as 0.

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

    
27.04.2018 / 04:01