6

My database is latin1_swedish_ci but all the tables which contain foreign characters (german, turkish...) are utf8_general_ci.

Before the upgrade to php 5.6, I used 

mysql_query("SET CHARACTER SET utf8;");
mysql_query("SET NAMES utf8");

before mysql_query() and everything was displayed correctly in my page (<meta http-equiv="content-type" content="text/html;charset=UTF-8" /> in page header).

After the conversion of all mysql_query(...) to mysqli_query(id,...) and running under php 5.6, all the foreign languages are now scrambled with ? and �. Switching back to php 5.4 does not help. phpMyAdmin displays the mysql database (which has not changed) correctly.

I have looked around for a solution but nothing works... am I missing something?

What do I need to change in my code to work properly?

NaijaProgrammer
  • 2,892
  • 2
  • 24
  • 33
Paul Godard
  • 1,055
  • 1
  • 14
  • 30

3 Answers3

8

After searching again and again...

MAMP MySQL not recognizing my.cnf values in OSX

Does MySQL included with MAMP not include a config file?

http://www.toptal.com/php/a-utf-8-primer-for-php-and-mysql

Here is the solution.

In my php scripts, I had to add a charset query after connecting to database.

$con=mysqli_connect($host",$user,$password,$db);
mysqli_set_charset($con,"utf8");

The previous charset & names I used with mysql_query() up to php 5.4 were not enough anymore.

mysqli_query($con,"SET CHARACTER SET utf8;");
mysqli_query($con,"SET NAMES utf8");

On my local server, I also had to recompile mysql after adding a my.cnf file containing the following lines :

[client]
default-character-set=utf8
[mysql]
default-character-set=utf8
[mysqld]
default-storage-engine = InnoDB
character-set-client-handshake
collation-server=utf8_general_ci
character-set-server=utf8
[mysqld_safe]
default-character-set=utf8

I also had to add the utf8 charset to MAMP by editing the MAMP/Library/share/charsets/index.xml and adding the folling lines :

<charset name="utf8">
  <family>Unicode</family>
  <description>UTF8 Unicode</description>
  <alias>utf8</alias>
  <collation name="utf8_general_ci" id="33">
   <flag>primary</flag>
   <flag>compiled</flag>
  </collation>
  <collation name="utf8_bin"        id="83">
    <flag>binary</flag>
    <flag>compiled</flag>
  </collation>
</charset>

On my web server, the 2 steps above were not necessary.

Community
  • 1
  • 1
Paul Godard
  • 1,055
  • 1
  • 14
  • 30
  • Thanks @paulgodard. Querying this `SET CHARACTER SET utf8` and `SET NAMES utf8` worked for me! Please select this as the answer if it worked for yourself. – James Wong Sep 20 '18 at 04:16
  • This answer should be checked as the Best Answer. – HF_ Jan 23 '19 at 15:40
1

These gibberish characters are the result of a browser or other user-visible software package rendering utf-8 characters as if they were ASCII or Latin-1.

EDIT In the Chrome browser, you can view the encoding with which your browser is rendering your page. Click the chrome menu (three little horizontal lines) in the upper right corner. Click More Tools> Click Encoding> Then you will see a choice of character sets. Try choosing a different one.

Try putting this line into your HTML documents' <HEAD> sections.

<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />

That may force the browser to use the correct character set.

END edit

After your switch to mysqli, did you keep the SET CHARACTER SET and SET NAMES queries as you opened up your mysql session? If not, put them back and see if it helps.

It's possible your database is working correctly but php is telling browsers you're using Latin-1. In fact that's very likely because phpmyadmin is working properly.

Try doing the things suggested here: Setting PHP default encoding to utf-8?

There's a lot of conceptual confusion around character sets and collations. When you say

My database is latin1_swedish_ci

you mean that the default character set for a newly defined table is latin1 and the default collation is case-insensitive Swedish. These are only defaults. What counts for data storage is the character set used for each column. You say that's utf8. The collation (utf8_general_ci) only counts for searching and matching.

Community
  • 1
  • 1
O. Jones
  • 103,626
  • 17
  • 118
  • 172
  • "Conversion" is rather vague. There were several steps; please elaborate. Also, provide `SELECT col, HEX(col) FROM tbl WHERE ...;` for some non-ascii string. That will help determine the extent of the damage. – Rick James Aug 04 '15 at 23:18
  • By conversion I mean : changing all the instances of the old php mysql functions by the new mysqli. That is absolutely all that I did, so my website would display correctly under php 5.6. Before that conversion, all the foreign characters were displayed correctly. – Paul Godard Aug 05 '15 at 02:23
  • The database is latin but all the tables and fields containing foreign characters are utf8. Nothing was changed on the database, tables or fields during the conversion. – Paul Godard Aug 05 '15 at 02:25
  • The suggested solution on the link Ollie provided... adding the line default_charset = "utf-8" in php.ini file did not help on my local server. Besides, will that change not affect other website containing only latin characters? – Paul Godard Aug 05 '15 at 02:34
  • IMO, the database is working properly as it was working fine with the old php mysql functions, and because phpmyadmin displays it correctly. BTW I confirm that I am still doing the SET CHARACTER SET and SET NAMES mysqli queries before any reading of data from the databse. – Paul Godard Aug 05 '15 at 02:36
  • I did another search on the subject, but I do not see anything relevant... please help. The site looks very bad in foreign language... [link]http://www.pelicanworldwide.com/ – Paul Godard Aug 05 '15 at 12:03
  • Do you have `$mysqli_obj->set_charset('utf8');`? – Rick James Aug 28 '15 at 02:38
  • To verify that the data in the tables is 'correct', do `SELECT col, HEX(col) FROM ... WHERE ...` and present it here to see if it is utf8. – Rick James Aug 28 '15 at 02:39
  • Your web site looks ok to me, in Chrome, in english, german, russian, and turkish: no garbage characters are rendered. You seem to have added the meta content-type utf8 tag I suggested at the top of the pages. (If I change the Chrome character set to something like Turkish or Latin 1 I get garbage characters galore.) – O. Jones Aug 28 '15 at 12:06
0

I had the same problem. Not sure if this is helpful, but as a noob, there is no way I was able to follow or implement the complexities of the answer to this issue.

First, I added this to my html page: <meta charset="utf-8"/>. Then, I went into PHPMyAdmin, clicked on the column in which I saw Diamond-circled Question Marks in my browser, and simply reset the encoding to UTF-8 at the database level.

I hope it's helpful. Certainly was easy for me.

PHPMYADMIN

David Weisser
  • 117
  • 1
  • 10