php while loop stepped

Question

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"
                }
            ]
        }
    ]
}

be careful with a column named date as date is a MySQL keyword and so you can easily cause yourself an error if you don't correctly wrap it or escape it in SQL queries. Same applies to engine . It's easier in the long run if you use another wording for these things. — Mm-Art-In
– Mm-Art-In, Commented Oct 23, 2016 at 12:37
Thanks for the comment, i will apply some changes to my name formatting. — RonEskinder
– RonEskinder, Commented Oct 24, 2016 at 0:44

Ivnhal · Accepted Answer · 2016-10-13 07:33:24Z

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;

        }

Collectives™ on Stack Overflow

php while loop stepped

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related