Trying to get JSON data from API into HTML table

[speckytwat]

Hi, I have JSON data being pulled through from an API which is obviously in the horrible JSON format and practically unreadable, so I need to have it put into an HTML table. I've seen various methods for doing this but haven't found any that work. The problem is it isn't static data, it changes depending on what the user is searching for, so the examples containing static JSON data aren't of any use to me.

So in a PHP script I pulled through the JSON data, which I can then also create a file from (I've looked in the file and it contains the data so that all seems to work)

$result = curl_exec($ch);
$fp = fopen('result.json', 'w');
fwrite($fp, $result);

But then I need a way (using some JQuery or Javascript I guess) for that resulting .json file to be read and the data to be put into a HTML table. Anyone know a simple way I can do this?

 

[sunfighter]

curl_exec() only returns TRUE or FALSE not data. Using fopen() in the 'w' mode will over write the data that is already there, so can't believe 'result.json' has the data you want.
BUT what is needed for an answer to your problem is the contents of this data and how you want the table to look.

by the way that data IS static. You download it when you call the curl functions, which also gives me cause to think - shouldn't the user initiate the exchange of data when he/she changes that data and why is it you need curl?

 

[speckytwat]

Thanks for the reply. I was using curl to get the data from an API URL and it seems to work well. The only issue I have is the awful format of the JSON data and how it make it presentable.

The user doesn't change any data as such, they search for data based on a search keyword and the data depends on that. So what I need is that resulting search results data to be presented in a table on the page? But obviously it's going to be different for different searches so I can't figure out how I can have to added into a Javascript or other code which can then render it into a table?

The JSON data being pulled through (as an example) is like this:

{  "correlationId": "1a9fcfb0-647e-11eb-b00e-068768768",  "totalSize": 847090,  "companies":
[    {      "id": "GB-1-xxxxxx",      "country": "GB",      "safeNo": "xxxxxxx",      "name": "xxxxxx",      "address": {        "simpleValue": "xxxxxxxxxxxxxxxxxxxx",        "street": "xxxxxxxxxxx",        "city": "xxxxxxx",        "postCode": "xxxxxx"      },      "status": "xxxxxx",      "type": "xxxxxx",      "dateOfLatestChange": "2020-03-27T06:14:48.000Z",      "phoneNumbers": [        "xxxxxxxxx"      ],      "statusDescription": "Active"    },

Followed by more results starting with { "id": and so on.

I've replaced sensitive data here with xxxxxx

 

[sunfighter]

I now have some time. I believe your problem lies in getting values from a Json string and so I'll take some time and explain it to you. If this is not what your looking for or if you have a problem extracting a specific value please ask me how.

First we start with a simple example:

myOBJ = {
    "id": "GB-1-xxxxxx",
    "country": "GB",
    "phoneNumbers": [        "(111) xxx-xxxx", "(222) xxx-xxxx"     ],
    "safeNo": "safeNo",
    "name": "name"
}

We extract the id using two JS commands
myJSON = JSON.stringify(myOBJ);
obj=JSON.parse(myJSON);
We will always use these two commands!
We will show the value by placing it into a DIV with an ID of "demo".
The right side IS the value we extract. So this is what you always want.

document.getElementById("demo").innerHTML = obj.id;

Notice the use of square brackets on the element "phoneNumbers" This show an array. We can't use
document.getElementById("demo").innerHTML = obj.phoneNumbers; to get a single number, we get the entire array. So we need to use
document.getElementById("demo").innerHTML = obj.phoneNumbers[0]; to get the single numbers out of an array.
Remember this, we'll be getting back to it.

If a Json object contains a number of Json string it is normally written as an array like this:

myOBJ = [{
    "id": "GB-1-xxxxxx",
    "country": "GB",
    "safeNo": "safeNo",
    "name": "name"
},
{
"id": "GB-2-2x2x2",
    "country": "GB",
    "safeNo": "safeNo",
    "name": "name"
}];

We find our different element by referring the array elements:
document.getElementById("demo").innerHTML = obj[1].id;

Now lets look at what you gave us:

myOBJ = { "correlationId": "1a9fcfb0-647e-11eb-b00e-068768768",  "totalSize": 847090, "companies":[{
    "id": "GB-1-xxxxxx",
    "country": "GB",
    "phoneNumbers": [        "(111) xxx-xxxx", "(222) xxx-xxxx"     ],
    "safeNo": "safeNo",
    "name": "name"
},

    {
        "id": "GB-2-xxxxxx",
        "country": "GB22",
        "phoneNumbers": [        "(111) x222-xxxx", "(222) x222-xxxx"     ],
        "safeNo": "safeNo",
        "name": "name"
    }
]};

We can get "correlationId" or "totalSize" like in the first example:
document.getElementById("demo").innerHTML = obj.correlationId;
But "companies" is an array, we get the "id"s like this:
document.getElementById("demo").innerHTML = obj.companies[1].id;
And the phone numbers like this:
document.getElementById("demo").innerHTML = obj.companies[1].phoneNumbers[1];

Hope this helps you and is what you were asking.

---

 

[speckytwat]

Many thanks for the detailed reply. I've tried to work out what I now need to do, do I add the following into a <script> tag in the document header?

myOBJ = {
    "id": "GB-1-xxxxxx",
    "country": "GB",
    "phoneNumbers": [        "(111) xxx-xxxx", "(222) xxx-xxxx"     ],
    "safeNo": "safeNo",
    "name": "name"
}
myJSON = JSON.stringify(myOBJ);
obj=JSON.parse(myJSON);
document.getElementById("demo").innerHTML = obj[1].id;

And then put the following in the body?

<div id="demo"></div>

I did try exactly this, but it doesn't output anything unfortunately. Also, I don't know if this has anything to do with it, but the JSON data is pulled in by authorizing with an API using PHP curl, this is then placed in a variable (which definitely contains the right data if i output it to the screen)

curl_setopt($ch, CURLOPT_HTTPHEADER, array('Content-Type: application/json','Authorization: '.$token3.'')); //token3 is the user / pw
curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
curl_setopt($ch, CURLOPT_URL, 'https://myconnectionurl/companies?countries=GB&name='.$companyname); //where $companyname is taken from a form input filled by the user

//Execute the request
$result = curl_exec($ch);

All very frustrating as I can see the data on the screen- it just needs to be placed in a HTML table...

 

[sunfighter]

Here is why the first code segment you posted did not work. The myOBJ you had is a single Json object. The line that shows it 'document.getElementById("demo").innerHTML = obj[1].id;' is for a Json array.
The myOBJ 's I gave you were for training. You should set myOBJ equal to the object your getting from the API. Or you could just use the variable you have already set the data to - just remenber to change myOBJ to that variable name. Once you have that you add this to your code:

myJSON = JSON.stringify(YOUR VARIBLE GOES HERE);
obj=JSON.parse(myJSON);

I used <div id="demo"></div> for training. It was there to display the elements of the Json file. You can use it to see if things are working for you, but you really want to display the Json in the table you want or have. Do you have the table already programed?

 

[speckytwat]

Thanks, I did try setting up a table previously with another script but it didn't work.

The problem with setting the myOBJ equal to the object I get from the API is that the data is likely to be different each time- how would I write this into the script? I did set up some PHP to grab that data from the screen and save it as a JSON file results.json - should I put this in there? e.g

myJSON = JSON.stringify(results.json);

I created the results.json file (which contains valid data - I checked) using:

$fp = fopen('result.json', 'w');
fwrite($fp, $result);

 

[sunfighter]

$result is the variable name of the Json file you have saved. Use that.