0

I need some help in an issue I am having with spanish special characters. A brief summary of what I am doing first. I have written a PHP script which fetches data from Google Spreadsheet using Zend Google PHP API. This data is then put into MySql database.

$row=$Col->getText(); //getText is the Google API that returns the value in the row. print "$row"; // I print the content

But the issue is that some spanish characters such as ñer appear as ñer. Later on in the PHP code I go on to save these values in MySql. And even in the tables the same issue.Don't know where the issue is.

I tried various different things such as edit the PHP ini file and add:

mbstring.language               = Neutral       ; Set default language to Neutral(UTF-8) (default)
mbstring.internal_encoding      = UTF-8         ; Set default internal encoding to UTF-8
mbstring.encoding_translation   = On            ;  HTTP input encoding translation is enabled
mbstring.http_input             = auto          ; Set HTTP input character set dectection to auto
mbstring.http_output            = UTF-8         ; Set HTTP output encoding to UTF-8
mbstring.detect_order           = auto          ; Set default character encoding detection order to auto
mbstring.substitute_character   = none          ; Do not print invalid characters
default_charset                 = UTF-8         ; Default character set for auto content type header
mbstring.func_overload  = 7 ; All non-multibyte-safe functions are overloaded with the mbstring alternatives

Add in the below in MySql

init_connect='SET collation_connection = utf8_unicode_ci; SET NAMES utf8;'

character-set-server=utf8 collation-server=utf8_unicode_ci skip-character-set-client-handshake

Change Database and Table properties

ALTER DATABASE db_name
CHARACTER SET utf8
DEFAULT CHARACTER SET utf8
COLLATE utf8_general_ci
DEFAULT COLLATE utf8_general_ci
; 

ALTER TABLE tbl_name
DEFAULT CHARACTER SET utf8
COLLATE utf8_general_ci
;

Call SETNAMES etc just after mysql open connection.

$q="SET NAMES 'utf8'";
$r=mysql_query($q);
mysql_query("SET CHARACTER SET utf8");

But nothing seems to work. Please help

konsolenfreddy
  • 9,551
  • 1
  • 25
  • 36
sureshv
  • 21
  • 1
  • Please use SO's search. http://stackoverflow.com/questions/9174881/how-to-select-mysql-query-with-foreign-language/9176483#9176483 – Alfabravo Feb 08 '12 at 19:32
  • It makes no sense to blindly throw encoding configuration settings onto something if you have no clue in which encoding actually the data was retrieved and stored. `UTF-8` does not solve all encoding problems magically. – hakre Feb 08 '12 at 19:45
  • @hakre: - I am no expert when it comes to encoding. That's why I am asking for opinions from others like you. We for starters I am not sure where things are going bad. In the sense are things going bad when ZEND GDATA returns the row name? Or does it go bad when MySql gets into the picture. Any suggestions to debug this issue will be very welcome. All of my application works, just this encoding problem is killing me. – sureshv Feb 08 '12 at 20:46
  • @Alfabravo: - Thanks for your suggestion. I did search a lot and am still searching for the answer to my issue. Nothing is working as of now. And to add to my vows, I am not very experienced with encoding and all. – sureshv Feb 08 '12 at 20:47
  • The thread i referenced shows how to set connection encoding correctly. Also, if you have already inserted data in a wrong encoding, doesn't matter you change table encoding, the bad data stills! – Alfabravo Feb 08 '12 at 20:51
  • you mentioned that I can covert the bad data in MySql to have correct encoding and thus display the proper spanish accents? How do I do that? Because I already converted the database and table to utf-8 encoding. I used the alter statements above. – sureshv Feb 08 '12 at 20:59
  • @sureshv: I suggest you delete all data from the database (or start with a second from scratch) and test-wise fill it up with a first request. If that request is done right, you should be able to view the data in the database with a tool like phpmyadmin displaying the correct spanish characters. If not, the gdata request or the database client / server has a wrong encoding already. This should help you to pinpoint where the actual problem is located. – hakre Feb 09 '12 at 00:00

1 Answers1

1

Check if the browser is rendering the page a UTF-8 as well. Try adding:

<meta http-equiv="Content-Type" content="text/html; charset=utf-8"> to the <head> of your HTML page.

Also, $q="SET NAMES 'utf8'"; should be $q="SET NAMES utf8"; (no quotes)

Halcyon
  • 57,230
  • 10
  • 89
  • 128
  • Hi, Thanks for your reply. Well I am not running the script was the browser. It's run from command line. And yes I corrected the query to $q="SET NAMES utf8"; Still does not work. – sureshv Feb 08 '12 at 20:43
  • Is the result of `$Col->getText();` actually utf8? – Halcyon Feb 08 '12 at 20:45
  • Thats what I don't know. PLease let me know how I can find that out. Because when I print the output on the screen then it shows the weird characters. I don't know if that's a linux issue in displaying special characters or if the variable itself i use to store $Col->getText(); has some other encoding other than utf-8. Dunno if I am making sense here. – sureshv Feb 08 '12 at 20:56