#1
  1. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2007
    Location
    Glendale AZ
    Posts
    230
    Rep Power
    96

    Numbers are not inserting correctly


    I think this is a MySQL issue and not PHP. I'm pulling data from a CSV file. Three columns in question are the height, width, and depth of a shipping box. The table columns are setup as decimal 5,2.

    Values for one particular row are 1.5, 8, 17.5. They are inserting as 1.00, 8.00, 17.00.

    Ideally, I'd like to see them inserted as 1.5, 8, 17.5 and ditch the trailing zeros but I can live with them if there's no other solution besides turning those columns in varchar columns.

    I'm not even sure what to google for this answer even though I'm sure it's another easy one.

    Thanks,

    Mike

    EDIT: Just changed the columns to float 5,2 with no affect.

    When I process everything from the CSV file I wind up with everything in a separate array and that array echoes the data correctly.

    EDITED FINALE: Changed the column to double 5,2 and the bound parameter from type "i" to "d" and it's working. Should have caught the i earlier... Should the bound parameter be d or s in this case? I'm not really sure what d means as far as "double" but the data in these columns will never be used for searching or referencing. Just customer information.
    Last edited by big0mike; July 17th, 2017 at 06:57 PM.
  2. #2
  3. Lazy Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    16,326
    Rep Power
    9645
    Originally Posted by big0mike
    Should the bound parameter be d or s in this case? I'm not really sure what d means as far as "double" but the data in these columns will never be used for searching or referencing.
    Is the value an "i"nteger? Nope. Is the value a "s"tring? Nope. Is the value a real number, often called "d"ouble in the programming world? Yes. So you should use "d".
  4. #3
  5. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2007
    Location
    Glendale AZ
    Posts
    230
    Rep Power
    96
    Yeah, I knew it wasn't an integer, and the reason for the original failure, but I had no idea what double meant. The MySQL terms kinda make sense: integer, float, decimal... Double doesn't "intuitively" mean anything to me. But, now that I know what it means I can hopefully remember...
  6. #4
  7. Wiser? Not exactly.
    Devshed God 2nd Plane (6000 - 6499 posts)

    Join Date
    May 2001
    Location
    Bonita Springs, FL
    Posts
    6,079
    Rep Power
    4101
    The term 'double' is short for double-precision floating-point which has twice as many bits as a single-precision type (aka 'float').

    Note that both float and double types are approximate. You should use DECIMAL to use exact values. You could also bind as a string if you wanted, mysql will do the conversion for you implicitly. If you use the DECIMAL type, binding as a string is preferred to avoid any potential loss as a result of PHP doing a conversion.
    Recycle your old CD's



    If I helped you out, show some love with some reputation, or tip with Bitcoins to 1N645HfYf63UbcvxajLKiSKpYHAq2Zxud
  8. #5
  9. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2007
    Location
    Glendale AZ
    Posts
    230
    Rep Power
    96
    Originally Posted by kicken
    Note that both float and double types are approximate. You should use DECIMAL to use exact values. You could also bind as a string if you wanted, mysql will do the conversion for you implicitly. If you use the DECIMAL type, binding as a string is preferred to avoid any potential loss as a result of PHP doing a conversion.
    Interesting. I do have columns that are decimal. I guess if I needed EXACT values I'd probably already know why float and double are not exact. To me, exact would me there's no rounding involved for numbers with more places behind he decimal than the column allows. In that context decimal wouldn't be exact, either.
  10. #6
  11. Wiser? Not exactly.
    Devshed God 2nd Plane (6000 - 6499 posts)

    Join Date
    May 2001
    Location
    Bonita Springs, FL
    Posts
    6,079
    Rep Power
    4101
    The approximate nature of float/double is due to how they are represented internally. Calculations using the values might contain tiny factions that could throw off the results in some instances. For example 0.1 + 0.2 should be 0.3 but is actually something like 0.30000000447034836. Multiply that enough times and you'll get incorrect results.
    Code:
    create table test (a FLOAT, b FLOAT, c DECIMAL(5,2), d DECIMAL(5,2));
    insert into test(a,b,c,d) values (.1, .2, .1, .2);
    select (a+b) * 987654321 as floatResult, (c+d) * 987654321 as decimalResult from test;
    Code:
    floatResult             decimalResult
    --------------------------------------
    296296300.7151589	296296296.30
    The result using floats is about 4.42 higher than it should be due to all those tiny little fractions affecting the result.
    Recycle your old CD's



    If I helped you out, show some love with some reputation, or tip with Bitcoins to 1N645HfYf63UbcvxajLKiSKpYHAq2Zxud
  12. #7
  13. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2007
    Location
    Glendale AZ
    Posts
    230
    Rep Power
    96
    Originally Posted by kicken
    The approximate nature of float/double is due to how they are represented internally. Calculations using the values might contain tiny factions that could throw off the results in some instances. For example 0.1 + 0.2 should be 0.3 but is actually something like 0.30000000447034836. Multiply that enough times and you'll get incorrect results.
    Dang... fuzzy math like that makes me glad I'm not a programmer full time...

IMN logo majestic logo threadwatch logo seochat tools logo