menu

Questions & Answers

How to use object generated from SQL Server to populate HTML elements?

In my SQL query PHP I have:

<?php  
    $mstr_ID = $_GET['MF_ID'];  
    $sql = "select hospital, patient_id from masterfileaccess where masterfile_id = " . $mstr_ID . "FOR JSON PATH";  
    $patLookup = sqlsrv_query($conn,$sql);  
    $row = sqlsrv_fetch_array($patLookup, SQLSRV_FETCH_ASSOC);  
    echo json_encode($row);  
    sqlsrv_free_stmt($patLookup);  
    sqlsrv_close($conn);  
?>  

In my JavaScript function I have:

function chooseThisPatient(id,name){  
    const mstrID = name.substring(2);  
    const xhttp = new XMLHttpRequest();  
    xhttp.onload = function() {  
    const res = JSON.parse(xhttp.responseText);  
        alert(res)
        // what do I do here to access the JSON file?  
    }  
    let phpfile = "cardiac/phps/cardiacGetPatientData.php?MF_ID=" + mstrID;  
    xhttp.open("GET", phpfile);  
    xhttp.send();  
}  

When I run the select on SQL Management Studio, I get:

[{"hospital":"Good Hospital","patient_id":"12345678"}] 

When I run chooseThisPatient() withOUT the JSON.parse, alert(res) gives me:

{"JSON_F52E2B61-18A1-11d1-B105-008-5F49916B":"[{\"hospital\":\"Good Hospital\",\"patient_id\":\"12345678\"}]"}  

When I run chooseThisPatient() WITH the JSON.parse, alert(res) gives me: Object object

I have tried xhttp.responseXML, res[x] (where x is 0-10) - this gives me single characters '[','{','', etc. Have tried res.hospital (undefined), res['hospital'] (undefined), res.key, res.key(), res.key[0], res.keys, Object.keys(res), Object.keys(res[0], all without being able to see what the JSON is holding.

What I would LIKE to happen is to be able to put the object in a loop and update HTML elements, something like:

for( x in res){  
    const key = x.key;  
    const value = x[key];  
    document.getElementById(key).innerHTML = value;  
}  
Comments:
2023-01-21 23:01:22
For whatever reason, SQL Server is returning the JSON as the string value of an object's property. So you'd have to do something like const data = JSON.parse(Object.values(JSON.parse(xhttp.responseText))[0])‌​;. That is, double parse. I expect there's a way of making that work better via SQL Server and/or PHP, but I don't know those technologies well enough to answer. Maybe you don't need the json_encode?
Answers(0) :