Formatting float numbers before inserting into mysql

Question

I am working on mobile app which has a mysql backend. Mysql is already desined database and currently running for the PC site too. So, I am developing separate front-end on mobile, but using the same database and writing my own php code to update the same database. My problem is that database has a column(price) whose type is float(giving type as only float with no extra information like (5,4) when I used desc.).

Problem is --> When I insert data as it is, like if user enters 1234, I am inserting into database as 1234 and it is stored in the same way. But when the same information is shown on the PC site, it displays 0.01 as 1234. So, I did some search on the data that is inserted from the PC site. They are inserted into database column like 1234000000, if user inserts 1234. While on the price info on website it shows correct price like 1234.

So, do I need to append six 0's to my number to the end of it before inserting in my mobile php code or is there any type of formatting technique? Any help is greatly appreciated. Thanks in advance.

EDIT : Code snippet

$price = mysql_real_escape_string($_POST[$enteredprice]);

mysql_query("INSERT INTO item(s_secret, i_price) 
        VALUES('$secretcode','$price')",$db);

Where $price contains the value of price that user entered. As my above explanation, if user enters 1234, $price contains 1234.

Don't use a float for currency. Use a fixed precision type or an int that is in cents. (stackoverflow.com/questions/3730019/…) — Corbin
– Corbin, Commented Jan 10, 2013 at 5:54
@Corbin But it's already designed database. I can't do anything with it. Please suggest. — rick
– rick, Commented Jan 10, 2013 at 5:56
@Alex_ios - To improve your question and the likelyhood of getting help, you should include the PHP and/or MySQL code that is used to insert the records. I am assuming that issue lies there... — user985189
– user985189, Commented Jan 10, 2013 at 6:05
NO, Banks do not use floating point numbers! Floating point is inexact, which is an absolute no-go for financial data. Banks typically use fixed-point decimals. — user149341
– user149341, Commented Jan 10, 2013 at 6:09
@Nicarus I am doing a simple insertion into the table. Anyways I am gonna edit my question. — rick
– rick, Commented Jan 10, 2013 at 6:12

MaX · Accepted Answer · 2013-01-10 09:21:51Z

1

If you can't alter the table as suggested to use fixed point, and are bound to the format you describe, you can do as below. It seems strange, though, that the field is float when there is no actual decimal value being used.

"INSERT INTO item(s_secret, i_price) VALUES('$secretcode','".($price*1000000)."')"

This assumes that you can specify values with decimals in your form.

answered Jan 10, 2013 at 9:21

MaX

1,81514 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Formatting float numbers before inserting into mysql

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related