>If the bind type in $stmt->bind_param() is
>changed to "d" then the reproduce code works as expected.
Because you are binding a FLOAT?

I can hardly imagine a way to fit a float value in an integer var.
Doesn't look like a bug to me.

If you check the reproduce code, you'll see that the variable being bound (and also the db column type) is an integer ($id_val = 3900002281) but the problem occurs because the actual integer value is larger than the maximum (signed) 32bit integer value in PHP (while in MySQL it is a valid unsigned 32bit integer value).

Setting the bind type to "d" is a hack / workaround to the problem since double type is presumably 64bit (which will happily store a 32bit unsigned integer value).

The real issue here is that "bind_param("i", $id_val)" should work correctly with integers of any size, not limited to php internal MAX_INT_SIZE limit of 2147483647 (32bit signed integer).

Please let me know if you require further clarification.

Thanks,
Pieter

>the variable being bound is an integer ($id_val = 3900002281) 

Hmm.. No?
# php -r '$id_val = 3900002281; var_dump($id_val);'
float(3900002281)

>The real issue here is that "bind_param("i", $id_val)"
>should work correctly with integers of any size, not
>limited to php internal MAX_INT_SIZE limit of 2147483647
>(32bit signed integer).

It's not integer, it's double/float.
Binding floats as integers obviously may not work.

Ok, so technically you are correct that PHP is storing the value as a float.  My point is that the actual *number* is still an integer and not a float.  I understand that PHP automatically assigns the underlying datatype of a variable based on what is sees fit (which is fine) but in this case it is causing problems.

Consider a typical database application that has integer column types.  In MySQL, both 10000 and 3900002281 are valid (unsigned) 32bit integer values (and thus stored in column type "unsigned int"), while in PHP the former is stored as an "int" while the later as a "float".

Given that an application might expect a particular input value to always be an integer (regardless of its internal representation in PHP) it seems unreasonable and impractical to force the application to determine the internal variable type (at runtime) before binding the variable.

Demonstrating the problem with a small code fragment:

$integers = array(1, 500, 10000, 3800002281, 3900002281); // all valid MySQL unsigned integers
foreach ($integers as $i) {
	$stmt = $mysqli->prepare("SELECT id FROM temp_table WHERE id = ?");
	$stmt->bind_param(is_float($i) ? "d" : "i", $i); // forced to determine varible datatype at runtime
	$stmt->execute();
}

I guess when dealing with large integer values (that might possibly be stored as a float by PHP) it is safe to use the "d" type specifer instead of "i" in bind_parm().  At the very least, the documentation should be updated to make this point a little clearer.

Mental note to self: avoid using unsigned integer columns in mysql! :)