utf8 weird character encoding in "keyword" field only utf8 weird character encoding in "keyword" field only
 

News:

cpg1.5.48 Security release - upgrade mandatory!
The Coppermine development team is releasing a security update for Coppermine in order to counter a recently discovered vulnerability. It is important that all users who run version cpg1.5.46 or older update to this latest version as soon as possible.
[more]

Main Menu

utf8 weird character encoding in "keyword" field only

Started by flapane, June 28, 2014, 12:07:51 PM

Previous topic - Next topic

0 Members and 1 Guest are viewing this topic.

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
Flapane
www.flapane.com

Gallery
www.gallery.flapane.com

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.
Thanks!
Greg
My Coppermine Gallery
Need a web hosting account? See my gallery for an offer for CPG Forum users.
Send me money

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)
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
Flapane
www.flapane.com

Gallery
www.gallery.flapane.com

gmc

OK... one more place to specify utf8...
In /include/config_inc.php add line:
$CONFIG['dbcharset'] ='utf8';

This will force CPG to explicitly tell MySQL to use UTF8 encoding... I expect that will fix the display.
Thanks!
Greg
My Coppermine Gallery
Need a web hosting account? See my gallery for an offer for CPG Forum users.
Send me money

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.
Flapane
www.flapane.com

Gallery
www.gallery.flapane.com

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).
Thanks!
Greg
My Coppermine Gallery
Need a web hosting account? See my gallery for an offer for CPG Forum users.
Send me money