Skip to content

Instantly share code, notes, and snippets.

@muety
Last active January 25, 2024 07:42
  • Star 1 You must be signed in to star a gist
  • Fork 1 You must be signed in to fork a gist
Star You must be signed in to star a gist
Save muety/3dcbb22916a4812cf3ed40ff17f1d9e2 to your computer and use it in GitHub Desktop.
OwnTracks receiver script
<?php
$data = file_get_contents('php://input');
$json = json_decode($data);
header("Content-type: application/json");
if ($json->_type !== 'location') {
return;
}
$db = new mysqli('localhost:3306', 'otr', 'sshhh', 'otr');
if ($db->connect_error) {
die('Connection failed: ' . $db->connect_error);
}
$stmt = $db->prepare('INSERT INTO recordings (user, device, acc, alt, batt, bs, conn, created_at, lat, lon, t, tid, tst, vac, vel) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);');
if ($stmt === false) {
die('failed to prepare query: ' . $db->error);
}
if ($stmt->bind_param('ssiiiisiddssiii', $user, $device, $json->acc, $json->alt, $json->batt, $json->bs, $json->conn, $json->created_at, $json->lat, $json->lon, $json->t, $json->tid, $json->tst, $json->vac, $json->vel) === false) {
die('failed to bind params: ' . $stmt->error);
}
$user = $_SERVER['HTTP_X_LIMIT_U'];
$device = $_SERVER['HTTP_X_LIMIT_D'];
if ($stmt->execute() === false) {
die('failed to insert: ' . $stmt->error);
}
$stmt->close();
$db->close();
print json_encode(array());
@gorgonite
Copy link

Hello,
Thanks for this useful and short script :)
Is it possible to share a "create table" sample file ?

@muety
Copy link
Author

muety commented Dec 23, 2023

CREATE TABLE `recordings` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `acc` int DEFAULT NULL,
  `alt` int DEFAULT NULL,
  `batt` int DEFAULT NULL,
  `bs` int DEFAULT NULL,
  `conn` varchar(8) DEFAULT NULL,
  `created_at` bigint unsigned NOT NULL,
  `t` varchar(8) DEFAULT NULL,
  `tid` varchar(8) DEFAULT NULL,
  `tst` bigint unsigned NOT NULL,
  `vac` int DEFAULT NULL,
  `vel` int DEFAULT NULL,
  `user` varchar(64) NOT NULL,
  `device` varchar(64) NOT NULL,
  `source` varchar(16) DEFAULT NULL,
  `lat` float(9) DEFAULT NULL,
  `lon` float(9) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `recordings_unique` (`created_at`,`tst`),
  KEY `recordings_user_IDX` (`user`,`device`) USING BTREE,
  KEY `recordings_tst_IDX` (`tst`) USING BTREE
)

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment