Login   Register  
PHP Classes
elePHPant
Icontem

bind_params issue

Recommend this page to a friend!
Stumble It! Stumble It! Bookmark in del.icio.us Bookmark in del.icio.us

      Extended MySQLi  >  All threads  >  bind_params issue  >  (Un) Subscribe thread alerts  
Subject:bind_params issue
Summary:Float output
Messages:4
Author:chaitanya
Date:2012-10-09 16:10:18
Update:2012-10-14 16:46:15
 

  1. bind_params issue   Reply  
Picture of chaitanya
chaitanya
2012-10-09 16:10:18
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 ?

  2. Re: bind_params issue   Reply  
Picture of Camilo Sperberg
Camilo Sperberg
2012-10-10 14:31:25 - In reply to message 1 from chaitanya
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.

  3. Re: bind_params issue   Reply  
Picture of chaitanya
chaitanya
2012-10-13 09:52:42 - In reply to message 2 from Camilo Sperberg
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

  4. Re: bind_params issue   Reply  
Picture of Camilo Sperberg
Camilo Sperberg
2012-10-14 16:46:15 - In reply to message 3 from chaitanya
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/