kentor kentor - 4 months ago 20
SQL Question

Insert Values with special Characters using PDO

I have problems inserting values with special Characters in names like "Renè". The Name "Renè" shows up in my Database like this: "René"

I tried to change the field in the database to "utf8_general_ci" which didn't help, so its probably something in my code.

The name comes from a JQuery Ajax Request and I am able to echo this name inside the called .php file correctly.

$fullname = $_POST['playersearch'];
echo "<div class='n_ok'><p>Inserted $fullname successfully (PlayerID is $id)</p></div>";


I connect to my DB using PDO and I specified the UTF-8 charset:

$db = new PDO("mysql:host=$dbhost;dbname=$dbname",$dbuser,$dbpass,array('charset'=>'utf8'));
$db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);


The insert into the DB happens here:

$stmt = $db->prepare("INSERT INTO PlayerCards (playerName, buyPercentage) VALUES (?, ?)");
$stmt->bindParam(1, $fullname);
$stmt->bindParam(2, $buypercentage);


Why does it look so weird in my MySQL Database?

Answer

As per the manual:

It specifies that the character set syntax in PDO be used as:

$options = array(
    PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8',
);

Also read the warning on that page:

Warning The method in the below example can only be used with character sets that share the same lower 7 bit representation as ASCII, such as ISO-8859-1 and UTF-8. Users using character sets that have different representations (such as UTF-16 or Big5) must use the charset option provided in PHP 5.3.6 and later versions.

Therefore and in your case, by changing

array('charset'=>'utf8')

to

array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8")

if you wish to run an initialisation command after connecting; more on this in comments below.


As stated by Phil in comments; there is no need to run an initialisation command after connecting.

Using mysql:host=$dbhost;dbname=$dbname;charset=utf8 can also be used.

The 4th argument (options array) is for specific driver options (usually PDO attributes) set on the connection.

Thank you Phil.

Comments