mysql: fetching days in order

Question

PHP

It is necessary to implement a calendar of events, namely: the

user specifying 1.01.11 as the start value of the search and 1.02.11 as the end value would receive the list

1.01.11 - 2 events
2.01.11 - 3 events
01.3.11 - 2 events
4.01.11 - 2 events
5.01. 1 1- 2 events
…
31.01.11 — 2 events
1.02.11 — 2 events

is it possible to implement a selection of days using sql command? And if not, then you need to iterate over the value in a loop?

Answer 1

SELECT DATE(`date`), count(`id`) FROM `test` WHERE `date` BETWEEN '2011-12-01 00:00:00' AND '2011-12-02 23:59:59' GROUP BY DAY(`date`)
Where the date field is datetime

Answer 2

And in this case, it is required to display, for example, 2.01 that there is one exhibition?

Answer 3

It seems to me that one cannot do without a table of days in some form. At least as a table of days linked to the event table - when creating an event, the required number of records for the date range would be created in the auxiliary table. But to fill in such a table, you still need at least an array of numbers from 1 to the maximum number of days in the event.
In SQLite, you can create virtual tables - there, probably. You can also generate dates on request. I don’t know MySQL - maybe there are some procedures that can return a selection? Or, for example, create a view of 31 days, 12 months, and the desired number of years, and by combining them in the desired combinations, generate a table of days.

Answer 4

dummy tables:
select 1 from dual union
select 2 from dual union
...
combining different such tables, you can do a lot of things ... only the question of efficiency remains, but suddenly this solution will be more efficient than creating a special table.

Answer 5

Also as an option:

SELECT dates.begin, count( events.id )
SELECT `events` as `dates`
LEFT JOIN `events` as `events` on ( dates.begin <= events.begin 
                                   AND dates.end >= events.end )
GROUP BY dates.begin

Something like this…