Unique ID

Yii 2.0 General Discussions
1

How can I generate alphanumeric unique id, 10 character long, AZ(no lower case), 0-9 only.

I have four (4) tables.

Tables

  1. hmo

  2. company

  3. patient

  4. patient_dependent

hmo table

create table

(

hmo_id int auto_increment primary key,

hmo_name varchar(50) not null,

unique_id varchar(10) not null

);

How can I generate 2 digit unique id for the field unique_id.

It should take this format"

A1…A9

.

.

.

.Z1…Z9

When that is exhausted , it takes

AB…AZ

.

.

.

ZA…ZZ

It does the same thing for company table

company table

create table

(

company_id int auto_increment primary key,

hmo_id int not null,

company_name varchar(50) not null,

unique_id varchar(10) not null

);

Now for patient table it needs to generate alphanumeric unique id, 10 character long, AZ(no lower case), 0-9 only.

company table

create table

(

patient_id int auto_increment primary key,

hmo_id int not null,

company_id int not null,

patient_name varchar(50) not null,

unique_id varchar(10) not null

);

Now for the patient table,

it will take the first two digits from the related hmo_id: for example A1,

the next two digits from related company_id : foor example F3,

THE NEXT FIVE DIGITS will be five zero: 00000,

Then the last digit will be based on number of dependants in the dependant table. If it has three dependants,

it will be

000001

000002

000003

So the overall unique id will be something like:

A1F3000000

Please help me out

2

A few ideas for you.

In your HMO and company tables, you want to add two functions:

getLastId()

generateId()

GetLastId should be pretty self explanatory, you need to figure out what the last item is in sequence so you can get the next one. GenerateId will call getLastId and then generate the next ID in sequence. You will probably want to wrap the whole thing up in a transaction and maybe even lock the table to avoid any concurrency conflicts.

As far as patients - this should be pretty straightforward once you figure out HMO and company tables.

By the way - your company table should not know anything about patients, Your patients table should have a foreign key relationship to company table.

3

Please can you give me a sample of what getLastId() and generateId() functions will look like in the MVC

4

Many ways to tackle that.

Start here:

http://stackoverflow.com/questions/2673360/most-efficient-way-to-get-next-letter-in-the-alphabet-using-php

Your range of values should be defined in your model function. You can either calculate the next item, or you could even store an array of all the possible values in a database or file and reference that. I would probably just calculate it.

Since your range is basically A1-ZZ:

A1-A9

B1-B9

…

Z1-Z9

AA-AZ

…

ZA-ZZ

The result for next() on A9 should be B1, result for next() on Z9 should be ZA, and so on…

You need to setup a series of conditions to ‘test’ where you are at in the array and grab the next value.

If first digit != Z, second digit != 9, increment second digit

Else if first digit is not Z and second digit is 9, increment first digit, set second digit = 1

Else if first digit is Z and second digit not 9, increment second digit only

Else if first digit is Z and second digit is 9, set both to AA

Else if first digit is [A-Z] and second digit is not z, increment second digit

Else if first digit == Z and second digit == Z, panic??

You need to extensively test this. Setup a bunch of test cases for the function to test your assumptions… like…

echo generateId($previous=‘AA’);

echo generateId($previous=‘A9’);

echo generateId($previous=‘Z9’);

echo generateId($previous=‘AZ’);

echo generateId($previous=‘ZZ’); //panic!!

5

Thanks a lot am really grateful. You’ve really solved my problem.

Please don’t be offended, can you give me a sample, using model-view-controller in yii2.

It checks if not exist in database, ceates and saves it.

6

No, I can’t really take the time to write the code for you.

7

No problem. You’ve really tried.Thanks. Problem solved