PHP - Fetching UTF-8 data from sqlsrv database

Question

I'm Trying to fetch some data from sql server database and print in json , but some of the data in database is in UTF-8 (Arabic) . I searched the web and tried many solutions , but none of them worked for me , any help would be appreciated . Here is my code :

<?php

$serverName = "127.0.0.1"; //serverName\instanceName
$connectionInfo = array( "Database"=>"filmnak_com_", "CharacterSet" => "UTF-8", "UID"=>"filmnak", "PWD"=>"G5j^wk44");
$conn = sqlsrv_connect( $serverName, $connectionInfo);

$sql = "SELECT CAST(ID AS INT) AS ID, CAST(Film AS TEXT) AS Film ,CAST(Name AS TEXT) Name FROM dbo.Movies";

$result = sqlsrv_query( $conn, $sql);
if( $result === false ) {
     die( print_r( sqlsrv_errors(), true));
}

    // looping through all results
    // products node
    $response["Info"] = array();
  while ($row = sqlsrv_fetch_array($result)) {
        // temp user array
        $Info = array();
        $Info["ID"] = $row["ID"] ;
        $Info["Film"] = $row["Film"];

      $Info["Name"] = utf8_decode($row["Name"]);

    echo $row["Name"];
        // push single product into final response array
        array_push($response["Info"], $Info);
    }


echo json_encode($response , JSON_UNESCAPED_UNICODE);

sqlsrv_free_stmt($result);


?>

the result in browser :

???? ????1+1?????{"Info":[{"ID":8,"Film":"8O9","Name":"???? ????"},{"ID":9,"Film":"9O3","Name":"1+1"},{"ID":10,"Film":"10O5","Name":"?????"}]}

+ use the type `nvarchar` (or other similar like `ntext`, `nchar` — Karthi, Nov 12 '16 at 12:28
utf8_decode never does what you think it does. If your data is UTF 8 in the database and your connection charset is UTF 8 then PHP will receive it as UTF 8. PHP can handle UTF-8 just fine, no need for any special operations on them. — apokryfos, Nov 12 '16 at 12:30
Possible duplicate of [UTF-8 all the way through](http://stackoverflow.com/questions/279170/utf-8-all-the-way-through) — apokryfos, Nov 12 '16 at 12:31
@apokryfos , the data in database is UTF-8 , I set the characterset to UTF-8 , but when i use echo to print them , it will show question marks (?????) , ( echo $row["Name"]; gives question marks , utf8_decode($row["Name"]); gives question marks ) — Arash Mohammadi, Nov 12 '16 at 12:58
@ArashMohammadi you must say, are you tried of my previous comments? — Karthi, Nov 14 '16 at 04:40
@Karthi , yes I changed the collation to Arabic_CI_AI and it worked fine , I posted a comment here before about this but I dont know why the comment is not showing now , sry for that and TY for the solution — Arash Mohammadi, Nov 22 '16 at 20:53

score 17 · Answer 1 · answered Jul 22 '18 at 14:54

USE THIS CODE

    $serverName="(local)";
    $connetionInfo = array("DataBase"=>"your db","UID"=>"username","PWD"=>"pass",
                           "CharacterSet" => "UTF-8");<---------------this
    $this->dblink=sqlsrv_connect($serverName,$connetionInfo);

score 2 · Answer 2 · edited Apr 11 '22 at 17:06

2

You'll need to specify the character set when connecting to the sqlsrv server. You can do this by adding the CharacterSet parameter like this:

$serverName = "Your server name";
$connectionInfo = [ "Database"=>"your db",  "CharacterSet" =>"UTF-8"];
$conn = sqlsrv_connect( $serverName, $connectionInfo);

edited Apr 11 '22 at 17:06

Skatox

4,237
12
42
47

answered Apr 10 '22 at 09:57

Yrys

21
1

score 1 · Accepted Answer · answered Jul 23 '22 at 10:41

1

Set the database collation to utf8_general_ci

answered Jul 23 '22 at 10:41

Mahmood Rezaei

26
2

PHP - Fetching UTF-8 data from sqlsrv database

3 Answers3