0

When inserting a note into mysql, the apostrophe in the note is changed to this: ’

For example:

$id = 1;
$body = "How have things been with you? Hope it’s going well on your end...";

$q = "INSERT INTO notes (id, note) VALUES (?, ?)";
$stmt = $mysqli->prepare($q);
$stmt->bind_param('is', $id, $body);
$stmt->execute();
$stmt->close();

Saves to the database like so:

How have things been with you? Hope it’s going well on your end...

If I use a normal apostrophe it works, for example:

$id = 1;
$body = "How have things been with you? Hope it's going well on your end...";

$q = "INSERT INTO notes (id, note) VALUES (?, ?)";
$stmt = $mysqli->prepare($q);
$stmt->bind_param('is', $id, $body);
$stmt->execute();
$stmt->close();

Saves to the database like so:

How have things been with you? Hope it's going well on your end...

What is the correct way to do this so the first example apostrophe saves as expected?

Database

Name    Type        Collation
note    longtext    utf8_general_ci
sf_admin
  • 577
  • 4
  • 11

1 Answers1

0

Because the rendering of your data is changing somewhere between input on the one end, and presentation on the other, this is likely to be a problem with the character sets being provided by the mysql client application (eg php), combined with those configured on the server), and those provided to the web browser.

Don't confuse collation (which defines lexicographic order) with character set. You can ignore collation until you're comparing text.

All character sets define which character should be rendered for a certain sequence of bytes. But not all sequences of bytes represent the same character in every character set. So in addition to storing the bytes of the text, you also have to make sure you're informing the mysql connection and the http server side how you'll be handling the byte sequences.

Character sets come into play a few different ways:

  • text column definitions include a character set
  • Mysql connections have a character set that can be chosen by the client
  • mysql servers provide default character sets for
  • browsers will have default character sets for webpage rendering

All these things have to match in order for the data to be correctly stored, transferred, and rendered.

Byte values 0-127 are the same in ASCII, unicode, iso8859-1, and many other character sets, which is why most of your message did get stored properly.

On the mysql side, the client needs to inform the server of the character set for the data it provides to the server. If it tells the server the data is in, say, latin1, and then provides utf-8 data, the server has no way of knowing that the provided character set is incorrect.

On the browser side, again, the web server must inform the client of the character set of its data. If it is speaking latin1 on the mysql side, and takes that data and jams it out into a web response that is taken to be utf-8, the same data isn't going to render the same.

Various combinations of character sets in various places may end up accurately representing data accurately for some clients, but it's always incorrect for all these character sets to be anything but the same everywhere.

(https://www.mysqltutorial.org/mysql-character-set/#:~:text=A%20MySQL%20character%20set%20is,letter%20a%20is%20the%20encoding.)[this tutorial] seems to explain it pretty well if you want to think about character sets more.

erik258
  • 14,701
  • 2
  • 25
  • 31