My current project is for a delivery sheet creator.
Whilst I have not yet implemented it, I have to have it so that upon entering the order, you must select whether the order is a one-off, or whether it should be repeated weekly on the same day, or whether it should be repeated fortnightly.
What I think I will do is have a 'recur' field in my 'orders' table. The data from the form (e.g. a combo box) will go in this form, and be interpreted as follows:
0 = One-off order
1 = Weekly order
2 = Fortnightly order
I have an input box with a calendar, which is used to select the required date. This then redirects to a page with a query, which displays the results.
For testing purposes, I have used the following query, which works for weekly orders:
$result = mysql_query ("SELECT * FROM orders LEFT JOIN customers ON (customers.ID = orders.customer) WHERE DAYOFWEEK(date)=DAYOFWEEK(' . $DelDate . ')");
This works great - it shows items on the correct day.
I cannot work out how to do it for fortnightly orders, though.
I'll clarify what I mean here.
Say I have the following orders:
Order 1: 29 April, Weekly
Order 2: 6 April, Fortnightly
Order 3: 13 April, Weekly
These are all Tuesdays. If I were to search the 13 April, currently it would show me all 3. However, I don't want it to show me order 2 - that should appear for the 6th or the 20th, but not the 13th.
How can I go about this?
Someone suggested I use the following, but it gives me an error:
WHERE DATEDIFF(date, ' . $DelDate . ') MOD 14 = 0