I want to generate a unique ID that should start with 35801

Question

There a table in database with name add_sample to store the student sample records. I was storing the inserted information against the auto_increment id but now i want to assign a unique id like 35801 instead of that auto_increment id(e.g 1.) to the first student who entered his/her data. For the second user it should be 35802 and for third it should be 35803 and so on till 99. But when the student number reach to the 100 it should add the 100 to the 3580 to start a new series 36801.

I expect like this:

unique ID  |   Name   |  Email          |   Number_of_Sample | sample_Type
    35801  |   john   | john@gmail.com  |   3                | fiber      

unique ID  |   Name   |  Email          |   Number_of_Sample | sample_Type
    35801  |   john   |  john@gmail.com |   3                | fiber      
    35802  |   sam    |  sam@gmail.com  |   1                | yarn

Looks like this https://stackoverflow.com/questions/1485668/how-to-set-initial-value-and-auto-increment-in-mysql — Moshe Fortgang, Aug 05 '19 at 07:48
Don't do this. Allow the PRIMARY KEY to be a normal auto-incrementing id. Then use whatever tricks you like to store or construct your human-friendly id. — Strawberry, Aug 05 '19 at 07:48
Please edit your title to start FROM 35801. It's a different answer if you want to start from or start with — pr1nc3, Aug 05 '19 at 07:50
@pr1nc3 The "with" is perfectly fine. There's more to a question than just the title. First read it fully, then try to answer it if you understand it. If not then let others answer it. — KIKO Software, Aug 05 '19 at 07:52
@KIKO Software starting `with` refers to prefix value. starting `from` means higher than that. So in that case `with` is not fine at all. I agree that i rushed the answer though and did not read the full question. — pr1nc3, Aug 05 '19 at 07:54
I have to agree with Strawberry, storing this new, and weird, ID _instead_ of a normal auto incrementing id, is a really bad idea. Don't do it. You don't say **why** you want to do this. Would it be an option to have a PHP function that would convert the auto incrementing id into your new unique id? So `1` would give `35801`, `2` would give `35802` and `100` would give `36801`. You can the output your new number while working with the existing auto-id in the database. — KIKO Software, Aug 05 '19 at 08:00

score 0 · Answer 1 · answered Aug 05 '19 at 07:56

0

You could & should continue using the auto-increment ID but change the value to begin at whatever value you want to start at like so:

ALTER TABLE `add_sample` AUTO_INCREMENT=35800;

answered Aug 05 '19 at 07:56

Professor Abronsius

33,063
5
32
46

And what happens after a 100 entries? – KIKO Software Aug 05 '19 at 07:56
yeah - posted that before I fully read the question so it'll not accomplish that part of the goal – Professor Abronsius Aug 05 '19 at 07:57

score 0 · Answer 2 · answered Aug 05 '19 at 09:19

You could translate the auto increment id, as present in the database, to your new unique id by using a PHP function.

<?php

function getSpecialSampleId($autoIncrementId)
{
    $uptoHunderd  = $autoIncrementId % 100;
    $aboveHunderd = $autoIncrementId - $uptoHunderd;
    return 35800 + 10 * $aboveHunderd + $uptoHunderd;
}

echo getSpecialSampleId(1) . "<br>";
echo getSpecialSampleId(2) . "<br>";
echo getSpecialSampleId(3) . "<br>";
echo getSpecialSampleId(99) . "<br>";
echo getSpecialSampleId(100) . "<br>";
echo getSpecialSampleId(101) . "<br>";
echo getSpecialSampleId(250) . "<br>";
echo getSpecialSampleId(1555) . "<br>";

This will output:

You can use this function to output this special sample id to the outside world, while maintaining the normal auto increment id in your database.

I want to generate a unique ID that should start with 35801

2 Answers2