Cannot query correctly (MSSQL - PHP - JSON)

Question

I have a little problem on my project. And it turns out that the query result returns nothing when the value of first name or last name has 'ñ' in it.
Here's my code

config.php:

<?php
   ini_set('mssql.charset', 'UTF-8');
   header('content-type: text/html; charset=utf-8');
   $serverName = "192.168.1.21"; /* IP add of db Server */
   $connection = array("Database" => "db", "UID" => "?", "PWD" => "***");
   $conn = sqlsrv_connect($serverName, $connection);

if(!$conn){
echo "Connection could not be established.";
die(print_r(sqlsrv_errors(), true));
}
?>

MyQuery:

$idnumber = $_POST["idnum"];
$response = array();
$query2 = "SELECT cLName, cFName, cMName, cQualifier FROM student WHERE cIDNO = '$idnumber'";

try{
    $stmt2 = sqlsrv_query($conn, $query2);
}
catch(PDOExeption $ex){
    $response["Success"] = 0;
    $response["message"] = "Database Error!";
    die(json_encode($response));
}
$row2 = sqlsrv_fetch_array($stmt2, SQLSRV_FETCH_ASSOC);

    if($row2){
        $response["Success"] = 1;
        $response["message"] = "Data available";
        $last_name = $row2["cLName"];
        $first_name = $row2["cFName"];
        $qualify = $row2["cQualifier"];

        $first_name = html_entity_decode(htmlentities($first_name));
        $last_name = html_entity_decode(htmlentities($last_name));

        $name = $first_name." ".$last_name." ".$qualify;

        $response["Name"] = $name;

        echo json_encode($response);
    }

    else{
        $response["Success"] = 0;
        $response["message"] = "Database Error!";
        die(json_encode($response));
    }

I looked on these pages: 1, 2, 3, etc.

But i don't have clue why is this happening to my program.

Example in the Databases: cLName = "Española", cFName = "Edgar", cQualifier = "Jr."
Output: "Name":"Edgar Jr."
While: cLName = "Agustin", cFName = "Florence", cQualifier = "Jr."
Output: "Name":"Florence Agustin Jr."

Ideas? Please?

Note: I am not authorized to alter or change anything on the database.

Have you checked if there's a value stored in `$first_name` - so you can determine wether your problem is fetching the value from database or not (but looks like that). — Andreas Schrammel, Nov 10 '16 at 07:53
Add N before the search term. Select * from Tab1 where Col1 =N'YourColumn' — Shakeer Mirza, Nov 10 '16 at 07:55
@Andreas Yes, I have tried. I tried alot of trials for different ID, The names without "ñ" has a value and the names containing, there's no value. I tried to `echo` it without `json_encode()` it's the same. — kiLLua, Nov 10 '16 at 07:58
@cske I tried it. The `cLName` returns value but it's kinda wierd. because the `Lastname: Española .. Becomes: Espa\u00f1ola` — kiLLua, Nov 10 '16 at 08:06
@kiLLua ok then `json_encode($response,JSON_UNESCAPED_UNICODE)` preparing answer — cske, Nov 10 '16 at 08:10

score 1 · Accepted Answer · answered Nov 10 '16 at 08:12

I think its encoding error mssql con doest not in UTF-8 mode,

What i did if following is ISO-8859-1 endoced, and utf8_encode is missing the output is what OP got, but with utf8_encode it is correct

<?php


$row = ['cLName' => "Española", 'cFName' => "Edgar", 'cQualifier' => "Jr."];

$last_name  = utf8_encode($row["cLName"]);
$first_name = $row["cFName"];
$qualify    = $row["cQualifier"];

$first_name = html_entity_decode(htmlentities($first_name));
$last_name = html_entity_decode(htmlentities($last_name));

$name = $first_name." ".$last_name." ".$qualify;

$response["Name"] = $name;
$enc = json_encode($response,JSON_UNESCAPED_UNICODE);

print_r($name);
print_r($response);
print_r($enc);

Cannot query correctly (MSSQL - PHP - JSON)

1 Answers1