Why do I get invalid characters when converting MS SQL data to MYSQL?

Question

I am writing a PHP script to import data into a MYSQL database from a Microsoft SQL Server 2008 database.

The MSSQL server is installed with the convolution "SQL_Latin1_General_CP1_CI_AS", and the data in question is stored in a column of the type "nchar".

PHP is used in my web pages

<meta http-equiv="content-type" content="text/html; charset=utf-8"> 

to indicate that they should be displayed with UTF-8 character encoding.

I am retrieving data from an MSSQL database using the PHP sqlsrv extension.

 $sql = 'SELECT * FROM [tArticle] WHERE [ID] = 6429'; $stmt = &sqlsrv_query($dbHandler, $sql); while ($row = sqlsrv_fetch_object($stmt)) { // examples of what I've tried simply to display the data echo $row->Text1; echo utf8_encode($row->Text1); echo iconv("ISO-8859-1", "UTF-8", $row->Text1); echo iconv("ISO-8859-1", "UTF-8//TRANSLIT", $row->Text1); } 

Forget about entering data into the MYSQL database. I cannot correctly display a string on my PHP page. From the examples on my list:

 echo $row->Text1 

displayed by my browser as a clearly invalid character: "Lucy s"

all examples following that they appear as spaces: "Lucy"

It seems like a character set mismatch problem for me, but how can I get this data to display correctly from the MS SQL database (without changing my encoding on the web page)? If I can figure this out, I can probably handle storing it in the MYSQL database part.

Answer 1

If the rows in the source database are encoded in UTF-8, you should use utf8_decode , not utf8_encode .

But they are probably encoded on some kind of latin or "western" Windows code page . So I would try iconv("CP1252", "UTF-8", $row->Text1); , eg.

Another alternative is to run an SQL query that explicitly sets the known encoding. For example, according to the Windows Collation Name (Transact-SQL) documentation, this query would use page code 1252 to encode the Text1 field: SELECT Text1 COLLATE SQL_Latin1_General_CP1_CI_AS FROM ...

Answer 2

try this command for me:

 $connectionInfo = array( "Database"=>"DBName", "CharacterSet" =>"UTF-8");