Change decimal separator

In Sweden we’re using a comma instead of a dot as decimal separator. I want the user to be able to Insert or Update a post in MySQL writing a comma which will be translated to a dot in MySQL. I also want the user to be presented a comma when Selecting a post in MySQL which of course is stored with a dot.

I have tried the following but can’t get it to work:

$fmt = numfmt_create(‘sv_SE’, NumberFormatter::DECIMAL );
$num = numfmt_parse($fmt, ‘1,2’);
$numstr = numfmt_format($fmt, 1234567.891234567890000);

If I Select a post from the db it is still presented with a dot separator…

You don’t need to worry about how the number is stored in MySQL; your users never have to see that format. You should format the number when you render it to the page, using the code you’ve created above.

OK, tried it now in one of the scripts with INSERT and got the following error msg:

Could not perform query: INSERT INTO medlemmar (andel, Inpris, Indatum, Medlemsdatum, Saljare, Personnr, Agarprocent, Inpris2, Indatum2, id) VALUES (‘36’, ‘1500’, ‘20200505’,‘20200505’, ‘test’, ‘12345678-1234’, ‘47,5’, ‘’,NULL, NULL). Error message Data truncated for column ‘Agarprocent’ at row 1

‘Agarprocent’ is the field where I tried to Insert 47,5.

I thought that the code would allow the user to write a decimal - comma as separator as well as see the decimal-comma when being presented with the search result. But I can’t get it to work even if the code is added to my scripts holding the INSERT, UPDATE and SELECT commands

Ok. For adding the number to your database, you can use str_replace(',', '.', $number) to replace the comma with a decimal point.

It’s worth noting that this will only work as expected if there is only one comma in the user input; if there’s more than one, you’ll have to ask the user to try again. If you expect more than one, you’ll have to use different logic.

I got the first code to work after a bit of trial and horror;-). Thanks for your help!

Sponsor our Newsletter | Privacy Policy | Terms of Service