generate autoid

[abhaya9999]

Hi all
i am new to mysql and php.
i want an urgent help.
i am developing an application for an training centre.
i want to maintain a student table. To which i only enter his/her name and address, my requirement is it will generate students rollno. automatically.
according to the following condition
1.the 1st noumber of the rollno should be state code.
2.the second number of the rollno should be center code.
and then an auto increament number.
i have state code table and center code table.

state code;-
state code | state name
01--------|-----aaa
02--------|------bbb
03--------|-------ccc

centre code:-
centre code | center name
01-------|------xxx
02-------|-------yyy
03-------|-------zzz
fro example
if the state name is "aaa" and center nane is 'yyy" then the roll number
will be like :-0102-0001. (01 state code,02 center code, 0001-an auto increament no.)
any help is appreiciated.

thanks
abhaya

[Atli]

Hi abhaya.

When you say automatically, I assume you mean that you will be providing the state and centre codes, but the additional integer be created automatically?

If so, you would probably be best of simply creating a noraml Integer Auto_Increment Primary Key column and two Foreign Key columns for the state and centre ID's.

Then you could simply CONCAT them together to get your rollno.

And keep in mind that if you create a integer column that specifies the length of the number and Zerofill, MySQL will automatically pad the number with zeros to make it exactly that many characters long when selected.

Like for example, these three commands:

CREATE TABLE `myTable` (`Integer` Int(4) Zerofill);
INSERT INTO `myTable` VALUES (1), (2);
SELECT * FROM `myTable`;
 

would output something like:

+---------+
| Integer |
+---------+
|    0001 |
|    0002 |
+---------+
 

That might help you format your output.