PHP Issue rounding variables?

General
1

I can’t for the life of me figure out what is going on here, it seems that the PDO where clause is rounding down numbers!!???

I have this:

$gasql = 'SELECT * FROM modx_inventory_gage_long  WHERE id = '.$ga.' ;';
$gas = $this->modx->query($gasql);
while($ga = $gas->fetch(PDO::FETCH_ASSOC)){
     $this->modx->log(modX::LOG_LEVEL_ERROR, 'ga = ' . print_r($ga, TRUE));
     $min = $ga['min'];
     $max = $ga['max'];
}
$arrayWhere[] = array('ga:>=' => $min);
$arrayWhere[] = array('ga:<=' => $max);

it logs this:

(
[id] => 73
[ga] => 0
[min] => 2.250
[max] => 2.490
[display] => 2.250
)

Which is correct… a little later on:

$criteria->where($arrayWhere);

$criteria->limit($limit, $offset);

$criteria->leftJoin('InventoryReserve','InventoryReserve');

$criteria->prepare();

$this->modx->log(modX::LOG_LEVEL_ERROR, 'search SQL = ' . $criteria->toSQL());

$tags = $this->modx->getCollection('InventoryData', $criteria);

Pretty normal, right? Well this is what I get in the logs:

SELECT <<a bunch of stuff>> FROM `modx_gssi_inventory_data` AS `InventoryData` 
LEFT JOIN `modx_gssi_inventory_reserve` `InventoryReserve` ON `InventoryData`.`id` =  `InventoryReserve`.`tag_id` 
WHERE  
	( `InventoryData`.`id` >= 1 
	AND  ( `InventoryData`.`product_category` IN ('STAINLESS') AND `InventoryData`.`material_type` IN ('BAR','PIPE','TUBE','SEAMLESS-TUBE','WIRE') )  
	AND `InventoryData`.`ga` >= 2 AND `InventoryData`.`ga` <= 2 
	)  
ORDER BY gage ASC, width ASC LIMIT 33

!!! notice the inventorydata.ga both have the same value?

am I just staring right at this or is something done wrong?

2

Seems to have been an issue before:

Maybe a wrong dbtype / phptype combination in your schema.
I tried some combinations but couldn’t reproduce your problem.

3

I know bizarre right?? The schema looks fine and is nearly identical to another table that does not do this:

<object class="InventoryGageLong" table="inventory_gage_long" extends="xPDOSimpleObject">
        <field key="ga" dbtype="int" precision="4" phptype="integer" null="false" />
        <field key="min" dbtype="decimal" precision="4,3" phptype="float" null="true" />
        <field key="max" dbtype="decimal" precision="4,3" phptype="float" null="true" />
        <field key="display" dbtype="decimal" precision="4,3" phptype="float" null="true" />
    </object>

As for the database - it looks fine as well, I believe I actually just copied the other (working) table and added the one extra field.

CREATE TABLE `modx_gssi_inventory_gage_long` (
	`id` INT(4) UNSIGNED NOT NULL AUTO_INCREMENT,
	`ga` INT(4) NOT NULL,
	`min` DECIMAL(4,3) UNSIGNED ZEROFILL NULL DEFAULT NULL,
	`max` DECIMAL(4,3) UNSIGNED ZEROFILL NULL DEFAULT NULL,
	`display` DECIMAL(4,3) UNSIGNED NULL DEFAULT NULL,
	PRIMARY KEY (`id`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
ROW_FORMAT=COMPACT
AUTO_INCREMENT=90
;

Quite mystified at this point!

4

If I dump my criteria array just before I prepare it and getCollection the ga values are correct.

arrayWhere = Array
(
    [0] => Array
        (
            [id:>=] => 1
        )
    [1] => Array
        (
            [product_category:IN] => Array
                (
                    [0] => STAINLESS
                )
        )
    [2] => Array
        (
            [ga:>=] => 0.017
        )
    [3] => Array
        (
            [ga:<=] => 0.021
        )
)
5

try to use phptype string

6

It was worth a try - but no dice :frowning:

$gage = $this->modx->getObject('InventoryGageLong',$ga);

$this->modx->log(modX::LOG_LEVEL_ERROR, 'ga/type/min-val = ' . $ga .' ' . gettype($gage->get('min')).' '.$gage->get('min'));

$this->modx->log(modX::LOG_LEVEL_ERROR, 'ga/type/max-val = ' . $ga .' ' . gettype($gage->get('max')).' '.$gage->get('max'));

settype($gage->get('min'),'double');

settype($gage->get('max'),'double');

$this->modx->log(modX::LOG_LEVEL_ERROR, 'ga/type/min-val = ' . $ga .' ' . gettype($gage->get('min')).' '.$gage->get('min'));

$this->modx->log(modX::LOG_LEVEL_ERROR, 'ga/type/max-val = ' . $ga .' ' . gettype($gage->get('max')).' '.$gage->get('max'));

$arrayWhere[] = array('ga:>=' => $gage->get('min'));

$arrayWhere[] = array('ga:<=' => $gage->get('max'));

/model/liveinventory.class.php : 299) ga/type/min-val = 54 double 2.25
/model/liveinventory.class.php : 301) ga/type/max-val = 54 double 2.49
/model/liveinventory.class.php : 307) ga/type/min-val = 54 double 2.25
/model/liveinventory.class.php : 309) ga/type/max-val = 54 double 2.49

`AND `InventoryData`.`ga` >= 2 AND `InventoryData`.`ga` <= 2 )
7

I could reproduce the behavior on my webserver.
It seems the binding in the xPDOQueryCondition gets assigned the wrong type. PDO::PARAM_INT instead of PDO::PARAM_STR.

The query works if I add 'float' to this array.

protected $_quotable= array ('string', 'password', 'date', 'datetime', 'timestamp', 'time', 'json', 'array', 'float');

But it is probably not a good idea to change the MODX core code. :wink:

8

This post solved my problem, though as I understand it is a PDO issue and not modx, it will get fixed when PDO gets updated. from the sounds of it PDO will be a dependency for modx 3 and installed via composer…

9

This IS a change that needs to be made to the xPDO core code. See recent discussion at Round decimal by Fi1osof · Pull Request #25 · modxcms/xpdo · GitHub for more information. It seems this issue has sat unresolved and unintentionally ignored for some time.

1 Like
10

By the way, setting the phptype to string in the xpdo schema can temporarily work around the problem, but may prevent the use of other float-specific features in xpdo for that field.

1 Like