2

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.

rick
  • 4,665
  • 10
  • 27
  • 44
  • 1
    Don't use a float for currency. Use a fixed precision type or an int that is in cents. (http://stackoverflow.com/questions/3730019/why-not-use-double-or-float-to-represent-currency) – Corbin Jan 10 '13 at 05:54
  • @Corbin But it's already designed database. I can't do anything with it. Please suggest. – rick Jan 10 '13 at 05:56
  • 1
    @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... –  Jan 10 '13 at 06: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. –  Jan 10 '13 at 06:09
  • @Nicarus I am doing a simple insertion into the table. Anyways I am gonna edit my question. – rick Jan 10 '13 at 06:12
  • What if you passed it in without the single quotes? –  Jan 10 '13 at 06:25
  • @Nicarus As usual. The same thing happens. – rick Jan 10 '13 at 06:37

1 Answers1

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.

MaX
  • 1,765
  • 13
  • 17