CODEWITHSUNDEEP
mysql
Chinmay235
Chinmay235

Reputation: 3414

How to get all dates between two dates?

Please look at my books table:

books

+-----+----------+------------+------------+
| id  | venue_id | from_date  | to_date    |
+-----+----------+------------+------------+
|  1  |  8       | 2015-07-21 | 2015-07-28 |
|  2  |  5       | 2015-08-03 | 2015-08-25 |
+-----+----------+------------+------------+

I want to view all dates between from_date and to_date for which venue_id = 8

My output should be:

Array
(
    [0] => Array
        (
             [id] => 1
             [venue_id] => 8
             [today] => 2015-07-21
        )
    [1] => Array
        (
             [id] => 1
             [venue_id] => 8
             [today] => 2015-07-22
        )
    [2] => Array
        (
             [id] => 1
             [venue_id] => 8
             [today] => 2015-07-23
        )
    [3] => Array
        (
             [id] => 1
             [venue_id] => 8
             [today] => 2015-07-24
        )
    [4] => Array
        (
             [id] => 1
             [venue_id] => 8
             [today] => 2015-07-25
        )
    [5] => Array
        (
             [id] => 1
             [venue_id] => 8
             [today] => 2015-07-26
        )
    [6] => Array
        (
             [id] => 1
             [venue_id] => 8
             [today] => 2015-07-27
        )
)

today calculates from from_date date to to_date date. But my mind is blank about that query. How to write a MySQL query to get output like the output shown above?

Upvotes: 2

Views: 2680

Answers (2)

Stand Still
Stand Still

Reputation: 117

I think the below query should fetch the results. Though I haven't tested the query.

select b.id,b.venue_id,d.date from books b 
join
(select a.Date, "8" as venue_id2
from (
select curdate() - INTERVAL (a.a + (10 * b.a) + (1000 * c.a)) DAY as Date
from (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a
cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b
cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as c
) a
where a.Date between '2010-01-20' and '2010-01-24') d on d.venue_id2 = b.venue_id and b.venue_id = 8

Upvotes: 2

shankar kumar
shankar kumar

Reputation: 648

Following query should be use to get desired result. select * from books where venue_id=8 and from_date>='2015-07-21' and to_date<='2015-07-27'

Upvotes: 0

Related Questions