[eluser]hyperfire[/eluser]
Well guys, thanks! The thing is that a sorta events list, so it will be listing all days that have an 'event', plus, telling the user the days that have not.
Its like a calendar...but linear and paged, the user can navigate trough pages like first 1, 2, 3 > last and the list is something like I've already told on the first post.
ATM, it is ignoring the days witch do not have any event, and to be honest, it might fit.
The only catch is when user clicks an event day, witch have no events, so then listing will became something like this:
Day 12 - No events
Day 15 - Event X
Day 15 - Event Y
Day 16 - Event Z
...
Witch I already have done here.
The catch about doing it like Colin said, is that when I request db data, I have a specific amount of data returned by offset pagination, so dealing with the dates on PHP would mess the amount of records listed on every page.
A brazilian dude, sugested me the following solution witch I have not tested yet.
An SP like this:
Code:
DELIMITER $$;
DROP PROCEDURE IF EXISTS `test`.`Gera_data`$$
CREATE PROCEDURE `test`.`Gera_data` (data_ini date)
BEGIN
DROP TEMPORARY TABLE IF EXISTS `test`.`Gera_data`;
CREATE TEMPORARY TABLE `test`.`Gera_data`(
DATA date NOT NULL,
PRIMARY KEY (DATA)
);
WHILE data_ini <= NOW() DO
INSERT INTO `test`.`Gera_data`(DATA)
VALUES (data_ini);
SET data_ini = INTERVAL 1 DAY + data_ini;
END WHILE;
END$$
DELIMITER;$$
execute the sp
Code:
call gera_data('2007-01-01');
Code:
SELECT gd.data , GROUP_CONCAT(g.dia, ' ') AS event_dates
from sase.guia g
RIGHT JOIN gera_data gd ON g.dia = gd.data
WHERE gd.data between '2007-01-01' and '2007-12-31'
GROUP BY gd.data;
Witch might really work, so I'm willing to try it here.