UTF8 driving me up the wall again!

ultranerds has asked for the wisdom of the Perl Monks concerning the following question:

Hi,

First of all, I've read perlunitut, and also the page linked to from it (regarding UTF8). From my understanding, what I gotta do is:

use Encode;
my $data = decode('UTF-8', $IN->param("data"));
[download]

...process it here

$data = decode('UTF-8', $data);

In my actual example script, I've used:

map { 
    $cat_add->{$_} = encode("UTF-8",$cat_add->{$_} );
} keys %$cat_add;
[download]

(my script is creating a hash, which is getting passed into a DB module). The output of the script seems ok - but for some reason its broken in phpMyAdmin :/

Adding: Agricultura/Fumigación Aérea
$VAR1 = { 'FatherID' => '75321', 'Full_Name_Flat' => 'agricultura/fumi
+gacion aerea', 'Full_Name' => 'Agricultura/Fumigación Aérea', 'Name_E
+nglish' => 'Aerial fumigation', 'Name' => 'Fumigación Aérea', 'CatDep
+th' => '0' }; Done...
[download]

...and in phpmyadmin:

75321     Agricultura     0     0     0     Agricultura               
+              0     0     No     No     NULL    2012-09-04 03:11:10  
+   0             Agriculture     agricultura
75322     FumigaciÃ³n AÃ©rea     75321     75321     1     Agricultura
+/FumigaciÃ³n AÃ©rea                             0     0     No     No
+     NULL    2012-09-04 03:11:10     0             Aerial fumigation 
+    agricultura/fumigacion aerea
[download]

The annoying part is it works EVERYWHERE apart from phpmyadmin ... am I missing anything? I've been coding for a fair time now, and I've only just taken the time to really look into the best practices for utf8/unicode... makes a LOT more sense now - but for some reason this just doesn't seem to wanna play ball :/

TIA

Andy

Comment on UTF8 driving me up the wall again! Select or Download Code

Replies are listed 'Best First'.
Re: UTF8 driving me up the wall again! by Ovid (Cardinal) on Sep 04, 2012 at 11:03 UTC
I'm not entirely certain what's happening here as there are a few missing pieces of the puzzle. Is your table's encoding set as UTF-8? Also, when you say "the output of your script is OK", do you mean the Data::Dumper output? And have done a SELECT directly against the database table to see if the data is being store in the format you're expecting? If you want to understand more about handling Unicode, my book is temporarily online for free and Chapter 9 contains a Unicode section which, while incomplete (it would take an entire book of its own), does explain the basics of Perl's Unicode handling well enough for you to debug most problems. Cheers, Ovid Live and work anywhere in the world. The official Perl 6 wiki.	[reply]
Re^2: UTF8 driving me up the wall again! by ultranerds (Hermit) on Sep 04, 2012 at 12:44 UTC
Hi, Thanks for the reply. By "every where else", I literally mean that :) What I see in the admin panel for this script: `ID 75322 edit delete Name Fumigación Aérea Subcategory of Agricultura` [download] ..and in the interface for viewing the records: (user interface) `Fumigación Aérea` And a similar system to phpmyadmin (called mySQLMan): `75300 Fumigación Aérea 75299 75299 1 Agricultura/Fumigación Aérea` The only place the accents are not working is in phpmyadmin :( I'm just trying to get user setup so I can access it from a local hosted GUI interface for mySQL (HeidiSQL)... I'll post once I've managed to do a test with that TIA Andy	[reply] [d/l] [select]
Re^3: UTF8 driving me up the wall again! by remiah (Hermit) on Sep 04, 2012 at 13:22 UTC
When you see utf-8 string 'Fumigación Aérea' with iso-8859-1 encoding, you will see this `FumigaciÃ³n AÃ©rea` [download] So, I guess if you change browser's encoding to UTF-8 manually, you will see what you want. So, check PHPMyAdmin's configuration. You will see "misc" menu at right side of menus, there will be "variables" pull down entry. At that entry, You will see configuration key, value pairs. What is "character set ***" values? I am sorry for my lack of knowledge how to set these values, I am not good at mysql.	[reply] [d/l]
Re^4: UTF8 driving me up the wall again! by ultranerds (Hermit) on Sep 04, 2012 at 13:57 UTC
Re^5: UTF8 driving me up the wall again! by remiah (Hermit) on Sep 04, 2012 at 21:16 UTC
Re: UTF8 driving me up the wall again! by Neighbour (Friar) on Sep 04, 2012 at 11:20 UTC
There are lots of 'environments' that you could check for UTF8-compatibility: Whatever console you are running your perl scripts from The table-definition in your database The connection to your database phpmyadmin and everything related (webserver, php etc) Some are trivial, others not so much :) Does it work when you are inserting the data using phpmyadmin? What about other mysql-clients...what do they show when retrieving the data? What is shown when you query the data from the database using a perl script and dump it to (your UTF8-supporting) console? Maybe phpmyadmin is borked. Maybe your connection to mysql is not set to UTF8, which causes the mysql-driver to convert your UTF8-data to latin1 before it ends up in the database. Maybe it's something else entirely.	[reply]
Re^2: UTF8 driving me up the wall again! by ultranerds (Hermit) on Sep 04, 2012 at 12:51 UTC
Hi, Mmm ok - its looking like a phpmyadmin issue :( I just added a test record from the admin panel, and its working fine. As soon as I try and edit it from phpmyadmin, for example "ábc ítest" then it comes up all broken when viewing the data... yet it works fine elsewhere. I'll get my host to take a look at phpmyadmin, as its looking like its a configuration error there somewhere (its a system resource, so I don't have access to edit the settings). I wish I'd realized this 3 hours ago - would have saved myself a lot of stress :s Thanks for the replies though guys :) Andy	[reply]
Re: UTF8 driving me up the wall again! by Anonymous Monk on Sep 04, 2012 at 11:27 UTC
from phpmyadmin ... am I missing anything? Easy search phpmyadmin utf -> php - Latin characters in phpMyAdmin with UTF-8 collation - Stack Overflow	[reply]
Re: UTF8 driving me up the wall again! by rpnoble419 (Pilgrim) on Sep 04, 2012 at 16:22 UTC
if you are using MySQL then you may want to try the following connection. `our $dbh = DBI->connect($dsn,'User','Pass',{mysql_enable_utf8 => 1}) o +r die "$DBI::errstr\n";` [download] also don't forget to include the following line at the head of your script `use utf8;` [download]	[reply] [d/l] [select]
Re^2: UTF8 driving me up the wall again! by Corion (Patriarch) on Sep 04, 2012 at 16:25 UTC
The utf8 documentation says (bold as found in the documentation): Do not use this pragma for anything else than telling Perl that your script is written in UTF-8. ... so that won't help at all with encoding troubles related to input and output of data, which is what the post seems to be about.	[reply]
Re^2: UTF8 driving me up the wall again! by ultranerds (Hermit) on Sep 04, 2012 at 16:27 UTC
Thanks - its actually working fine in the perl side of things - just seems to be phpmyadmin thats not liking it :( I've even tried setting up the latest version in a www folder... and that has the same problem. Guess I'll have to keep digging around :( (as far as I can see I'm doing everything correctly in the perl/mysql side... so I can't see whats causing it).... the joys of unicode :(	[reply]