SitePoint Sponsor |
|
User Tag List
Results 1 to 4 of 4
-
Jan 25, 2005, 13:46 #1
- Join Date
- Mar 2004
- Location
- Tulsa, OK
- Posts
- 43
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
autoincrementing a field other than ID
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.
-
Jan 25, 2005, 14:45 #2
- Join Date
- Sep 2003
- Location
- belgium
- Posts
- 29
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Originally Posted by goatlip
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³
-
Jan 25, 2005, 14:47 #3
- Join Date
- Feb 2004
- Location
- Örebro, Sweden
- Posts
- 2,716
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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.
Code:proj_num INT(4) UNSIGNED ZEROFILL NOT NULL
ERIK RIKLUND :: Yes, I've been gone quite a while.
-
Jan 25, 2005, 15:39 #4
- Join Date
- Mar 2004
- Location
- Tulsa, OK
- Posts
- 43
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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?
Bookmarks