foreach date loop

[elogicmedia]

Hi All,

I was wondering how would I go about setting up a loop of all dates within a range so I could then check if they match my database record.

I have these variables:

$the_start = date('Y-m-d H:i:s', $_REQUEST['start']);
$the_end = date('Y-m-d H:i:s', $_REQUEST['end']); 

I need to somehow look though every date between these dates and check each one and cross reference each date's date("w") with a field in my database, if it matches then I print a record.

The idea is I have a month range from the $the_start to $the_end and I am trying to get a print out of say, each thursday. It's for reocurring events in a calendar.

Is there an easy way to create this loop?

Thanks

[AnthonySterling]

You don't.

SELECT foo FROM table WHERE date > this AND date < that;

[elogicmedia]

That doesn't work for what I need. It doesn't show me all the 'thursdays' within that range so I can match them up to the reocurring 'thursday' database record.

To explain further:

I have a table that stores the event name and the weekly reocurring day (date("w")) I then need to loop though the date range and each day that matches my reocurring field to then echo out the event name.

I hope this makes sense.

Thanks for the reply.

[AnthonySterling]

So you only want records that are on a Thursday? All others you'll, simply not use?

[elogicmedia]

Yeah kind of, it will be for any day of the week but I am using 'thursday' as the sample.

I only need the dates within the range that have a matching DB record for that reocurring date.

[Michael Morris]

Read up on the MySQL Date functions. This is still quite doable with SQL alone.

[elogicmedia]

Read up on the MySQL Date functions. This is still quite doable with SQL alone.

So your saying with MySQL I can select 1 record from a table, and it will generate 4 records for my script? e.g. I select the 1 record that has a reocurring day of thursday then it generates the 4 thursdays in my date range even though these records don't exist?

I don't see how?

[guido2004]

To do to make it more clear to the others what you want to achieve, I guess what you need to do is give some example data from your database, and how you want that data to display.

Like:

table data:
Thursday Soccer game

Date range: 14 december 2010 - 24 december 2010

Result:

14
15
16 Soccer game
17
18
19
20
21
22
23 Soccer game
24

[elogicmedia]

To do to make it more clear to the others what you want to achieve, I guess what you need to do is give some example data from your database, and how you want that data to display.

Like:

table data:
Thursday Soccer game

Date range: 14 december 2010 - 24 december 2010

Result:

14
15
16 Soccer game
17
18
19
20
21
22
23 Soccer game
24

Yes thank you that is basically what I am trying to say. It would work as follows:


table data:
Thursday Soccer game

Date range: 14 december 2010 - 24 december 2010

Result:

16 Soccer game
23 Soccer game

[r937]

table data:
Thursday Soccer game

this is barely enough to go on

would you kindly do a SHOW CREATE TABLE for this table

[AnthonySterling]

So, ideally you want you result set from the database that looks like:-

football | home | 2010-12-24 00:00:00
football | home | 2010-12-25 00:00:00
football | home | 2010-12-26 00:00:00

From a record that looks like this:-

football | home | 2010-12-24 00:00:00 | 2010-12-26 00:00:00 | 1

?

If so, you need to figure out how to get the desired result set I'd say.

[cranial-bore]

$day_of_week = 4; //0 = sun, 6 = sat
$start = '2010-12-01';
$end = '2010-12-19'; 

SELECT `fields` FROM `table`
WHERE DATE BETWEEN $start AND $end AND DATE_FORMAT(DATE, '%w') = $day_of_week

This assumes your events are only date (not datetime) resolution. You may need to adjust if the time of day matters.
Instead of BETWEEN you'd probably need to compare < the start of the next day, to ensure events up to midnight are included.

Change $day_of_week to your desired value.