UTF-8 + MySQL + PHP = A Mystery

[nrobi]

I started developing an online store on my local computer to keep my costs down until I started to code things that I didn't really know how to setup on my WinXP computer (email server, and gd package). I'm essentially running apache 1.3.34/php 4 something/MySql 4 something "out of the box." I did enable the mb_string package in PHP as I want to have an international website.

And setup my keyboard to type arabic and latin characters to test my web pages out. So everything seemed to work fine. I store the string in my DB then when a user logs in. It is retrieved from my DB and assigned to a session variable. That is displayed on the webpage. And everything was fine on my machine.

Then I ported it over to my new linux hosting account. And that little bit of code crashes my webapp, but if I have the string only consisting of latin characters everything works fine. My hosting company doesn't want to help me. So I discovered this forum :).

Here's my tickets, slightly editted to fit in the space alotted.

[quote]The problem I'm having is my site works fine on your servers when I use "Nolan" as my first name in your database. I use the first name in a greeting on my site so I store it in a session variable. But when I use "[size=18pt]نُلَن[/size]ْ" I receive a recursive error (because my error page is also experiencing the same error).

I log this in my log file:

2006-10-29 00:12:10 ERROR
[long path]/db.php 24 Note: SELECT COUNT(*) FROM servicearea WHERE stid=1 AND (rid= OR rid=265) (error #256) 68.191.77.170 2006-10-29 00:12:10 WARNING
[2 more errors]

Notice one of the 'rid' values is missing. This rid is the country, which should be 230 for the US. And that missing piece resulted in the other two errors.

My session variables look like this:

uname|s:5:"Nolan";uid|s:4:"1000";country|s:3:"230";region|s:3:"265";ip|s:13:"68.191.7
7.170";

uname|N;s:4:"1000";country|s:3:"230";region|s:3:"265";ip|s:13:"68.191.77.170";error|i:10;

The first is where my name is in English, the second in Arabic. Interestingly in both cases the country session variable is set.

I set my DB character set and collation to utf8. All of my tables were already set to utf8  for bth collation and characterset when I imported them. It looks as if the characters are stored correctly in the DB as when I do a select query on the users table I see the arabic characters in my browser.

The php statement that is causing the error is:

if(isset($_SESSION[SESS_RGN])) {
$row = mysql_fetch_array(wwm_query("SELECT COUNT(*) FROM servicearea WHERE stid=".STORE_ID." AND (rid=".$_SESSION[SESS_CTRY]." OR rid=".$_SESSION[SESS_RGN].")"));
//some other stuff
}
------------------------------------------------------------------
In the script that handles the login I simply do:

$_SESSION[SESS_UNAME] = $row['first'];

Where $row is derived from:

mysql_fetch_assoc(mysql_query("SELECT u.first,..... FROM users as u,...... WHERE u.email LIKE '".$_POST['email']."' AND u.pword LIKE BINARY '".md5($_POST['pwd'])."'"));
-----------------------------------------------------------------
The problem is still persisting but 've tried a few things in the mean time. I noticed that at the begnning of the webshell interface when you first get in there is an option to set the character set. I set it to 'utf-8' and have opened and saved all my files.

Something interesting occured. After resaving all my files (in my login script I omitted the 4 lines of code further down this e-mail in the resave) , I now see arabic characters written properly for the session variable but are displayed as سيبيب in the form fields.

I've changed all the mb_string settings one-by-one in my local php.ini to match with yours, in each case it still worked fine on my computer.

As for MySQL collation and charset variables all match yours except for "server" collation and character set. On mine they are utf8 on yours latin.

On the top of most (now all of them) pages I have:
header('Content-type: text/html; charset=UTF-8');
mb_internal_encoding('UTF-8');
mb_http_output('UTF-8');
wwm_query("SET NAMES utf8");

I realized I didn't have this in my login script after I resaved so upon inserting it my script once again displays it properly in the form fields but doesn't properly write the session variable and causes me to crash.
------------------------------------------------------------
What specifically happens when the four lines of code are NOT included is:

1 - I log in.
2 - Goto "my account" and type in some arabic characters in my first name.
3 - Press "update"
4 - Script does it's work and sends me to http_referer.
5 - Says the update was successful, I see the arabic displayed properly from the session variable but all the form fields look garbled.
6 - I goto another page but am automatically logged out.
7 - I log in again, the session variable is displayed properly.
8 - Repeat #6

What specifically happens when the four lines of code ARE included is:

1 - I log in.
2 - Goto "my account" and type in some arabic characters in my first name.
3 - Press "update"
4 - Script does it's work and sends me to http_referer.
5 - Says it was successful but I land on the index page not the refering page and I'm automatically logged out.
6 - I log in again, I'm automatically directed to my error page (because of the messed up query that occured because the session variable isn't writen properly).[/quote]

When I look at the database record in the last case. It displays the first name in arabic as I typed it out in the form field. Where do I start to debug this thing? Thanks

[nrobi]

I see many people found this a mystery as well. I thought of something to do and was wondering if anyone is familiar with the internal format of UTF-8. My basic thinking was converting them into "&#[i]n[/i];" format but I need to be able to figure out the integer value of the utf-8 character. Is anyone familiar with that?

I'm hoping this'll work as I just did it with a blog I'm writing. I entered in "é" which was displayed as "é" but when I changed it to é it was displayed properly. (I was too lazy to change the header to UTF-8.... on my blog not my website :) ). I'm a little peeved that I'm forced to use what I consider bandaid code (and use slightly more DB space)... but if it gets my project up and running w/o a problem so be it.

[mhitendra]

Hi,

I have a database for storing company data for various print catalogues. Now we have a requirement to get Arabic information from companies as well. Hence, the company data table should now contain Arabic text along with the English.

To achieve that I first converted the table from latin1 to UTF8 (utf8_general_ci). Then I changed the charcter set on my web pages containing the update forms to UTF-8.

Now, the arabic data entered using the webpages is displayed fine in the resultant php pages, but when we want to see the same data in mysql (using mysql query browser/SQL yog/EMS Mysql Manager) the data shows up in binary form or ????.

I fail to understand how can I view Arabic text as it is in a mysql query window, so that when I epxort data it comes as it is and does not come in form of special characters.

Have tried searching the net but no solutions have worked so far.

Kindly advise.

Best regards

Hitendra