SitePoint Sponsor

User Tag List

Results 1 to 4 of 4

Hybrid View

  1. #1
    SitePoint Enthusiast
    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.

  2. #2
    SitePoint Enthusiast jR≥'s Avatar
    Join Date
    Sep 2003
    Location
    belgium
    Posts
    29
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by 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.
    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≥

  3. #3
    Tranceoholic lilleman's Avatar
    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
    Yours, Erik.
    ERIK RIKLUND :: Yes, I've been gone quite a while.

  4. #4
    SitePoint Enthusiast
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •