How to program a recurring billing/invoice system using PHP and MySQL

Question

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.

Answer 1

Cron sounds good. (Also it is worth to mention the MySQL Event Scheduler, but again I would go for a Cronjob)

A copy would be something like this SQLFIDDLE:

create table t ( id int, d date );

insert into t values( 0, CURDATE() );
insert into t values( 2, CURDATE() );
insert into t values( 1, CURDATE() );

insert into t ( select id+1,curdate() from t order by id desc limit 1 );

Above example is to copy the latest order as a copy, of course you could put a where clause where id=1 or what id your reference order is.

Answer 2

BigScar's reference of "How to copy a row and insert in same table with a autoincrement field in MySQL?" seems to solve your copy-insert problem.

However, since you are mostly doing a specific group of DB queries, instead of cronjobs, you may use MySQL events. If your MySQL version supports them (check in phpmyadmin: select a DB and look to the top menu bar, you can create them there without even have to know the syntax), it's a good practical alternative.