Double-checking if my PHP script would parse and add JSON data to database

Firmware
1

Hi folks

I am trying to parse JSON data
example is

{"event":"cellData","data":"MCC:111, MNC: 11, LAC:53a9, CI:74c1, BSIC:0b, 
Arfcn:00111, Arfcn_ded:INVALID_ARFCN","published_at":"2019-05-09T10:58:20.970Z",
"coreid":"11111111111111111111111"}

The data will be sent from Electron via webhook to my php script on my server. PHP script will parse it and append a record line to a growing MySQL database.

I am new in Particle/PHP and try to avoid obvious errors at the beginning. I have seen some similar topics but could you please skim over my php code prototype and point out what is wrong and missing?

<?php

$json=$_POST;

//not sure of this return product and how to address elements of it correctly
$data=json_decode($json, true);

/* for debugging json decoding
var_dump($data);
*/

//should be careful with putting this in php explicitly - see methods of hiding it
$hostname="localhost";
$username="user";
$password="password";
$db="TESTDB";

$dbconnect=mysqli_connect($hostname,$username, $password,$db);

if ($dbconnect->connect_error) {
	die("Database connection failed:" . $dbconnect->connect_error);
}

//not sure that this is correct way to address to elements of parsed array
$mcc=$data['MCC'];
$mnc=$data['MNCC'];
$lac=$data['LAC'];
$ci=$data['CI'];
$bsic=$data['BSIC'];

//need to think how to convert it correctly - see tips in the folder
$ts=$data['published_at'];

$query="INSERT INTO MyTest (mcc, mnc, lac, ci, bsic, ts)
VALUE ('$mcc', '$mnc', '$lac', '$ci', '$ci', '$bsic', '$ts')";

if (!mysqli_query($dbconnect, $query)){
	die('An error occured.');
}
else{
	echo "Success!";
}

// should I disconnect it here from db?

?>

Rickkas7 kindly published tutorial for working with Google Firebase

but I would like to use my own server.

Thanks for your feedback and apologies if I have missed some identical threads!

2

Not addressing the PHP side, but rather your “source” data.
Your sample data seems not to be valid JSON.

Unless your endpoint doesn’t actually expect valid JSON your data entry should also be wrapped in curly braces and each individual key should be wrapped in double quotes.

3

I am right, I think it should be as follows. When I was receiving this event in terminal using curl request to webhook the individual keys were in double quotes also as below:

{"event":"cellData",
{"data":"MCC:111, MNC: 11, LAC:53a9, CI:74c1, BSIC:0b, 
Arfcn:00111, Arfcn_ded:INVALID_ARFCN",
"ttl":60, 
"published_at":"2019-05-09T10:58:20.970Z",
"coreid":"11111111111111111111111"}}
4

I’d rather expect it to look like this

{"event":"cellData",
"data": "{ \"MCC\":111, \"MNC\": 11, \"LAC\":\"53a9\", \"CI\":\"74c1\",
\"BSIC\":\"0b\", \"Arfcn\":00111, \"Arfcn_ded\": \"INVALID_ARFCN\" }",
"ttl":60, 
"published_at":"2019-05-09T10:58:20.970Z",
"coreid":"11111111111111111111111"}

Where \" stands for embedded double quotes and for the numeric literals it may be required that they also are wrapped in embedded double quotes but the HEX and BIN values would either require the correct prefix (0x/0b) or should be represented as DEC while values starting with 0 may be interpreted as OCT unless wrapped in double quotes.

5

OK, I guess your version is correct one

6

The original JSON is invalid - use JSON Lint to check once you have updated it.

Once you get your PHP working - this is a great way to make it run as a service Getting a Python script to run in the background (as a service) on boot

7

OK I found a valid JSON

{
	"event": "cellData",
	"data": {
		"MCC": 111,
		"MNC": 11,
		"LAC": "53a9",
		"CI": "74c1",
		"BSIC": "0b",
		"Arfcn": "00111",
		"Arfcn_ded": "INVALID_ARFCN "
	},
	"ttl": 60,
	"published_at": "2019-05-09T10:58:20.970Z",
	"coreid": "11111111111111111111111"
}
8

Could you comment on my php script prototype? Any obvious mistakes or pieces missing? Thanks

9

Hi, I am no PHP expert - a pure beginner - I did check against a similar SQL insert script I use and it seems very similar. Sorry can’t help much more