I've been thinking on this for a few hours, and I can't work out a viable solution - I can think of solutions, but they are clunky.
Using MySQL, I want to create an invoice table using auto increment for the invoice number. Pretty standard stuff. HOWEVER, my challenge is that I wish to have multiple companies generating invoices in this table, but all still with the unique invoice number that is auto incremented from the previous one.
For example:
- Company A's previous invoice is 3010
- Company B's previous invoice is 2144
- Company C's previous invoice is 7889877
When Company A creates their next invoice I want it to be 3011. B to be 2145 etc.
I had the idea of doing a query WHERE company = 'B' ORDER BY id DESC
then using PHP to increase that value++, then insert a new record with the invoice number inserted from PHP. In this case the 'id' field would be irrelevant to the actual invoice number, and just be a unique identifier.
Is there a better way?