bind_params issue

In a query I am fetching float column containing value "0.01"
but it returns me " 0.0099999997764826".

I tried one more example without using direct MySQLi APIs. Which worked absolutely fine after adding %f.

--------Example Starts---------------

$mysqli = new mysqli("localhost", "user", "pass", "db");

if (mysqli_connect_errno()) {
printf("Connect failed: %s\n", mysqli_connect_error());
exit();
}

/* prepare statement */
if ($stmt = $mysqli->prepare("SELECT POINTS_FORUSER, POINTS_FORGROUP FROM tbl_loyalty_points_variant WHERE VARPOINTID = 1")) {
$stmt->execute();

/* bind variables to prepared statement */
$stmt->bind_result($col1, $col2);

/* fetch values */
while ($stmt->fetch()) {
printf("%f %f\n", $col1, $col2);
}

/* close statement */
$stmt->close();
}
/* close connection */
$mysqli->close();

--------Example Ends---------------

How to implement this in this class developed by you ?

Thanks for using my class.

I will review this later on today to also include support for MySQL's "BIT" field type and hopefully also "NULL" type data. (Which fallback to "0").

Thanks for the debug, I haven't experienced myself this problem. Can send me the table definition to try to replicate the exact same conditions?

Greetings.

CREATE TABLE IF NOT EXISTS `tbl_loyalty_points_variant` (
`VARPOINTID` int(10) unsigned NOT NULL AUTO_INCREMENT,
`LOCATIONPOINTID` int(10) unsigned NOT NULL DEFAULT '0',
`LOCATIONID` int(10) unsigned NOT NULL DEFAULT '0',
`TIERID` int(10) unsigned NOT NULL DEFAULT '0',
`POINTS_FORUSER` float(10,2) NOT NULL DEFAULT '0.00',
`POINTS_FORGROUP` float(10,2) NOT NULL DEFAULT '0.00',
`MAXDAILY` smallint(4) NOT NULL,
`UPDATEDON` double(20,0) NOT NULL DEFAULT '0',
PRIMARY KEY (`VARPOINTID`),
KEY `LOCATIONPOINTID` (`LOCATIONPOINTID`),
KEY `LOCATIONID` (`LOCATIONID`),
KEY `TIERID` (`TIERID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

I've looked over this issue and the problem isn't resolvable from the class without it knowing first what the table definition is.

You see, float is an INEXACT data type in MySQL (1) and because of this, the exact value stored by MySQL isn't 0.01, but differs slightly to 0.009999999776482582, which is what the class receives and also displays. When you format it with printf or any other function, they will round the value.

The solution to this is to use an exact data type, such as decimal (2), MySQL will store this exactly as you say to store it, so that you don't get this type of problems.

As I say, I see no way of casting the data type without knowing before what type of data we are dealing with, which will imply another query to get the data type of the implied tabled and parsing them, which will add a lot of overhead and make everything much slower.

In (3) I've added a screenshot when your same table is using decimal instead of float as data type.

Greetings.

(1) http://dev.mysql.com/doc/refman/5.0/en/precision-math-examples.html
(2) http://imageshack.us/photo/my-images/706/screenshot20121014at172.png/
(3) http://imageshack.us/photo/my-images/547/screenshot20121014at174.png/