CREATE TABLE EMPLOYEE
(
    EMP_ID VARCHAR(6) NOT NULL,
    EMP_FNAME VARCHAR(10) NOT NULL,
    EMP_LNAME VARCHAR(10) NOT NULL,
    PRIMARY KEY(EMP_ID),
);
above is my employee table in the mysql database. The emp_id is like 'EMP001', 'EMP002' and so on.
i would like the addemployee.php page to automatically displayed the next available emp_id. For example,
if the last emp_id is 'EMP54', then the addemployee.php page would display 'EMP55'. Is this possible?
i can do it if the emp_id are number, but i can't do it if it involved characters.

Well, it makes sense that you can't do it with characters. I mean, what comes after EMP009? If you said EMP010, you'd be wrong. Look at an ascii chart. The next character after 9 is the colon (:).

You can use PHP to figure out the next number in the sequence. You can use a string manipulation function (e.g. regular expression match) to find the digits part of the string, convert that string to an integer, increment the integer, and then recreate the string. Don't forget to left-pad your number with zeros! Otherwise, EMP001 would increment to EMP2.

Also, this limits you to 999 employees. (I'm assuming there is no EMP000.) What happens when you hire the 1000th employee? You'll have to modify your database schema since your column limits the string size to 6 characters in the string. All of a sudden, EMP1000 doesn't fit anymore.

Can you see why using strings as primary key fields is not a good way to go?

If you want a numerical index, don't confuse things by storing these numerical indexes as strings. For one thing, it takes more room. An integer typically takes four bytes to store. The string "EMP001" takes 6 if using a fixed-size string. Since you're using a VARCHAR field, the actual length of the string is greater than 6, because you need to store the length of the string as well.



Take care,

Nik
http://www.bigaction.org/