解析JSON数据并插入MySQL [英] Parsing JSON data and inserting to MySQL
问题描述
所以基本上我想在PHP中解析一个JSON文件并将数据插入到特定的表/列中.目前，我有一个有效的脚本，但需要我对其进行大量修改，直到它起作用为止.但是，由于我要收集的JSON数据的大小可能会有所不同，因此会有更多的数据行，因此最终将无法正常工作.
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.
JSON文件的结构与我所见过的大多数不同.可能是因为其输出数据来自传感器单元.我想将数据和序列号插入数据表中，并有一个error_log表，可以在其中将序列号和错误消息存储为字符串.我该如何实现?
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 文件:
{
"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 代码:
<?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));
?>
表测试数据表和error_log表
Tables Test data table and error_log table
JSON数组var_dump- JSON var dump
JSON array var_dump - JSON var dump
任何帮助都会很棒
(在我掌握了要点之后，我想合并PDO)
(After i get the general gist i want to incorporate PDO)
推荐答案
- 不要盲目地将json转换为关联数组.这会带来更多问题.
- 用于访问包含特殊字符或保留字符的属性
单词使用占位符，例如
$data->{'$ts'} - 根据需要遍历数组和对象.
- 向表中添加自动增量
id列有助于存储数据 一台设备. - 将
time也添加到error_log表是一个好主意
- 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
idcolumn to tables helps to store data for one device. - It is a good idea to add
timetoerror_logtable as well
经过测试的原始问题的简短版本，它可以正常工作.
Tested bellow short version of your original question and it works.
<?php
$_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);
?>
这篇关于解析JSON数据并插入MySQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
