Why won't my MySQL insert work on PHP when it contains a ® character?

Question

First as a side note, I am using the old deprecated mysql_query, but at this point I am unable to vastly change my code yet.

OK when I try to do an INSERT with a ® character in it through PHP (coming from a form POST), I'm getting a failure with this code and the insert doesn't happen:

$insert_title = "
    INSERT INTO pin (
        title, 
        front
    ) VALUES (
        '".mysql_escape_string($_POST['pintitle'])."', 
        '".mysql_escape_string($_POST['pinfront'])."'
    )";

But when I paste this into MySQL Workbench and execute the statement it does the insert just fine:

INSERT INTO pin ( title, front ) 
VALUES ( 'test', '®' )

What am I doing wrong on the PHP side?

Here is what mysql_error() is showing me:

1366: Incorrect string value: '\xAE' for column 'front' at row 1

Are you checking for errors using `mysql_error()` after the query? Also [UTF-8 all the way through](http://stackoverflow.com/q/279170) — Pekka, Dec 01 '13 at 23:51
@Pekka웃 - Just updated my question with the `mysql_error()` return — Ethan Allen, Dec 02 '13 at 00:08
@EthanAllen Are you sure, that mysql_escape_string produces the correct result for ® symbol? It looks suspicious, that the same symbol works with Workbench. — user4035, Dec 02 '13 at 00:18
Several possible answers here http://stackoverflow.com/questions/1168036/how-to-fix-incorrect-string-value-errors — Shai, Dec 02 '13 at 00:23
It's most likely a collation problem. Either the input is UTF-8 and the connection isn't, or the other way round. — Pekka, Dec 02 '13 at 00:57

Ethan Allen · Accepted Answer · 2013-12-02T20:42:07.607

3

My issue was that I wasn't properly UTF-8 encoding on the website side (PHP in my case). I just added this and it was solved:

<?php

header('Content-type: text/html; charset=utf-8');

?>

edited Dec 02 '13 at 20:42

answered Dec 02 '13 at 05:01

Ethan Allen

14,425
24
101
194

Just to point out this has nothing to do with how PHP is handling the data but rather it is telling the browser submitting the form to use UTF8. Also as another point, this won't fix the problem in older versions of IE – Tomdarkness Dec 02 '13 at 05:10
Is there a solution for older IE versions? – Ethan Allen Dec 02 '13 at 20:42

Why won't my MySQL insert work on PHP when it contains a ® character?

1 Answers1