Preventing Duplicate Records

[cancer10]

Hi,

I am using PHP/MySQL to build an application.

My application is a multi-user application where in more then 1 people will be entering data.

There is an employee table in my database tbl_employee which has a serial_no column. This column holds the serial nos of all the employees in the format of EMP00XX where XX is the row count of that table + 1. For example: tbl_employee has 50 records, so if someone adds a new employee, the serial # for the 51th row will be something like EMP0051.

Now Problem:
If there are 5 people doing the addition of employee simultaneously then all of those 5 people will be adding the next employee code as EMP0051.

Question:
How can we prevent from inserting duplicate employee nos. under such scenario?

Plz Help :)

[stephan.gerlach]

That's simple

I got 2 solutions.

Solution 1:
Make the employee code UNIQUE in the database. This means the database wont be able to add the same value more than once in the same column.

Solution 2:
Before inserting simply query the database and see if the employee code has been entered if so simply don't insert it.

[NogDog]

Sounds to me like you should be using an auto-increment integer field as the primary key field of that table. This will automatically take care of assigning a unique, sequential number for each insert. If you then need to display it in the "EMPnnnn" format, that can be done either in the select query or in your PHP output code via the applicable formatting functions, e.g.:

SELECT CONCAT('EMP', LPAD(serial_no, 4, '0')) AS emp_no . . .

...or...

printf("Employee Nbr.: EMP%04d", $row['serial_no']);

[cancer10]

Is it possible to prefix 00 in my auto id?

[NogDog]

In MySQL you can specify the display length of an integer field (the value in parentheses after the type when you define the column) and add the ZEROFILL argument which will left pad it with zeros (see http://dev.mysql.com/doc/refman/5.0/en/numeric-types.html), but I generally prefer to directly control any such padding with with PHP's sprintf()/printf() functions.

[SyCo]

do you always want to prefix with 2 zeros or when you get to id 100 do you want to drop to one zero so you simply have 4 digits?