CODEWITHSUNDEEP
phpmysqldatabase
Carol.Kar
Carol.Kar

Reputation: 5345

Query special characters from mysql db

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`:

enter image description here

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

Any suggestions how to fix this error?

I appreciate your replies!

Upvotes: 1

Views: 989

Answers (2)

Daniele
Daniele

Reputation: 129

$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.

Upvotes: 1

Victor Perov
Victor Perov

Reputation: 1764

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.

Upvotes: 2

Related Questions