MySQL Retrieve Weekly events stored with DAYOFWEEK

I have lessons stored in a table. Theses lessons are repeated every week, so I only store the index of the day (0: Sunday, 1: Monday, etc...)

Here's an example: (My real table has much more columns)

ID   NAME      DAY
1    English   1
2    Math      2
3    Physic    3

If I want to get the lessons of today, I can look for lessons where DAY = DAYOFWEEK(NOW())

Now I want to get all lessons from one date to another, and that range can be greater than a week. And I need to get the date that match with that occurrence of the lesson.

If I look for lessons from 2013-12-23 to 2013-12-31 I expect something like that:

ID  NAME      DATE
1   English   2013-12-23
2   Math      2013-12-24
3   Physic    2013-12-25
1   English   2013-12-30
2   Math      2013-12-31

My original idea was to join a virtual table that contains all the dates of the range to my lessons table on LESSONS.DAY = DAYOFWEEK(DATES.DATE), but I didn't figured how to do that.

Is it possible to do that within the mysql query ? I'm using it in a PHP program.

I've already searched a lot on the web but I couldn't find anything like that.

Thanks in advance.

PS: English is not my native language.

Edit Here's my actual query. I can get the lessons that are within the range but they are unique and without their corresponding date.

SELECT * FROM Lessons
WHERE Day_Lesson > DAYOFWEEK(:datefrom) AND Day_Lesson < DAYOFWEEK(:dateto)

Solution

Use PHP to create a virtual table that contains all the days in the time range, using UNION.

$begin = new DateTime('2013-12-23');
$end = new DateTime('2014-01-01'); // DatePeriod is exclusive of end date
$interval = new DateInterval('P1D');
$period = new DatePeriod($begin, $interval, $end);

$sqlarray = array();
foreach ($period as $date) {
    $sqlarray[] = 'select "' . $date->format('Y-m-d') . '" AS date';
}
$union = implode(' UNION ', $sqlarray);
$sql = "SELECT *
        FROM ($union) AS d
        JOIN Lessons AS l
        ON DAYOFWEEK(d.date) = l.day";