0

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.

Jonathan Hall
  • 75,165
  • 16
  • 143
  • 189
Neil
  • 95
  • 1
  • 1
  • 7
  • How are you determining that information is getting lost? Do you run a select? – Tim Biegeleisen Oct 21 '18 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 Oct 21 '18 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 Oct 21 '18 at 14:41
  • 1
    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 Oct 21 '18 at 14:48
  • How are you obtaining the name and inserting it into the db ? – ivanivan Oct 21 '18 at 14:52
  • im collecting it from a JSON payload from a curl request i am making, and then writing that to the db using PHP, i have cut out the middle man just now by defining the values in a php script and no longer making a curl request, and the issue is still happening. – Neil Oct 21 '18 at 14:54
  • See "Mojibake" in https://stackoverflow.com/questions/38363566/trouble-with-utf8-characters-what-i-see-is-not-what-i-stored – Rick James Oct 25 '18 at 03:26

1 Answers1

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/

Neil
  • 95
  • 1
  • 1
  • 7
  • 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 Oct 25 '18 at 03:38
  • And it links to an older blog that is riddled with a higher percentage of misinformation. – Rick James Oct 25 '18 at 03:41