1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

Making id incremental

Discussion in 'Databases' started by crumblepie, Mar 25, 2013.

  1. #1
    I'm a total newbie when it comes to databases and I've created a database for an arcade that has 0 as the id for all games. How can I make this value incremental so that each game has a different id? Please note that the games have already been imported into the database and dropping the table is not an option.

    I'm using phpmyadmin. step by step instructions would be much appreciated.
    Solved! View solution.
    crumblepie, Mar 25, 2013 IP
  2. #2
    In phpmyadmin, go to the table question, in the structure tad click change on the id field. Click the A_I check box. If you want the id field to start from a number other than one, type in the SQL box:

    ALTER TABLE tablename AUTO_INCREMENT = number you want it to start from;
    Steve202, Mar 25, 2013 IP
  3. crumblepie

    crumblepie Well-Known Member

    Messages:
    2,598
    Likes Received:
    41
    Best Answers:
    0
    Trophy Points:
    110
    #3
    Thank you! I tried it and it says "#1075 - Incorrect table definition; there can be only one auto column and it must be defined as a key"

    I don't have anything defined as the primary key. I tried setting the column "id" as the primary key, but since the table is already full, it says "duplicate entry 0 for key 1". What do I do?
    crumblepie, Mar 25, 2013 IP
  4. Steve202

    Steve202 Member

    Messages:
    59
    Likes Received:
    9
    Best Answers:
    1
    Trophy Points:
    45
    #4
    Define your primary key and it should work. Instead of click change on the id field, just click primary (should have a key next to it).
    Steve202, Mar 25, 2013 IP
  5. crumblepie

    crumblepie Well-Known Member

    Messages:
    2,598
    Likes Received:
    41
    Best Answers:
    0
    Trophy Points:
    110
    #5
    I tried.. that's when it says "duplicate entry 0 for key 1". Note that all the fields in the table "id" have a 0 in them.
    crumblepie, Mar 25, 2013 IP
  6. Steve202

    Steve202 Member

    Messages:
    59
    Likes Received:
    9
    Best Answers:
    1
    Trophy Points:
    45
    #6
    You will need to change each id so its unique. How many entries do you have in the table?
    Steve202, Mar 25, 2013 IP
  7. crumblepie

    crumblepie Well-Known Member

    Messages:
    2,598
    Likes Received:
    41
    Best Answers:
    0
    Trophy Points:
    110
    #7
    Too many :oops:

    If I do it all manually, you think it would work?
    crumblepie, Mar 25, 2013 IP
  8. Steve202

    Steve202 Member

    Messages:
    59
    Likes Received:
    9
    Best Answers:
    1
    Trophy Points:
    45
    #8
    UPDATE tablename SET id = id + 1 WHERE id = 0 should do the trick.
    Steve202, Mar 25, 2013 IP
  9. crumblepie

    crumblepie Well-Known Member

    Messages:
    2,598
    Likes Received:
    41
    Best Answers:
    0
    Trophy Points:
    110
    #9
    That made them all 1 ;)

    I also tried UPDATE tablename SET id = id + 1 and that made them all 2. Will tinker a bit more.
    crumblepie, Mar 25, 2013 IP
  10. master1989

    master1989 Active Member

    Messages:
    80
    Likes Received:
    6
    Best Answers:
    1
    Trophy Points:
    53
    #10
    I can provide you the php code which will makes ids incremental, do you have any column which identify the row as unique( Such as gameid)?
    master1989, Apr 2, 2013 IP