Preventing Duplicate Records

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

 

hey could you post some code ?ta.

 

you can probably use a innodb to be able to use transactions and avoid problems with concurrency

 

The most simple solution is probably to make the serial_no column unique.

ALTER TABLE tbl_employee ADD UNIQUE (serial_no)

PHP:

This will prevent any duplicate values from being inserted.

 

Your database design looks bad.

If you use artificial IDs, at the minimum make the primary|unique/auto increment.

Peace,

 

and how do I prefix EMP00 followed by the auto ID?

Thanx