Cannot insert ♥ character in MySQL table

Question

I am trying to insert a ♥ into a MySQL table with PHP. It comes from an input field.

The table's charset is utf8_general_ci, and in PHP I use mysql_query("SET NAMES 'utf8'"); right after connection is made.

But the ♥ just becomes a '?' when inserted.

I have also tried inserting ♥ into the table from phpMyAdmin, but it returns this error:

Warning: #1366 Incorrect string value: '\xE2\x99\xA5' for column 'subject' at row 1

The result is also '?' instead of ♥.

Any ideas on what causes this?

Answer 1

This is because of incompatible character set and collation defined on table's column.

Try changing character set of your table or column to UTF8.

ALTER TABLE table_name CONVERT TO CHARACTER SET utf8;

or

ALTER TABLE table_name MODIFY col VARCHAR(255) CHARACTER SET utf8;