Hi,

I have a problem and seen as though I'm new to MYSQL I thought I better ask someone how to get around it.

I have a table of employee numbers, the problem is that these numbers also contain characters for instance EMP001. How can I auto increment it so it goes up for example .....

EMP001
EMP002

then

EMP003

etc etc ..

Anyone know, its probably easy to do just I couldn't figure it out.

Thanks

Jona

unless you you are going to have to store other prefixes to rows, such as CUSxxx,SUPxxx then
-remove the EMP prefix, if all are EMP then its not needed
-set an autoincrement for the prime key

if you do need to have EMP001..999, CUS001..999 etc then consider a redesign
use a different primamry key and create your person reference using a stored procedure int he db (MySQL 5 on) or a function within the user interface

the process is relatively simple
-secure am exclusive lock on the table that contains the current number
-store that number
-increment a copy of the number
-write it back to the table that contains the current number
-release the lock
-use the stored number

how you store the number is up to you. The safest is probably as a separate row in a table somewhere, and store it as the 'next available' number, ie already incremented ready for the next requirement. Other may suggest some form of sql querry on the current data to find its max value. No doubt there are other ways. Of these two the first is by far the better solution, if for no other reason its more scalable and has less chance of affecting other processess or users using the data.

HTH

unless you you are going to have to store other prefixes to rows, such as CUSxxx,SUPxxx then
-remove the EMP prefix, if all are EMP then its not needed
-set an autoincrement for the prime key

if you do need to have EMP001..999, CUS001..999 etc then consider a redesign
use a different primamry key and create your person reference using a stored procedure int he db (MySQL 5 on) or a function within the user interface

the process is relatively simple
-secure am exclusive lock on the table that contains the current number
-store that number
-increment a copy of the number
-write it back to the table that contains the current number
-release the lock
-use the stored number

how you store the number is up to you. The safest is probably as a separate row in a table somewhere, and store it as the 'next available' number, ie already incremented ready for the next requirement. Other may suggest some form of sql querry on the current data to find its max value. No doubt there are other ways. Of these two the first is by far the better solution, if for no other reason its more scalable and has less chance of affecting other processess or users using the data.

HTH