CODEWITHSUNDEEP
phpmysqlsqlintegerdecimal
NeverPhased
NeverPhased

Reputation: 1566

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

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?

Upvotes: 0

Views: 5951

Answers (1)

Kennith Nichol
Kennith Nichol

Reputation: 338

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.

Upvotes: 2

Related Questions