Search code examples
phpsqlsql-serveriso-8859-1

SQL result not english characters

I always work with MySQL but in but I am forced now to work with SQL Server and I am lost. I just want to get a row in spanish and I can't make it work. Here is the code, hopefully everything makes sense.

$connection = odbc_connect("Driver={SQL Server Native Client 11.0};Server=$server;Database=$database;", $user, $password);
$sql="SELECT * FROM my_table";
$res=odbc_exec($connection,$sql)or die(exit("Error en odbc_exec"));
while($arr = odbc_fetch_array($res)) {
    $var = $arr["OkRef"];
    echo "1.- ".iconv("Windows-1256", "UTF-8", "$var")."<br />";
    echo "2.- ".iconv("CP437", "UTF-8", $var)."<br />";
    echo "3.- ".iconv("CP850", "UTF-8", $var)."<br />";
    echo "4.- ".utf8_decode($arr["OkRef"])."<br />";
    echo "5.- ".utf8_encode($arr["OkRef"])."<br />";
    echo "6.- ".$arr["OkRef"]."<br />";
    echo "7.- ".mb_convert_encoding($arr["OkRef"], "utf-8", "windows-1251")."<br />";
    echo "8.- ".htmlspecialchars( iconv("iso-8859-1", "utf-8", $var) );
    }
}

I get this as result:

1.- ér àçHه¬´§d_meta_packet1Y³§0ت.122) ¸ؤ

2.- Θr ατHσ¼┤ºd_meta_packet1Y│º0╩.122) ╕─

3.- Úr ÓþHÕ¼┤ºd_meta_packet1Y│º0╩.122) ©─

4.- ?r ??H????d_meta_packet1Y??0?.122) ??

5.- ér àçH嬴§d_meta_packet1Y³§0Ê.122) ¸Ä

6.- �r ��H����d_meta_packet1Y��0�.122) ��

7.- йr азH嬴§d_meta_packet1Yі§0К.122) ёД

8.- ér àçH嬴§d_meta_packet1Y³§0Ê.122) ¸Ä

I tried also to add the following (not at once obviously) to make it work as it is:

<meta http-equiv="content-type" content="text/html; charset=UTF-8">
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
header('Content-Type: text/html;charset=utf-8');
header('Content-Type: text/html;charset=iso-8859-1');
ini_set('mssql.charset', 'UTF-8');

The server is a Microsoft SQL Server Enterprise Edition, and the server Collation is Modern_Spanish_CI_AS.

Solution

  • I know, that this answer is posted too late, but I am in similar situation these days, so I want to share my experience. My configuration is almost the same - database and table columns with Cyrillic_General_CS_AS collation. Note, that I use PHP Driver for SQL Server, not build-in ODBC support.

    The steps below have helped me to resolve my case. I've used collation from your example.

    Database:

    CREATE TABLE [dbo].[MyTable] (
    [TextInSpanish] [varchar](50) COLLATE Modern_Spanish_CI_AS NULL,
    [NTextInSpanish] [nvarchar](50) COLLATE Modern_Spanish_CI_AS NULL
)


INSERT [dbo].[MyTable] (TextInSpanish, NTextInSpanish) 
VALUES ('Algunas palabras en español', N'Algunas palabras en español')

    PHP:

    Set default_charset = "UTF-8" in your php.ini file. Encode your source files in UTF-8. I use Notepad++ for this step.

    Read data from database:

    • With default connection encoding. For reading data from database use $data = iconv('CP1252', 'UTF-8', $data); Note, that by default data is returned in 8-bit characters as specified in the code page of the Windows locale that is set on the system. Any multi-byte characters or characters that do not map into this code page are substituted with a single-byte question mark (?) character. This is the default encoding.
    • With UTF-8 connection encoding. Column must be of type 'nchar' or 'nvarchar'.

    HTML:

    Use: <meta http-equiv="Content-Type" content="text/html; charset=utf-8">

    Working Example:

    test.php (PHP 7.1, PHP Driver for SQL Server 4.3, file test.php is UTF-8 encoded):

    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html>
    <head>
    <meta http-equiv="X-UA-Compatible" content="IE=edge"/>
    <meta charset="utf-8">

<?php
// Connection settings
$server = '127.0.0.1\instance,port';
$database = 'database';
$user = 'username';
$password = 'password';
$cinfo = array(
    "CharacterSet"=>SQLSRV_ENC_CHAR,
    #"CharacterSet"=>"UTF-8",
    "Database"=>$database,
    "UID"=>$user,
    "PWD"=>$password
);
$conn = sqlsrv_connect($server, $cinfo);
if ($conn === false)
{
    echo "Error (sqlsrv_connect): ".print_r(sqlsrv_errors(), true);
    exit;
}

// Query
$sql = "SELECT * FROM MyTable";
$res = sqlsrv_query($conn, $sql);
if ($res === false) {
    echo "Error (sqlsrv_query): ".print_r(sqlsrv_errors(), true);
    exit;
}

// Results
while ($arr = sqlsrv_fetch_array($res, SQLSRV_FETCH_ASSOC)) {
    # Use next 2 lines with "CharacterSet"=>SQLSRV_ENC_CHAR connection setting
    echo iconv('CP1252', 'UTF-8', $arr['TextInSpanish'])."</br>";
    echo iconv('CP1252', 'UTF-8', $arr['NTextInSpanish'])."</br>";
    # Use next 2 lines with "CharacterSet"=>"UTF-8" connection setting
    #echo $arr['TextInSpanish']."</br>";
    #echo $arr['NTextInSpanish']."</br>";
}

// End
sqlsrv_free_stmt($res);
sqlsrv_close($conn);
?>

    </head>
    <body></body>
</html>