Help figuring out code

[Canman2005]

Hi all

 

I have a code I need to write and wondering if anyone can help;

 

Basically I have 2 tables, ill explain what they both are;

 

--orders--

id (auto id number)

startdate (yyyy-mm-dd)

 

--items--

id (auto id number)

orderid (id number from `orders` table)

repeat (either set to 1,2,3 or 4)

 

In the `items` table I have several rows and they each have a number under the field `repeat`, the numbers mean the following;

 

1 = repeat every week

2 = repeat once every 2 weeks

3 = repeat once every 3 weeks

4 = repeat once every 4 week

 

So what I want to do is grab all rows from the `orders` table which have a start date equal to or later than todays date.

 

Lets say today was a Tuesday, it would only return rows that the `startdate` is also a Tuesday, so for example it would grab a row if it has a startdate of 2009-01-20 (which is a Tuesday) but would not grab a row which had a `startdate` of 2009-01-21 (which is a Wednesday).

 

Then what I need to do is look in the `items` table and grab all the rows with the linked `orderid`.

 

I want to add some more but didnt want to overflow this message with information.

 

Does what im trying to do make sense so far?

 

Thanks

 

Ed

[Canman2005]

Let me try and give an example of what I want to do next

 

Say todays day was a Thursday and in the `orders` table we had a row which looked like

 

id    startdate

2    2009-01-01

 

So as you can see, the `startdate` is set to '2009-01-01' which is a Thursday, then every Thursday it should return this row.

 

Now say the assocated rows returned from the `items` table are the following;

 

id    orderid    repeat

32    2          1

33    2          2

34    2          2

35    2          3

36    2          4

37    2          4

 

So on the '2009-01-01' it would return all of the above, but then a week later on the '2009-01-07' it would just return rows which have a repeat value of 1 (because value 1 means return every week)

 

Every 2 weeks since the `startdate` is would return rows with a `repeat` value of 1 and 2 (because 1 is every week and 2 is every 2 weeks)

 

Then a week later it would return rows with a `repeat` value of 1 and 3 (because 1 is every week and 3 is every 3 weeks)

 

Then a week later it would return rows with a repeat value of 1,2 and 4 (because 1 is every week, 2 is every 2 weeks and 4 is every 4 weeks)

 

so depending on what repeat value is stored, that is when that row should be returned.

 

Just to mention, on the 3rd week (or 6th week, or 9th week ....) from the `startdate`, it would return rows with a `repeat` value of 1,2 and 3 (simply because 1 is every week, 2 is every 2 weeks and 3 is every 3 weeks)

 

As mentioned before

 

1 = repeat every week

2 = repeat once every 2 weeks

3 = repeat once every 3 weeks

4 = repeat once every 4 week

 

I know this is a bit of a mess, but hopefully it makes some kind of sense.

 

Does anyone understand what I am trying to do?

[Canman2005]

Can anyone help?