Int value changes when insert into mysql database - php

Question

I have a table in mysql with a field called "userId":

`userId` int(20) NOT NULL

Insert statement is:

$result = mysql_query("INSERT INTO users (userId, firstName, lastName, dateCreated) VALUES (".$me['userId'].", '".$me['first_name']."', '".$me['last_name']."', CURDATE()) ")or die(mysql_error());

The value of userId when echoed in php is 100000517980247, but when inserted it changes to 2147483647. When I insert it into a varchar field it's fine. This has to be something really simple but I searched around a bit and I'm just not seeing it.

Akhil Surapuram · Accepted Answer · 2018-12-12 11:49:25Z

10

MySQL ints are a signed 32bit integer. You're inserting a number that's far above the limit, so you're seeing the maximum possible integer, which is 2147483647.

Change the data type to bigint for a 64bit data type, which will handle your number.

edited Dec 12, 2018 at 11:49

Akhil Surapuram

7121 gold badge8 silver badges24 bronze badges

answered Jan 6, 2011 at 3:59

Marc B

362k44 gold badges433 silver badges508 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

tessa Over a year ago

thanks! Seems like an exception would be more useful in this case, rather than just inserting an acceptable value.

Marc B Over a year ago

You can turn on strict mode (dev.mysql.com/doc/refman/5.0/en/…), which'd make MySQL reject any out-of-bounds values. Otherwise it'll silently coerce the value to fit into the field

Zhasulan Berdibekov · Accepted Answer · 2011-01-06 04:01:05Z

2

You use the type INT. And you need to use type BIGINT.

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

answered Jan 6, 2011 at 4:01

Zhasulan Berdibekov

1,0883 gold badges20 silver badges39 bronze badges

Collectives™ on Stack Overflow

Int value changes when insert into mysql database - php

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related