[SOLVED] Removing Spaces in a Column?

[Solarpitch]

Hey Guys,

 

I have a table with a username column. When people registered to the site I allowed them to enter a username using spaces, but now I want to make their username into a subdomain so they can go myusername.example.com

 

Is there any way I can run a command so remove any Spaces from the username? Eg:

 

Currently:

Solapitch Boy 41 -> solarpitchboy41

Usr Delta - > usrdelta

.. and so on

[cags]

One method would be this...

 

$result = mysql_query("SELECT * FROM table");
$subdomains = array();
while($row = mysql_fetch_assoc($result)) {
   $subdomains[] = array('id'=>$row['id'], 'subdomain'=>str_replace(" ", "", $row['username']);
}

foreach($subdomains as $k=>$v) {
   mysql_query("UPDATE table SET subdomain='". $v['subdomain'] . "' WHERE id='" . $v['id'] . "'");
}

 

...but it does seem a bit on the long winded side. You could probably combine the loops if you wanted. MySQL may have a method for removing spaces to allow it in a single query, but if it doesn't I don't know it. Having said that, you will only need to run this function once, so I don't suppose it matters a great deal.

[PFMaBiSmAd]

UPDATE your_table SET your_column = REPLACE(your_column,' ','')

[cags]

Ahh, thought it would be possbile.

[Solarpitch]

Thanks Guys,

 

PFMaBiSmAd, that command was perfect.