I've set Swedish locale to use comma as decimal separator. For example it should be 123,45 instead of 123.45.
To test it:
echo 100/3;
It gives desirable result:
33,333333333333
However when fetching or inserting data in the database using PHP PDO, it does not seem to work. For if I try to update a table row:
$sql = "UPDATE
table_name
SET
column_name = 123,45 //does not work but 123.45 works.
WHERE
row_id = 1
";
$q = $db->prepare( $sql );
$q -> execute();
In the above code I get error if I try to use a comma separator eg. 123,45 but it works if I use dot. Similarly when I select the data
$sql = "SELECT
column_name
FROM
table_name
WHERE
row_id = 1
";
$q = $db->prepare( $sql );
$q -> execute();
$test = $q -> fetch( PDO::FETCH_COLUMN );
var_dump($test); //string "123.45"
It will return the result with dot as a decimal separator. ie. 123.45 while I would like 123,45.
What I'm doing wrong? Edit: The column data type is numeric with scale 2.
This would be impossible for MySQL to handle because the comma is also the separator character in a list. Take for example the following query:
INSERT INTO table (a,b) VALUES(123,45,67)
What does this mean? Are the values 123 and 45.67? Or 123.45 and 67? Could be either one. It would be totally ambiguous.
I would suggest doing all your business logic in PHP using en_US and changing the locale to Swedish only for output.
I think this is because the comma is mistakenly read by the SQL as the comma separator in the query statement.
So I think you could do something like this:
$sql = "UPDATE
table_name
SET
column_name = '123,45'
WHERE
row_id = 1
";
You can use PHP number_format() function.
This function accepts either one, two, or four parameters (not three):
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.
Example:
<?php
$number = 1234.56;
// english notation (default)
$english_format_number = number_format($number);
// 1,235
// French notation
$nombre_format_francais = number_format($number, 2, ',', ' ');
// 1 234,56
$number = 1234.5678;
// english notation without thousands separator
$english_format_number = number_format($number, 2, '.', '');
// 1234.57
?>