Change decimal separator

[clfo]

Hi,
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…

[skawid]

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.

[clfo]

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.

[clfo]

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
.

[skawid]

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.

[clfo]

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