0

I have a table called users, with a field full_name.

most of the entries have names such as : JOhn perry, lee adams, MONICA Brown. Is there a function in phpmyadmin to standardize these names into a proper format like : John Perry, Lee Adams, Monica Brown? It's for an experimental forum. :)

kaninabu
  • 94
  • 2
  • 12
  • phpMyAdmin is a GUI that helps you perform MySQL queries. If you want to change the underlying data, going against what might be the user's intention, you can issue UPDATE queries. But if you want to keep the underlying data as-is, you can manipulate it when it comes out, via whatever server-side language you use. Which do you prefer? What have you tried already? – jcmeloni Feb 02 '12 at 18:02
  • Yea i did modify it graphically using CSS's "Capitalize", but i don't really like it since i'll have to add this text-transform into all my existing classes that uses the full name. (too many of them) So i'm trying to modify it once and for all:) – kaninabu Feb 02 '12 at 18:06
  • it's on the same column, I found the same question here: http://stackoverflow.com/questions/3278207/mysql-capitalize-first-letter-of-each-word-in-existing-table but they were nt using phpmyadmin, and the only way I knw how to use mysql is tru myadmin (I'm only in my first year). – kaninabu Feb 02 '12 at 18:11

2 Answers2

1

You can always use PHP to do

ucfirst(strtolower($row["full_name"]))

then update the nicely formatted string back to database

Or just simply use the above in your forum script

ajreal
  • 46,720
  • 11
  • 89
  • 119
  • wow thanks alot, that's really helpful, I can even use this so that future users get their names properly formatted right when they sign up :) – kaninabu Feb 02 '12 at 18:08
0

First off, I recommend separating the first and last name into separate columns. (first_name and last_name)

If that's the case, then the solution is much easier. You can update the field in mysql using this sql query:

UPDATE users SET full_name = CONCAT(UCASE(SUBSTRING(full_name, 1, 1)),LCASE(SUBSTRING(full_name, 2)));

However, this will make the result be:

MONICA Brown ---> Monica brown DOnald Doe ---> Donald doe

In order to capitalize the first letter of each word, you will need to create a mysql function.

Here's a site with an function you can probably use: http://www.thingy-ma-jig.co.uk/blog/30-09-2010/mysql-how-upper-case-words

Steph Rose
  • 2,126
  • 3
  • 23
  • 35
  • thank you so much, but in order to split them up into separate column I'm gonna need another function, isn't there a way to do it straight from one column? – kaninabu Feb 02 '12 at 18:20