format phone# ?

[fuzzy1]

Hey All,
Phone number format in our mysql db is (big surprise) constantly inconsistent.
We get anything from xxxxxxxxxx, (xxx) xxx-xxxx, (xxx)-xxx-xxxx, xxx.xxx.xxxx,
xxx.xxx.xxx .... you get the idea.

While I have found numerous examples of code to format phone numbers, nothing
I have encountered quite addresses the seemingly limitless possibilities.

It seems the thing to do would be to strip all non-numeric characters from all
phone#'s before formating as (xxx)xxx-xxxx, but I'm not sure where to start with that.

$query= mysql_query("SELECT phone_mobile FROM contacts WHERE phone_mobile !='$phone_mobile_formated'");

foreach($phone_mobile){
$query= mysql_query("UPDATE phone_mobile = '$formated_phone_mobile'");
}
Any input would be greatly appreciated!

[paulnaj]

Hi,

You can use preg_replace() to strip out the non-numerical chars ...

$number  = preg_replace('/[^0-9]/', '', $number);

I'd probably just store the plain number in the db ... wouldn't be tied down to any one 'display'.

Also, in the first query, you would need to retrieve the primary key in the SQL as well as the number ... you need it in the second query to identify which record to update.

I've just guessed that the primary key of the contacts table is called "contact_id", so you might have to change that. And I can't debug it ... so there could be typo's.

<?php 
$select_sql = "
SELECT `contact_id`, `phone_mobile` 
FROM `contacts`
WHERE `phone_mobile` REGEXP '[^0-9]'
";
$result = mysql_query($select_sql);
while($assoc = mysql_fetch_assoc($result){
	$orig_number = $assoc['phone_mobile'];
	$form_number = preg_replace('/[^0-9]/', '', $orig_number);
	$update_sql = "
UPDATE `contacts` 
SET `phone_mobile` = '".$form_number."' 
WHERE `contact_id` = '".$assoc['contact_id']."'
";
	//Make sure it's working first, eh!!
	//mysql_query($update_sql);
	echo '<pre>'.$update_sql.'</pre>';
}
?>

Paul.