Edit report at https://bugs.php.net/bug.php?id=60695&edit=1
ID: 60695
User updated by: nbari at dalmp dot com
Reported by: nbari at dalmp dot com
Summary: bind_param 'd' appends a 0
Status: Open
Type: Bug
Package: MySQLi related
Operating System: FreeBSD
PHP Version: 5.3.8
Block user comment: N
Private report: N
New Comment:
The point is that when using 'd' a 0 shouldn't be appended to the inputed value.
Using 's' works but the 'posible' bug report is related that to the 'd'
When using 'i' the stored value is like the expected, example 99.3 will store
only 99 and remove the .3 .
But when using 'd' the stored value instead of been or continue to be like the
input, in some cases an 0 is appended.
example:
99.3 is stored like 99.30
99.31 is stored like 99.31
1.1 is stored like 1.10
1.31 is stored like 1.31
for an unknown reason only doubles with 1 decimal are stored or reformatted to
be
have 2 decimals
Previous Comments:
------------------------------------------------------------------------
[2012-01-10 23:13:35] anon at anon dot anon
>The type of column 'name' is text()
Then bind it as 's'.
------------------------------------------------------------------------
[2012-01-10 12:08:47] nbari at dalmp dot com
The type of column 'name' is text()
After inserting the data, I query via mysql console directly on a terminal and
notice the 0, so i can confirm it is not an issue with a software displaying or
formatting numbers with 2 decimal places.
I made the same test using 'i' (for integer) and instead of 99.3 the stored
value
was just 99 (no 0 appended), when using 's' is is working but I start noticing
this strange behaivor when the search string was an double.
my search query/code is like this:
$sql = 'select id from test where name=?';
$param = $_POST['name'];
if (is_numeric($param)) {
$param = !strcmp(intval($param), $param) ? (int)$param :
(!strcmp(floatval($param), $param) ? (float)$param : $param);
}
$type = is_int($param) ? 'i' : (is_float($param) ? 'd' : (is_string($param) ?
's' : 'b'));
$stmt->bind_param($type,$param);
$stmt->execute();
$stmt->close();
Initially I though that the 'sanitation' made was affecting the results but
later manually I specified the types and notice that only with doubles 'd' this
was happening.
------------------------------------------------------------------------
[2012-01-10 05:44:34] anon at anon dot anon
What is the type of the column 'name'?
If it's a string, then bind it as 's' instead of 'd'. If it's a double, there's
no problem; it's the same number and the internal bits of a double do not have
a way to represent "93.3" and "93.30" differently -- the bits are identical, so
the issue is the program or code you're using for *displaying* the number is
formatting it with 2 decimal places on purpose.
------------------------------------------------------------------------
[2012-01-10 02:51:01] nbari at dalmp dot com
Description:
------------
---
>From manual page: http://www.php.net/mysqli-stmt.bind-param#refsect1-mysqli-
stmt.bind-param-parameters
---
When inserting a 'double' using prepared statements
d corresponding variable has type double
if the number is on the format n.y example:1.3, or 99.3, an '0' is appended to
the number, storing something like 1.30, or 99.30 instead of just 1.3 or 99.3
Test script:
---------------
$mysqli = new mysqli('localhost','dalmp','test','test');
/* check connection */
if (mysqli_connect_errno()) {
printf("Connect failed: %s\n", mysqli_connect_error());
exit();
}
$name = 99.3;
if ($stmt = $mysqli->prepare('INSERT INTO test set name=?')) {
$stmt->bind_param('d',$name);
$stmt->execute();
$stmt->close();
}
$mysqli->close();
#this will store 99.30 instead of just 99.3
Expected result:
----------------
do not alter/append the input with a 0 when selecting 'd' as the bind_parameter
------------------------------------------------------------------------
--
Edit this bug report at https://bugs.php.net/bug.php?id=60695&edit=1
