• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
consecutive days resultset from db

#1
[eluser]hyperfire[/eluser]
Hello all!

I'm trying to achieve something that's actually looking kinda hard to figure out here, any help is welcome.

I need to return a list from a specific date > 2025-01-01.
Something like:

2009-01-10 - Record X
2009-01-10 - Record Y
2009-01-10 - Record Z
2009-01-11 - No records found for this date
2009-01-12 - Record X
2009-01-12 - Record Y.... and so on...

But as in my db, the result is only giving me the dates I really have records. Like:


2009-01-10 - Record X
2009-01-10 - Record Y
2009-01-10 - Record Z
2009-01-12 - Record X
2009-01-12 - Record Y.... and so on...

What I need to do to get a consecutive result list with all dates?
Any ideas?

#2
[eluser]Future Webs[/eluser]
i guess you would need to do a loop within a loop

loop your dates first and build the query based on the current date of your loop

#3
[eluser]Colin Williams[/eluser]
One query will suffice. Nothing looks wrong with your result. You just need to loop through the dates, not the result set. So, for each day, loop through result and show results for the current day, or show an "empty" message for days without records.

That might be what w3bm was saying, but it sounded like he was saying to run a query for each date, which would be overkill.

#4
[eluser]Future Webs[/eluser]
yes your way is more efficient i guess as their is only one call to the database.

i suppose it depends on how many days and how many results etc

#5
[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.

#6
[eluser]Future Webs[/eluser]
i would maybe list the events by showing a month page or a week page or a day page

you would then know which results to call from the db

you could have the month / week/ day etc in the url and grab the filter that way

#7
[eluser]obiron2[/eluser]
Two choices.
1)
Cribbing from above, which is actually quite a nice idea: Create a temporary table on the fly which holds a list of all the dates you are interested int. This requires a stored procedure on the database so is outside the MVC model of CI and its functionality would be hidden from the CI application codebase.

use this temporary table as the driving table and do a left outer join to the events. Generally if you have to use a right join you have not defined the query properly. You need to make sure you do an outer join so that you return the records in the temporary table where there is no data in the events table.

2)
Alternatively, as you are only interested in sequential dates, simply return your existing data set, ordered by date ascending.

loop through the records in the data set and check to see if the date is the same as the previous record. If it is not then compare it to the previous record + 1 day. If it still doesn't match then output 'no records for dd/mm/yyyy' and repeat the comparison until it does match.
Continue until the last record in the dataset.

If you need to potentially include dates that will fall outside the dataset, then you would need to start the date comparison with a known date and the first date in your dataset.

Obiron

#8
[eluser]hyperfire[/eluser]
Another idea witch just popped out here, is to create a table with all dates till 2024 (the end of the world LOL), get the date of the last 'event' trough one little select, then generate the second select from the selected start date, trough the last date returned form the first one, and do the pagination for those records. Is this ugly as it looks like?
Maybe its less resource intensive that creating this table all the time, since its a kinda portal website.


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2019 MyBB Group.