Create unique ID based on value in row in MySQL

Question

I'm creating a web application for a genealogy project. I want each person added to the database to have a unique ID based on the first 3 characters of their last name (let's call it the "last name string" for the sake of explanation), with an auto-increment (from 00001 represents each unique "surname string").

For example - A person's last name is "Smith". The last name string will be SMI, and since they are the first last name string "SMI", the full reference will be SMI00001. The other person's last name is Black, so their last name string is BLA, and because they are the first person with the last name string BLA, their reference will be BLA00001. The third person's last name is also Smith - they are the second person with the SMI last name string, so their reference number should be SMI00002.

This unique ID will be used in the people URL to search for people in the database and create relationships between people.

I don't know how to solve this problem logically.

I haven't tried anything yet. This is beyond my imagination!

Answer 1

This method is rough, usually you should use database auto-increment (number) or automatically generate unique ID, such as md5(time().'randomsalt'.$username).

But if you must use the XXX00000 format, you will need the following functions:

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

This will be very slow after a while, and each name will have a maximum of 99999 chances to exist, after which you will need to change the BLA to BL1, BL2, etc.