Concatenate 2 fields into 1

Databases
1

I have taken over a website from another development company and I want to tidy up the database a bit and would like to do the following.

The current “users” table has the following 2 fields:

firstName
lastName

I would like to create a new field called

name

And to populate this field by concatenating all the first names with the lastnames.

I can’t do it manually as there are over 16,000 users in the database.

Is this something I can do in phpMyAdmin and if so how?

Thanks

Paul

2

Thanks guelphdad

Good point, though I only plan to use the name to address the users in their welcome emails and possibly when they login to the site. I think if the site was more complex, I would definitely store both their first name and last name in separate fields.

Many thanks

Paul

3

It will be harder to search for names with one field. if you only have a last name you’ll have to search for all those records using

WHERE name LIKE '%partofthename'

and thus you won’t be able to take advantage of an index on the column, slowing your search time as your table grows.

4

No idea why you’d want to do that, but take a look at this:
http://dev.mysql.com/doc/refman/5.1/en/string-functions.html#function_concat-ws

5

Thanks guido

The reason I want to do it is because the site previously asked for first name and last name as separate form fields on the registration form.

I want to now just ask for their name and have one field. For backwards compatibility I want to update the database so all previous users data is in the same format.

I have found a solution using the following:



UPDATE users SET name = CONCAT(first_name, ' ', last_name)

Didn’t think it would be that simple.

Thanks

Paul