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
– Andreas Schrammel, Commented Nov 10, 2016 at 7:53
Add N before the search term. Select * from Tab1 where Col1 =N'YourColumn' — Shakeer Mirza
– Shakeer Mirza, Commented Nov 10, 2016 at 7: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
– kiLLua, Commented Nov 10, 2016 at 7:58
@cske I tried it. The cLName returns value but it's kinda wierd. because the Lastname: Española .. Becomes: Espa\u00f1ola — kiLLua
– kiLLua, Commented Nov 10, 2016 at 8:06

cske · Accepted Answer · 2016-11-10 08:12:53Z

1

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

answered Nov 10, 2016 at 8:12

cske

2,2334 gold badges27 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Cannot query correctly (MSSQL - PHP - JSON)

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related