broken UTF8 characters in MySQL using PHP

Question

I have noticed when dealing with some names that are not of normal spelling ie standard alphabet UK/US are getting lost from my inserting of a record to what actually shows up in the database. I have done quiet a bit of reading regarding the Collation type, which is what I thought was causing the issue, but not sure if this is the case or I'm still doing it wrong as my problem is still persisting.

Below is an example of a record I am creating as well as my database structure, and as you can also see the last_name field has "ö", when I lookup the record I actually see the last_name "KÃ¶rner"

CREATE TABLE `data` (
  `id` bigint(20) NOT NULL,
  `profile_id` int(11) NOT NULL,
  `first_name` varchar(100) NOT NULL,
  `last_name` varchar(100) NOT NULL,
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

ALTER TABLE `data`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `profile_id` (`profile_id`);

ALTER TABLE `data`
  MODIFY `id` bigint(20) NOT NULL AUTO_INCREMENT;


INSERT IGNORE INTO data (profile_id, first_name, last_name) VALUES (1, 'Brent', 'Körner');

The field collation on the last_name is set to 'utf8_general_ci' which that I understand or should I say thought would sort this issue out.

This seems to be something I am doing wrong / missing with PHP, as when I execute the INSERT query within PhpMyAdmin it saves fine.

How are you determining that information is getting lost? Do you run a select? — Tim Biegeleisen
– Tim Biegeleisen, Commented Oct 21, 2018 at 14:31
i am using phpMyAdmin to look at the record, and thats when i can see the name has them different characters in, "KÃ¶rner" instead of "Körner", and when i click on the filed and change it manually it saves with the correct one. Which is leading me to beleive if i can do it manually with phpMyAdmin maybe i am doing something wrong. — Neil
– Neil, Commented Oct 21, 2018 at 14:37
If you set the encoding for your MySQL table to UTF-8, then maybe the problem is on the PHP side of things. — Tim Biegeleisen
– Tim Biegeleisen, Commented Oct 21, 2018 at 14:41
Ive just done the Insert Query within the PhpMyAdmin tools and it actually saves fine, which seems to prove the issue is somewhere with PHP — Neil
– Neil, Commented Oct 21, 2018 at 14:48
How are you obtaining the name and inserting it into the db ? — ivanivan
– ivanivan, Commented Oct 21, 2018 at 14:52

Neil · Accepted Answer · 2018-10-21 15:07:51Z

1

it seems the issue was down to PHP in the end, and i wasn't setting the charset.

For mysql

mysql_set_charset('utf8');

For mysqli

mysqli_set_charset('utf8');

ref https://akrabat.com/utf8-php-and-mysql/

answered Oct 21, 2018 at 15:07

Neil

1251 gold badge1 silver badge8 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Rick James Over a year ago

Be cautious about links that have not been updated in nearly a decade. That particular link fails to mention utf8mb4, refers to antique version of PHP, mentions the mysql_* interface in PHP, which should not be used, etc.

Rick James Over a year ago

And it links to an older blog that is riddled with a higher percentage of misinformation.

Collectives™ on Stack Overflow

broken UTF8 characters in MySQL using PHP

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related