Strange Characters in database text: Ã, Ã, ¢, â‚ €,�

[SRD75]

I'm not certain when this first occured.

 

I have a young drop-shipping affiliate website, and receive an exported copy of the product catalog from the wholesaler. I format and import this into Prestashop 1.4.4.

 

The front end of the website contains combinations of strange characters inside product text: Ã, Ã, ¢, â‚ €, etc.

 

These characters are present in about 40% of the database tables, not just product specific tables like ps_product_lang.

 

Another website thread for software unrelated to Prestashop says this same problem occurs when the database connection string uses an incorrect character encoding type.

 

In /config/setting.inc, there is no character encoding string mentioned, just the MySQL Engine, which is set to InnoDB, which matches what I see in PHPMyAdmin.

 

I exported ps_product_lang, replaced all instances of these characters with correct characters, saved the CSV file in UTF-8 format, and reimported them using PHPMyAdmin, specifying UTF-8 as the language.

 

However, after doing a new search in PHPMyAdmin, I now have about 10 times as many instances of these bad characters in ps_product_lang than I started with.

 

If the problem is as simple as specifying the correct language attribute in the database connection string, where/how do I set this, and what to?

[SRD75]

UPDATE: PHPMyAdmin says:

 

MySQL charset: UTF-8 Unicode (utf8)

 

This is the same character set I used in the last import file, which caused more character corruptions. UTF-8 was specified as the charset of the import file during the import process.

[phrasespot]

There is actually more than charset to it. It is almost a black art

 

To start with you have character sets then the collation settings, and it is not only the storage but the communication between web server and the DB server. When you export and import, you are involving four way communications hurdle (old webserver<->DB, new webserver<->DB server) and the settings for each of those must be correct not to have any character issues.

 

Below are the relevant settings in MySQL configuration

 

character_set_client

character_set_connection

character_set_database

character_set_filesystem

character_set_results

character_set_server

character_set_system

collation_connection

collation_database

collation_server

 

And here is a link that will give a little more insight into charset issues.

[SRD75]

Thanks for the reply. I am saving the file in Open Office explicitly as UTF-8, so is it safe to assume the file is now in a proper UTF-8 format?

 

If PHPMyAdmin can import UTF-8 files into a latin1 database, does PHPMyAdmin do the translation, or does the MySQL server?

 

I ran an SQL command in PHPMyAdmin to display the following settings:

• character_set_client utf8
  
• character_set_connection utf8
  
• character_set_database latin1
  
• character_set_filesystem binary
  
• character_set_results utf8
  
• character_set_server latin1
  
• character_set_system utf8
  

[phrasespot]

Thanks for the reply. I am saving the file in Open Office explicitly as UTF-8, so is it safe to assume the file is now in a proper UTF-8 format?

I did not test this but it should, just make sure a byte-order mark is not set in the file created.

 

If PHPMyAdmin can import UTF-8 files into a latin1 database, does PHPMyAdmin do the translation, or does the MySQL server?

Did you read the link I posted? "Several character set and collation system variables relate to a client's interaction with server", client=PHPMyAdmin, server=MySQL and the answer depends on those collation and character set system variables. The link answers your question, with examples.