1

I have a database table which contain a float field ('ferate') and I have set the length to 15 and precision to 2. Whenever I execute a select statement like so, it returns a whole number. I would like to retrieve 'ferate' as a float with 2 decimal places. The default values for 'ferate' should be 44.86 but I am getting 45.

public function get_defaultrates () {
    $defaultfuelrates = NULL; // array 
    if (!$this->dbconn->connect_errno) {
        $stmt = $this->dbconn->prepare (
            "SELECT * FROM dffuelexciserates"
        );
        $stmt->execute();
        if ($stmt->errno === 0) {
            $result = $stmt->get_result();

            if ($result->num_rows >= 1) {
                $defaultfuelrates = array ();
                while ($row = $result->fetch_assoc()){
                    var_dump ($row);
                    $tr_dffuelexciserates = new tr_dffuelexciserates ();
                    $tr_dffuelexciserates->dfferid = $row['dfferid'];
                    $tr_dffuelexciserates->ftid = $row['ftid'];
                    $tr_dffuelexciserates->ferate = $row['ferate'];
                    array_push ($defaultfuelrates, $tr_dffuelexciserates);
                }
            }
            $result->free();    
        }
        $stmt->close();
    }
    return $defaultfuelrates;
}
Dat Nguyen
  • 21
  • 5
  • 44.86? is it save in db table field `ferate`? try once `$tr_dffuelexciserates->ferate = (float)$row['ferate'];` – Alive to die - Anant Feb 27 '17 at 10:29
  • yes, 44.86 is the value stored in the db table in 'ferate' field. the dump result of $row shows that it is 45. Also, casting float does not work. here's the dump. `array (size=3) 'dfferid' => int 1 'ftid' => int 1 'ferate' => float 45 object(tr_dffuelexciserates)[24] public 'dfferid' => int 1 public 'ftid' => int 1 public 'ferate' => float 45 array (size=3) 'dfferid' => int 2 'ftid' => int 2 'ferate' => float 45 object(tr_dffuelexciserates)[25] public 'dfferid' => int 2 public 'ftid' => int 2 public 'ferate' => float 45` – Dat Nguyen Feb 27 '17 at 10:36
  • What is a ferate anyway? Is it like a stoat? See DECIMAL. – Strawberry Feb 27 '17 at 10:36
  • @DatNguyen try once :- `$tr_dffuelexciserates->ferate = (float)$row['ferate'];` and then check var_dump(); – Alive to die - Anant Feb 27 '17 at 10:39
  • Anant, if dumping $row shows 45, I don't think casting a float will work? here's the dump of what you have suggested. It remains 45. `object(tr_dffuelexciserates)[25] public 'dfferid' => int 2 public 'ftid' => int 2 public 'ferate' => float 45` – Dat Nguyen Feb 27 '17 at 10:45
  • @Strawberry, it's a mix between a feral cat and a rat. Which Decimal documentation should I be reading? Can I please have the address? – Dat Nguyen Feb 27 '17 at 22:45
  • 1
    See the manual: https://dev.mysql.com/doc/refman/5.7/en/numeric-types.html – Strawberry Feb 27 '17 at 23:06
  • I have changed the type from FLOAT(15,2) to DECIMAL(15,2) and it is working now. I found a good hint from here: [basic link](http://stackoverflow.com/questions/5150274/difference-between-float-and-decimal-data-type). You can store a Base 10 numeric data to a Float field but I think there's a side effect when retrieving a Float value and convert it to a Base 10 value in php using mysqlnd. – Dat Nguyen Feb 27 '17 at 23:56

1 Answers1

0

I am answering my own question. I have changed the type from FLOAT(15,2) to DECIMAL(15,2) and it is working now. I found a good hint from here: basic link. You can store a Base 10 numeric data to a Float field but I think there's a side effect when retrieving a Float value and convert it to a Base 10 value in php using mysqlnd.

Dat Nguyen
  • 21
  • 5