3

Having the 2147483647 MYSQL issue.

When inserting an 11 digit number in the telephone field i am getting this number in return saved in the DB: 2147483647

TEL is set as VARCHAR not INT tough.

Also, on a database hosted on SERVER 1 i have the same exact database table and not having the same issue tough?! what might be?

This is the SQL and PHP

tel_no  varchar(20) utf8_general_ci  Yes    NULL
mob_no  varchar(20) utf8_general_ci  Yes    NULL

That's the MYSQL. Yes i am inserting an 11 digit number as string in the DB 

$telno = GetValue($post['tel_no']);
$mobno = GetValue($post['mob_no']); 

$sql = "UPDATE ".$this->dbpre."bookings SET tel_no='$telno', mob_no='$mobno'...

Can you please help? I am getting crazy!

Chris Zammit
  • 43
  • 1
  • 4
  • 1
    Possible duplicate http://stackoverflow.com/questions/10255724/incorrect-integer-2147483647-is-inserted-into-mysql – Hackerman Mar 04 '13 at 21:10
  • Doesn't make sense. Are you using the same front-end to accept the phone number to test both platforms? Always good to show your SQL as well. – ron tornambe Mar 04 '13 at 21:12
  • Somewhere you have some non-SQL code that pulls out the varchar column and starts treating it as an integer. – nos Mar 04 '13 at 21:15
  • 1
    Are you saying you have a VARCHAR(11) and you're putting an 11 digit number into it and getting back something else? Maybe it's not MySQL, but your programming language that is not able to handle 64 bit numbers...? – Ian Mar 04 '13 at 21:15
  • I am not having the same issue on a different database hosted on another server .. that's very strange. Can it be a server setting of some sort? – Chris Zammit Mar 04 '13 at 21:34
  • Have you printed the SQL (`$sql`) so you can see whether the trouble is in the PHP or in MySQL itself? If the problem is actually in the PHP, can you force `$telno` to be a string by concatenating an empty string with the value from `GetValue()`? Does `GetValue()` force the numeric conversion? – Jonathan Leffler Mar 05 '13 at 07:20

3 Answers3

5

2147483647 is the largest int value for mysql. Just change the type from int to bigint.

George
  • 6,006
  • 6
  • 48
  • 68
  • 2
    While you are right that the number is largest (signed) int value, the answer isn't in changing to bigint, answer is changing from int to varchar since telephone numbers can't be saved as integers correctly (due to unknown lengths, prefixes if applicable, space characters for formatting etc). – N.B. Jul 22 '13 at 09:09
  • thanks @N.B. I guess you are right for international numbers, especially when [mysql_real_escape_string](http://php.net/manual/en/function.mysql-real-escape-string.php) is not used. – George Jul 22 '13 at 10:32
0

Check Integer Types (Exact Value) - INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT and answer

Community
  • 1
  • 1
Eugine Joseph
  • 1,552
  • 3
  • 18
  • 40
-1

solution : just change the type from int to bigint or varchar to bigint.

lalna s m
  • 11
  • 4