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
 ]
 }
}

Answer 1

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();
}

Answer 2

$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;
}