With PHP and MySQL, how do I properly write smart quotes to the database?

First, make sure your MySQL table is using UTF-8 as its encoding. If it is, it will look like this:

mysql> SHOW CREATE TABLE Users (
...
) ENGINE=InnoDB AUTO_INCREMENT=30 DEFAULT CHARSET=utf8 |

Next, make sure your HTML page is set to display UTF-8:

<html>
    <head>
        <meta http-equiv="content-type" content="text/html;charset=UTF-8" />
    </head>
    ....
</html>

Then it should work.

EDIT: I purposefully did not talk about collation, because I thought it was already considered, but for the benefit of everyone, let me add some more to this answer.

You state,

I have the charset set to UTF-8 … in the MySQL table collation.

Table collation is not the same thing as charset.

Collation is the act of automagically trying to convert one charset to another FOR THE PURPOSES OF QUERYING. E.g., if you have a charset of latin1 and a collation of UTF-8, and you do something like SELECT * FROM foo WHERE bar LIKE '%—%'; (UTF-8 U+2014) on a table with a charset of latin1 that match either L+0151 or U+2014.

Not so coincidentally... if you were output this latin1 encoded character onto a UTF-8 encoded web page, you will get the following:

This is a â€œtestâ€.

That seems to be the output of your problem, exactly. Here's the HTML to duplicate it:

<?php
$string = "This is a “test”.";
?>
<html>
    <head>
        <meta http-equiv="Content-Type" content="text/html;charset=utf8"/>
    </head>
    <body>
        <p><?php echo $string; ?></p>
    </body>
</html>

Make sure you save this file in latin1...

To see what charset your table is set to, run this query:

SELECT CCSA.character_set_name, TABLE_COLLATION FROM information_schema.`TABLES` T,
       information_schema.`COLLATION_CHARACTER_SET_APPLICABILITY` CCSA
WHERE CCSA.collation_name = T.table_collation
  AND T.table_schema = "database"
  AND T.table_name = "table";

The only proper results for your uses (unless you're using multiple non-English languages) is:

+--------------------+-----------------+
| character_set_name | TABLE_COLLATION |
+--------------------+-----------------+
| utf8               | utf8_general_ci |
+--------------------+-----------------+

Thanks for the upvotes ;-)

With PHP and MySQL, how do I properly write smart quotes to the database?

Table collation is not the same thing as charset.

Related

Recent Posts