Mysql - decimals keeps rounding when inserting through php

PHP
1

When inserting decimal numbers through php into mysql database, it keeps rounding.
As I set decimal(10, 6), so the number 1.123456 rounds as 1.000000, but when inserting directly in phpmyadmin, it works just fine.
Data is fetched from xml and inserted into mysql, that really doesn’t matter but someone might wonder where data is coming from.

Should I do something in php with those decimalls? I saw the same problem on laracasts community forum, but there is no answers. Any help would be great.

2

@Nah your title says phpadmin keeps rounding decimal number, but then you say

These seem to be contradictory. Which is the problem?

3

Edited!

4

While inserting through phpmyadmin, it works. And when inserting through php, it keeps rounding decimal number.

5

perhaps ask this question in the php forum? it’s not a mysql problem, is it

6

Good point. Moved to the PHP forum.

7

@Nah it would probably be helpful to see the code you’re using.

1 Like
8

That’s not what the decimal field type does. decimal(10,6) would accurately store any number from -9999.999999 to 9999.999999. It would not round.

So, as Rudy pointed out, the problem isn’t in your database.

Depends on what you’re trying to store. If you’re trying to accurately store the value “1.123456”, no, you shouldn’t do anything with them. If you’re trying to store the closest integer to your data, you would round the data using PHP and store the result; that said, if you’re intending on doing that, why is your database field a 6-decimal-place decimal and not just an int?

9

It’s not an int because it must be decimal. Values came from xml in that form, so it’s not allowed to store as int or whatever datatype. It only should be stored as decimal.

Yes, I realized that it has nothing to do with database, but still I can’t make it work.

10

as Gandalf said, we’ll need to see the code; something in your PHP is rounding the value.

11 
class ExchangeRates
{
    // have xml and json data
    // this one goes with json, but it works same as xml

    $i = 0;
   foreach ($jsonData as $item) {
       $unit = $item["Unit"];
       $buyRateForeign = $item["buyRateForeign"];
       $meanRate = $item["meanRate"];
       $sellRateForeign = $item["sellRateForeign"];
       
       $database = new Database();
       $sql = "INSERT INTO currency_list (unit, source, buyRateForeign, meanRate, sellRateForeign) VALUES (:unit, :source, :buyRateForeign, :meanRate, :sellRateForeign)";
       $stmt = $database->getConnection->prepare($sql);
       $stmt->bindValue(':unit', $unti);
       // etc...
       $stmt->execute();
       $i++;
    }
}

If I use SELECT query, data will be returned as a string, so for a second I thought that be a reason for not working properly.

12

There’s no way that that is your actual code. It would fail to run at all, for starters because $unti is undefined.

Show us the ACTUAL code. Not what you think we want to see, but the actual code.

13

No, unit here is 1. Every currency list has unit and every currency has its own unit value.

14

$unit != $unti

Your code has an undefined variable.
If you’re not going to show us the actual code, we can’t help you properly.

16

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.