Saving special characters to MySQL database

Question

I am currently having an issue saving special characters (® & ©) to a mySQL database.

On a local stack (local to my development machine) the operation runs smoothly and what is entered in the front-end gets saved as what is expected.

With the same front-end code on the centralised server when saving to the DB the character is proceeded with other characters it is saved as ®. This isn't an issue when viewing the record on the front-end as it reverses what it does and displays correctly.

The issue comes from another separate system which utilises the same database and doesn't do any alteration so appears as the ® instead of simply ®.

The reason for my confusion is that it seems to work as required on my local stack but not on the centralised server.

I am looking for ideas as to what to look for which might be causing this on the servers configuration compared to my local.

Thanks in advance Mark

Answer 1

@Pranav Hosangadi (thanks) covers three areas to check for consistency of encoding. The following solution adds to that. It may also be worth considering (a variation of) @Soaice Mircea's answer (thanks also) for some scenarios whereby this answer doesn't fix the problem although this wasn't required when I was able to reproduce and find a solution to your problem. @Pranav's line of thinking seems to be successful for this problem as it's about consistency of using one character set everywhere rather than a particular one.

five things to do:

1. ensure database charset and tables use same charset throughout, inspect this in phpmyadmin for example, note and this charset for use below

2. use php header() function with database charset e.g.:

   header('Content-Type: text/html; charset=latin1_swedish_ci');

3. insert meta tag in html header e.g.:

   <meta http-equiv="content-type"
   content="text/html;charset=latin1_swedish_ci">

4. add charset-accept in form tag

   <form action=\"testsubmit.php\" method=\"post\" accept-charset=\"latin1_swedish_ci\">

5. set charset of mysql connection e.g.:

   $con = mysql_connect("localhost","test","test");      
   mysql_set_charset ( "latin1_swedish_ci", $con );
   

Answer 2

after you select the DB in php code add this:

mysql_query("set names 'utf8'"); 

also check if the column where you store the text has utf8 encoding.