JSON - PHP - MySql

Question

I have a question about how to get the following json output into a mysql database with two columns. If the json had keys called "time" and "kwh", that would have been very helpful, but alas, it does not.

The data that interests me is the time and the first argument that follows:

1418515200000 and the number 17.55
1418601600000 and the number 12.15
1418688000000 and the number 12.49, etc.

If the keys and objects were labled with "time":"1418515200000","kwh":"17.55" -- I would know what to do with this data, ..

$obj=json_decode(http://json-file.com);
$dates = $obj->{"time"};
$kwh = $obj->{"kwh"};
INSERT INTO TABLE (dates, kwh) VALUES ($dates, $kwh);

JSON data:

{
    "status": "ok",
    "data": {
        "1418515200000": [
            17.55,
            "undef"
        ],
        "1418601600000": [
            13.24,
            "undef"
        ],
        "1418688000000": [
            12.15,
            "undef"
        ],
        "1418774400000": [
            12.49,
            "undef"
        ],
        "1418860800000": [
            14.02,
            "undef"
        ],
        "1418947200000": [
            12.57,
            "undef"
        ],
        "1419033600000": [
            15.18,
            "undef"
        ],
        "1419120000000": [
            13.52,
            "undef"
        ],
        "1419206400000": [
            23.9,
            "undef"
        ],
        "1419292800000": [
            16.44,
            "undef"
        ],
        "1419379200000": [
            19.39,
            "undef"
        ],
        "1419465600000": [
            12.08,
            "undef"
        ],
        "1419552000000": [
            21.42,
            "undef"
        ],
        "1419638400000": [
            15.46,
            "undef"
        ],
        "1419724800000": [
            20.46,
            "undef"
        ],
        "1419811200000": [
            16.6,
            1.92
        ],
        "1419897600000": [
            12.69,
            17.57
        ],
        "1419984000000": [
            16.71,
            14.18
        ],
        "1420070400000": [
            13.21,
            13.19
        ],
        "1420156800000": [
            15.99,
            13.55
        ],
        "1420243200000": [
            23.34,
            10.36
        ],
        "1420329600000": [
            11.51,
            11.95
        ],
        "1420416000000": [
            11.15,
            13.9
        ],
        "1420502400000": [
            11.18,
            11.33
        ],
        "1420588800000": [
            12.9,
            11.76
        ],
        "1420675200000": [
            17.12,
            13.52
        ],
        "1420761600000": [
            12.38,
            14.75
        ],
        "1420848000000": [
            12.59,
            22.06
        ]
    }
}

Barmar · Accepted Answer · 2015-01-11 22:27:56Z

2

Use the second argument to json_decode to get associative arrays instead of objects. Then you can use foreach.

$json = json_decode($response, true);
$stmt = $pdo->prepare("INSERT INTO TABLE (dates, kwh) VALUES (:dates, :kwh)");
$stmt->bindParam(':dates', $dates);
$stmt->bindParam(':kwh', $kwy);
foreach ($json['data'] as $dates => $kwh) {
    $stmt->execute();
}

answered Jan 11, 2015 at 22:27

Barmar

789k57 gold badges554 silver badges669 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

MakG · Accepted Answer · 2015-01-11 22:32:27Z

0

$data = json_decode('json');
foreach($data->data as $time => $entry)
{
  $value = $entry[0];

  // $time is time
  // $value is the first value for certain time

  // process your data or echo
  echo $time . '_' . $value;
}

answered Jan 11, 2015 at 22:32

MakG

1,2749 silver badges14 bronze badges

Collectives™ on Stack Overflow

JSON - PHP - MySql

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related