autoincrementing a field other than ID

[goatlip]

hi, I'm trying to find how I can autoincrement a field in addition to the ID field. Here's the layout.

ID (int, primary, unique, indexed, not null, autoincrement)
PROJ_NUM (int, not null, autoincrement)
PROJ_NAME (varchar, not null)

So i can show something like this:

0023 PROJECT ALPHA
0024 PROJECT BETA
...

Is there a way to do this and to also have the zeros show in the front? thanks.

[jR³]

hi, I'm trying to find how I can autoincrement a field in addition to the ID field. Here's the layout.

ID (int, primary, unique, indexed, not null, autoincrement)
PROJ_NUM (int, not null, autoincrement)
PROJ_NAME (varchar, not null)

So i can show something like this:

0023 PROJECT ALPHA
0024 PROJECT BETA
...

Is there a way to do this and to also have the zeros show in the front? thanks.

You can use this as insert statement ( if Proj_num also is a numeric field, integer)
insert into project(proj_num,proj_name) values((select max(id)+1 from project),'projectname');
But why would you like to increment to fields with the same value?
for the zeroes in front, that's more a manner of representation,
you need to use a string formatting function like
SELECT LPAD(id,4,'0') from project
which ads up to 4 zeroes to the id field..
see also http://dev.mysql.com/doc/mysql/en/string-functions.html
greets,
jr³

[lilleman]

Hi,

I think that you can have only one field set to auto_increment, so you will have to increment the value for the second field manually. As for the other question, use the ZEROFILL attribute.

proj_num INT(4) UNSIGNED ZEROFILL NOT NULL

Yours, Erik.

[goatlip]

Thanks, I think that will help. I want to auto-increment another field that has a different value from the ID. Right now we have projects that are organized by number. The last one was 0023. I want to set up the database so it starts at 0023 and then autoincrements from there, 0024, 0025, etc. whenever we add a new project.

make sense?