0

Hi I am storing some text in a mysql column. The error I am getting is whenever there is an apostrophe (') in the text it's getting displayed as '’'even though apostrophe is being displayed in the database Any ideas what would be the error? I have spent days but no luck

You can check the error here : http://beta.writiely.com/

Here how it's connecting and fetching result

$conn = new mysqli($servername, $username, $password, $dbname);

if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

$sql = "SELECT * from textDocs";
$result = $conn->query($sql);

$res = array();
if ($result->num_rows > 0) {
    while($row = $result->fetch_assoc()) {
array_push($res,array('t'=>$row['t'],'co'=>$row['cover'],
     'text'=>$row['txt']));
    }
} else {
    return false;
}
$conn->close();
Ishan Garg
  • 178
  • 2
  • 11
  • What is the charset of your MySQL table vs your page? Seems to me like you must have a difference there. It would be best to keep everything in utf-8 if you can. – dmgig Mar 14 '16 at 18:34
  • it's utf8_general_ci, that's the first thing I checked I even tried changing it to to utf8_bin but no luck – Ishan Garg Mar 14 '16 at 18:35
  • 1
    Show how you query this database, and what is the database encoded as? (charset != collation) – Qirel Mar 14 '16 at 18:35
  • @Qirel - that's right, the charset. My mistake. I updated my comment. – dmgig Mar 14 '16 at 18:36
  • Possible duplicate of [UTF-8 all the way through](http://stackoverflow.com/questions/279170/utf-8-all-the-way-through) – chris85 Mar 14 '16 at 18:37
  • charset is utf8 and I am using the normal select * query – Ishan Garg Mar 14 '16 at 18:37
  • My point was just that even if the collation is set to say `utf8_general_ci`, it's not necessarily that the charset is utf8 as well. – Qirel Mar 14 '16 at 18:37
  • @user3772366 How did you check that the charset is utf8? Did you just look at the collation-field? Also, what I ment by *Show how you query this database* is the code for that query in PHP - the connection included. – Qirel Mar 14 '16 at 18:38
  • Besides charset issue, you have an issue with html entities. If you save html in db using htmlentities, then you have to output result without using htmlentities again (there is a lot of ` ` in your page). – fusion3k Mar 14 '16 at 18:41
  • @Qirel I ahve updated my question with the code – Ishan Garg Mar 14 '16 at 18:47
  • @fusion3k how can i output with keeping the html tags? – Ishan Garg Mar 14 '16 at 18:48
  • Try adding `$conn->set_charset("utf8");` right after creating the connection, specifying the charset there. As for the reverse function of `htmlentities()`, that's `html_entity_decode()`. – Qirel Mar 14 '16 at 18:49
  • @Qirel tried that :(. Even tried 'utf8mb4' as another answer suggested doesn't seem to work...the mysql version is 5.5.45 and my server charset is UTF 8 Unicode – Ishan Garg Mar 14 '16 at 18:56

1 Answers1

1

’ is the Mojibake for the utf8 RIGHT SINGLE QUOTATION MARK, which is not normally used in computer circles. (You probably got it from some Word processing package.)

htmlentities should not be relevant to this discussion. If the web page has ’, you have a big mess. I don't think it has ’ based on what you have said.

Yes, $conn->set_charset("utf8"); is needed. And this is usually the fix for this type of problem.

However, you also need the table/column to be CHARACTER SET utf8, is it? Do SHOW CREATE TABLE to check.

utf8 and utf8mb4 do not differ in this context. The latter is needed for Emoji and some of Chinese.

The "collation" (such as utf8_general_ci) is not relevant to this discussion. However, utf8_general_ci applies only to utf8, so some of the comments were unambiguous.

Summary:

  • Your bytes are apparently utf8,
  • Do the set_charset,
  • Check the table/column,
  • HTML4 needs <meta http-equiv="Content-Type" content="text/html;charset=UTF-8">; HTML5 can shorten it: <meta charset="UTF-8"> (but apparently this is not the issue).
Rick James
  • 135,179
  • 13
  • 127
  • 222