Numbering invoices yearly and different structures with SQL and PHP

I'm a beginner in SQL and I tried looking for an answer without any luck.

I would like to generate invoice numbers automatically with the following distinctions:

I have 2 different structures invoicing
I would like to avoid any numbering issue, for example not having a missing invoice number if something gets altered (ex: 1, 2, 4, 5, 8, 9 ...)
I would like them to be reset each year, so the numbering starts back to 1 every year for each structure :

ex: ENG_2022_1, ENG_2022_2 ... ENG_2023_1

ex: FR_2022_1, FR_2022_2, FR_2022_3, ... FR_2023_1

I have thought of setting a field with auto-increment, but that wouldn't work with the structure and year distinctions unless there's a workaround?

The other solution would be to get the year and structure before invoicing, and comparing it with a SQL MAX of the invoice number, before numbering but i'm not sure how good that solution?

Any help would be greatly appreciated

Solution

The use of an auto-increment column seems obvious, however, in your case you want to restart the numbering at the beginning of each year, so something should accommodate that.

I would create, at least, four columns for this in the invoice table:

invoice_id: This is the auto-increment column.
invoice_date: You can get the year with YEAR(invoice_date) from this date.
invoice_sequence: Either FR or ENG.
invoice_number: Contains the actual invoice number.

To create the invoice number you would need to know the number of invoices in a sequence for the current year. This could be retrieved in a separate query:

SELECT
  COUNT(*) AS invoice_count
FROM
  invoice_table
WHERE
  invoice_sequence = :sequence AND
  YEAR(invoice_date) = YEAR(:date);

In this query the :sequence and :date are placeholders for binding the values you want to look for.

After that you can insert the actual invoice. Here's a mock-up of such an insert:

INSERT INTO
  invoice_table
  (invoice_date,
   invoice_sequence,
   invoice_number,
   ......)
VALUES
  (:date,
   :sequence, 
   CONCAT(:sequence, '_', YEAR(:date), '_', :invoiceCount),
   ......)

In this query the :date, :sequence and :invoiceCount are placeholders for binding the values we already know. Note that in some cases you cannot repeat the same placeholder name. In that case use something like :name1 and :name2, or just ?.

Note that you could put the query to retrieve the count in the insert query as a sub-query. I didn't do that here for better readability.