I have this table structure:
EDIT more complex example: add hidden range
category| day | a |
--------|------------|-------|
1 | 2012-01-01 | 4 |
1 | 2012-01-02 | 4 |
1 | 2012-01-03 | 4 |
1 | 2012-01-04 | 4 |
1 | 2012-01-05 | 5 |
1 | 2012-01-06 | 5 |
1 | 2012-01-07 | 5 |
1 | 2012-01-08 | 4 |
1 | 2012-01-09 | 4 |
1 | 2012-01-10 | 4 |
1 | 2012-01-11 | 5 |
1 | 2012-01-12 | 5 |
1 | 2012-01-16 | 5 |
1 | 2012-01-17 | 5 |
1 | 2012-01-18 | 5 |
1 | 2012-01-19 | 5 |
...
with 'category-day' as unique keys. I would extract a range of dates, for each category, according with column "a" and given limit range, like so:
1,2012-01-01|2012-01-04,4
1,2012-01-05|2012-01-07,5
1,2012-01-08|2012-01-10,4
1,2012-01-11|2012-01-12,5
1,2012-01-13|2012-01-15,0
1,2012-01-16|2012-01-19,5
or similar.
I search the best way for do it. Using only mysql preferably but also with a little bit of php.
NOTE1: not all day are inserted: between two days non-contiguos could not be other days. In this case I would in output the missed range with column "a" = 0.
NOTE2: I did it with a simple query and some rows of php but I don't like it because my simple algorithm need a cycle for each day in range multiplied for each category found. If range is too big and there are too much categories, that's not so good.
FINAL EDIT: OK! After reading all comments and answers, I think not exists a valid, efficient and, at same time, readable solution. So Mosty Mostacho answer is a no 100% valid solution, but it has 100% valid suggestions. Thank you all.