[SOLVED] Recurring orders

I hope this isn't a silly question, but I am new to PHP, still learning, and cannot think of a way to achieve this easily!

I have a table in my database called 'orders'.  This table has the fields 'ID', 'customer', 'baen' (this field contains the order information in encoded form) and 'date'.  This is fine for one-off orders, but the primary function for the site is something more complex.

Basically, I want to have a check box on the order page.  When it is unticked, the order is placed as a one-off.  However, when the box is ticked, the order recurrs on every given day.  There is a part of the site which lists daily deliveries, and this should automatically be told when someone wants a delivery, for example, every Friday.

For example, say Mr. Smith wants to place a recurring order, starting 23 April 2008.  That is a Wednesday.  I want to be able to select 23 April 2008, tick the box and the deliveries lists will automatically include Mr. Smith on every Wednesday after this date.

Can anyone suggest how I might go about this?

Incidentally, I have to be able to look at the deliveries sheet for any day in the future, at least for the next month, shall we say. 

Thanks in advance for any assistance, it is greatly appreciated!

You need an extra column in the table to tell you if it is a recurring order or not.

Sorry, I forgot to mention that I would put an extra column in, and of course that would be defined by the check box.  Once I have that, though, how do I actually go about making the order recur on the correct day?

SELECT id, customer, baen, date

FROM orders

WHERE (date = CURDATE() AND recur = 0) OR (DAY(date) = DAY(CURDATE() AND recur = 1)

That's great, thanks!!

Is it just me, or is there a closing bracket missing from that somewhere?  I admit you're the knowledgable one, I'm just a newbie, so it is probably just me!!

I wanted to have it so a calendar is used to select the date examined.  I set this up, the form goes to another php page and posts 'DelDate' from the calendar.  I then used the following:

$DelDate = $_POST["DelDate"];
$result = mysql_query ("SELECT ID,customer,baen,date FROM orders WHERE DAY(date) = DAY(" . $DelDate . ")");
$total = mysql_num_rows($result);

(I haven't implemented the check box yet, so I decided to just try it without the recurring part for now)

I got the following error:

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /mounted-storage/home90b/sub008/sc14707-CTMU/www/delivery/contacts/viewreport.php on line 18

Line 18 is the $result line.

I am not very experienced with MySQL, so am not too up on the correct syntax.  Could someone point out where I am going wrong, please?

I tried something basic, and just put an actual date in the query.  I have 4 entries in the database with the date 2008-04-24.

The following worked, and produced 4 results:

$result = mysql_query ("SELECT ID,customer,baen,date FROM orders WHERE date='2008-04-24'");

However, the following did not:

$result = mysql_query ("SELECT ID,customer,baen,date FROM orders WHERE DAY(date)=DAY('2008-04-24')");

I got:

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /mounted-storage/home90b/sub008/sc14707-CTMU/www/delivery/contacts/viewreport.php on line 18

The rest of the code was as per my last post, so line 18 is the $total line.

Any suggestions?

Sorry, that should be

SELECT id, customer, baen, date

FROM orders

WHERE (date = CURDATE() AND recur = 0) OR (DAYOFWEEK(date) = DAYOFWEEK(CURDATE()) AND recur = 1)

Sorry for the delay in replying - other things have taken priority over coding for a bit, unfortunately.

That's lovely, thanks! 

Incidentally, can you think of a way of filtering the query for the same day, but fortnightly instead of weekly?  I'm guessing the answer could be in working out whether it is an odd or even week, but it seems complex.  Is there a simple way?

Thanks again!

*EDIT* found the solved button!