MySql data type for price/cost of product?

Hi, I need an appropriate data type for mysql to set the price of a product. I have gone with decimal but the problem is if a user enters $200000, I want it to automatically show as $2,00,000 with the commas and I do not know how to get that done.

I will also have an advanced search feature that will allow me to look for products between 1,00,000 to 2,00,000 so having the comma in place using php might not allow sql to process the query I suspect.

How do I go about this. Thanks in advance!

Keep the decimal data type.

This is something you’ll have to handle when displaying the data to the user (add the comma’s) and when using the user input to do the search in the database (remove the comma’s if the user entered a value like 200,000). Just another step in the user input sanitization :wink:

Thanks brother. Although, how am I supposed to do this? Can you please drop a hint. Thank you.

It depends on what data types your database has available.

The main point is do not store the commas but only digits (and the decimal point)
i.e. if you have the type Float specify 2 decimal points.

If none of the values have decimal parts you could get away with using Integer.

If none of the values have decimal parts you could get away with using Integer.

Yes, it is a property portal script. So what we have is amounts that go big which is why I need the commas. But I still have no idea how I can get the commas to appear without them being in the db numerical figure.

If you have PHP there is a handy function http://php.net/manual/en/function.number-format.php

If only one parameter is given, number will be formatted without decimals, but with a comma (“,”) between every group of thousands.

If two parameters are given, number will be formatted with decimals decimals with a dot (“.”) in front, and a comma (“,”) between every group of thousands.

If all four parameters are given, number will be formatted with decimals decimals, dec_point instead of a dot (“.”) before the decimals and thousands_sep instead of a comma (“,”) between every group of thousands.

1 Like

NOOOOOOOoooooo…

please… use DECIMAL, not FLOAT

1 Like

Thanks for catching that. I was mixing PHP terminology with MySQL terminology again :blush:

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.