kiLLua kiLLua - 29 days ago 10
JSON Question

Cannot query correctly (MSSQL - PHP - JSON)

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.

Answer

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);