RonEskinder RonEskinder - 1 month ago 5
JSON Question

php while loop stepped

I have the following GPS data, im trying to get trips (Routes) made by the user based on the engine status, and ordered by date where

engine=1
is a engine start and
engine=2
is a engine off event, so every time i find an
engine=2
i know that is the end of a trip. I need to present this trips in a JSON format, im doing that from php, but im stuck in getting the actual trips individually.

Any help will be appreciated.

mysql> select imei, lat, lon, date, engine from dataGps order by date ;
+-----------------+-----------+------------+---------------------+--------+
| imei | lat | lon | date | engine |
+-----------------+-----------+------------+---------------------+--------+
| 864251020174384 | 12.292415 | -86.236351 | 2016-10-04 10:53:34 | 2 |
| 864251020174384 | 12.134856 | -86.251427 | 2016-10-04 14:14:40 | 1 |
| 864251020174383 | 12.137000 | -86.254501 | 2016-10-04 14:15:21 | 2 |
| 864251020174383 | 12.135400 | -86.253342 | 2016-10-04 14:16:24 | 1 |
| 864251020174383 | 12.134140 | -86.251671 | 2016-10-04 14:19:12 | 1 |
| 864251020174383 | 12.135820 | -86.249687 | 2016-10-04 14:19:57 | 1 |
| 864251020174383 | 12.134770 | -86.250549 | 2016-10-04 14:19:57 | 1 |
| 864251020174383 | 12.136580 | -86.248581 | 2016-10-04 14:20:02 | 1 |
| 864251020174383 | 12.137000 | -86.247551 | 2016-10-04 14:20:02 | 1 |
| 864251020174383 | 12.137160 | -86.246262 | 2016-10-04 14:20:03 | 2 |
| 864251020174383 | 12.137080 | -86.245621 | 2016-10-04 14:22:33 | 1 |
| 864251020174383 | 12.136490 | -86.243942 | 2016-10-04 14:23:28 | 1 |
| 864251020174383 | 12.135990 | -86.243080 | 2016-10-04 14:43:05 | 1 |
| 864251020174383 | 12.135820 | -86.241798 | 2016-10-04 14:43:57 | 1 |
| 864251020174383 | 12.135820 | -86.240211 | 2016-10-04 14:47:04 | 1 |
| 864251020174383 | 12.132720 | -86.237892 | 2016-10-04 14:49:02 | 1 |
| 864251020174383 | 12.133971 | -86.238281 | 2016-10-04 15:11:27 | 1 |
| 864251020174383 | 12.104250 | -86.253792 | 2016-10-11 20:01:36 | 2 |
| 864251020174383 | 12.105340 | -86.251129 | 2016-10-11 20:01:45 | 1 |
| 864251020174383 | 12.106010 | -86.249069 | 2016-10-11 20:02:02 | 1 |
| 864251020174383 | 12.102820 | -86.245644 | 2016-10-11 20:02:20 | 1 |
| 864251020174383 | 12.087050 | -86.231468 | 2016-10-11 20:02:32 | 1 |
| 864251020174383 | 12.065980 | -86.212334 | 2016-10-11 20:02:48 | 1 |
| 864251020174383 | 12.065560 | -86.208298 | 2016-10-11 20:02:56 | 1 |
| 864251020174383 | 12.064720 | -86.205040 | 2016-10-11 20:03:12 | 1 |
| 864251020174383 | 12.064050 | -86.202888 | 2016-10-11 20:03:20 | 1 |
+-----------------+-----------+------------+---------------------+--------+


It should be 3 Routes for device 864251020174383 and one trip for device 864251020174384, but im not getting the hold of it. Any help will be appreciated.

This is what i have so far

<?php

require_once 'includes/PolylineEncoder.php';
require_once 'includes/config.php';

$dbHost = DB_HOST;
$dbUser = DB_USER;
$dbPass = DB_PASS;
$dbBase = DB_DATA;
$charset = 'utf8';

$dsn = "mysql:host=$dbHost;dbname=$dbBase;charset=$charset";
$opt = [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
PDO::ATTR_EMULATE_PREPARES => false,
];

$db = new PDO($dsn, $dbUser, $dbPass, $opt);
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

//date_default_timezone_set('America/Miami');
header('Content-type: application/json');

if ($_POST['idUser']) {
$response = getGpsData($db, $_POST['idUser']);
echo json_encode(['gpsData' => $response]);
} else {
echo json_encode(['error' => 'Faltan parametros']);
}

/**
* Get ID idUser base on the name
* @param $idUser
*/
function getGpsData($db, $idUser) {
try {
// Get Devices from the user
$sql = "SELECT * FROM device WHERE idUser = :idUser;";
$result = $db->prepare($sql);
$result->bindParam(':idUser', $idUser, PDO::PARAM_STR);
$result->execute();

$devices = array();

if ($result->rowCount() > 0) {
$result->bindColumn('idDevice', $idDevice);
$result->bindColumn('idVehicle', $idVehicle);
$result->bindColumn('idSimcard', $idSimcard);
$result->bindColumn('alias', $alias);
$result->bindColumn('imei', $imei);
$result->bindColumn('status', $status);

while ($result->fetch(PDO::FETCH_BOUND)) {

$routes = getGps($db, $imei);

$row = array('idDevice' => $idDevice,
'idVehicle' => $idVehicle,
'idSimcard' => $idSimcard,
'alias' => $alias,
'imei' => $imei,
'routes' => $routes);
array_push($devices, $row);
}
return $devices;
}
} catch (exception $e) {
return $e;
}
}

/**
* Get ID idUser base on the name
* @param $idUser
*/
function getGps($db, $imei) {

$polylineEncoder = new PolylineEncoder();

try {
$sql = "SELECT * FROM dataGps WHERE imei = :imei ORDER BY date;";
$result = $db->prepare($sql);
$result->bindParam(':imei', $imei, PDO::PARAM_STR);
$result->execute();

$routes = array();
$startLocation = array();
$endLocation = array();

if ($result->rowCount() > 0) {
$i = 1;
$result->bindColumn('lat', $lat);
$result->bindColumn('lon', $lon);
$result->bindColumn('date', $date);
$result->bindColumn('engine', $engine);
$result->bindColumn('gpsAccuracy', $accuracy);

while ($result->fetch(PDO::FETCH_BOUND)) {

$polylineEncoder->addPoint($lat, $lon);
//echo "engine:" . $engine ."\n";

if ($engine == 1) {
// Start Location
if ($i == 1) {
// Add Start Location
$startLocation = [
'lat' => $lat,
'lon' => $lon,
'accuracy' => $accuracy
];
$points['startLocation'] = $startLocation;
}
} else if ($engine == 2) {
// Add End Location
$endLocation = [
'lat' => $lat,
'lon' => $lon,
'accuracy' => $accuracy
];
$points['endLocation'] = $endLocation;
$points['path'] = $polylineEncoder->encodedString();
$i = 1;
}

$routes[] = $points;
$i++;
}
return $routes;
}
} catch (exception $e) {
return $e;
}
}

//$decodedPoints = PolylineEncoder::decodeValue("_`dyD~ps|U_eg}@nnqC_mqNvxq`@");
//var_dump($decodedPoints);
?>


The correct format should be this:

"routes": [
{
"startLocation": {
"lat": "xxxxxx",
"lon": "xxxxxx",
"accuracy": "xx"
},
"endLocation": {
"lat": "xxxxxxx",
"lon": "xxxxxxx",
"accuracy": "xx"
},
"path": "sz_jAd`jmO"
},
{
"startLocation": {
"lat": "xxxxxx",
"lon": "xxxxxx",
"accuracy": "xx"
},
"endLocation": {
"lat": "xxxxxxx",
"lon": "xxxxxxx",
"accuracy": "xx"
},
"path": "sz_jAd`jmO"
}
]


Actual JSON Response:

{
"gpsData": [
{
"idDevice": "1",
"idVehicle": "1",
"idSimcard": "1",
"alias": "RonEskinder",
"imei": "864251020174383",
"routes": [
{
"endLocation": {
"lat": "12.137",
"lon": "-86.254501",
"accuracy": "1"
},
"path": "goaiArqmmO"
},
{
"endLocation": {
"lat": "12.137",
"lon": "-86.254501",
"accuracy": "1"
},
"path": "goaiArqmmO"
},
{
"endLocation": {
"lat": "12.137",
"lon": "-86.254501",
"accuracy": "1"
},
"path": "goaiArqmmO"
},
{
"endLocation": {
"lat": "12.137",
"lon": "-86.254501",
"accuracy": "1"
},
"path": "goaiArqmmO"
},
{
"endLocation": {
"lat": "12.137",
"lon": "-86.254501",
"accuracy": "1"
},
"path": "goaiArqmmO"
},
{
"endLocation": {
"lat": "12.137",
"lon": "-86.254501",
"accuracy": "1"
},
"path": "goaiArqmmO"
},
{
"endLocation": {
"lat": "12.137",
"lon": "-86.254501",
"accuracy": "1"
},
"path": "goaiArqmmO"
},
{
"endLocation": {
"lat": "12.13716",
"lon": "-86.246262",
"accuracy": "1"
},
"path": "goaiArqmmO~HgFzFmI}B_FqEkDkFkLrAlEsBoM"
},
{
"endLocation": {
"lat": "12.13716",
"lon": "-86.246262",
"accuracy": "1"
},
"path": "goaiArqmmO~HgFzFmI}B_FqEkDkFkLrAlEsBoM"
},
{
"endLocation": {
"lat": "12.13716",
"lon": "-86.246262",
"accuracy": "1"
},
"path": "goaiArqmmO~HgFzFmI}B_FqEkDkFkLrAlEsBoM"
},
{
"endLocation": {
"lat": "12.13716",
"lon": "-86.246262",
"accuracy": "1"
},
"path": "goaiArqmmO~HgFzFmI}B_FqEkDkFkLrAlEsBoM"
},
{
"endLocation": {
"lat": "12.13716",
"lon": "-86.246262",
"accuracy": "1"
},
"path": "goaiArqmmO~HgFzFmI}B_FqEkDkFkLrAlEsBoM"
},
{
"endLocation": {
"lat": "12.13716",
"lon": "-86.246262",
"accuracy": "1"
},
"path": "goaiArqmmO~HgFzFmI}B_FqEkDkFkLrAlEsBoM"
},
{
"endLocation": {
"lat": "12.13716",
"lon": "-86.246262",
"accuracy": "1"
},
"path": "goaiArqmmO~HgFzFmI}B_FqEkDkFkLrAlEsBoM"
},
{
"endLocation": {
"lat": "12.13716",
"lon": "-86.246262",
"accuracy": "1"
},
"path": "goaiArqmmO~HgFzFmI}B_FqEkDkFkLrAlEsBoM"
},
{
"endLocation": {
"lat": "12.10425",
"lon": "-86.253792",
"accuracy": "0"
},
"path": "goaiArqmmO~HgFzFmI}B_FqEkDkFkLrAlEsBoMN_CtBoIbBkD`@_G?}HjRoMyFlAvxD|_B"
},
{
"endLocation": {
"lat": "12.10425",
"lon": "-86.253792",
"accuracy": "0"
},
"path": "goaiArqmmO~HgFzFmI}B_FqEkDkFkLrAlEsBoMN_CtBoIbBkD`@_G?}HjRoMyFlAvxD|_B"
},
{
"endLocation": {
"lat": "12.10425",
"lon": "-86.253792",
"accuracy": "0"
},
"path": "goaiArqmmO~HgFzFmI}B_FqEkDkFkLrAlEsBoMN_CtBoIbBkD`@_G?}HjRoMyFlAvxD|_B"
},
{
"endLocation": {
"lat": "12.10425",
"lon": "-86.253792",
"accuracy": "0"
},
"path": "goaiArqmmO~HgFzFmI}B_FqEkDkFkLrAlEsBoMN_CtBoIbBkD`@_G?}HjRoMyFlAvxD|_B"
},
{
"endLocation": {
"lat": "12.10425",
"lon": "-86.253792",
"accuracy": "0"
},
"path": "goaiArqmmO~HgFzFmI}B_FqEkDkFkLrAlEsBoMN_CtBoIbBkD`@_G?}HjRoMyFlAvxD|_B"
},
{
"endLocation": {
"lat": "12.10425",
"lon": "-86.253792",
"accuracy": "0"
},
"path": "goaiArqmmO~HgFzFmI}B_FqEkDkFkLrAlEsBoMN_CtBoIbBkD`@_G?}HjRoMyFlAvxD|_B"
},
{
"endLocation": {
"lat": "12.10425",
"lon": "-86.253792",
"accuracy": "0"
},
"path": "goaiArqmmO~HgFzFmI}B_FqEkDkFkLrAlEsBoMN_CtBoIbBkD`@_G?}HjRoMyFlAvxD|_B"
},
{
"endLocation": {
"lat": "12.10425",
"lon": "-86.253792",
"accuracy": "0"
},
"path": "goaiArqmmO~HgFzFmI}B_FqEkDkFkLrAlEsBoMN_CtBoIbBkD`@_G?}HjRoMyFlAvxD|_B"
},
{
"endLocation": {
"lat": "12.10425",
"lon": "-86.253792",
"accuracy": "0"
},
"path": "goaiArqmmO~HgFzFmI}B_FqEkDkFkLrAlEsBoMN_CtBoIbBkD`@_G?}HjRoMyFlAvxD|_B"
}
]
},
{
"idDevice": "2",
"idVehicle": "2",
"idSimcard": "2",
"alias": "Karen Molina",
"imei": "864251020174384",
"routes": [
{
"endLocation": {
"lat": "12.292415",
"lon": "-86.236351",
"accuracy": "1"
},
"path": "sz_jAd`jmO"
},
{
"endLocation": {
"lat": "12.292415",
"lon": "-86.236351",
"accuracy": "1"
},
"path": "sz_jAd`jmO"
}
]
}
]
}

Answer

You never have startLocation in your result array because $i is never = 1. The first row in your data have time = 2016-10-04 10:53:34. It add an endPoint, because the engine = 2, set $i = 1 and after set $i++. Thats why $i never takes value = 1.

You need to move $i from end of loop into $engine = 1 block:

 while ($result->fetch(PDO::FETCH_BOUND)) {

            $polylineEncoder->addPoint($lat, $lon);
            //echo "engine:" . $engine ."\n";

            if ($engine == 1) {
                // Start Location
                if ($i == 1) {
                    // Add Start Location
                    $startLocation = [
                        'lat' => $lat,
                        'lon' => $lon,
                        'accuracy' => $accuracy
                    ];
                    $i++;
                    $points['startLocation'] = $startLocation;
                }
            } else if ($engine == 2) {
                // Add End Location
                $endLocation = [
                    'lat' => $lat,
                    'lon' => $lon,
                    'accuracy' => $accuracy
                ];
                $points['endLocation'] = $endLocation;
                $points['path'] = $polylineEncoder->encodedString();
                $i = 1;
            }

            $routes[] = $points;

        }
Comments