Parsing JSON data and inserting to MySQL

[hokletrain]

Hey, so basically i want to parse a JSON file in PHP and insert the data into specific tables/columns. At the moment i have a working script but requires me to modify the JSON largely until it works. Which might be a pain because the JSON data I'm collecting can vary in size having more data rows. The JSON file is structured differently to most i have seen , maybe because its output data from sensor units. I want to insert the data and the serial number into the data table, and have an error_log table where i can store the serial number and error messages as strings. 

 

JSON File -  https://puu.sh/xtmzy/15585e6ae8.json

 

PHP :

<?php
    //connect to mysql db
  $myConnection= mysqli_connect("localhost","root","******", "ii") or die ("could not connect to mysql"); 

    //read the json file contents
    $jsondata = file_get_contents('test.json');
    
    //convert json object to php associative array
    $data = json_decode($jsondata, true);
    
   
    $id = $data['device']['sn'];
     $ts = $data['data']['$ts'];
    $RH = $data['data']['RH'];
  $AT = $data['data']['AT'];
    $MINVi = $data['data']['MINVi'];
    $PTi = $data['data']['PTi'];
    $SDB = $data['data']['SDB'];
    $LWS = $data['data']['LWS'];
    $WSAV = $data['data']['WSAV'];
    $WSMX = $data['data']['WSMX'];
    $WSMN = $data['data']['WSMN'];
    $PR_TOT = $data['data']['PR_TOT'];
    $RAIN = $data['data']['RAIN'];
    $FDI = $data['data']['FDI'];
    $DT = $data['data']['DT'];
    $LAT = $data['data']['LAT'];
    $LON = $data['data']['LON'];
     $WD = $data['data']['WD'];
    $P1 = $data['data']['P1'];
    $AVGCi = $data['data']['AVGCi'];

 
    
    //insert into mysql table
    $sql = "INSERT INTO test(sn, date, RH, AT, MINVi, PTi, SDB, LWS, WSAV, WSMX, WSMN, PR_TOT, RAIN, FDI, DT, LAT, LON, WD, P1, AVGCi)
    VALUES('$id', '$ts', '$RH','$AT', '$MINVi', '$PTi', '$SDB', '$LWS', '$WSAV', '$WSMX', '$WSMN', '$PR_TOT', '$RAIN', '$FDI', '$DT', '$LAT', '$LON', '$WD', '$P1', '$AVGCi')";
   

 $query=mysqli_query($myConnection, $sql) or die(mysqli_error($myConnection)); 
?>

MySQL Tables:

 

Data Table

 

 

        Error_Log Table

 

 

Any help would be greatly appreciated!

 

(P.S After I have the general gist of things I'm planning on incorporating PDO)

Edited September 7, 2017 by hokletrain

[Sepodati]

Are you sure that's the right code? $data['data']['RH'] doesn't exist, based on that JSON file. $data['data'][4]['RH'] does. The fourth element of $data['data'] has all of those values.

 

Is that what you mean by having to modify the JSON heavily?

 

When you say the file could have more data "rows", what do you mean? There'd be more of these:

 

{

"$ts": 170801170000,

"RH": 67.15,

"AT": 12.87,

"MINVi": 3.81,

"PTi": 23.4,

"LWS": "0*T",

"WSAV": 0,

"WSMX": 0,

"WSMN": 0,

"PR_TOT": 156,

"RAIN": 0,

"FDI": 0.239,

"DT": 2.881,

"WD": "0*T",

"P1": "0*T",

"AVGCi": 175

}

 

One after the other, or what?

[hokletrain]

Yes i gathered the  $data['data']['RH'] part of the code isn't right , hence why i posted it just to see if i can find the proper way of doing it? And oh okay the fourth element, can you please explain that part? I haven't used JSON with PHP before so i'm quite new to how some of the code works. And yes that's correct sometimes there will be more of those: 

 

{

"$ts": 170801170000,
"RH": 67.15,
"AT": 12.87,
"MINVi": 3.81,
"PTi": 23.4,
"LWS": "0*T",
"WSAV": 0,
"WSMX": 0,
"WSMN": 0,
"PR_TOT": 156,
"RAIN": 0,
"FDI": 0.239,
"DT": 2.881,
"WD": "0*T",
"P1": "0*T",
"AVGCi": 175
}

 

If the file contained more sets it would look like this :

 

{"$ts":170725171400,"RH":94.43,"AT":11.418,"MINVi":3.74,"PTi":21,"LWS":"0*T","WSAV":3.73,"WSMX":5.74,"WSMN":2.02,"PR_TOT":132.8,"RAIN":0,"FDI":0.661,"DT":-0.039,"WD":"0*T","P1":"0*T","AVGCi":162},
{"$ts":170725173000,"$msg":"WDT;WV01"},
{"$ts":170725173000,"$msg":"WDT;SDI12"},
{"$ts":170725173000,"$msg":"WDT;LWS"},
{"$ts":170725173000,"RH":94.4,"AT":10.935,"MINVi":3.75,"PTi":20.2,"LWS":"0*T","WSAV":2,"WSMX":2.45,"WSMN":0,"PR_TOT":132.8,"RAIN":0,"FDI":0.449,"DT":-0.007,"WD":"0*T","P1":"0*T","AVGCi":167},
{"$ts":170725174400,"$msg":"WDT;LWS"},
{"$ts":170725174400,"$msg":"WDT;WV01"},
{"$ts":170725174400,"$msg":"WDT;SDI12"},
{"$ts":170725174400,"RH":94,"AT":10.681,"MINVi":3.74,"PTi":19.6,"LWS":"0*T","WSAV":1.93,"WSMX":2.51,"WSMN":0,"PR_TOT":132.8,"RAIN":0,"FDI":0.44,"DT":0.046,"WD":"0*T","P1":"0*T","AVGCi":168},
{"$ts":170725180000,"$msg":"WDT;WV01"},
{"$ts":170725180000,"SDB":"0*I"},

Edited September 7, 2017 by hokletrain

[Sepodati]

The way you've decoded it, $data['data'] is an array. The first four elements (0-3) are those {"$ts": 170801170000,"$msg": "WDT;LWS"} entries. Each {} and whatever is in it is an element of the data[data] array.

 

The fifth Element (key 4, arrays start at zero) is the actual data you're after.

 

Knowing that, will the data always start at element 5? Or could there be more or less of the $ts/$msg elements? (EDIT: Just saw your edit. The code below will still work.)

 

One idea is that you could loop through $data['data'] with a foreach ($data['data'] as $value). Check if isset($value['RH']) and if it is, you're in an element with data.

 

Check this out: https://3v4l.org/K7iU1

Edited September 7, 2017 by Sepodati

[kutchbhi]

For now don't worry about JSON. The following converts JSON into a regular PHP array. An array which has more arrays inside it  ( Multidimensional  array)

//convert json object to php associative array
$data = json_decode($jsondata, true);

Simple explanation: For an array

    $carsArr = array(
        array("Volvo", 22, 18),
        array("BMW", 15, 13),
        array("Saab", 5, 2),
        array("Land Rover", 17, 15)
    );

$carsArr[0] - first item in the array .
Basically $carsArr[0] is equal to ("Volvo", 22, 18)

Now $carsArr[0][1] would be the second item of $carsArr[0] . That is  '22' here.
$carsArr[0][0] would be the - "Volvo "

 

And so on

[Sepodati]

Yeah, you're not really dealing with JSON. It's all PHP arrays when you decode it like that, which is fine.

[hokletrain]

Thanks for clearing up so much guys! Sepodati thanks for adding some code as well. As far as inserting each value into the database tables, whats the best way to go about that?

 

 

[Sepodati]

Easiest way would be to create and execute a query each time you find the data you're after.

 

You could also create a single query in the format of

 

INSERT INTO table () VALUES (...),(...),(...);

 

building each (...) set of data in your loop.

[hokletrain]

Okay i'm gonna give that a crack. Is it bad to execute so many query's separately?  Once again thanks for all the help Sep.

[Sepodati]

Seeing you reference an 'SDB' element of the data, which isn't there, leads me to believe the data in those blocks can change? In that case, creating a query within reach loop, with the relevant columns, would be best.

 

Which method is better or worse is all relative... Do whichever makes sense to you.

 

John

[Barand]

Inserting with the multiple insert sytax

INSERT INTO (...) VALUES (...),(...),(...);

is much faster (600x faster in the test here)

 

https://forums.phpfreaks.com/topic/302727-multiple-updates/?do=findComment&comment=1540277