Question 16828 in Laravel Question posted in
The official Laravel Documentation can be found here.

Create a unique ID based on the value in a row for MySQL – Laravel

I am in the process of creating a web application for a genealogy project. I want each person I add onto the database to have a unique id, based on the first 3 characters of their surname (let’s call it, for the purpose of explaining, ‘surname-string’), concatenated with an autoincrement (which starts from 00001 for each unique ‘surname-string’).

For example – a person’s surname is "Smith". The surname-string will be SMI and due to the fact that they are the first surname-string "SMI" the full reference will be SMI00001. Another person’s surname is Black, making their surname-string BLA, and because they are the first one with the surname-string BLA, their reference will be BLA00001. A third person’s surname is also Smith – they are the second person with the SMI surname-string, so their reference should be SMI00002.

This unique id will be used in the persons URL, to be searched by and create relationships between people in the database.

I have no clue how to approach this logically.

I have not tried anything yet. It goes way over my head!

Tags:

3

Answers


  1. 0

    Hy
    On Create Table you can make the default value of UID like this :

    Create Table ‘Users’
    (ID…
    UID varchar(100) Default (CONCAT(SUBSTRING(Firstname, 1, 3), LPAD(ID, 8, ‘0’)))

    Substring to return the first 3 char of the username
    LPAD to convert the int to 8 digits (ex : 1 => 00000001)

    Login or Signup to reply.
  2. 0

    This method is sketchy, normally you should use auto-increment from database (numeric) or auto generate unique ID, for example md5(time().’randomsalt’.$username).

    But if you have to use XXX00000 format you will need function to:

    • check if there is BLA00001 in database
    • if yes, check BLA00002 etc.
    • if no, create new entry

    This will be very slow after some time plus every name have maximum 99999 chances of existence, after that you need to change BLA to BL1, BL2 etc.

    Login or Signup to reply.
  3. 0

    You can do this using a before insert trigger.

    Consider the following table, where uniqu_identifier is the unique identifier based on the surname:

    CREATE TABLE test(
id bigint NOT NULL AUTO_INCREMENT,
surname varchar(20),
uniqu_identifier varchar(30) ,
  PRIMARY KEY (id)
  )   ;

    You have to use a trigger because MySQL doesn’t allow using the auto_increment column on a generated as column.

    A trigger would be something like:

    CREATE TRIGGER test_BEFORE_INSERT
       BEFORE INSERT ON test 
       FOR EACH ROW
         BEGIN
            IF EXISTS (SELECT 1 FROM test WHERE left(surname,3) = left(new.surname,3)) THEN
             SET new.uniqu_identifier = (select concat(upper(left(new.surname,3)),'0000' ,max(right(uniqu_identifier,1)) +1) from test  );
           ELSE 
              SET new.uniqu_identifier = concat(upper(left(new.surname,3)),'00001');
         END IF ;
    END

    Some insert values

    insert into test (surname) values ('SMITH');
insert into test (surname) values ('SMITH1');
insert into test (surname) values ('JOHN');

select * 
from test;

    Result:

    id  surname     uniqu_identifier
1     SMITH      SMI00001
2     SMITH1     SMI00002
3     JOHN       JOH00001

    https://dbfiddle.uk/Wc58Ne_j

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search