I came across a problem with storing a large integer in a BIGINT
column on MySQL via PDO
If i run this test:
$number = "30123456789";
var_dump($number); //prints string(11) "30123456789"
$new_number = (int)$number;
var_dump($new_number); //prints int(30123456789)
So far so good...
If I run this SQL directly in MySQL:
update my_table set bigint_field = 30123456789 where id_field = 1
Everything works fine...
The problem arise when I try to save that number via PDO and I reduced the problem to this line of code:
//parameterized query
//update my_table set bigint_field = :bigint_field where id_field = :id_field
$statement->bindValue(":bigint_field", $new_number, PDO::PARAM_INT);
If the optional third type
parameter is absent or equals PDO::PARAM_STR
then the value is saved jut fine, if not the value is truncated to 58685709. If I try to save 20288976024, the value is truncated to 0. What is happening here
I'm running PHP 5.5.33 and MySQL 5.6.25 on Debian Wheezy x64
I cannot reproduce your case.
On a x86 system intval() already makes 2147483647 On a 64 bit system everything works fine.
Binding bigint values as strings may lead to wrong results, as value will be cast to a float and lose precision.
Edit: it turned out to be an old libmysql issue. Having PHP configured this way I was able to reproduce the problem:
$number = 30123456789;
$new_number = 20288976024;
var_dump($number, $new_number);
$pdo->query("CREATE TEMPORARY TABLE bint_test(i BIGINT unsigned)");
$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, FALSE);
$stmt = $pdo->prepare("insert into bint_test values (?)");
$stmt->bindValue(1, $number, PDO::PARAM_INT);
$stmt->execute();
$stmt->bindValue(1, $new_number, PDO::PARAM_INT);
$stmt->execute();
echo json_encode($pdo->query("SELECT * FROM bint_test")->fetchAll());
prints out
int(30123456789)
int(20288976024)
[{"i":"58685717"},{"i":"0"}
Two possible solutions:
php-mysqlnd
, which you have to do anyway, because mysqlnd is an new replacement for the old libmysql connector. If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With