json数据导入mysql,解析JSON数据并插入MySQL

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. However, it won't end up working 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. How can I achieve this?

JSON File:

{

"device": {

"sn": 5165654,

"name": "FDI_AWS_DEMO",

"v": "2.7B3"

},

"channels": [

{

"code": "RH",

"name": "Relative Humidity",

"unit": "%"

},

{

"code": "AT",

"name": "Air Temperature",

"unit": "C"

},

{

"code": "MINVi",

"name": "Min voltage",

"unit": "V"

},

{

"code": "PTi",

"name": "Processor temperature",

"unit": "C"

},

{

"code": "SDB",

"name": "Network signal dB",

"unit": "dB"

},

{

"code": "LWS",

"name": "Leaf Wetness",

"unit": "%"

},

{

"code": "WSAV",

"name": "Wind Speed Avg",

"unit": "km/h"

},

{

"code": "WSMX",

"name": "Wind Speed Max",

"unit": "km/h"

},

{

"code": "WSMN",

"name": "Wind Speed Min",

"unit": "km/h"

},

{

"code": "PR_TOT",

"name": "PR Tot",

"unit": "mm"

},

{

"code": "RAIN",

"name": "Rain",

"unit": "mm"

},

{

"code": "FDI",

"name": "fdi",

"unit": "Unit"

},

{

"code": "DT",

"name": "Delta-T",

"unit": "C"

},

{

"code": "LAT",

"name": "Latitude",

"unit": "deg"

},

{

"code": "LON",

"name": "Longitude",

"unit": "deg"

},

{

"code": "WD",

"name": "Wind Direction",

"unit": "Degrees"

},

{

"code": "P1",

"name": "Par1",

"unit": ""

},

{

"code": "AVGCi",

"name": "Average Current",

"unit": "mA"

},

{}

],

"data": [

{

"$ts": 170801164400,

"$msg": "SD_FAIL;1"

},

{

"$ts": 170801170000,

"$msg": "WDT;WV01"

},

{

"$ts": 170801170000,

"$msg": "WDT;SDI12"

},

{

"$ts": 170801170000,

"$msg": "WDT;LWS"

},

{

"$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

},

{}

]

}

PHP Code:

//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));

?>

Tables Test data table and error_log table

JSON array var_dump - JSON var dump

Any help would be great

(After i get the general gist i want to incorporate PDO)

解决方案Do not convert json to associative array blindly. It creates more problems.

For accessing properties containing special characters or reserved

words use placeholders like $data->{'$ts'}

Loop through arrays and objects if needed.

Adding an auto increment id column to tables helps to store data

for one device.

It is a good idea to add time to error_log table as well

Tested bellow short version of your original question and it works.

$_user = 'root';

$_password= 'root';

$_db = 'localtest';

$_host = 'localhost';

$_port = 3306;

$con = new mysqli($_host, $_user, $_password, $_db) or die(mysql_error);

//read the json file contents

$jsondata = file_get_contents('test.json');

//do not convert to array

$json = json_decode($jsondata);

$id = $json->device->sn;

foreach($json->data as $key => $data){

if(empty($data) || !isset($data->{'$ts'})){

continue;

}

if (isset($data->{'$msg'})){

$msg = $data->{'$msg'};

$time = $data->{'$ts'};

$sql="INSERT into error_log (sn, time, MSG) VALUES (?,?,?); ";

$stmt = $con-> prepare($sql);

$stmt -> bind_param("iss", $id,$time, $msg);

$stmt -> execute();

}else{

$time = (isset($data->{'$ts'}))? $data->{'$ts'}:'';

$RH = (isset($data->RH))? $data->RH:'';

$AT = (isset($data->AT))? $data->AT:'';

$MINVi = (isset($data->MINVi))? $data->MINVi:'';

//insert into mysql table

$sql="INSERT into test (sn, date, RH, AT, MINVi) VALUES (?,?,?,?,?); ";

$stmt = $con-> prepare($sql);

$stmt -> bind_param("issss", $id,$time,$RH,$AT,$MINVi);

$stmt -> execute();

}

}

mysqli_close($con);

?>