Jump to content

Parsing JSON data and inserting to MySQL


hokletrain

Recommended Posts

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

 

afc5c35cd7.png

 

        Error_Log Table

 

bba127d9c7.png

 

Any help would be greatly appreciated!

 

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

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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"},

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.