Updating usernames

[csteff24]

I have a database of clubs, and each of them has an advisor

As an easy way to add users to the members database, I was thinking I could use mysql to find the first letter of each advisor name, and then the last name (everything after the space) and combine them to create the username to be saved on the members database. Also, I would like the club ID from the club database to be saved to the "club" field of the members database.

 

Any suggestions??

 

Thank you!

 

[ignace]

This might be what your looking for:

 

INSERT INTO members (username, club) SELECT concat(upper(left(firstname, 1)), '. ', upper(substr(lastname, 0, 1)), substr(lastname, 1)), club_id FROM users

 

One problem though:

upper(substr(lastname, 0, 1)), substr(lastname, 1)

 

This might be able to be simpler but I don't know a function that does something like ucfirst()

[csteff24]

Thank you! The only problem is my advisors field is first and last names, they're not seperated :/

Is there a way to change all of the spaces into underscores? I think I'll just do full name user names

 

ex: advisor: Dan Smith --> username: dan_smith

 

I have no idea if this is the correct, but this is what I have so far...

 

INSERT INTO members2 (username, club) SELECT strtolower(str_replace(" ","_", advisor)), id FROM clubs

[rajivgonsalves]

I don't think that will work, try this, strtolower and str_replace are not mysql functions

 

INSERT INTO members2 (username, club) SELECT lower(replace(advisor, " ", "_")), id FROM clubs

[csteff24]

I guess using PHP in MySQL would be a slight problem... haha shows how little I know

Thank you! It worked!