Walid Walid - 4 months ago 20
reST (reStructuredText) Question

Getting the ID from a REST GET Request with PHP - Oracle Right Now

I am new at REST and trying to get only the ID from the query

SELECT ID from CO.A_Country where Name="xxx";
using the ROQL. I am using REST with PHP:

<?php
/**
* Example API call
* GET profile information
*/

// the ID of the profile

$profileID = 2;

// the token

$token = 'your token here';

// set up the curl resource

$ch = curl_init();
curl_setopt($ch, CURLOPT_URL,
"https://User:Pass@test.help.com/services/rest/connect/v1.3/queryResults
/?query=select%20ID%20from%20CO.A_Country%20where%20CO.A_Country.A_Country=%27xxx%27");
curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
curl_setopt($ch, CURLOPT_HEADER, 1);
$output = curl_exec($ch);

echo($output). PHP_EOL;

curl_close($ch);


?>

I am getting the following result:

HTTP/1.1 200 OK Date: Thu, 04 Aug 2016 14:58:02 GMT Server: Apache Content-Language: en-US RNT-Time: D=58455 t=1470322682582920 RNT-Machine: 128.64 Transfer-Encoding: chunked Content-Type: application/json { "items": [ { "tableName": "CO.Affected_Country", "count": 1, "columnNames": [ "id" ], "rows": [ [ "12" ] ] } ], "links": [ { "rel": "self", "href": "https://test.help.com/services/rest/connect/v1.3/queryResults?query=select%20ID%20from%20CO.A_Country%20where%20CO.A_Country.Affected_Country=%27USA%27" }, { "rel": "canonical", "href": "https://test.help.com/services/rest/connect/v1.3/queryResults" }, { "rel": "describedby", "href": "https://test.help.com/services/rest/connect/v1.3/metadata-catalog/queryResults", "mediaType": "application/schema+json" } ] }


How to get only the desired ID to be in $output?

Answer

You cannot just get the ID from a ROQL query from the OSvC REST API; especially since your query could return multiple results. This other data will always be returned as part of your results (and other relevant data depending on the endpoint that you use). You need to parse those results to get the ID from your query.

Do not return the header as part of the response body:

curl_setopt($ch, CURLOPT_HEADER, 0);

Then use json_decode to convert the ROQL results into a PHP object, as @Manish Shukla suggests.

$results = json_decode($output);

Then, your ROQL results are parsable. Rows are returned as an array that you can loop through. Since your query doesn't have a limit statement, you should either add that or account for multiple values being returned.

if(!is_null($results['rows'])){ foreach($results['rows'] as $row){ echo $row; //Will output the ID from your select statement. Change this to do something with the ID as needed. } }