CODEWITHSUNDEEP
phpmysqlmysqli
Uranium Developers
Uranium Developers

Reputation: 132

Mysqli Update UTF8

So, when I update stuff in my database it turns for example this: Š to this: u0160 it would be ok if it turned into this: \u0160 so it would display right but it doesn’t.

This is my connection file:

<?php
define('DB_SERVER', 'localhost');
define('DB_USERNAME', 'root');
define('DB_PASSWORD', 'pwd');
define('DB_DATABASE', 'database');
$db = mysqli_connect(DB_SERVER, DB_USERNAME, DB_PASSWORD, DB_DATABASE);
mysqli_set_charset($db,"utf8");
mysqli_query("set names utf8");
?>

And this is the query:

mysqli_query($db, "UPDATE schools SET subjectData='$json' WHERE id='$schoolID'");

Upvotes: 0

Views: 270

Answers (2)

Alex
Alex

Reputation: 17289

So you showed the variable value, but you didn't show the code where you extract values from database.

And another note : your value is not really utf8, it is json encoded string:

{"subject":"A","desc":"A\u017d"}

As you see, the original string is not equal to value stored in database:

{"subject":"A","desc":"Au017d"}

Saving raw json in database is not very best practice. I would suggest to save just values not objects.

But if you need this, you should use this when you insert and/or update records:

mysqli_query($db, "UPDATE schools SET subjectData='".mysqli_escape_string($json)."' WHERE id='$schoolID'");

Upvotes: 1

deceze
deceze

Reputation: 522042

This has nothing to do with UTF-8 at all, you're only handling pure ASCII values. Your issue is that a backslash, a necessary part of the JSON encoding, is disappearing as you enter it into the database. The reason for that would be that you're not escaping your data correctly. A backslash has a special escaping meaning in SQL, if you want to insert a literal backslash into the database it requires escaping. With mysqli, you should be using prepared statements:

$stmt = $db->prepare('UPDATE schools SET subjectData = ? WHERE id = ?');
$stmt->bind_param('ss', $json, $schoolID);
$stmt->execute();

See How can I prevent SQL injection in PHP? and The Great Escapism (Or: What You Need To Know To Work With Text Within Text).

Upvotes: 4

Related Questions