1

I have a website hosted on a GoDaddy (Shared Windows server) which I wrote some time ago using procedural PHP and a MySQL database. Some tables in the database hold data that is inserted by users through some pages and in some cases they insert Greek text. Everything was working fine for the last 3-4 years until recently when I was informed that the website was down for some reason. I contacted GoDaddy, they told me that it must be an issue with my scripts but I have not made any changes to the code for a long time now. Anyway, apparently it was resolved the same day without me doing anything so I guess it was an issue with their servers, although they told me that nothing was changed from their side.

The problem is that since then, all the pages which display Greek characters selected from the database show them like this: Άλλο Αίτημα instead of proper Greek text. I know that they were stored like that in the database (when I access it with phpMyAdmin) but I thought this was normal until now because when they were selected and displayed in the pages they were fine. So I don't know what has happened now and they stopped appearing correctly. By the way, Greek characters (not from the database) are displayed correctly in the pages. I've searched the internet a lot about this and I have tried all possible solutions, I believe. Some things I have checked (copied from another SO answer):

  • All PHP files are saved as UTF-8 encoding (without BOM, using Notepad++)
  • MySQL database, tables and columns are all set to utf8_unicode_ci
  • PHP's default character set is UTF-8

I use the following code in my header file:

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

and then in html head:

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

I use the following code to connect to the database in my db connection file:

$dbc = mysqli_connect (DB_HOST, DB_USER, DB_PASSWORD, DB_NAME) OR die ('Could not connect to MySQL: ' . mysqli_connect_error() );
mysqli_set_charset($dbc, 'utf8');

The server is running PHP version 5.2.17 and MySQL version is 5.0.96.

Another weird thing is that when I export the database and import it locally (using XAMPP) and using the same PHP files as on the website, Greek characters are displayed correctly on the pages. However, XAMPP on my laptop is running PHP version 5.4.7 and MySQL is version 5.5.27.

Any suggestions would be greatly appreciated.

Thanks

Isaac Bennetch
  • 11,830
  • 2
  • 32
  • 43
Kyris
  • 95
  • 1
  • 9
  • How are you saving Greek letters in database? I mean your query to insert data in database. – Haris Feb 12 '17 at 19:02
  • Hi @HK007 and thanks for your comment. I just use `mysqli_query($connection,$query)` where query is in the form of `INSERT INTO table (col1, col2) VALUES ('$val1', '$val2')`. – Kyris Feb 13 '17 at 17:14
  • Use `mysqli_set_charset($connection,"utf8");` just after your database connection code and do not send any php headers to set character set else in the code. – Haris Feb 14 '17 at 14:26
  • @HK007 you mean that `header('Content-Type: text/html; charset=utf-8');` is the reason for having this issue?? – Kyris Feb 14 '17 at 15:20
  • May be or may be the reason is that you're encoding it multiple times. Because the same issue was with me and in my case I was encoding multiple times before saving in database. – Haris Feb 14 '17 at 15:28

2 Answers2

1

Are you expecting Άλλο Αίτημα? Do SELECT HEX(col)... -- This would be correct hex: CE86 CEBB CEBB CEBF 20...; this would be "double encoded": C38E E280A0 C38E C2BB C38E C2BB C38E C2BF 20...

Read about Mojibake and Double encoding.

Community
  • 1
  • 1
Rick James
  • 135,179
  • 13
  • 127
  • 222
  • Hi and thanks for your reply. the result of SELECT is this: `C38EC2A5C38EC2A0C38EC2A3C38EE28098C38EC2A1C38EC2A7...`. So it's double encoded. What is a possible solution for this problem then? – Kyris Feb 13 '17 at 07:37
  • I tried reading here [Fixes](http://mysql.rjweb.org/doc.php/charcoll#fixing_double_encoding_) but I'm not sure which approach to take.. Please advise. Thanks – Kyris Feb 13 '17 at 07:51
  • See [_this section of that page_](http://mysql.rjweb.org/doc.php/charcoll#fixes_for_various_cases) on how to fix the _data_. (I'm sorry that that web page is such a jumble.) Notice that there are two cases for double-encoding, depending on your charset. – Rick James Feb 13 '17 at 16:49
  • Thanks Rick! I have replaced all the affected columns and I can see that now they are readable in phpmyadmin. However, I had to comment out the line `mysqli_set_charset($dbc, 'utf8');` in mysql.php file in order to see the data properly in the pages (I have no idea why). However, how am I going to ensure that they are entered properly from now on in the database tables? As I have told HK007 in the comments above, I simply use `mysqli_query($connection, $query);` for inserting data into these columns, where $query is in the form of `INSERT INTO table (col1, col2) VALUES ('$val1', '$val2');` – Kyris Feb 13 '17 at 17:35
  • Another weird thing just happened. So I updated all the rest of the tables which contained these encoded greek characters and then tried refreshing the original page which was previously fixed. Although the tables I updated later do not affect that page, it seems that the data in that page are now displayed simply with question marks (?). I resolved it by **uncommenting** now the line with the `mysqli_set_charset($dbc, 'utf8');` code and this fixed the issue. Please let me know, however, if you have any idea how to ensure proper insertion/updating of these columns from now now.. Thanks – Kyris Feb 13 '17 at 18:06
  • '???' -- Go back to the link in my Answer, search for question marks. Double check the HEX for the data in the table; Greek hex will be `CExx` or `CFxx`. `set_charset()` is important. – Rick James Feb 13 '17 at 18:17
  • Yes that was fixed with `mysqli_set_charset()`. Is there something I should do to ensure that future inserts or update will not mess up the data again? – Kyris Feb 13 '17 at 18:28
  • My link has a "Best Practices"; I don't know of anything better. I have been fighting this battle since I first accidentally stumbled into "double encoding" a decade ago (in my own coding!). The more I tried to come to grips with the issue, the more variants I encountered. – Rick James Feb 13 '17 at 18:47
0
default_charset = "utf-8";

Using htaccess files

AddDefaultCharset UTF-8

test:

<?php header("content-type: text/html;charset=utf-8") ?>
<!doctype>
<html>
<head>
    <meta charset="utf-8">
</head>
<body>
    <?php echo "α β γ δ ε ϝ ϛ ζ η θ ι κ λ μ ν ξ ο π ϟ ϙ ρ σ τ υ φ χ ψ ω ϡ" ?>
</body>

  • Thanks for your answer but, as I said, the server is on Windows so there is no htaccess file. I also said that the issue is only with Greek characters from the database and that all other Greek text (from php scripts) is displayed correctly. – Kyris Feb 12 '17 at 18:57
  • when browse data throw phpmyadmin is data shows correctly or same "Άλλο" ? –  Feb 12 '17 at 19:02
  • No, the same. But I think this was normal until now because when they were selected and displayed in the pages they were fine until this week. – Kyris Feb 12 '17 at 19:36
  • No this is how it always was and it was working fine. And this is how it is on my localhost (same db exported, using xampp) and they get displayed correctly.. – Kyris Feb 12 '17 at 19:50
  • try use different browser and can you post any value from db here –  Feb 12 '17 at 20:13