0

I am trying to save some value prefixed with the currency symbol like in €10. Yet when I manually enter them in the DB the euro sign gets turned now and then in ?. When then I query the line I get sometimes again the question mark with the value and some other times NaN for the full value. The issue changes if I query the line by using the email field or the unique identifier. Using $ or ® instead of € presents no problems; even ™ is turned to ?, though.

What is strange is that if I try to replace the question mark with the original character, MariaDB complains that there is no change in the line, like if that character were in fact present even if not shown!

I tried restarting MariaDB, just in case, but the problem remained. I am using UTF32 for encodage and utf32_unicode_ci for collation. I am testing the thing with Sequel_pro without even touching php not to stack things. At any rate if I execute the query from a php script and parse the result with JSON I get null for the value.

What could be the issue with those special characters?

Fabrizio Bartolomucci
  • 4,948
  • 8
  • 43
  • 75
  • Unless the tool you are using to edit the data into the database is encoding in that same way as the database is encoded you will get issues like this. Also is UTF-8 not enough for what you want to do? – RiggsFolly May 01 '16 at 15:01
  • Possible duplicate of http://stackoverflow.com/questions/279170/utf-8-all-the-way-through – RiggsFolly May 01 '16 at 15:02
  • The other 3d mentions the option of changing the settings for all the tables, but I have no cue about how to do it in SequelPro. – Fabrizio Bartolomucci May 01 '16 at 15:29
  • Using UTF8 changes nothing, when I query the line the € is again turned to ?. – Fabrizio Bartolomucci May 01 '16 at 15:41
  • Thats not what I was trying to say. I was saying if you are entering in ascii into a UTF-Anything it will cause issues. You have to be sure whatever you are using to add the € symbol is also in the same encoding – RiggsFolly May 01 '16 at 15:53
  • I am using the keyboard to enter the value... – Fabrizio Bartolomucci May 01 '16 at 16:18
  • Yes but what are you entering the value from the keyboard INTO – RiggsFolly May 01 '16 at 16:23
  • I am entering the value with the euro sign. At any rate I changed politics and for the time being I just save te value as a Double. Yet the issue shall surface again when I port to MariaDB another app storing arbiter strings in any alphabets. So I would eventually like to know how to configure MariaDB to allow for that. – Fabrizio Bartolomucci May 01 '16 at 16:26
  • See this as I dont seem to be able to make you understand http://stackoverflow.com/questions/279170/utf-8-all-the-way-through – RiggsFolly May 01 '16 at 16:30
  • That post talks about php issues, what is interesting of course, but I already got the problem when manually entering the data in the DB! – Fabrizio Bartolomucci May 01 '16 at 16:32

1 Answers1

0

Plan A: Store the amount as a string and do not try to get the value out of it. This requires, as already mentioned, "utf8 all the way through".

Plan B: Store only the amount in a numeric field. Either store the 'currency' in another field as 'EUR' or 'USD' or ... Or simply assume that all amounts are Euros. Then put the Euro sign in front of the amount when you print it.

Do not use DOUBLE or FLOAT, you get an undesirable extra rounding. Instead, consider DECIMAL(11,2). That will exactly handle amounts in most countries. (A few countries need 4 decimal places; some can live with 0.)

Do not use utf32; use utf8 (or utf8mb4).

A database is a repository of data, not a formatting tool. Keeping this distinction will help avoid problem like this.

Rick James
  • 135,179
  • 13
  • 127
  • 222