Fill in missing SQL rows?

Question

PHP

There is a request like this:

SELECT DATE_FORMAT(`orders`.`date`, '%d.%m.%Y') as date, SUM((`orders_items`.`price`)*(`orders_items`.`count`)) AS `money` FROM `orders_items` 
LEFT JOIN `orders_groups` ON `orders_items`.`order_group_id` = `orders_groups`.`id`
LEFT JOIN `orders` ON `orders_groups`.`order_id` = `orders`.`id`
LEFT JOIN `goods` ON `goods`.`id` = `orders_items`.`good_id` 
LEFT JOIN `users` ON `orders`.`user_id` = `users`.`id`
LEFT JOIN `orders_status` ON `orders_status`.`id` = `orders_items`.`status_id` 
WHERE date(`orders`.`date`)
BETWEEN '2016-08-02' AND '2016-08-15' 
GROUP BY DATE_FORMAT(`orders`.`date`, '%d.%m.%Y')
ORDER BY `orders`.`date` DESC

The result is as follows:

As you can see, there are 4 days missing between the dates 08/11/2016 and 08/15/2016. That is, these days there were no records (0 lines). Actually, the question is how to display all dates in the interval specified in BETWEEN, even if there are no rows (08/12/2016 - 0, 08/13/2016 - 0 ...)?

Answer 1

An array of dates can be created by a query (not strong in mysql), something like this:

SELECT DATE_ADD('2016-08-02' ,INTERVAL help_topic_id DAY) as mydate
 FROM mysql.help_topic order by help_topic_id asc limit 14

Then use left join to join your query.

Answer 2

стандартное решение это создать таблицу-календарь и присоединить ее с right join.