Welcome to WebmasterWorld Guest from

Forum Moderators: open

Message Too Old, No Replies

adding an id field to a database after content

11:55 am on Jul 27, 2010 (gmt 0)

Preferred Member

10+ Year Member

joined:Dec 10, 2007
votes: 0

Hi all,

I have a database with content already in, but no unique primary id thingy.

How do I add one in phpmyadmin and make sure each table row is asigned a unique id number from 1 right through to the end, which might end be hundreds.

5:19 pm on July 27, 2010 (gmt 0)

Senior Member

WebmasterWorld Senior Member rocknbil is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:Nov 28, 2004
votes: 0

You'd add an auto_increment field:

alter table mytable add id int(11) primary key auto_increment;

Then you'd really want it to be the first column on the left, not that it has to be, it's just a bit of a standard. Simply put, you create a temporary table, select the fields in the order you want them, insert, drop old table, rename temp table. From the documentation,

1. Create a new table with the columns in the new order.
2. Execute this statement:
mysql> INSERT INTO new_table SELECT columns-in-new-order FROM old_table;
3. Drop or rename old_table.
4.Rename the new table to the original name:
mysql> ALTER TABLE new_table RENAME old_table;

If you select data by column-name, everything will function as it did, if you select anything by array index you will have to shift your array over by one.

// These are equivalent
$title = $row[0];
$title = $row['title'];

// These are equivalent
$title = $row[1]; // id is now 0
$title = $row['title'];
7:09 pm on July 27, 2010 (gmt 0)

Preferred Member

10+ Year Member

joined:Dec 10, 2007
votes: 0

Ah, auto_increment field - thats the term i was after.

Thanks for the help. Should be able to solve it now.


Join The Conversation

Moderators and Top Contributors

Hot Threads This Week

Featured Threads

Free SEO Tools

Hire Expert Members