UserID

[Solar]

Hello and Thankyou for your time;

 

I would love to change;

userid varchar(32) utf8_swedish_ci  Yes NULL

 

to;

 

id int(5)

 

But my database keeps messing up and I am not able to log into my website when this is changed.

 

I have close to 200 registered members.

 

Is there a way to change userid to id int(5) and have all 200 members automatically counted inside the table?

Like for example;

 

ID: 1 USERNAME: Steven

ID: 2 USERNAME: Solar

etc.

 

If this cannot work, is it possible to add table id int(5) and have it auto counted like above?

 

Thanks!

[andrewgauger]

ALL MySQL:

 

CREATE TABLE usertest SELECT * FROM {This is where you type in the name of the table that holds the original data}

 

This will create a new table called usertest holding all the data from your original table (needless to say you need to but the first table's name in the query

 

ALTER TABLE usertest ADD id int(5)
UPDATE TABLE usertest SET (id=CONVERT(userid, UNSIGNED))

 

Do not bother dropping userid because you have plenty of room for growth.  This also assumes that userid in your table is a number, if it is text, you are going to need to create your own id sequence.

 

[Solar]

Thanks for your help.

 

The two first mysql queries worked but this last one isn't.

I can't see why it doesn't :/

 

UPDATE TABLE usertest SET (id=CONVERT(userid, UNSIGNED))

 

SQL query: 

UPDATE TABLE usertest SET( id = CONVERT( userid, UNSIGNED ) ) 

MySQL said:  

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'TABLE usertest SET (id=CONVERT(userid, UNSIGNED))' at line 1 

[andrewgauger]

Sorry, you apparently don't have to cast/convert strings to numbers:

 

UPDATE usertest SET id=userid

 

should work

Also my bad don't use the "TABLE" in the query

[Solar]

I'm sorry maybe I didn't clarify myself as well enough.

 

All 200 of my members have userid's like;

For example;

Username: Steven Userid: fa98c641369c6d9798314c97df52f1be

Username: Emily    Userid: 6a5cf112a6e83fac907d0b45a53bba91

Username: tyman  Userid: f874f5ca582697f25465e521e79ea903

 

So what I would like to have is still Userid, But have a new table; id int(5)

Username: Steven Id: 1 Userid: fa98c641369c6d9798314c97df52f1be

Username: Emily    Id: 2 Userid: 6a5cf112a6e83fac907d0b45a53bba91

Username: tyman  Id: 3 Userid: f874f5ca582697f25465e521e79ea903

 

 

What I am trying to ask is, is there a query that will allow itself to go through every username and give it an ID Number for example like above. Or does it need to be done manually?

[ignace]

No you can automate it. You can do this by:

 

CREATE TABLE myNewTableName (id int(5), userid varchar(..), username varchar(..), PRIMARY KEY (id));
INSERT INTO myNewTableName (userid, username) SELECT userid, username FROM yourOldTableName

[Solar]

Thanks for all the replies.

 

CREATE TABLE users2 (id int(5), userid varchar(32), username varchar(30), PRIMARY KEY (id));INSERT INTO users2 (userid, username) SELECT userid, username FROM users

 

Works, but it seems to me like it doesn't want to. It only does the first row and then finds the error:

 

#1062 - Duplicate entry '0' for key 1 

 

So does this mean that once it hits the second row in table users, it wants to put the ID for username Emily as 0? When I username Steven is the first row allready at 0?

 

Thanks for the troubles.

[fenway]

You're missing AUTO_INCREMENT.

[Solar]

Thankyou for all your help! It's been really appreciated, as silly as my question was, I hope more people have found there solution by looking at mine. Thanks abunch.