插入select语句，包括对字段和逻辑思维的更改

Question

I'm working on some php code having to do with creating recurring invoices. I need some help thinking through the logic. I think ultimately I will end up with a type of Insert/Select statement. The php code that is ultimately generated here will run in a nightly cron.

First, let me show you some of my table items for a frame of reference and explain a bit about what they do. The table is called Invoice and there are other tables associated with it, but I think if I can nail now this one table's logic, I can apply it to the others.

ID = This is the primary key that auto increments and also happens to be the invoice number.

recurring = This is whether the invoice is one time or recurring. O=One time, M=Monthly etc

recurred = This will be set to N on insert and is the logic I'm using to see if the next recurring invoice that will be created in the table should be based off this invoice or not. When a new recurring invoice is inserted into the table, this will be changed to Y so its not processed again by the cron job.

date = This is the date that the invoice was inserted/created. For example, if date the invoice was created is the 15th of this month, the next invoice created will be the 15th of next month. Essentially M represents monthly. However, I haven't figured out yet what to do about a created date of the 31st but the next month only has 30 days, could use some help here.

So here is my logic. First, I run a query to find all rows that are not O and recurred = N.

$result = mysql_query("SELECT * FROM invoice WHERE recurring != 'O' and recurred

= 'N'") or die(mysql_error());

Next, I work on each kind of recurring M for monthly, Q for quarterly, etc..

if recurring = M
 if todays date day = 'date' day
  copy line item into a new row with the following changes: new ID, todays date, and mark the recurred to N for the new insert (will mark the old one Y)

Obviously that is not actual code, just what I'm thinking about.

So my questions are 1.) How to create a insert select statement to deal with changed variables and 2.) how to deal with dates where if the invoice was created on the 31st of one month and the next month only has 30 days (or 28).

Answer 1

take only rows that need to be re-inserted:

SELECT *, DATEDIFF(`date`, NOW()) as diff FROM invoice WHERE recuring != 'O' 
AND recured = 'N' AND diff >= 30;

iterate through that list and use a new date for each row by calculating it with:

$newDate = date("Y-m-d", strtotime($oldDate+" +1 month"));

This should solve your 30th day of month problem.

Now insert newly created data.

Also, good advice to use enum for recuring field, and boolean for recured.