Fetching Asian languages from MySQL

[monty]

Hey y'all,

Anybody out there with some know-how on retrieving Thai data from a MySQL database (or any non-English encoded charset for that matter)? I am able to store it in MySQL using both UTF-8 or TIS620 (a Thai charset supported by MySQL) but getting it from the database and into the browser screen succesfully hasn't worked out yet. All I get is ??????? instead of Thai.

The problem is not in the browser, the charset is well defined in the head section. The problem is also not with the PHP script fetching the stuff since I am able to echo any static Thai language without problems.

When I run mysql_client_encoding() I get "latin1", which I think should be either UTF-8 or TIS620 (depending on the settings from the database). I have tried all sorts of mb funtions and iconv functions without any joy.

I have worked on other non-english projects before, but it was always about static data. I did some research on character encoding and it gets quite dirty quite fast, further more there are some known bugs regarding UTF-8 in several MySQL versions.

Anybody out there able to push me in the right direction? Tnx much!

-monty

 

[123finder.com]

Is the MySQL table encoded in latin1? Check its schema.

 

[monty]

Like I stated in my original post, storing the data is not the issue here, all the collations are set the way they should; either UTF-8 or TIS620.

 

[sacx13]

Probably your http server is not set up to work with utf8. Set you appache/IIS to handle this type of data

Regards

Adrian

 

[monty]

Probably your http server is not set up to work with utf8. Set you appache/IIS to handle this type of data

Regards

Adrian

If the issue was with Apache, PHP wouldn't be able to echo static Thai language into the browser which doesn't cause any problems, please look my orginal post.

I don't mean to offend anybody who is willing to help me, but please respond only if you have experience with MySQL and non-english character sets.

Apache is setup correctly, so are the collations in MySQL, the problem is either with MySQL or the handling of data between PHP and MySQL.

-monty

 

[sacx13]

We just try to help you ... probably I dont read everything or I drunk to much. Anyway ... did you try to :

mysql_query("SET NAMES 'utf8'", $conn);

before query ?
please try and let me know if is ok .

Regards
Adrian.
P.S. I worked some time ago with some chinese customers, so I have some experiences.

 

[Kate]

...
mysql_query("SET NAMES 'utf8'", $conn);
...

Yes you need to issue that statement before retrieving the data from mySQL and sending it to the browser.

 

[monty]

We just try to help you ... probably I dont read everything or I drunk to much. Anyway ... did you try to :

mysql_query("SET NAMES 'utf8'", $conn);

before query ?
please try and let me know if is ok .

Regards
Adrian.
P.S. I worked some time ago with some chinese customers, so I have some experiences.

I know you're just trying to help me; sorry, if I'v offended you. That remark I made wasn't very friendly. Really appreciate the help I am getting. I tried the SET NAMES query already and this gives me strange output: some Thai characters (although not the same data as in the database) and some other character as well (like %). And when I use mysql_client_encoding(), this still outputs "latin1" (a bug maybe?).

Anyway, do I still need to do some formatting on the data from the database?

Thanks sacx13 and sdsinc

 

[primacomputer]

Try a “SET CHARACTER SET” as well.

Posting a link and source would make things much easier and probably get more useful answers.

 

[Kate]

Monty could you post some code (php) + your page (html) maybe ?

On most of my sites I am using mysql 4.1.x, unicode support and InnoDB tables with UTF-8ci collation. The markup language is XHTML 1.0 transitional, UTF-8 charset. My sites work fine.
As an example see my sig
You can even see thai here: http://www.sweeturl.com/domain-name-detail.php?id=344

 

[monty]

Stupid, stupid, stupid! After all the switching between different character types, I had everything set to the right set (UTF-8) except for the the browser, which was still set to deal with the TIS620 set. Anyway, problem fixed; all working greatly.

Thanks a million guys!

-monty

We're getting there! However, now inserting Thai trough my script provides problems, it inserts stuff like: ฟหà¸?à¸?หดà¹?หà¸?ดà¸*à¸?หà¹?.

Before I run the update query, I echo it to the browser and it looks perfectly fine and when I copy-paste it into phpMyAdmin SQL screen and run it, it inserts fine but when I let the script run the query->no joy! Any suggestions?

this function is part of a small CMS for the website, I use a wrapper class around the database connection:

function update()
	{
		$page_id = $_GET['id'];
		$page_title = $_GET['title'];
		$page_content = $_GET['content'];
		$page_menu  = $_GET['menu'];
		
		$db = new DB_connection();
		$db->selectdb('INTouch');
		
		//update CMS_pages entry
		$query = "UPDATE CMS_pages SET page_id = '$page_id', page_title = '$page_title', par1 = '$page_content' WHERE page_id = '$page_id'";
		echo $query;
		mysql_query("SET NAMES 'utf8'");
		mysql_query("SET CHARACTER 'utf8'");
		$db->query($query);
		
		//update CMS_navigation entry
		$query = "UPDATE CMS_navigation SET navigation_id = '$page_id' WHERE navigation_id = '$page_id'";
		
		page_editor();
	}

Stupid, stupid, stupid! After all the switching between different character types, I had everything set to the right set (UTF-8) except for the the browser, which was still set to deal with the TIS620 set. Anyway, problem fixed; all working greatly.

Thanks a million guys!

-monty

 

[Kate]

The mysql_query("SET NAMES 'utf8'"); should be issued *before* your update/insert statements... basically it says to mysql: prepare to receive UTF-8 encoded input and deal with it accordingly...

 

[monty]

The mysql_query("SET NAMES 'utf8'"); should be issued *before* your update/insert statements... basically it says to mysql: prepare to receive UTF-8 encoded input and deal with it accordingly...

The mysql_query("SET NAMES 'utf8'") is issued before the insert/update query. The update query is perform by the query() method from the $db instance. However, the problem is fixed already; I rewrote the whole function and now all is well, probably just a syntax error in the script. Anyway, tnx again for all the help!

-monty