0

I am writing PHP scripts to migrate data from Microsoft SQL Server to MySQL, this has been smooth so far, except multibyte characters. can somebody help me to get over with this. What are the points that should be taken care of from PHP and MySQL database perspective.

  1. Do i have to make some changes in MySQL configuration?
  2. Do i have to change character encoding in PHP for multibyte characters, if yes how?
  3. Is there anything else i am missing?

Thanks.

nv185001
  • 35
  • 1
  • 6

1 Answers1

0

I recently added a translation module for our company's website. I did the following to make sure the DB was up for multibyte goodness

  1. Setting encoding and collation to UTF8
  2. Make sure your MySQL connection is configured to accept UTF8 characters. You can do this by running the query SET NAMES utf8 prior to transferring. However, there are other ways to do this (I added SET NAMES utf8 because that's how I did it.)

The following threads have more info

SET NAMES utf8 in MySQL?

Whether to use "SET NAMES"

Community
  • 1
  • 1
JohnP
  • 49,507
  • 13
  • 108
  • 140
  • It looks like the problem is much more than what i am expecting it to be. The data from where we are migrating contents is from another application whithin intranet and there happens to be a nText column, values of this column cannot be migrated instead it fetch "?". I have also tried to convert the field type VARCHAR but it seems to be of little help. – nv185001 May 22 '11 at 15:45
  • Here is what i am doing to convert ntext field PROCESSDETAILS to varchar: $query = "SELECT CONVERT(VARCHAR(8000),CONVERT(VARBINARY(8000),PROCESSDETAILS)) AS PROCESSDETAILS FROM PROCESSTBL" $result = mssql_query($query); while($row = mssql_fetch_array($result)) { echo iconv("UCS-2LE","UTF-8",$row ["ProcessDetails"]); } the above code snippet causing an error message "Explicit conversion from data type ntext to varbinary is not allowed". Is there any workaround to this problem? – nv185001 May 22 '11 at 16:21
  • @nv185001 you could simply code a page in PHP that will load that data into a form and then submit it into your new conversion script. That way you don't need to do a direct server to server conversion. This depends on the amount of data, but you should be able tweak it once the data is validated – JohnP May 22 '11 at 16:58