mySQL: I need to input a decimal into an int column

Question

I have a table with a current structure as follows:

enter image description here

Currently this is populated as follows:

enter image description here

The data stored for product value is a decimal value and the end digits are cut off once it is inserted into the database.

I have tried changing the table structure as follows:

enter image description here

However this only leads to the following:

enter image description here

As you can see all values have a .00 appended if none exists, however I want to store all these values with no decimal places. Except the product value.

How can I do this?

your confusing storage and formatting issues, store the correct value, and format(display) it any way you like. — user557846
– user557846, Commented Feb 9, 2012 at 19:46
But if I select a value from the database and want to output it to the screen without the .00 will I not need to apply a round() function each time? — NeverPhased
– NeverPhased, Commented Feb 9, 2012 at 19:47
So do you mean if I stick to the int stucture, the correct value is still stored only I can't see it until extracted and outputted? — NeverPhased
– NeverPhased, Commented Feb 9, 2012 at 19:49
you will need to check if there is a decimal and you will need to use round() or floor() or sprintf() to format your number. there is no way around, this is normal. you might try the datatype FLOAT if you dont like it. — Kaii
– Kaii, Commented Feb 9, 2012 at 19:52

Kennith Nichol · Accepted Answer · 2012-02-09 20:10:32Z

The trouble is you are converting a decimal (float / double) to an integer, so the value is simply truncated (decimal values are chopped off).

If you really don't want to use floats (decimal values) in the database you can use this hack work around will work:

Multiply the number by 100 before inserting it, and then be sure to divide it by 100 when you use the data. This will allow you to maintain 2 decimal points while using integer storage.

Thus, 2.4 would be stored as 240, 53 would be 5300, 20.74 becomes 2074 etc...

I want to note that this is not an ideal solution, but rather a hack.

I highly recommend what the other users suggested in the comments: storing the decimal value (as you have) and formatting it when presenting it.

--- In addition ---

Your real problem appears to be with the way the database is setup.

Each of those values should have their own field since they will be repeated for each product.

Collectives™ on Stack Overflow

mySQL: I need to input a decimal into an int column

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related