Clean up mySQL fields

[Wm]

I have three phone/fax number fields in a database that people are spamming
with text info (marketing hype B.S.) and I want to figure out how to clean
up the info and then limit it in the future. I think I need to do two
steps -- delete existing data in each field other than numbers and "-"
marks, and then limit the fields to ONLY numbers and "-" marks.

1. Is there a safe way to edit the existing data using phpMyAdmin? (I only
know how to to the preg_replace in PHP, but I know I can't do that in mySQL)

2. Other than the preg_replace, what's the best way to limit input in those
fields to only numbers and hyphens?

Thanx!
Wm

[Jeffrey Silverman]

On Tue, 30 Sep 2003 16:58:16 +0000, Wm wrote:
<snip!>

2. Other than the preg_replace, what's the best way to limit input in
those fields to only numbers and hyphens?

preg_* is a good way. what else do you need?

Another suggestion, though...

Set the column type to a number type -- int(10) or somesuch:

mysql> alter table information modify phone phone int(10);

Then strip all non-number characters out before you submit:

$phone = preg_replace("/\D/", "", $phone);

This will prevent any text data from being able to be submitted. Then
format the phone numbers later if you need to print them. It is always
easier to add formatting later than to have to change formatting once it
is in place.

WARNING: Untested code snippets! Use at own risk.
--
Jeffrey D. Silverman | jeffrey AT jhu DOT edu
Johns Hopkins University | Baltimore, MD
Website | http://www.wse.jhu.edu/newtnotes/

[Geoff Berrow]

I noticed that Message-ID: <pa****************************@jhu.edu> from
Jeffrey Silverman contained the following:

Set the column type to a number type -- int(10) or somesuch:

and for a number like 01782555555 ?

--
Geoff Berrow
It's only Usenet, no one dies.
My opinions, not the committee's, mine.
Simple RFDs http://www.ckdog.co.uk/rfdmaker/

[Andy Hassall]

On Tue, 30 Sep 2003 19:38:00 +0100, Geoff Berrow <[e-mail]>
wrote:

I noticed that Message-ID: <pa****************************@jhu.edu> from
Jeffrey Silverman contained the following:

Set the column type to a number type -- int(10) or somesuch:

and for a number like 01782555555 ?

Exactly - phone numbers are not integers, they're strings. You can't add or
multiply two phone numbers together, and the leading zeroes are significant.

What about numbers like [phone] as well? Use varchar.

--
Andy Hassall (an**@andyh.co.uk) icq(5747695) (http://www.andyh.co.uk)
Space: disk usage analysis tool (http://www.andyhsoftware.co.uk/space)

[Jeffrey Silverman]

On Tue, 30 Sep 2003 20:48:20 +0100, Andy Hassall wrote:
<snip!>

Exactly - phone numbers are not integers, they're strings. You can't add
or
multiply two phone numbers together, and the leading zeroes are
significant.

What about numbers like [phone] as well? Use varchar.

--
Andy Hassall (an**@andyh.co.uk) icq(5747695) (http://www.andyh.co.uk)
Space: disk usage analysis tool (http://www.andyhsoftware.co.uk/space)

Allrighty, I take it back. Don't use int(). But neither of you responded
with a solution for the OP. Any suggestions?

--
Jeffrey D. Silverman | jeffrey AT jhu DOT edu
Johns Hopkins University | Baltimore, MD
Website | http://www.wse.jhu.edu/newtnotes/

[Andy Hassall]

On Tue, 30 Sep 2003 16:58:16 GMT, "Wm" <LA*******@hotmail.com> wrote:

I have three phone/fax number fields in a database that people are spamming
with text info (marketing hype B.S.) and I want to figure out how to clean
up the info and then limit it in the future. I think I need to do two
steps -- delete existing data in each field other than numbers and "-"
marks, and then limit the fields to ONLY numbers and "-" marks.

1. Is there a safe way to edit the existing data using phpMyAdmin? (I only
know how to to the preg_replace in PHP, but I know I can't do that in mySQL)
There doesn't appear to be a 'filter' type function in MySQL, so a one-off
process of reading it in PHP, filtering through preg_replace, and doing an
update sounds like the way to go. Not really any neat way to do it with
phpMyAdmin I can think of.
2. Other than the preg_replace, what's the best way to limit input in those
fields to only numbers and hyphens?

The best way would be to use a database that support CHECK constraints, and
get the database to enforce it.

The next best way is at insert time with PHP; preg_match is a reasonable
choice there.

--
Andy Hassall (an**@andyh.co.uk) icq(5747695) (http://www.andyh.co.uk)
Space: disk usage analysis tool (http://www.andyhsoftware.co.uk/space)

[Geoff Berrow]

I noticed that Message-ID: <pa****************************@jhu.edu> from
Jeffrey Silverman contained the following:

Allrighty, I take it back. Don't use int(). But neither of you responded
with a solution for the OP. Any suggestions?

He wants to clean up the data in the existing table right? AFAIK, you
can only edit a row at a time in phpMyadmin.

But if he knows how to do preg replace, presumably he can pull the data
from the table, preg replace it and then put it back?

--
Geoff Berrow
It's only Usenet, no one dies.
My opinions, not the committee's, mine.
Simple RFDs http://www.ckdog.co.uk/rfdmaker/