0

I have problems with the output of icons within a text from a mysql database (InnoDB DEFAULT CHARSET=utf8) via php (online - PHP version 7.3.23).

Text like this:

Alles gut✅✅✅

In the phpMyadmin view (OFFLINE and ONLINE) they are displayed correctly to me. I also have no problems reading them in the script offline (PHP version 7.4.24) on the local server (Apache) in order to display them to me. Online with almost the same script I only get the pictures displayed as question marks. It is also interesting that I have to edit the text online with utf8_encode. Offline it just works without that to show me other special characters. This is absolutely the only thing in which the scripts now differ online and offline. What did I do wrong online?

Does anyone know what to do and can give me a tip? What do I have to consider? Is there a safe way to output it?

Shenya
  • 334
  • 1
  • 3
  • 14
  • Does this answer your question? [How to store Emoji Character in MySQL Database](https://stackoverflow.com/questions/39463134/how-to-store-emoji-character-in-mysql-database) – Jacob Mulquin Dec 17 '21 at 10:39
  • Thank you for the fast reaction. In the database, the icons are stored correctly, as it looks like. (There are no problems with the output in phpMyAdmin local and online) I need a solution for my own PHP output script. The thing is the right output, not the input I guess. – Shenya Dec 17 '21 at 10:55

2 Answers2

1

When you're viewing the page "online", you're loading the page through HTTP; in addition to that, it's likely your script outputs HTML instead of plaintext (?). It's unclear.

Either way, HTTP adds 1 additional layer of text encoding into the mix (check the HTTP headers in your browsertools with F12 -> Network tab -> Refresh the page). HTML adds another couple layers into the mix, mainly within the <head> section. In both cases you want to see if any (text/character)encodings are mentioned, if it isn't UTF-8 (like your database), such as say UTF-16 or ISO-8859-1, then that may be the root of your problem.

Another possibility is that you've treated the text in a non-multibyte fashion since it was retrieved from MySQL. PHP unfortunately still has quite a few functions like that. An example of that would be if you used substr() instead of mb_substr(). You could rule this possibility out by printing the string value directly after it was retrieved from the database (before doing any manipulation or concatenation or the like).

Another option may be that you're actually describing a font issue instead of an encoding issue. Different fonts support different segments of unicode, so that could be your problem as well. PHPMyAdmin may well be using a different font than your webpage.

A fourth option is related to the database connection (in php). Databases define the type of character encoding at multiple levels: every database selects a charset*, every table, every char/varchar/text field individually; but (more relevantly), each connection has its own encoding. in your mysqli_connect/PDO::connect-style function, you could have another characterset selected.

* = As you had already determined yourself aswell, the problem cannot be with the database itself.

A fifth option is, basically identical to the fourth: it is possible that your database server has the wrong charset defined as per-connection default. In such a situation it could be that phpmyadmin defines its own per-connection charset (and thus overrules the mysql default), but your own website doesn't manually select one and thus gets the bad default. This option seems the least likely, but it is technically possible.

Raxi
  • 2,452
  • 1
  • 6
  • 10
  • Thanks for the interesting answer. But the problem is also that it works locally with almost the same script. So maybe it is the communication between MySQL and PHP that I mean. The text is only output with a str_replace. And it works locally. It is also interesting that I have to edit the text online with utf8_encode. Offline it just works without that to show me other special characters! This is absolutely the only thing in which the scripts now differ online and offline. – Shenya Dec 17 '21 at 11:29
  • Could you show the relevant script code ? (mainly around the connect() and query() statements, and any lines where you manipulate or print/echo the column that contains this icon); or post the url if you don't care about keeping that private; -- the fact that you have to use utf8_encode in one situation but not in the other suggests the problem is likely encoding related. – Raxi Dec 17 '21 at 11:31
  • while($row = $result -> fetch()) { $string .= str_replace('\\','',utf8_encode($row['col'])); } echo $string; utf8_encode () is only used online. – Shenya Dec 17 '21 at 11:39
  • best to edit it into your original question, using the code `{}` button/markup. half your code gets eaten here :) and add the db connect statement aswell if you can – Raxi Dec 17 '21 at 11:43
  • The `str_replace()` looked like a likely cause, however the fact that you had to use `utf8_encode` in that location (if you indeed had to), actually suggests the problem is with the per-connection charset. Because you're doing a **iso->utf8** conversion right after `fetch()`ing the data from a UTF8 database table, which should only be needed if it got converted to *iso8859* along the way – Raxi Dec 17 '21 at 11:48
0

I used to have them with me when I asked. It was definitely missing here, although the special characters were output without any problems. That was the solution apparently.

$ db-> exec ("set names utf8");

It definitely just works with this line. Maybe there are other options. Thank you very much for your answers. I'll probably take a closer look at all of this.

The only question that remains for me to understand is which Unicode or Html entity is behind it. Because they are shown to me in black and white instead of colored, for example in the PDF. For some I exchanged there with a picture. But I can't read others from the code. There is always the generated image or a question mark.

Shenya
  • 334
  • 1
  • 3
  • 14
  • Yea i figured that had to be it, it's not uncommon to have to define the right charset during the `connect()` or right after that using a separate `SET` statement like you did here. – Raxi Dec 17 '21 at 11:59