1

I was trying to export database query to CSV and the need arised to use different decimal comma. It seems impossible to change the decimal comma in MySQL, so I tried in PHP:

setlocale(LC_NUMERIC, "cs_CZ");

But it seems that all the database functions like mysql_fetch_row and mysql_fetch_assoc are retrieving data of type string instead of double:

$res = mysql_query("select 50/3");
$row = mysql_fetch_row($res);
var_dump($row); // $row[0] is of type "string"

So in general PHP already doesn't have data of type double, but only strings!

So is there some general, clean way to specify output of the decimal point?

I ended up converting the strings using str_replace('.', ',', $row[0]) but this is ugly for two reasons:

  • you have to know which field is of type double
  • it's a dirty string job.
Community
  • 1
  • 1
Tomas
  • 57,621
  • 49
  • 238
  • 373

3 Answers3

3

I don't know which database client/driver you're using but there is something like mysql_field_type­Docs which gives you the type based on it's offset, like 0.

This should do the job to find out if a certain column needs re-formatting or not.

To reformat, there is number_format­Docs.

With these you can do the conversion automatically.

Edit: Regarding your comments:

If you want to get PHP datatypes mapped, consider using the MySQL Native Driver­Docs. Use it together with PDO:

Advantages of using mysqlnd for PDO

mysqlnd returns native data types when using Server-side Prepared Statements, for example an INT column is returned as an integer variable not as a string. That means fewer data conversions internally. Source

So depending of what you try to achieve, use the right tool.

See as well the multiple options you have when fetching data from a PDO Statement­Docs.

Community
  • 1
  • 1
hakre
  • 193,403
  • 52
  • 435
  • 836
  • thanks. It seems it is possible to code the solution, but I would hope for some really simple solution like setting locale and go. Why is the PHP/MySQL so complicated that you need to code something which normally works just by setting locale? – Tomas Dec 29 '11 at 21:12
  • Because a locale is only inside a system that understands it. And even then, not all functions are locale aware. There is no magic in computing, the computer is dumb as bread, it can't read your mind. It's your code that makes the computer do what you want it to do. So not PHP/MySQL is complicated at all, what's complicated is probably your view on things. Imagine that this software is used by millions of users, so think a minute about it what this means. – hakre Dec 29 '11 at 21:16
  • hakre, I would rather say, the design of PHP/MySQL is broken here. Locale was invented exactly for this purpose, but it's not used here. – Tomas Dec 29 '11 at 21:20
  • Consider you get a double value from the database. How could it have a locale then? That would break the concept of the value. But I get as well what you're looking for probably. It might be possible to overload the returned results with mysqli and/or pdo. This might be what you're looking for making the result locale aware the way you need it. – hakre Dec 29 '11 at 21:22
  • *"Consider you get a double value from the database."* then there are two possibilities: 1) either the value is read as type `double` in PHP (this is not the case unfortunatelly) and PHP handles it like `double` and the locale applies for echoing it to output; or 2) the value is read as type `string` in PHP and therefore conversion `double` -> `string` has already been applied - probably by the MySQL - so then it should have used the locale set in MySQL. Unfortunatelly none of these 2 possibilities applies so the design doesn't look very clean. I'd prefer the solution 1), looks better. – Tomas Dec 29 '11 at 21:27
  • You're wrong in: 1) for *"the locale applies for echoing it to output"* - it's just not the case and right so. 2) for *"conversion double -> string [...] it should have used the locale set in MySQL"*. -- That's just wrong as well, again for various reasons, I assume you only write that because you currently miss such a feature. Add what you miss with your own code or gain the knowledge to re-use existing tools to lower the burden for doing so. I also extended my answer a little for some pointers. – hakre Dec 29 '11 at 22:03
2
// French notation
$nombre_format_francais = number_format($number, 2, ',', ' ');
Mark Baker
  • 209,507
  • 32
  • 346
  • 385
  • thanks, Mark, but there is a drawback that you have to specify the number of decimal points. The only thing I want to change is the decimal separator. – Tomas Dec 29 '11 at 21:17
  • @Tomas - Then you could try setting a locale and using money_format() ( http://uk.php.net/manual/en/function.money-format.php ) assuming you're not on Windows – Mark Baker Dec 29 '11 at 21:48
  • @Tomas: You're not really pragmatic, just make the thousands separator an empty string and for the number of decimal points use a large value. If a number doesn't have that much decimals, the function will not create them. – hakre Dec 29 '11 at 22:05
0

Try 

number_format($row[0], 2, ",", ".");

That should change the format of the number. (german format)

Dion
  • 3,145
  • 20
  • 37