none
UTF-8 encoding problem RRS feed

  • Question

  • Hi,

    I cannot get UTF-8 to work with PHP and MSSQL. Running a query always return strings with '?' instead of the proper accents (like 'éàê')...

    Here is my configuration:

    - Windows Server 2008 R2 with IIS 7.5

    - PHP 5.4.16 NTS

    I've googled and tried the following suggestions, none of them worked:

    - Adding 'mssql.charset="UTF-8"' to the ini file.

    - Using ini_set('mssql.charset', 'UTF-8') before connecting.

    All my fields are of type nvarchars. The PHP file itself is encoded using UTF-8 with signature - Codepage 65001. I've restarted W3SVC after making any of the changes mentioned above. One strange thing is that the mssql.charset does not appear in my php info page. I don't know if this is normal.

    Here are the relevant lines of my PHP.ini:

    [PHP_SQLSRV]
    extension=php_sqlsrv_54_nts.dll
    [PHP_PDO_SQLSRV]
    extension=php_pdo_sqlsrv_54_nts.dll

    [MSSQL]
    mssql.charset="UTF-8"

    Any help would be greatly appreciated.

    Thank you!

    Sunday, June 16, 2013 3:55 AM

Answers

  • mssql and sqlsrv are different.

    mssql is the name of the extension written by the PHP group, which is now deprecated - it stopped getting support after SQL Server 7.

    sqlsrv is Microsoft's own extension, written for SQL Server 2005 and above.

    To use UTF-8 successfully, make sure that...

    1. The columns in your database are NCHAR or NVARCHAR (for other readers, I know you have confirmed this)
    2. You open your connection in PHP in UTF-8 mode:

      $connectInfo = array('Database' => [DB Name], 'CharacterSet' => 'UTF-8' ); // If using SQL Server credentials (recommended), add: $connectInfo['UID'] = [User Name]; $connectInfo['PWD'] = [Password]; // make connection $conn = sqlsrv_connect( '[Server Name]', $connectInfo );

    3. It helps if your PHP source files are encoded in UTF-8. With UTF-8 connection option, any script or string parameters* you send are expected to be UTF-8.  (Again, noted from your question.)

      * You can set specific parameter-types if required, the default string type is UTF-8.

    Rob

    • Marked as answer by sixstorm1 Monday, June 17, 2013 10:02 PM
    Monday, June 17, 2013 10:19 AM

All replies

  • Hi sixstorm1,

    First, I suggest querying the data from SQL Server Management Studio to make sure the data was stored correctly rather than '?', and then we can refer to the suggestion on the following site to convert the data to UTF-8 format:

    Getting data with UTF-8 charset from MSSQL server using PHP FreeTDS extension
    http://stackoverflow.com/questions/13377812/getting-data-with-utf-8-charset-from-mssql-server-using-php-freetds-extension


    Allen Li
    TechNet Community Support

    Monday, June 17, 2013 1:32 AM
    Moderator
  • Thanks for your reply. The checked and the data is indeed stored correctly. I do not need the FreeTDS extension, FreeTDS is for PHP running on Linux. My PHP is installed on IIS 7.5 and Windows Server 2008 R2, the same physical machine as my SQL Server. I am already aware about the mb_convert_encoding function, and it indeed works. But there must be a cleaner and more efficient way of pulling UTF data from SQL Server than converting the query results everytime.
    • Edited by sixstorm1 Monday, June 17, 2013 2:16 AM
    Monday, June 17, 2013 2:16 AM
  • mssql and sqlsrv are different.

    mssql is the name of the extension written by the PHP group, which is now deprecated - it stopped getting support after SQL Server 7.

    sqlsrv is Microsoft's own extension, written for SQL Server 2005 and above.

    To use UTF-8 successfully, make sure that...

    1. The columns in your database are NCHAR or NVARCHAR (for other readers, I know you have confirmed this)
    2. You open your connection in PHP in UTF-8 mode:

      $connectInfo = array('Database' => [DB Name], 'CharacterSet' => 'UTF-8' ); // If using SQL Server credentials (recommended), add: $connectInfo['UID'] = [User Name]; $connectInfo['PWD'] = [Password]; // make connection $conn = sqlsrv_connect( '[Server Name]', $connectInfo );

    3. It helps if your PHP source files are encoded in UTF-8. With UTF-8 connection option, any script or string parameters* you send are expected to be UTF-8.  (Again, noted from your question.)

      * You can set specific parameter-types if required, the default string type is UTF-8.

    Rob

    • Marked as answer by sixstorm1 Monday, June 17, 2013 10:02 PM
    Monday, June 17, 2013 10:19 AM
  • The 'CharacterSet' => 'UTF-8' connection option worked perfectly. Many thanks!

    Monday, June 17, 2013 10:02 PM