CODEWITHSUNDEEP
phpmysqlmysqli
Jakob Nielsen
Jakob Nielsen

Reputation: 5198

Unable to store Bigint data to mysql database using send_long_data

In my mysql database I need to store very large id values. For that reason I set the type of the field to bigint(11).

When I try to insert values in the table from a prepared mysqli statement in php, all values get set to 2147483647, which is the highest Integer value in mysql, even though the type of the field is bigint.

For example:

echo $id;
$stmt_insert->bind_param("i", $id);
$stmt_insert->execute;

if( $stmt_insert === false ) {
    $Logger->error( mysqli_error($conn) );
}

Gives me to output 2300679976 but in the database 2147483647 gets stored.

I then read the documentation of the bind_param function and found out that for storing large data the send_long_data function has to be used. So I tried it the following way:

echo $id;
$stmt_insert->bind_param("b", null);
$stmt_insert->send_long_data(0, $id);
$stmt_insert->execute;

if( $stmt_insert === false ) {
    $Logger->error( mysqli_error($conn) );
}

But this did not solve the problem. Still 2147483647 is stored even though the table field is set to bigint.

Upvotes: 2

Views: 668

Answers (2)

Jakob Nielsen
Jakob Nielsen

Reputation: 5198

I just figured out the problem was indeed in the mysql database, where the type was incorrectly set.

I did set it to bigint, but accidently with a fixed size of 11, which is the normal int size.

I removed the fixed size and now the field got correctly set to bigint(20) and values are stored correctly now.

Upvotes: 1

James Wiley
James Wiley

Reputation: 489

Since the max int size in MySQL on 32-bit systems is 2147483647, you'll have to change the datatype. There are a couple of ways to solve this problem.

  1. Change the MySQL datatype to double. (the technically correct method) Doubles can hold more precision than ints. So one way to handle this is to change your value 2300679976 into a double that looks like 2.300679976 before storing it in the MySQL table. The catch with this method is that you have to add code to your getters/setters that convert the value to a double going in, and a string coming out. I call this the 'technically correct method' because it maintains the data as a numeric value. If you were to move this data to a different database like MsSQL or PostgreSQL, it would stand up to the migration.

  2. Change the MySQL datatype to Varchar. (the simple, yet not integris method) Varchar gives you room to have ids up to 255 chars long. This method is simple because it doesn't require you to convert your values as they go in and out of the database. PHP doesn't care if the value is a string coming out of the database, it will coerce it to a numeric type when you use it in numeric situations anyway. I call this the 'not integris method' because it's storing the data as a different type than the type that is being used in code, and it's taking advantage of PHPs dynamic typing to automatically make the conversion. If you were to move this data to a different database, you would likely have some things to fix in your code to get it back to working condition because they are not so lenient when it comes to datatype integrity.

Upvotes: 0

Related Questions