Jump to content
hokletrain

Parsing JSON data and inserting to MySQL

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)

Edited by hokletrain

Share this post


Link to post
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?

Share this post


Link to post
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"},

Edited by hokletrain

Share this post


Link to post
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

Edited by Sepodati
  • Like 1

Share this post


Link to post
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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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?

 

 

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
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

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


×
×
  • 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.