Parsing Inconsistent JSON data: PHP

Question

The application Pipedrive gives me inconsistent json data. For example, in some array elements, it gives me "formatted_value":"$3,500","weighted_value":2100,"formatted_weighted_value":"$2,100","rotten_time":null, while in others, it gives me, "formatted_value":"$2,950","rotten_time":null,"weighted_value":2950,"formatted_weighted_value":"$2,950". I would like the json data to be in the order of formatted_value,weighted_value,formatted_weighted_value,rotten_time in every array element, but that's not the case sadly.

Does anyone know of a way to check that the right data is written the right column based on column name and key name?

Below is my code to parse the json data:

function parseFunction($startPos) {
$url = 'urlToCallJsonData';
$ch = curl_init($url); //initialize connection with a URL


if(is_callable('curl_init'))
{
    echo "Enabled";
}
else
{
    echo "Not enabled";
}

curl_setopt($ch, CURLOPT_URL, $url);
curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1); 
curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, true);
curl_setopt($ch, CURLOPT_SSL_VERIFYHOST, 2);
curl_setopt ($ch, CURLOPT_CAINFO, dirname(__FILE__)."/cacert.pem");
$json_response = curl_exec($ch);
$info = curl_getinfo($ch);
$status = curl_getinfo($ch, CURLINFO_HTTP_CODE);
if ( $status != 200 )
{
    die("Error: call to URL $url failed with status $status, response $json_response, curl_error " . curl_error($ch) . ", curl_errno " . curl_errno($ch));
}
curl_close($ch);

$response = json_decode($json_response, true);

$count = Count($response['data']);

for ($x=0; $x<$count; $x++)
{
    $currentRecord = $response['data'][$x];
}

//open writing to file
if($startPos == 0)
{
    $fp = fopen('cacheDeals.csv', 'w');
}
else
{
    $fp = fopen('cacheDeals.csv', 'a');
}

$test_array = $response['data'][0];//test_array = first row of data

// writes the headers to the csv file. 
if($startPos == 0)
{
    $keys = array_keys($test_array);
    fputcsv($fp, $keys);
}

$array_records = $response['data'];//all of incoming data
//write data to csv file
foreach ($array_records as $fields)
{
    fputcsv($fp, $fields);
}

//check to see if more data should be written
$more = $response[additional_data][pagination][more_items_in_collection];
$nextStart = $response[additional_data][pagination][next_start];
if($more =="true")
{
    downloadPipedriveDealsData($nextStart);
}

}//end of function

parseFunction(0);

what does the order of the elements matter? it's better to rely on the name, which is the whole point of associative type structures. You can ksort the array or some other sorting method, but this comes with a performance cost. — ArtisticPhoenix, Jul 21 '15 at 18:35
why not [sort the array by keys](http://php.net/manual/en/function.ksort.php) after you get the response? otherwise key arrangement shouldn't matter. — iam-decoder, Jul 21 '15 at 18:44
@iam-decoder I can't simply sort an array with `ksort` because the data i'm parsing is JSON data with objects and arrays embedded within one another. — pHorseSpec, Jul 21 '15 at 19:04
@ArtisiticPhoenix I used the naming method for a few `key:value` pairs but it actually made my data less consistent, but that may be because I'm combining the `namedKeyArray` with the proper array in the JSON data. I'm going to try the naming method with every `key:value` pair in the json data, and I'll let you know how it goes. — pHorseSpec, Jul 21 '15 at 19:07
@pHorseSpec you only gave me a snippet of JSON, I just acted on that. but you should be able to make your own parse function to deal with something like that with a combination of `json_decode`s and `ksort`s — iam-decoder, Jul 21 '15 at 19:20
your using json_decode( $jsonstring, true ); right? never mind I see it, some of this code makes no sense though, for example this for ($x=0; $x<$count; $x++) { $currentRecord = $response['data'][$x]; } — ArtisticPhoenix, Jul 21 '15 at 19:49
@ArtisiticPhoenix Yes, `$response = json_decode($json_response, true);` — pHorseSpec, Jul 21 '15 at 19:51
you could do a loop and then ksort each set, and put them in a new array. — ArtisticPhoenix, Jul 21 '15 at 20:00
I would simply use the first objects keys as the column names. The ksort approach will fail unless all objects contain the exact same keys. Since you don't control the incoming data, you shouldn't assume that. — Phil, Jul 21 '15 at 23:24
I ended up not using `ksort` and I mapped each column's data to a specific value in the json data object — pHorseSpec, Jul 22 '15 at 18:46

score 0 · Accepted Answer · edited May 23 '17 at 12:14

Sorry but I cant point this out in comments alone, some of this could be cleaner such as

    //open writing to file
    if($startPos == 0)
    {
        $fp = fopen('cacheDeals.csv', 'w');
    }
    else
    {
        $fp = fopen('cacheDeals.csv', 'a');
    }

    $test_array = $response['data'][0];//test_array = first row of data

    // writes the headers to the csv file. 
    if($startPos == 0)
    {
        $keys = array_keys($test_array);
        fputcsv($fp, $keys);
    }

Could be simply this

  // writes the headers to the csv file. 
    if($startPos == 0){
        $fp = fopen('cacheDeals.csv', 'w');
        fputcsv($fp, array_keys($response['data'][0]));
    }else{
        $fp = fopen('cacheDeals.csv', 'a');
    }

I don't see a purpose to this whole block at all

$count = Count($response['data']);

for ($x=0; $x<$count; $x++)
{
    $currentRecord = $response['data'][$x];
}

This syntax is invalid

$more = $response[additional_data][pagination][more_items_in_collection];
$nextStart = $response[additional_data][pagination][next_start];

And will issue a Notice ( undefined constant assuming '' ) etc. because there are no quotes around the string keys in the arrays. In the unlikly event that one of those keys is a constant, that's a whole other can of worms. Because you will never get your data out then. They should be done this way with ' or " 's around them. see also What does the PHP error message "Notice: Use of undefined constant" mean?

$more = $response['additional_data']['pagination']['more_items_in_collection'];
$nextStart = $response['additional_data']['pagination']['next_start'];

Just saying.

@pHorseSpec because it's an unquoted string literal, and php will see these as being constants, which they are not, it falls back to them being a string but issues a warning. https://en.wikipedia.org/wiki/String_literal, http://php.net/manual/en/language.types.array.php — ArtisticPhoenix, Jul 21 '15 at 23:10

Parsing Inconsistent JSON data: PHP

1 Answers1