4

I am trying to receive JSON data from an external system, and then process and save to my local DB. When an event triggers, the external system sends the data below to my system.

The external system logs show:

Log 1:   Date/Time: 16 August 2017 11:54:44 AM
Status: Failure
Server: Apache https://blahblah.com/abc/data.php
Status Code:    OK
Event Content:  [{"field":"_USERNAME","value":""},
{"field":"_PASSWORD","value":""},
{"field":"_TOKEN","value":""},
{"field":"_CODE","value":"L77H4XD6ZA"},
{"field":"_SUBMITTEDDATE","value":"2017.08.16.01.54"},
{"field":"_SUBMITTEDDATEEXT","value":"2017.08.16.01.54.39.610"},
{"field":"_EDITEDDATE","value":"2017.08.16.01.55"},
{"field":"_SEQUENTIALID","value":"39a1cad9-2582-e711-9477-06c7814985cc"},
{"field":"_COMPLETETYPE","value":"Complete"},
{"field":"_LANGUAGE","value":"en"},
{"field":"_TOTALTIME","value":"12.59"},{"field":"_LINKURL","value":"http%3a%2f%2fsurv.blah.com%2ftest3%3fusr%3dL77H4XD6ZA"},
{"field":"GENDER","value":"TEXT%3aFemale%3bVALUE%3a2"},
{"field":"AGE","value":"TEXT%3a40%2b-%2b44%3bVALUE%3a6"},
{"field":"STATE","value":"TEXT%3aVIC%3bVALUE%3a2"},
{"field":"END_CHC","value":"TEXT%3aComplete%3bVALUE%3a2"},
{"field":"D2H","value":""},
{"field":"D2V","value":""},
{"field":"PCODE","value":""},
{"field":"PSTATE","value":""},
{"field":"PREGION","value":""},
{"field":"STATEREGION","value":""},
{"field":"TEST1","value":""}]

So, in my php file (https://blahblah.com/abc/data.php) I have

$json = file_get_contents('php://input');
$obj = json_decode($json);

and I have been trying a foreach() loop to try and get each field and value, to then put into a database, like:

foreach($obj as $key => $value)
{
    $qry = $conn->prepare('INSERT INTO `key_value`(`db_id`, `rkey`, `rvalue`) VALUES (NULL,$key,$value)');
    $qry->execute();
}

But it isn't working, and I either get errors about the foreach loop, or if I play around with just echoing or var_dumping, I get NULL in the response, when I use the Postman or ARC Chrome API testing Apps.

So, I suspect I am on the completely wrong track of how to do this.

Can anyone help guide me back?

EDIT: I have had a look at Receive JSON POST with PHP and a few others, but the working answers aren't clear.

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
RossS
  • 49
  • 1
  • 2
  • 8

1 Answers1

3

You've got a couple of problems here. First, you need to pass true as the second parameter to json_decode in order to get an associative array instead of a stdClass. Then, when you're iterating the results, you don't need the $key, and the $value is each entry in the results array (each "row"). Try running this to see what's going on:

<?php
$json = <<<JSON
[{"field":"_USERNAME","value":""},
{"field":"_PASSWORD","value":""},
{"field":"_TOKEN","value":""},
{"field":"_CODE","value":"L77H4XD6ZA"},
{"field":"_SUBMITTEDDATE","value":"2017.08.16.01.54"},
{"field":"_SUBMITTEDDATEEXT","value":"2017.08.16.01.54.39.610"},
{"field":"_EDITEDDATE","value":"2017.08.16.01.55"},
{"field":"_SEQUENTIALID","value":"39a1cad9-2582-e711-9477-06c7814985cc"},
{"field":"_COMPLETETYPE","value":"Complete"},
{"field":"_LANGUAGE","value":"en"},
{"field":"_TOTALTIME","value":"12.59"},{"field":"_LINKURL","value":"http%3a%2f%2fsurv.blah.com%2ftest3%3fusr%3dL77H4XD6ZA"},
{"field":"GENDER","value":"TEXT%3aFemale%3bVALUE%3a2"},
{"field":"AGE","value":"TEXT%3a40%2b-%2b44%3bVALUE%3a6"},
{"field":"STATE","value":"TEXT%3aVIC%3bVALUE%3a2"},
{"field":"END_CHC","value":"TEXT%3aComplete%3bVALUE%3a2"},
{"field":"D2H","value":""},
{"field":"D2V","value":""},
{"field":"PCODE","value":""},
{"field":"PSTATE","value":""},
{"field":"PREGION","value":""},
{"field":"STATEREGION","value":""},
{"field":"TEST1","value":""}]
JSON;

$obj = json_decode($json, true);

foreach($obj as $currEntry)
{
    echo $currEntry['field'].':'.urldecode($currEntry['value'])."\n";
}

Also some of the results are url encoded, so you'll probably want to decode that before persisting.

Rob Ruchte
  • 3,569
  • 1
  • 16
  • 18