-----------------------------------

nickison99

Mon Jul 12, 2004 11:05 pm


PHP/MySql assistance needed

-----------------------------------

Hi,
I have a field in my database which lists phone numbers. I was wondering if somebody could help me out here; I need some code to remove all brackets "(" , ")" and dashes "-" from the phone number (Phone_Number ) field in the db. Any help would be greatly appreciated. Thanks.


-----------------------------------

Amailer

Mon Jul 12, 2004 11:51 pm



-----------------------------------


$phone_number = $row['phone_number']; // info from the database..

$phone_number = str_replace("(", "", $phone_number);
$phone_number = str_replace(")", "", $phone_number);
$phone_number = str_replace("-", "", $phone_number);

echo $phone_number;

that' should do it


-----------------------------------

wtd

Tue Jul 13, 2004 1:41 am



-----------------------------------

Remove anything that isn't a number or a space.  The collapse any spaces to a single space.

$phone_number = // however you get it from the DB
$phone_number = preg_replace("/[^\d\s]/", "", $phone_number);
$phone_number = preg_replace("/\s+/", " ", $phone_number);


-----------------------------------

nickison99

Tue Jul 13, 2004 8:00 am



-----------------------------------

Hi, THanks for the code. Is there any way that I can do the above to the database (MySQL) so that if I were to look up a phone number in the database it would all be numbers without any formating? Thanks.


-----------------------------------

wtd

Tue Jul 13, 2004 5:07 pm



-----------------------------------

Hi, THanks for the code. Is there any way that I can do the above to the database (MySQL) so that if I were to look up a phone number in the database it would all be numbers without any formating? Thanks.

Any other database... fairly easily.  MySQL is rather limited.  Making changes to the contents of the field from PHP is your best bet.


-----------------------------------

nickison99

Tue Jul 13, 2004 11:06 pm



-----------------------------------

Thanks.


-----------------------------------

octopi

Tue Jul 13, 2004 11:15 pm



-----------------------------------

mysql_query("update TABLENAME set Phone_number = REPLACE(Phone_number,'(','')");
mysql_query("update TABLENAME set Phone_number = REPLACE(Phone_number,')','')");
mysql_query("update TABLENAME set Phone_number = REPLACE(Phone_number,'-','')");

that should work, change TABLENAME to the name of the table.


-----------------------------------

nickison99

Wed Jul 14, 2004 8:58 am



-----------------------------------

GREAT!!! It worked like a charm.
Nick


-----------------------------------

nickison99

Wed Jul 14, 2004 10:44 am



-----------------------------------

Actualy I have another question...
I have a field in the db which stores the postal code (lets call it PostalCode) but I want to have two fields for it. One for the first 3 letters/numbers (lets call it Field1) and the second field (lets call it Field2) would be for the last 3 letters/numbers. The first 3 and the last 3 letters/numbers are separated by a space. Any assistance would be greatly appreciated.


-----------------------------------

wtd

Wed Jul 14, 2004 4:28 pm



-----------------------------------

You'd use something like:

SELECT CONCAT_WS(" ", my_table.postal_code_1, my_table.postal_code_2) AS postal_code FROM my_table


-----------------------------------

octopi

Wed Jul 14, 2004 4:54 pm



-----------------------------------

mysql_query("update TABLENAME set Field1 = SUBSTRING_INDEX(PostalCode,' ',1), Field2=SUBSTRING_INDEX(PostalCode,' ',-1)");

This will take the value of 'PostalCode', and split it into two parts, and store those in the db.


-----------------------------------

wtd

Wed Jul 14, 2004 6:18 pm



-----------------------------------

mysql_query("update TABLENAME set Field1 = SUBSTRING_INDEX(PostalCode,' ',1), Field2=SUBSTRING_INDEX(PostalCode,' ',-1)");

This will take the value of 'PostalCode', and split it into two parts, and store those in the db.

But of course first you'll need to create those fields.  :)

ALTER TABLE my_table ADD COLUMN postal_code_1 VARCHAR(3);
ALTER TABLE my_table ADD COLUMN postal_code_2 VARCHAR(3);


-----------------------------------

nickison99

Wed Jul 14, 2004 8:26 pm



-----------------------------------

Once again - thanks a lot!
Nick


-----------------------------------

nickison99

Thu Jul 15, 2004 9:12 am



-----------------------------------

This is my last question in this topic... I Promise :D 
I am working with my db and I am trying to create a form that updates a persons information. Can someone tell me why the form doesn't update the persons record? I have been working on this for a while and I cannot figure it out.