I thought I would share my code with you all to parse the post data via php into a mysql database:
table geo_fence
CREATE TABLE `geo_fence` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`timestamp` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`device_name` VARCHAR(50) NOT NULL,
`entered_or_left` VARCHAR(50) NOT NULL,
`fence_name` VARCHAR(50) NOT NULL,
`date_time` DATETIME NOT NULL,
`latitude` DECIMAL(10,8) NULL DEFAULT NULL,
`longitude` DECIMAL(10,8) NULL DEFAULT NULL,
PRIMARY KEY (`id`)
);
table geo_fence_live
CREATE TABLE `geo_fence_live` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`timestamp` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`device_name` VARCHAR(50) NOT NULL,
`entered_or_left` VARCHAR(50) NOT NULL,
`fence_name` VARCHAR(50) NOT NULL,
`date_time` DATETIME NOT NULL,
`latitude` DECIMAL(10,8) NULL DEFAULT NULL,
`longitude` DECIMAL(10,8) NULL DEFAULT NULL,
PRIMARY KEY (`id`)
);
mysql.php
<?php
// MYSQL Config
$mysql_server = "localhost" ;
$mysql_user = "tracker" ;
$mysql_pass = "changeme" ;
$mysql_db = "tracker" ;
$mysql_table_geo = "geo_fence";
$mysql_table_geo_live = "geo_fence_live";
?>
geo_fence.php
<?php
ini_set('display_errors', 'On');
// If you want to see output on the HTTP return turn this into a 1
$output_debug = 0;
// MYSQL database/username/password configuration
require("config/mysql.php");
// Make sure that it is a POST request.
if(strcasecmp($_SERVER['REQUEST_METHOD'], 'POST') != 0){
throw new Exception('Error');
}
// Make sure that the content type of the POST request has been set and contains application/json
$contentType = isset($_SERVER["CONTENT_TYPE"]) ? trim($_SERVER["CONTENT_TYPE"]) : '';
if(strpos($contentType, 'application/json') != 0){
throw new Exception('Error');
}
// Receive the RAW post data.
$json_data = trim(file_get_contents("php://input"));
// Attempt to decode the incoming RAW post data from JSON.
$data = json_decode($json_data, true);
// If json_decode failed, the JSON is invalid.
if(!is_array($data)){
throw new Exception('Received content contained invalid JSON!');
}
$device_name = $data['device_name'];
$entered_or_left = $data['entered_or_left'];
$fence_name = $data['fence_name'];
$date_time = $data['date_time'];
$latitude = $data['latitude'];
$longitude = $data['longitude'];
// Insert data into database
geo_mysql_insert($device_name,$entered_or_left,$fence_name,$date_time,$latitude,$longitude);
function geo_mysql_insert ($device_name,$entered_or_left,$fence_name,$date_time,$latitude,$longitude){
global $mysql_server;
global $mysql_user;
global $mysql_pass;
global $mysql_db;
global $mysql_table_geo;
global $mysql_table_geo_live;
global $output_debug;
// Opens a connection to a MySQL server
$conn=new mysqli($mysql_server, $mysql_user, $mysql_pass, $mysql_db);
// Check connection
if ($conn->connect_error) {
die("Database connection failed: ". $conn->connect_error);
}
$sql = "INSERT INTO ". $mysql_table_geo ." (
device_name,
entered_or_left,
fence_name,
date_time,
latitude,
longitude
) VALUES (
'$device_name',
'$entered_or_left',
'$fence_name',
'$date_time',
'$latitude',
'$longitude'
)";
$sql_update = "UPDATE ". $mysql_table_geo_live ."
SET
timestamp=now(),
device_name='$device_name',
entered_or_left='$entered_or_left',
fence_name='$fence_name',
date_time='$date_time',
latitude='$latitude',
longitude='$longitude'
WHERE
device_name='$device_name'
LIMIT 1
";
if ($output_debug == 1){
echo "SQL Query:\n$sql\n";
echo "SQL Update:\n$sql_update\n";
}
if ($conn->query($sql) === TRUE) {
if ($output_debug == 1){
echo "New record created successfully\n";
}
} else {
if ($output_debug == 1){
echo "Error: " . $sql . "<br>" . $conn->error;
}
}
if ($conn->query($sql_update) === TRUE) {
if ($output_debug == 1){
echo "Record updated successfully\n";
}
} else {
if ($output_debug == 1){
echo "Error: " . $sql_update . "<br>" . $conn->error;
}
}
$conn->close();
}
