homepage Welcome to WebmasterWorld Guest from 54.198.25.229
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

    
adding an id field to a database after content
surrealillusions

5+ Year Member



 
Msg#: 4177228 posted 11:55 am on Jul 27, 2010 (gmt 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.

Thanks.

 

rocknbil

WebmasterWorld Senior Member rocknbil us a WebmasterWorld Top Contributor of All Time 5+ Year Member



 
Msg#: 4177228 posted 5:19 pm on Jul 27, 2010 (gmt 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.

old:
title|content
$row=mysql_fetch_array($result);
// These are equivalent
$title = $row[0];
$title = $row['title'];

new:
id|title|content
$row=mysql_fetch_array($result);
// These are equivalent
$title = $row[1]; // id is now 0
$title = $row['title'];

surrealillusions

5+ Year Member



 
Msg#: 4177228 posted 7:09 pm on Jul 27, 2010 (gmt 0)

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

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

:)

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved