• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 508
  • Last Modified:

storing a phone number (10 digit) in mysql for use in msaccess application

I am developing a toll free number management utility for a customer. This customer specifically wants the tfn(toll free number) field to be numeric. They want the field to be numeric in order to sort the field in datasheet view within an msaccess app.

According to http://www.w3schools.com/sql/sql_datatypes.asp mysql stores unsigned int 0 to 4294967295 these tfn's are 8881231234 and larger.

I cant use the bigint datatype because access doesn't handle bigint. I have tried changing the fields to bigint but access returns #DELETED for all records.

Please help with how to solve storing this tfn number in a mysql table but still able to be sorted numerically within an access db in datasheet view?
0
Dalexan
Asked:
Dalexan
  • 5
  • 3
  • 3
  • +1
1 Solution
 
Andrew DerseIT ManagerCommented:
Have you tried storing is as a varchar...maybe that will help?
0
 
DalexanAuthor Commented:
Storing as a varchar doesnt sort numerically in msaccess datasheet view????
0
 
Andrew DerseIT ManagerCommented:
I'm not sure...just offering a suggestion.  I generally store all my phone numbers as a varchar in my MySQL databases and then sort them...I've not had a need to integrate it with Access...so I'm not sure if it will actually work for you...just thought I would suggest it.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Shaun KlineLead Software EngineerCommented:
As long as you are storing all ten digits, MS Access will sort numbers stored in a string correctly. I.e. all numbers starting with 0 will be first, followed by 1 and so on. The only issue is when you have a string field with numbers of different length, such as 20 vs. 10987. In this case, the 10987 would appear before 20.
0
 
Andrew DerseIT ManagerCommented:
So basically, if he stores it as a varchar and keeps them all the same length it will sort it correctly...nice
0
 
Ray PaseurCommented:
SQL will treat a string as a string, and your number will be safe.  However PHP may mung data that has leading zeros.  You can prepend the letter "A" to the string if you have to carry numeric values with leading zeros through PHP.  Just chop it off whenever you need to use the numbers.

IIRC there are no US area codes that start with 0 so you should be OK.  Here is how I sanitize phone numbers.  HTH, ~Ray
<?php // RAY_phone_numbers.php - USING USA PHONE NUMBERS
error_reporting(E_ALL);

// A FUNCTION TO VALIDATE A PHONE NUMBER AND RETURN A NORMALIZED STRING
// MAN PAGE: http://discuss.fogcreek.com/joelonsoftware3/default.asp?cmd=show&ixPost=102667&ixReplies=15
function strtophone($phone, $format=FALSE, $letters=FALSE, $dlm='-')
{
    if ($letters)
    {
        // TRANSLATE INPUT LIKE 1-800-BIG-DOGS
        $phone = strtoupper($phone);
        if (preg_match('/[A-Z]/', $phone))
        {
            $phone = str_replace('A', '2', $phone);
            $phone = str_replace('B', '2', $phone);
            $phone = str_replace('C', '2', $phone);

            $phone = str_replace('D', '3', $phone);
            $phone = str_replace('E', '3', $phone);
            $phone = str_replace('F', '3', $phone);

            $phone = str_replace('G', '4', $phone);
            $phone = str_replace('H', '4', $phone);
            $phone = str_replace('I', '4', $phone);

            $phone = str_replace('J', '5', $phone);
            $phone = str_replace('K', '5', $phone);
            $phone = str_replace('L', '5', $phone);

            $phone = str_replace('M', '6', $phone);
            $phone = str_replace('N', '6', $phone);
            $phone = str_replace('O', '6', $phone);

            $phone = str_replace('P', '7', $phone);
            $phone = str_replace('Q', '7', $phone);
            $phone = str_replace('R', '7', $phone);
            $phone = str_replace('S', '7', $phone);

            $phone = str_replace('T', '8', $phone);
            $phone = str_replace('U', '8', $phone);
            $phone = str_replace('V', '8', $phone);

            $phone = str_replace('W', '9', $phone);
            $phone = str_replace('X', '9', $phone);
            $phone = str_replace('Y', '9', $phone);
            $phone = str_replace('Z', '9', $phone);
        }
    }

    // DISCARD NON-NUMERIC CHARACTERS
    $phone = preg_replace('/[^0-9]/', NULL, $phone);

    // DISCARD A LEADING '1' FROM NUMBERS ENTERED LIKE 1-800-555-1212
    if (substr($phone,0,1) == '1') $phone = substr($phone,1);

    // IF LESS THAN TEN DIGITS, IT IS INVALID
    if (strlen($phone) < 10) return FALSE;

    // IF IT STARTS WITH '0' OR '1' IT IS INVALID, SECOND DIGIT CANNOT BE '9' (YET)
    if (substr($phone,0,1) == '0') return FALSE;
    if (substr($phone,0,1) == '1') return FALSE;
    if (substr($phone,1,1) == '9') return FALSE;

    // ISOLATE THE COMPONENTS OF THE PHONE NUMBER
    $ac = substr($phone,0,3); // AREA
    $ex = substr($phone,3,3); // EXCHANGE
    $nm = substr($phone,6,4); // NUMBER
    $xt = substr($phone,10);  // EXTENSION

    // ADD OTHER TESTS HERE AS MAY BE NEEDED - THESE ARE FOR LOCAL APPS
    if ($ac == '900') return FALSE;
    if ($ac == '976') return FALSE;
    if ($ex == '555') return FALSE;

    // IF NOT FORMATTED
    if (!$format) return $phone;

    // STANDARDIZE THE PRINTABLE FORMAT OF THE PHONE NUMBER LIKE 212-555-1212-1234
    $formatted_phone = $ac . $dlm . $ex . $dlm . $nm;
    if ($xt != '') $formatted_phone .= $dlm . $xt;
    return $formatted_phone;
}



// DEMONSTRATION OF THE FUNCTION IN ACTION.
if (!empty($_GET["phone"]))
{

    // VALIDATE PHONE USING FUNCTION ABOVE
    if (!$phone = strtophone($_GET["phone"], TRUE))
    {
        // FUNCTION RETURNS FALSE IF PHONE NUMBER IS UNUSABLE
        echo "BOGUS: {$_GET["phone"]} ";
    }
    else
    {
        // SHOW THE FORMATTED PHONE
        echo "VALID: {$_GET["phone"]} == $phone";
    }
}


// END PHP, PUT UP THE FORM TO TEST PHONE NUMBERS
?>
<form>
ENTER A PHONE NUMBER:
<input name="phone" /><br/>
<input type="submit" />
</form>
TRY SOME OF THESE (COPY AND PASTE):
<br/>1-800-5551212
<br/>202-537-7560
<br/>202 537 7560
<br/>1-202-537-7560
<br/>(202) 537-7560
<br/>1.202.537.7560
<br/>123456789
<br/>703-356-5300 x2048
<br/>(212) 555-1212
<br/>1 + (212) 555-1212
<br/>1 (292) 226-7000

Open in new window

0
 
Ray PaseurCommented:
Regarding numbers of a different length like 20 and 107, I think you can ORDER BY "CAST AS INT" or something like that.  I do not recall the exact syntax, but I have dealt with this before, and using integer ordering enabled me to get the correct order and also get the string representation of the number.
0
 
DalexanAuthor Commented:
So I made the field varchar(12) in the mysql table, I relinked the table within access and sorted the field ascending. Here's what it shows in ascending order:

0000000022
0000000023
0000000024
0000000025
0000000026
0000011733
0000011734
0000011735
0000011788
0000011789
0000011790
0000123456
11791
11792
8000000001
8000000002
8000000003
8000000003
8000000004
8002013671
8002019762
8002033384
8002039335
0
 
DalexanAuthor Commented:
0000123456 is a bigger number than 11791
0
 
DalexanAuthor Commented:
I prepended the fields with 0 and my problem is solved
0
 
Ray PaseurCommented:
sorted the field ascending

How, exactly?  Can you please post the CREATE TABLE script and the query you used to retrieve these rows?  Thanks, ~Ray
0
 
DalexanAuthor Commented:
I manually changed the values adding zero's to the left of the number until all columns were 10 digits zero left padded.

I'm currently looking for a way to change the field in mysql so that the field defaults to zero padding. Do you know of a way to pad zero's in a varchar field by default in mysql?
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 5
  • 3
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now