Re: MySql and lost formatting

[Jonathan Shaltz]

There are two easy ways to handle this.
The best way is to keep storing phone numbers as integers (this keeps the files small and index-able), and use a function to format them. Note that to store ten digits, you will need to use DECIMAL(10, 0) rather than INT.
You can set the format of an Access control to "000-000-0000," or similar. In an Access query, you can use the function "format([FieldName], "000-000-0000")." If you're using this database online, I wrote this PHP function to check that a phone number is valid and format it with dashes:

function FormatPhone($Input)
{
if ($Input == "") return "";
if (is_numeric($Input) == FALSE) return $Input;
if ($Input == 0) return "";
if ($Input > 9999999999) return "";

$TextForm = strval($Input);

if (strlen($TextForm) <= 7)
{
while (strlen($TextForm) < 7) $TextForm = "0" . $TextForm;
return substr($TextForm, 0, 3) . "-" . substr($TextForm, 3);
}

while (strlen($TextForm) < 10) $TextForm = "0" . $TextForm;
return substr($TextForm, 0, 3) . "-" . substr($TextForm, 3, 3) . "-" . substr($TextForm, 6);
}

The second way is to change your PhoneNumber fields to CHAR(14). The downside of this is that different people store phone numbers differently, and your data can be messy. Because of this, you will not be able to easily search or sort the data. Finally, it will take up more space.

Hope this helps!