Help Needed with Fetching and Updating Vehicle Data Using API

[GaneshNaiknavre]

Message:

Hello,

I am working on a PHP project where I need to fetch mileage and location data from an API for vehicles stored in my database. The code below is meant to:

Fetch an access token using the appid and login_key.

Retrieve mileage and location data for each vehicle using the IMEI from the database.

Update the created_jobs table with the fetched data (run KM, current KM, latitude, longitude).

However, the data is not being updated correctly in the database. The issue seems to be with fetching or processing the API responses.

Here’s the code I’m using:

 

 

 

<?php
// Enable error reporting and logging
error_reporting(E_ALL);
ini_set('display_errors', 0);
ini_set('log_errors', 1);
ini_set('error_log', 'php_error.log');
header('Content-Type: application/json');

// Include database connection
include('db.php'); // Ensure this file contains valid DB connection details
if ($mysqli->connect_error) {
    error_log("Database connection failed: " . $mysqli->connect_error);
    exit("Database connection error");
}

// Function to call API
function callApi($url, $headers = [], $method = 'GET', $data = null) {
    $ch = curl_init();
    curl_setopt($ch, CURLOPT_URL, $url);
    curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
    curl_setopt($ch, CURLOPT_CUSTOMREQUEST, $method);

    if ($method === 'POST' && $data !== null) {
        curl_setopt($ch, CURLOPT_POSTFIELDS, json_encode($data));
    }

    if (!empty($headers)) {
        curl_setopt($ch, CURLOPT_HTTPHEADER, $headers);
    }

    $response = curl_exec($ch);
    if (curl_errno($ch)) {
        error_log("cURL Error: " . curl_error($ch));
        return null;
    }

    curl_close($ch);
    return json_decode($response, true);
}

// Function to fetch access token
function getAccessToken($appid, $login_key) {
    $timestamp = time();
    $signature = md5(md5($login_key) . $timestamp);
    $url = "https://open.iopgps.com/api/auth";
    $data = [
        "appid" => $appid,
        "time" => $timestamp,
        "signature" => $signature
    ];

    $response = callApi($url, ["Content-Type: application/json"], 'POST', $data);

    if (isset($response['accessToken'])) {
        return $response['accessToken'];
    }

    error_log("Failed to fetch access token. API response: " . json_encode($response));
    return null;
}

// API credentials
$appid = "xyz";
$login_key = "xyz";

// Fetch access token
$access_token = getAccessToken($appid, $login_key);
if (!$access_token) {
    error_log("Failed to fetch access token");
    exit("Access token error");
}

// Fetch distinct vehicles from customer table
$query = "SELECT DISTINCT vehicle_no, gps_imei FROM customer";
$result = $mysqli->query($query);

if ($result && $result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
        $vehicle_no = $row['vehicle_no'];
        $imei = $row['gps_imei'];

        // Log the IMEI and Vehicle Number for debugging
        error_log("Processing vehicle_no: {$vehicle_no}, IMEI: {$imei}");

        // Define time range for mileage API (last 1 hour)
        $start_time = date("Y-m-d H:i:s", strtotime("-1 hour"));  // last hour dynamically
        $end_time = date("Y-m-d H:i:s", time());  // current time

        // Fetch mileage data (distance traveled)
        $mileage_api_url = "https://open.iopgps.com/api/device/miles?accessToken={$access_token}&startTime={$start_time}&endTime={$end_time}&imei={$imei}";
        $mileage_response = callApi($mileage_api_url, ["Content-Type: application/json"]);

        // Log the response for mileage data
        error_log("Mileage API Response: " . json_encode($mileage_response));

        // Fetch location data (latitude and longitude)
        $location_api_url = "https://open.iopgps.com/api/device/location?imei={$imei}&accessToken={$access_token}";
        $location_response = callApi($location_api_url, ["Content-Type: application/json"]);

        // Log the response for location data
        error_log("Location API Response: " . json_encode($location_response));

        // Validate mileage data
        $api_miles = 0;
        if (isset($mileage_response['miles']) && is_numeric($mileage_response['miles'])) {
            $api_miles = $mileage_response['miles'];
        } else {
            error_log("Invalid mileage data for IMEI: {$imei}. API response: " . json_encode($mileage_response));
        }

        // Validate location data
        $latitude = $longitude = null;
        if (isset($location_response['lat']) && isset($location_response['lng'])) {
            $latitude = $location_response['lat'];
            $longitude = $location_response['lng'];
        } else {
            error_log("Invalid location data for IMEI: {$imei}. API response: " . json_encode($location_response));
        }

        // Update the database if data is valid
        if ($api_miles > 0 && $latitude !== null && $longitude !== null) {
            $update_query = "UPDATE created_jobs SET run_km = IFNULL(run_km, 0) + ?, current_km = IFNULL(current_km, 0) + ?, latitude = ?, longitude = ? WHERE vehicle_no = ?";
            $stmt = $mysqli->prepare($update_query);

            if ($stmt) {
                $stmt->bind_param("ddsss", $api_miles, $api_miles, $latitude, $longitude, $vehicle_no);
                $stmt->execute();
                error_log("Updated vehicle_no: {$vehicle_no} | KM Added: {$api_miles} | Lat: {$latitude} | Lng: {$longitude}");
            } else {
                error_log("Failed to prepare statement for vehicle_no {$vehicle_no}: " . $mysqli->error);
            }
        } else {
            error_log("Skipping update for vehicle_no {$vehicle_no} due to invalid data.");
        }
    }
    echo "Run KM, Current KM, and Location data updated for all matching vehicles.";
} else {
    error_log("No vehicles found in customer table.");
    echo "No vehicles found.";
}

$mysqli->close();
?>

Issue:

The API is returning data, but it’s not updating the database as expected.

I'm seeing responses in the log, but the created_jobs table is not getting updated.

Request:

Can anyone help me identify what might be wrong with the logic, or if there's an issue with the API calls or the way I’m handling the database update?

I appreciate any insights!

[Barand]

 mysqli_report(MYSQLI_REPORT_ERROR|MYSQLI_REPORT_STRICT);

Add the above line just before the line that creates your db connection.

Make sure error reporting is on.

[Moorcam]

The script checks if the mileage and location data are valid before attempting to update the database. If either of these checks fails, the update will be skipped.

// Update the database if data is valid
if ($api_miles > 0 && $latitude !== null && $longitude !== null) {
    $update_query = "UPDATE created_jobs SET run_km = IFNULL(run_km, 0) + ?, current_km = IFNULL(current_km, 0) + ?, latitude = ?, longitude = ? WHERE vehicle_no = ?";
    $stmt = $mysqli->prepare($update_query);

    if ($stmt) {
        $stmt->bind_param("ddsss", $api_miles, $api_miles, $latitude, $longitude, $vehicle_no);
        if (!$stmt->execute()) {
            error_log("Failed to execute statement for vehicle_no {$vehicle_no}: " . $stmt->error);
        } else {
            error_log("Updated vehicle_no: {$vehicle_no} | KM Added: {$api_miles} | Lat: {$latitude} | Lng: {$longitude}");
        }
    } else {
        error_log("Failed to prepare statement for vehicle_no {$vehicle_no}: " . $mysqli->error);
    }
} else {
    error_log("Skipping update for vehicle_no {$vehicle_no} due to invalid data.");
}

See if that helps.

Edited December 22 by Moorcam