which field type do I use to accept this kind of data... 1

[garethashton]

I connect to a MySQL database with PHP. On one of my forms, I need to get a money ammount into my database so I can make calculations on it.
If I enter 2000 then it stores it in a number field as 2000.
But if I enter 2,000 then it stores it as 2.

Is there a field type in MySQL that can store a number (not a string) but also regcognise a comma (,) as a 1000's place divider and not a period (.) Is it also possible to enter a curreny symbol ($)?

--Or can PHP remove non-digit signs and still give the correct number to MySQL?
Any help is appreciated.

 

[rycamor]

MySQL doesn't have a "money" datatype, such as that in MSSQL, and a few others. Your two choices are:

1. Use a string, and use some regular expressions work to handle commas, periods and the dollar sign.

2. (the better choice) just use DECIMAL(10,2), or whatever width you need. DECIMAL(10,2) could hold values up to 8 digits before the decimal, and 2 digits after, so amounts up to $99,999,999.99 could be stored. The number would look like 99999999.99 in the database, but you can output it with the $ and commas using the sprintf() command.

 

[Guest_imported]

you can use simple mediumint(int for large money counts) to
store.

then, with php, you can use the function
number_format($var);
to get the data formatted same as money. This function has
many options so you can obtain more information about this on php.net/manual/es


greetz

 

[rycamor]

Yes, you can use number_format(); thanks for the reminder, mopicus. That is a nice PHP-specific function for just this sort of problem. However, I don't recommend using mediumint, for the very reason that it is an integer number type, meaning it can't store decimal values. You could always multiply by 100 before storing in the INT field and divide by 100 when outputting, but that is a recipe for confusion. Stick with DECIMAL.

 

[garethashton]

Thankyou both mopicus and rycamor. Both of your sugestions were a help to me. I did end up going with the DECIMAL type. I will be sure to come here again should I have any further questions.
-Gareth