'Value being inserted as 2147483647 into database

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?

phpmysqlinteger


Solution 1:[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

Solution 2:[2]

if you want to store it as a number use bigint because int has it's max value equal to 2147483647.

So whatever number you try to store that is higher than 2147483647 will be stored as 2147483647.

Here is some reference for mysql types:

http://dev.mysql.com/doc/refman/5.0/en/integer-types.html

Solution 3:[3]

I am also facing this type of problem and I have solved that problem after Updating datatype From 'INT' TO 'BIGINT'.

#php #mysql #database #sql

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2 piacente.cristian
Solution 3

Related Questions