I have already programmed a basic invoicing system using PHP/MySQL which has a table structure as follows;
Invoice table; invoice_id, invoice_date, customer_id, etc Invoice line table; invoice_line_id, invoice_id, quantity, price, description, etc
I need the system to automatically generate future invoices at set intervals (for example every 1 week or every 2 months, etc). I was thinking of creating a new table as follows;
Invoice schedule table; invoice_schedule_id, invoice_id, interval (e.g. 1), interval_unit (months), start date, next_processing_date
My idea was to then setup a cron job which would execute a PHP file once a day. The PHP file would then generate an invoice when the next_processing_date matched today's date and update the next_processing_date in the database. I'm comfortable on how to achieve this but what I'm stuck with is how to actually insert the new invoice into the table/database. Does MySQL have any type of 'copy row' feature as the new invoice would be identical to the original one except for the invoice_date having to be updated.