Mysql store incorrect encoded char

Question

i'm trying to insert accented char into mysql table with encoding utf8_general_ci.

This is a simple string that i'm trying to insert:

Porto leça da palmeira

To insert that string i'm using php in this way:

$string = "Porto leça da palmeira";
$id     = "xxxx";
$sql    = "INSERT INTO city_translates (id,city) VALUES ('$id','$string')";
$dbh = new PDO("mysql:host.....");  
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$dbh->query($sql);

When i try when to access from MysqlWorkBench that string, it has been stored in this way:

Porto - Le?Ãa da Palmeira

if i try to use utf8_encode such as:

$string = utf8_encode($string);

it will be stored in this way:

Porto - Le?ÃƒÂƒÃ‚ÂŸa da Palmeira

How can i solve? What could be my error?

Thanks!

Use `$dbh = new PDO("mysql:host=$dbHost;dbname=$dbName;charset=utf8", $dbUser, $dbPass, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES 'utf8'"));` and changing the variables to your own. It should work. You can also use `$dbh->exec("set names utf8");` — Funk Forty Niner, Jul 03 '14 at 22:13
@Fred-ii- Thank you very much!!! using charset into PDO constructor fixed the problem! :) THANKS! :D — Jayyrus, Jul 03 '14 at 22:16
Might I also suggest that you use placeholders instead of variables. [**See PDO prepared statements**](http://php.net/pdo.prepared-statements) for more information on those. Even though you are using PDO, you are still open to SQL injection, strangely enough. — Funk Forty Niner, Jul 03 '14 at 22:19
@JackTurky: Please, never ever use `utf8_encode()` and `utf8_decode()` unless you are absolutely positively sure that what you try to encode is in latin1 aka ISO-8859-1. If you haven't looked closely those two functions are part of the XML Parser functions. They have been conveniently put there by some fool. If you have to deal with charset conversion use appropriate tools like e.g. the `iconv` extension. — ragol, Jul 03 '14 at 22:29

score 4 · Accepted Answer · edited May 23 '17 at 12:22

4

(As suggested I put my comment as an answer)

Use

$dbh = new PDO("mysql:host=$dbHost;dbname=$dbName;charset=utf8", $dbUser, $dbPass,array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES 'utf8'"));

and changing the variables to your own.

You can also use $dbh->exec("set names utf8");

As per http://www.php.net/manual/en/ref.pdo-mysql.connection.php

Might I also suggest that you use placeholders instead of variables. See PDO prepared statements for more information on those. Even though you are using PDO, you are still open to SQL injection, strangely enough.

edited May 23 '17 at 12:22

Community

1
1

answered Jul 03 '14 at 22:22

Funk Forty Niner

74,450
15
68
141

1

@Darren Oh, you saw that, did ya Darren? Yeah, let's just say that I'm helping out for a very few deserving ones who are faced with more complex problems, than mere "quotes around tables and columns". I give out more comments than answers lol – Funk Forty Niner Jul 04 '14 at 01:40
@Darren Yeah, me too. But just not as many "answers". There are too many low-quality questions lately, where all they do is open up cans of worms, or aggravation, and/or no results because the person at the other end has chosen the wrong career move. – Funk Forty Niner Jul 04 '14 at 01:56
Oh couldn't agree with you more on that. The amount of recent "comment answers" is blowing my mind aswell! – Darren Jul 04 '14 at 02:17
1

@Darren I have also noticed a rather significant decrease lately in (PHP/SQL) questions though. I guess the wind must've blown through some of their ears, as to how things are run around here. – Funk Forty Niner Jul 04 '14 at 02:19

Mysql store incorrect encoded char

1 Answers1