Value being inserted as 2147483647 into database

Question

All phone numbers I am trying to enter into my database are being inserted as 2147483647.

The database field is an integer(11).

Before the phone number is inserted, it goes through the following code in order to remove all spaces, dashes, and brackets:

if (!empty($hphone)) $phone = $hphone;
else if (!empty($HomePhone)) $phone = $HomePhone;
else if (!empty($Phone1)) $phone = $Phone1;
$phone = preg_replace("/[^0-9]/", "", $phone);

Why is it inserting the phone number as 2147483647 every time, no matter what the phone number is?

Answer 1

If you can, convert the phone number to a VARCHAR field, don't store it as a signed (or unsigned) numeric value (int, bigint, double, etc...).

In this case, the limit for signed INT in MySQL of 2147483647 is what is causing your issue. Anything larger inserted into this field will be capped at that maximum value.

For example the phone number 555-555-5555 if bigger than that limit (5555555555 >2147483647), as such storing it would result in the max value 2147483647.

I also recommend not storing it as a BIG INT or any other numeric type. How will you handle extension or special encoded characters like:

   +02 112020 10020  
   1-333-232-9393 x203

BTW: don't know if the first is real non-US number format, but you get the idea

Also, phone numbers with relevant leading 0's would be have some of it lost no mater how large the number:

 021-392-9293

Would be the number 213929293