ISO woes :(

[richrock]

I've been tearing my hair out over this one, and I'm not 100% sure if it's a mysql problem, or a php one.  I'll figure the origins of the data reside in mysql, so I'll start there...

 

I've got a database full of mostly european towns/names, etc... I inserted this into my DB using phpmyadmin.  The problem is I seem to get variable results depending on what I do...

 

I either get the square blocks, a string of three characters (I've identified as the BOM), or incorrect characters.  This is coming from an excel file, which displays the characters perfectly.  I've tried exporting the CSV as utf8, latin1, 8859-1, all with no success...

 

I've also tried setting utf8 in both Joomla (which is my template) and the php script I've been developing.  I even tried the other options just for fun  :-\

 

The database is currently set to UTF8 for this particular table, and have tried different collations with no success.

 

What on earth am I doing wrong?  Is it too much to ask for French, German, Polish, etc characters to display in my browser properly?  This needs to work as there is a name search function which cannot work on the foreign names like 'François'...  If the database won't store it properly, people can't search for it.

 

I've tried many tutorials, but can't figure what's going on.  Any advice/pointers would be greatly appreciated.

[Mchl]

Checklist:

1. Database column set to UTF-8

2. PHP source saved as UTF-8 without BOM

3. PHP-MySQL connection set to UTF8 (using SET NAMES 'utf8' query, or mysqli's dedicated function)

4. HTML output set to UTF-8

 

It is possible that the data you already have in the database has been inserted using wrong connection encoding, and as such it will be hard or impossible to make it work properly.

[richrock]

Hi,

 

Seems  to have hit a bit of confusion now... What I thought I was doing was setting a 'generic' utf8 in the mysql DB, but now I'm not so sure...

 

I have set the db to utf8_bin.  However, in phpmyadmin there are lots of options available...

 

<optgroup label="utf8" title="UTF-8 Unicode">
<option value="utf8_bin" title="Unicode (multilingual), Binary" selected="selected">utf8_bin</option>
<option value="utf8_czech_ci" title="Czech, case-insensitive">utf8_czech_ci</option>
<option value="utf8_danish_ci" title="Danish, case-insensitive">utf8_danish_ci</option>
<option value="utf8_esperanto_ci" title="Esperanto, case-insensitive">utf8_esperanto_ci</option>

<option value="utf8_estonian_ci" title="Estonian, case-insensitive">utf8_estonian_ci</option>
<option value="utf8_general_ci" title="Unicode (multilingual), case-insensitive">utf8_general_ci</option>
<option value="utf8_hungarian_ci" title="Hungarian, case-insensitive">utf8_hungarian_ci</option>
<option value="utf8_icelandic_ci" title="Icelandic, case-insensitive">utf8_icelandic_ci</option>
<option value="utf8_latvian_ci" title="Latvian, case-insensitive">utf8_latvian_ci</option>
<option value="utf8_lithuanian_ci" title="Lithuanian, case-insensitive">utf8_lithuanian_ci</option>
<option value="utf8_persian_ci" title="Persian, case-insensitive">utf8_persian_ci</option>
<option value="utf8_polish_ci" title="Polish, case-insensitive">utf8_polish_ci</option>
<option value="utf8_roman_ci" title="West European, case-insensitive">utf8_roman_ci</option>

<option value="utf8_romanian_ci" title="Romanian, case-insensitive">utf8_romanian_ci</option>
<option value="utf8_slovak_ci" title="Slovak, case-insensitive">utf8_slovak_ci</option>
<option value="utf8_slovenian_ci" title="Slovenian, case-insensitive">utf8_slovenian_ci</option>
<option value="utf8_spanish2_ci" title="Traditional Spanish, case-insensitive">utf8_spanish2_ci</option>
<option value="utf8_spanish_ci" title="Spanish, case-insensitive">utf8_spanish_ci</option>
<option value="utf8_swedish_ci" title="Swedish, case-insensitive">utf8_swedish_ci</option>
<option value="utf8_turkish_ci" title="Turkish, case-insensitive">utf8_turkish_ci</option>
<option value="utf8_unicode_ci" title="Unicode (multilingual), case-insensitive">utf8_unicode_ci</option>
</optgroup>

 

My guess is either _bin or _unicode.  The rest of the db is set to latin1_swedish_ci, as I am developing scripts and extensions for a Joomla system, and this seems to be the default.

 

I've checked all the other options.  No dice there.  So is unicode the way to go?

[Mchl]

http://www.phpfreaks.com/forums/index.php/topic,229038.0.html

http://forge.mysql.com/wiki/How_to_Use_Charsets_and_Collations_Properly