Select records fortnightly

[retro]

I accidentally marked my previous thread as solved (although technically, the original issue was!), so I'll ask my final part of the question here!

 

The database takes an order with a ship date.  It also has a tick box to state if the order should be repeated weekly.  Thanks to Barand, I have the following query, which works great:

 

SELECT id, customer, baen, date
FROM orders
WHERE (date = CURDATE() AND recur = 0) OR (DAYOFWEEK(date) = DAYOFWEEK(CURDATE()) AND recur = 1)

 

However, I now need to be able to list orders that are to be delivered fortnightly.  So, for example:

 

An order was delivered on Monday 14 April.  This order was to be repeated fortnightly.  Therefore, on 21 April, it should not show on the list, but it should on 28 April.

 

I have thought about this, and can't see a way of doing it!  Perhaps somehow determine whether it is an odd or even week?  Or is there an easier way?

 

Thanks in advance for any help - it is greatly appreciated!

[Barand]

Assuming recur = 2 for fortnightly deliveries

 

SELECT id, customer, baen, date
FROM orders
WHERE (date = CURDATE() AND recur = 0) 
        OR (DAYOFWEEK(date) = DAYOFWEEK(CURDATE()) AND recur = 1)
        OR (DATEDIFF(date, CURDATE() ) MOD 14 = 0 AND recur = 2)

[retro]

I'm now getting:

 

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in ..... on line 18

 

Line 18 is the line after the query:

 

$total = mysql_num_rows($result);

 

It was working fine on the weekly one.  Any ideas?