Create a unique ID composed of the current year number and an auto incremented value

Question

i want to create a Student ID number compose of 4 digit year and a 5 digit auto increment field. example: 2013-00001 and when the year ends, the 5 digit number will restart to zero (0) example: 2014-00001.

Thanks, Metta

With MyISAM engine, you can do that easily. With InnoDB, no. — ypercubeᵀᴹ, Mar 03 '13 at 10:51
Create a subtable for the year with the ID column being auto increment (Year2013, then ID is Auto) (Year2014, then ID is Auto) will give you the numbers and isolate them, but not sure this is what you really want to do. — Steven Scott, Mar 03 '13 at 20:30

score 0 · Answer 1 · answered Mar 03 '13 at 11:04

0

create Table width two columns: Year,LatestNumber 

function generateStudentId(){
    $year = date("Y");
    $latestNumber = 0;
    $res = mysql_query("select * from myTable where Year=$year");
    $result = mysql_fetch_array($res);
    if($result)
        $latestNumber = $result['LatestNumber'];
    $latestNumber++;
    if($result){
        mysql_query("update myTable set LatestNumber=$latestNumber where Year=$year");
    }
    else
        mysql_query("insert into myTable values($year,$latestNumber)");

    $fiveDigitNumber = return str_pad((int) $latestNumber,5,"0",STR_PAD_LEFT);
    $studentId = $year.$fiveDigitNumber;
    return $studentId;
}

answered Mar 03 '13 at 11:04

MIIB

1,849
10
21

is this the easiest and simplest way? – user2089597 Mar 03 '13 at 11:33
That's the way that came to my mind. There could be better solutions. I cant think of anything right now – MIIB Mar 03 '13 at 11:45
Wouldn't `Max('LatestNumber')` achieve the same as the extra table? – BrainPicker Mar 03 '13 at 11:56
We have no more than 1 row for each year. As you can see the LatestNumber will be updated for each year, so Max('LatestNumber') has no use here – MIIB Mar 03 '13 at 11:58
I meant dropping the new table that keeps updating and using Max on `myTable`. Maybe replace the - but the ids should all be bigger as each year > last year + appended increment. – BrainPicker Mar 03 '13 at 12:00

score 0 · Answer 2 · answered Mar 03 '13 at 20:36

You can use the StudentId table itself where you store all the data.

select LatestNumber from StudentIds where Year=2013 ORDER BY LatestNumber DESC LIMIT 1;

Keep the number separate from the year, so you can reset it on a new year by adding a dummy Year=2014, LatestNumber=0

$LatestNumber = $result['LatestNumber'];
$LatestNumber++;

insert into StudentIds (Year, LatestNumber, ...) values (2013, $LastestNumber, ...)

Add error handling in case of multiple queries etc...

Create a unique ID composed of the current year number and an auto incremented value

2 Answers2

Linked