Artiphp - CMS open source et gratuit
Vous êtes ici » Accueil/Forum
Saisissez votre recherche
RechercherRechercher   Liste des MembresListe des Membres   S'enregistrerS'enregistrer    ProfilProfil 
Se connecter pour vérifier ses messages privésSe connecter pour vérifier ses messages privés   ConnexionConnexion 


Parsing JSON data and inserting to MySQL

 
Poster un nouveau sujet   Répondre au sujet    Artiloo.com Index du Forum -> Le coin des Developpeurs
Voir le sujet précédent :: Voir le sujet suivant  
Auteur Message
jesuslewis



Inscrit le: 03 Juil 2017
Messages: 214
Localisation: new york usa

MessagePosté le: Lun 25 Sep 2017 7:58    Sujet du message: Parsing JSON data and inserting to MySQL Répondre en citant

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.
Code:

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

please help!
thank you!
Video animationVideo animation
Revenir en haut de page
Voir le profil de l'utilisateur Envoyer un message privé
jesuslewis



Inscrit le: 03 Juil 2017
Messages: 214
Localisation: new york usa

MessagePosté le: Mer 27 Sep 2017 11:28    Sujet du message: Re: Parsing JSON data and inserting to MySQL Répondre en citant

jesuslewis a écrit:
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.
Code:

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

please help!
thank you!
Video animation
Revenir en haut de page
Voir le profil de l'utilisateur Envoyer un message privé
Montrer les messages depuis:   
Poster un nouveau sujet   Répondre au sujet    Artiloo.com Index du Forum -> Le coin des Developpeurs Toutes les heures sont au format GMT + 1 Heure
Page 1 sur 1

 
Sauter vers:  
Vous ne pouvez pas poster de nouveaux sujets dans ce forum
Vous ne pouvez pas répondre aux sujets dans ce forum
Vous ne pouvez pas éditer vos messages dans ce forum
Vous ne pouvez pas supprimer vos messages dans ce forum
Vous ne pouvez pas voter dans les sondages de ce forum


Powered by phpBB © 2001, 2005 phpBB Group
Traduction par : phpBB-fr.com