How to get a list of dates?

Question

PHP

There is a table:
id date score
1 2017-02-01 10
2 2017-02-05 6
3 2017-02-05 7
4 2017-02-07 4
I need to get data in the form:
1 2017-02-01 10
2 2017 -02-02 0
3 2017-02-03 0
4 2017-02-04 0
5 2017-02-05 12
6 2017-02-06 0
7 2017-02-07 4
Those data that are available must be grouped by date and score, and the dates of which are not in the gap, you need to add and insert zeros.
How can this be done in MySQL?
As an option, I thought to get the data that is, in PHP create an array with dates and zeros, and then merge two arrays.

Answer 1

CREATE TABLE Data (d DATE, i INT);
INSERT INTO Data VALUES ('2017-02-01', 10), ('2017-02-05', 6),
                        ('2017-02-05',  7), ('2017-02-07', 4);

DELIMITER $$
CREATE PROCEDURE all_those_days(IN start DATE, IN stop DATE)
  BEGIN
    DROP TEMPORARY TABLE IF EXISTS series;
    CREATE TEMPORARY TABLE series (d DATE PRIMARY KEY) ENGINE = MEMORY;
    WHILE start < stop DO
      INSERT INTO series VALUE (start);
      SELECT DATE_ADD(start, INTERVAL +1 DAY) INTO start;
    END WHILE;
    SELECT series.d, COALESCE(SUM(Data.i), 0) AS i
    FROM series LEFT JOIN Data ON series.d = Data.d GROUP BY series.d;
  END $$
DELIMITER ;

CALL all_those_days('2017-02-01', '2017-02-10');