JSON - > PHP中的多维数组 - > SQL

Question

I am struggling with the problem of transferring json-multidimensional array data to SQL.

This is my json file (data.json):

{
    "Data 1": {
        "Text1": "Anything1",
        "Text2": "Anything2"
    },
    "Data 2": {
        "2018-08-02": {
            "1.": "145",
            "2.": "258"
        },
        "2018-08-03": {
            "1.": "428",
            "2.": "528"
        },
        "2018-08-04": {
            "1.": "727",
            "2.": "514"
        }
    }
}

I have a php code:

<?php
$db = new PDO('mysql:host=localhost;dbname=test','root','');
$jsonData = file_get_contents('data.json');
$data = json_decode($jsonData, true);
?>

So now I have a php array $data.

I need to load an array into the following columns in sql: "DATE" (eg "2018-08-02" etc), "1.", "2.". This is what the final effect in mysql should look like

And I do not know what to do next.

I tried to do as it is shown in this video: https://www.youtube.com/watch?v=4zTjCpBqSbw and ia other websites (https://www.w3schools.com/js/js_json_php.asp), but for me the date (eg "2018-08-02") is a variable and I do not know how to solve the problem in this case.

Answer 1

Here's one way you could do it.

https://3v4l.org/peLqP

BUT you should not be inserting data in this way - this is for illustration only. Use prepared statements or something similar.

    <?php
    $json = "{
    \"Data 1\": {
        \"Text1\": \"Anything1\",
        \"Text2\": \"Anything2\"
    },
    \"Data 2\": {
        \"2018-08-02\": {
            \"1.\": \"145\",
            \"2.\": \"258\"
        },
        \"2018-08-03\": {
            \"1.\": \"428\",
            \"2.\": \"528\"
        },
        \"2018-08-04\": {
            \"1.\": \"727\",
            \"2.\": \"514\"
        }
    }
}";

$jsonDecoded = json_decode($json, true);
foreach ($jsonDecoded['Data 2'] as $dateKey => $data) {
    $values = implode(',', $data);
    $statement = "INSERT INTO mytable (TheDate, Value1, Value2) VALUES ('{$dateKey}'," . $values . ");";
    echo $statement . PHP_EOL;
}

Output:

INSERT INTO mytable (TheDate, Value1, Value2) VALUES ('2018-08-02',145,258);
INSERT INTO mytable (TheDate, Value1, Value2) VALUES ('2018-08-03',428,528);
INSERT INTO mytable (TheDate, Value1, Value2) VALUES ('2018-08-04',727,514);