0

I have one database field "price". I used datatype bigint for this field. now as per my knowledge, bigint data type allows only 0-9. in this field, I add space in this field. so I want it to display like this: 2 000. but when I add it's become 2 and another side when I use varchar datatype my sorting functions low to high price is stopping... so PLease find out the way that it's displaying like 2 000. and my sorting function is not stopped.

Kishan
  • 5
  • 4
  • maybe share some codes – Khaled Alam Feb 19 '18 at 14:01
  • I think you should be using decimal. See this question: https://stackoverflow.com/questions/13030368/best-data-type-to-store-money-values-in-mysql/23353037 – flip Feb 19 '18 at 14:02
  • 2
    BIGINT for "price"? WTH are you selling? – brombeer Feb 19 '18 at 14:02
  • No. it's for the only listing purpose... – Kishan Feb 19 '18 at 14:05
  • @KhaledAlam it's general insert query. nothing else – Kishan Feb 19 '18 at 14:06
  • 2
    Simple.. don't put a space in your integer fields. Integers are integers, a space does not belong in an integer. I suggest you read up on what an integer is. You can format it when pulling it out of the database, otherwise use a char field if you don't need numerical comparisons. – Devon Bessemer Feb 19 '18 at 14:07
  • @Devon user put in text box 2 000 in it's 2k and I want to display this 2 000 when I used bigint instead of varchar it stops my sorting process of low to high function. – Kishan Feb 19 '18 at 14:16
  • Don't just blindly take user input into your app. Filter it, strip any unwanted chars and make sure you have an integer to insert into your database. Heck, HTML5 inputs even have a `pattern` attribute that would allow you to let users input integers only. – brombeer Feb 19 '18 at 14:27
  • @kerbholz thanks... I know all this type of validation and this type of query but I want to display like this is on the front side it's the must... (2 000) which user input with space. or not input with space... also I take care of my sorting function as well. – Kishan Feb 19 '18 at 14:35
  • Ok, like @Devon said, you can't store spaces in integers. Store it as integer (2000), when you want to display it you could use functions like [number_format](https://secure.php.net/manual/en/function.number-format.php) or [money_format](https://secure.php.net/manual/en/function.money-format.php) to give it the look you want. – brombeer Feb 19 '18 at 14:50
  • @kerbholz thanks... now I want to use number_format for displaying data. – Kishan Feb 19 '18 at 15:18

1 Answers1

0

BIGINT, INT, DECIMAL, etc are stored without spaces, commas, dots, etc.

If you want to display them with thousands-separators, etc, you must format them as needed. See FORMAT(). See "locale". Etc.

If you want to input them with such, it is up to your application to turn the number into a format that MySQL will accept. That is, 2000, not 2K, not 2,000, not 2.000, not two thousand.

Rick James
  • 135,179
  • 13
  • 127
  • 222