PHP & MySQL - Generate own unique reference-CodePudding

I am currently coding a web application which I will need to create unique reference for quotes and invoices.

I wanted to create a reference that included the year and month then an reference number. i.e YYYY-MM-001.

The web application will be multi tenant and several users will be using it at the same time. One of my concerns is, how would I generate my reference without it be duplicated at the same time if there is multiple users doing the same request at the same time?

What would be the best way for me to approach this?

I am using PHP 8 and a MySQL database.

CodePudding user response：

Hope this will help you.

1) echo strtotime(date("Y-m-d h:i:s")).'-YOUR_REFERENCE_NUMBER'; // O/P : 1671533979-YOUR_REFERENCE_NUMBER
2) echo date("Y-m-d_h-i-s").'-YOUR_REFERENCE_NUMBER'; // O/P : 2022-12-20_10-59-39-YOUR_REFERENCE_NUMBER

CodePudding user response：

You can use the primary key id or any unique column, then don't worry about the uniqueness of the data. Use that column/id to generate a unique reference as follows :

update invoices
set reference = concat(date_format(now(), "%Y-%m-00"), unique_id)
where unique_id = 1;

CodePudding user response：

If you're sure you won't have more than 999 quotes/invoices in one month, you could do something like the following...

CREATE TABLE quotes (
  id INT(14) NOT NULL AUTO_INCREMENT,
  YMFqid char(11) NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY (YMFqid));

CREATE TABLE YMnext (
  YM char(7) NOT NULL,
  next INT(3) NOT NULL DEFAULT 0,
  PRIMARY KEY (YM));

Then to get the next values, you'll need some queries like...

SELECT next AS latest FROM YMnext WHERE YM = '2022-12';

INSERT INTO YMnext VALUES ('2022-12', 0);

UPDATE YMnext SET next = next   1 WHERE YM = '2022-12' AND next = latest;

If the SELECT fails, then you insert, otherwise you update AND check that one and only one row was updated. If there's a chance you could have more than 999 quotes in a month, then you can alter the column sizes accordingly.