Query special characters from mysql db

Question

I am using PHP 7.1.8 and I am trying to query my db. However, the serialized arrays have special characters, which are shown as the following: �

Find below an example of my code:

// connect to db
$dbname = $conf['dbName'];
$dbuser = $conf['user'];
$dbpass = $conf['pwd'];
$dbhost = $conf['host'];

// Create connection
$conn = new mysqli($dbhost, $dbuser, $dbpass, $dbname);

$arr = $conn->query("SELECT * FROM postmeta WHERE post_id = "100" and meta_key = 'val' LIMIT 1;")->fetch_assoc()["meta_value"];

When querying the database directly, I get the correct value for �. See below my ``$arr`:

This error results basically in that I cannot unserialize the data correctly.

Any suggestions how to fix this error?

I appreciate your replies!

Check [this](https://stackoverflow.com/questions/13433734/php-mysql-set-names-utf8-collate-utf8-unicode-ci-doesnt-work-with-mysqli?rq=1) and [that](https://stackoverflow.com/questions/2159434/set-names-utf8-in-mysql/36511896) — Thomas G, Sep 08 '18 at 10:10

score 2 · Accepted Answer · answered Sep 08 '18 at 09:16

2

The problem lies in collation. Your MySQL returns specific symbols (temperature symbol) that is not supported by your current encoding.

Try to execute this query right before your selects: set names 'utf8';

Also, if you're showing this data in some kind HTML response, don't forget to add charset=UTF-8 to your meta tag of Content-type.

answered Sep 08 '18 at 09:16

Victor Perov

1,697
18
37

Thx for your reply! Do you mean that I should execute like that before my real query `$conn->query("set names 'utf8';");` ? – Carol.Kar Sep 08 '18 at 09:19
yes, you should "told" to the database that you're going to have utf8 answers – Victor Perov Sep 08 '18 at 09:26
From the manual SET NAMES is used to indicate what character set the client will use to send SQL statements to the server. For example: SET NAMES 'cp1251' tells the server, “future incoming messages from this client are in character set cp1251.” It also specifies the character set that the server should use for sending results back to the client. (For example, it indicates what character set to use for column values if you use a SELECT statement.). Normally the most used and compatible set names is utf8 so its recommended you use it to avoid such issues. – Sigma Sep 08 '18 at 09:30

score 1 · Answer 2 · answered Sep 09 '18 at 18:47

$conn = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
/* change character set to utf8 */
if (!$conn->set_charset("utf8")) {
    printf("Error loading character set utf8: %s\n", $conn->error);
    exit();
} else {
    printf("Current character set: %s\n", $conn->character_set_name());
}
$arr = $conn->query("SELECT * FROM postmeta WHERE post_id = "100" and meta_key = 'val' LIMIT 1;")->fetch_assoc()["meta_value"];

Please check the output.

Query special characters from mysql db

2 Answers2