utf8 weird character encoding in "keyword" field only

[flapane]

I've just noticed that some special characters are displayed well in the title but not in the keyword field. Please see http://www.flapane.com/gallery/displayimage.php?album=43&pid=2152#top_display_media where the letter ä is displayed as capital A with a tilde on it followed by a symbol in the keywords.
The page is in utf-8, however I see that the sql dbase is in latin1_swedish_ci. The dbase has been created under cpg1.3 or cpg1.4 years ago, but frankly I don't know why such encoding has been selected while creating the dbase.

Any hints?
Thanks in advance

[gmc]

You appear to be running 1.5.26. Please upgrade to 1.5.28 and see if it addresses it.
If not, does the data appear correct when viewed with a tool like phpMyAdmin? (direct in table)

See thread http://forum.coppermine-gallery.net/index.php/topic,77355.msg373996.html#msg373996 for converting and 'fixing' data not stored correctly (also references the fix in 1.5.28).
Even if 1.5.28 fixes your problem, converting the database/tables to utf8 is a good idea - especially when 'non-Latin' characters are in use.

[flapane]

Woops, I forgot to upgrade to 1.5.28.
Thanks, it did the trick.
Also, it seems that special characters are not shown correctly in PhpMyAdmin. Attached are screenshoots from _dict and _languages tables.

So I followed your instructions for, let's say, _pictures table (where letter ì in captions and titles are shown as ì in PhpMyAdmin, but are shown correctly in an utf-8 web page)

Code Select Expand

ALTER TABLE cpg132_pictures CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

UPDATE cpg132_pictures SET title= CONVERT(CAST(CONVERT(title USING latin1) AS binary) USING utf8);

UPDATE cpg132_pictures SET caption= CONVERT(CAST(CONVERT(caption USING latin1) AS binary) USING utf8);

UPDATE cpg132_pictures SET keywords= CONVERT(CAST(CONVERT(keywords USING latin1) AS binary) USING utf8);


Now ì is shown as ì in PhpMyAdmin, but it's become an ugly � in an utf-8 web page (Firefox).
Character encoding is set to utf-8 in cpg config and at web server level (AddDefaultCharset UTF-8).

In theory v1.5.28 fixed the issue, but it wouldn't hurt if tables could be converted to utf-8.

Thanks

[gmc]

OK... one more place to specify utf8...
In /include/config_inc.php add line:

Code Select Expand

$CONFIG['dbcharset'] ='utf8';

This will force CPG to explicitly tell MySQL to use UTF8 encoding... I expect that will fix the display.

[flapane]

Thanks, it worked great! Now I converted all the tables, taking care of the columns where "strange" data was present.
I wonder why it needs to be forced and it doesn't automatically recognize the encoding of the sql tables, and if it due to my web server (shared hosting) configuration.

[gmc]

Support for $CONFIG['dbcharset'] ='utf8'; was added specifically for cases where things don't synch properly.. In theory it shouldn't be needed - as the table setting should override.
MySQL tries to 'fix' mismatches - and doesn't always choose right. I haven't been able to figure out what settings (MySQL installation) drive this. We have had people need to add that statement with a change of hosts.. Or have characters start displaying incorrectly needing the conversion steps to resolve.

Please mark your topic as solved (bottom left of page).