2

I recently migrated from a PHP 4.3.9 / MySQL 4.1.22 setup to a PHP 5.3.3 / MySQL 5.1.69 setup. I seem to have a problem with character encoding. My database collation is latin1_swedish_ci.

Whenever I enter new data in my table through my PHP script, the data ends up garbled in MySQL , for example: à é ü ends up as à é ü.

I have tried setting the character set with mysql_set_charset or SET NAMES but neither seems to have any effect. I don't want to switch the table or code to utf8, I just want the current setup to work properly.

Already spend a couple of hours looking online and haven't found the solution yet.

All input is greatly appreciated.

tom

Tom S.
  • 51
  • 5
  • what is the encoding of your php file? and what mysql library are you using? – x4rf41 Aug 12 '13 at 09:42
  • how can I check that? I'm using php-mysql-5.3.3-23 if that's what you mean? – Tom S. Aug 12 '13 at 12:40
  • in your IDE you should look at the properties fo the .php file, it should show you the encoding of file, the same encoding is used for php strings. `mysql_set_charset` should be set to the encoding of the php file. also the php-mysql library is deprecated, it shouldnt be used anymore (but i guess your project is old and you dont want to switch) – x4rf41 Aug 12 '13 at 12:46
  • I'm not using an IDE, development has been done in vim directly on the server so I don't think that's an issue here. I tried using mysql_set_charset but it doesn't seem to make a difference. I am aware the mysql library is deprecated and will change over time. Unfortunately I need the current setup to work properly before I can start thinking of migrating. – Tom S. Aug 12 '13 at 12:48
  • you probably used `mysql_set_charset` to set the charset to that of the mysql database, but you should set it to the charset of the php file (mysql knows the encoding of its databases, but it doesnt know the encoding that php is using). look up how to detect the charset of a file in linux, if you find the charset, use it with `mysql_set_charset`, or you could just try `mysql_set_charset('utf8')` , `mysql_set_charset('latin1')` or any from this list: http://dev.mysql.com/doc/refman/5.6/en/charset-charsets.html that seems likely – x4rf41 Aug 12 '13 at 12:53
  • thanks for the continuing feedback. i determined the encoding of the file is us-ascii. If subsequently used mysql_set_charset('ascii') but the situation remains the same. Any other suggestions? – Tom S. Aug 12 '13 at 13:12
  • How can you say that the data ends up garbled in MySQL? Perhaps the data is garbled only after you fetched it again from MySQL and then by displaying it wrongly? – hakre Sep 08 '13 at 17:00
  • fair point. i don't know for sure. the data appears garbled in phpmyadmin, my own web interface and the mysql command line. this wasn't the case before I switched to php5 and mysql5. seeing as this problem is also showing on the command line, i think it's safe to assume the problem appears when inserting, not when selecting. – Tom S. Sep 17 '13 at 07:45

1 Answers1

3

Solved it by explicitly enabling: default_charset = "iso-8859-1" in php.ini Problem is now solved.

Tom S.
  • 51
  • 5