JSON for PHP + MYSQL

Question

My client sent me this JSON so that I loop and record the data in Mysql. Only I’m not getting it because it’s different than what I usually do;

Someone can give me some hint and what is the difference from one format to another.

Error shown is : Warning: Invalid argument supplied for foreach() in line 28

foreach($json_data['ordens_de_servico'] as $key => $value){

Former of the formator I’ve worked with:

{
     "ordem_de_servico": [
         {
             "oser_numero_os": 23940493,
             "oser_address_name": NAME;

CUSTOMER JSON

ordem_de_servico:
        {  
           "oser_numero_os":23940493,
           "oser_dt_abertura":"28/03/2018",
           "servico":{  
              "serv_cod_servico":60,
              "serv_descr_servico":"CORTE POR DEBITO"
           },
           "cliente":{  
              "clie_ident_cliente":638617,
              "nome":"MARIA APARECIDA FERREIRA DO NASCIMENTO"
           },
           "unidade_consumidora":{  
              "unid_ident_uc":2436434,
              "logr_nome_logr_expandido":"R JOSE GUIMARAES"
           },
           "faturas":[  
              {  
                 "total_fatura":"88.44",
                 "ftcd_mes_ano_fatmto":"2017-04-01"
              },
              {  
                 "total_fatura":"45.16",
                 "ftcd_mes_ano_fatmto":"2017-03-01"
              }
           ]
    }

My last Attempt

<?php
    $servername = "localhost";
    $username = "root";
    $password = "";
    $dbname = "webservice";
    // Create connection
    $conn = new mysqli($servername, $username, $password, $dbname);
    // Check connection
    if ($conn->connect_error) {
        die("Connection failed: " . $conn->connect_error);
    } 


    // Read JSON file
    $json = file_get_contents('oss.json');

    //Decode JSON
    $json_data = json_decode($json,true);


    foreach($json_data['ordens_de_servico'] as $key => $value){
        $os = $value["oser_numero_os"];
        $data_abertura = $value["oser_dt_abertura"];

        foreach($json_data['ordens_de_servico'][$key]['faturas'] as $index => $row){

            $valorParcelas = $row["total_fatura"];
            $sql = "SELECT numero_os FROM os WHERE numero_os = '$os'";
            $result = $conn->query($sql);
            if ($result->num_rows > 0) {
                    echo "PULAR FATURA"."<p>";
            }else {     
            $sql = "INSERT INTO faturas (valorParcelas, numero_os) VALUES ('$valorParcelas', '$os')";
                if ($conn->query($sql) === TRUE) {
                    echo "<strong>".$valorParcelas." - FATURA OK"."</strong>"."<p>";
                } else {
                    echo "Error Fatura";
                }
            }
        }
        $sql = "SELECT numero_os FROM os WHERE numero_os = '$os'";
            $result = $conn->query($sql);
            if ($result->num_rows > 0) {
                    echo "PULAR"."<p>";
            }
            else{
                $sql = "INSERT INTO os (numero_os, data_abertura) VALUES ('$os', '$data_abertura')";
                if ($conn->query($sql) === TRUE) {
                    echo "<strong>".$os." - GRAVADO"."</strong>"."<p>";
                } else {
                    echo "Error";
                }
            }

    }

    $conn->close();
    ?>

Answer 1

I took a test var_dump(json_decode($json,true)); and he was returning NULL. This value can be returned for several reasons. In your case the reason was a syntax error JSON_ERROR_SYNTAX. To fix this, I added some elements to the string:

$json = '{'.$json.'}';
$json = str_replace("ordem_de_servico","\"ordem_de_servico\"", $json);
$json_data = json_decode($json, true);

I did the test and the result did not generate any error.

Your code will look like this:

...

// Read JSON file
$json = file_get_contents('oss.json');

//Decode JSON

$json = '{'.$json.'}';
$json = str_replace("ordem_de_servico","\"ordem_de_servico\"", $json);
$json_data = json_decode($json,true);

foreach($json_data['ordens_de_servico'] as $key => $value){

...