What is the best way to increment invoice ID?

Question

I'm developing a simple invoice system using PHP and MySQL.
My initial thought was to use ID (auto incremented) for invoice ID. But that will not work.

A temporary invoice is created when user commits an order to my Payment Service Provider. If the transaction fails, I need to delete the temporary invoice.

The problem is that the invoice has been created and given and ID - and by law, I'm not allowed to delete any invoices that has been given a invoice number. So I need to add another column 'invoice_id' and add an ID after successful transaction.

Multiple users may do a purchase at the same time.

My question is, how can I make sure to retrieve the last created invoice ID and increment this?

Should I use a $_SESSION[]for storing invoice_id? Or should I retrieve latest ID from DB? If retrieving from DB, should I lock the table for this transaction?

Any guidance much appreciated.

Answer 1

Create a temporary table for invoices which have not been processes. Once the invoice has been processed, move over to permanent table and assign an invoice id using the AUTO_INCREMENT option in mysql

This will allow for two tables, one for unprocessed and the other for processed. You can also place an id field to keep track of the movement from temp -> perm

Answer 2

I wouldn't delete it, I would just cancel it. Additional usually after a failed transaction the trader reminds the customer to pay, which is not possible, when the invoice doesn't exist anymore.