How to create a javascript variable from MySQL using PHP and AJAX?

Question

I am not a native AJAX and javascript programmer. I am trying to pass the longitude and latitude values from MySQL to javascript. However, i don't seem to have much luck here. Can anyone advise me what i am doing wrongly?

I am trying to create a script that allows me to create a javascript variable by using AJAX to call the values from the GPS_tracker php file.

GPS_tracker.php:

<?php
$servername = "localhost";
$username = "db_001";
$password = "12345678";
$dbname = "gpstable";

// Create connection
$conn = new mysqli($servername, $username, $password);

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} 

$sql = "SELECT rider_id, track_time, track_lng, track_lat FROM db_001.gpstable";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    // output data of each row
    while($row = $result->fetch_assoc()) {
        $lat = $row['track_lat'];
        $lng = $row['track_lng'];
        $gpsjson = json_encode(array($lat, $lng));
    }
} else {
    echo "0 results";
}

$conn->close();

?>

Script.js:

$.ajax({
   url: 'gps_tracker.php',
   dataType: 'json'
}).done(
   function(gpsjson){
     var tag_name = gpsjson[0];
     var client_id = gpsjson[1];
   }
);

// Show the user's position on a Google map.
function showMap(lat, lon) {
    // Create a LatLng object with the GPS coordinates.
    var myLatLng = new google.maps.LatLng(lat, lon);

    // Create the Map Options
  var mapOptions = {
    zoom: 8,
    center: myLatLng,
    mapTypeId: google.maps.MapTypeId.ROADMAP
  };

  // Generate the Map
  var map = new google.maps.Map(document.getElementById('map'), mapOptions);

  // Add a Marker to the Map
  var marker = new google.maps.Marker({
      position: myLatLng,
      map: map,
      title: 'Found you!'
  });
}

If your query returns more than one row (which it almost certainly will given there is no `WHERE` clause), your PHP script will return invalid JSON. — Nick, Sep 01 '19 at 12:23
Append each `$gpsjson` to an array then `json_encode`/`echo` it - that's the data you want in your `success(gspjson)` callback. — user7290573, Sep 01 '19 at 12:38
you have to say: echo $gpsjson after the while loop. and also Nick is right that if there is no where clause there will be more than one row being fetched so the way you have it $gpsjson will just contain the last row in the table... Note: also you can check the "Network" tab in the developer tools to see if any JSON was returned in the response of the AJAX call. — Sarah, Sep 01 '19 at 16:53

LatoWszędzie · Answer 1 · 2019-09-01T13:06:37.107

In php while loop

$gpsjson = json_encode($row);

Parse json in javascript:

// in ajax response
var json = JSON.parse(gpsjson);
for(var i in json){
    alert(json[i].track_lat);
    alert(json[i].track_lng);
    // Show in console js text or errors 
    // Show console hit: CTRL+SHIFT+K
    console.log(json[i].track_lng);
}

In SELECT add (for last record):

ORDER BY rider_id DESC LIMIT 1

See here Iterating through/Parsing JSON Object via JavaScript

Regards.

score 0 · Answer 2 · answered Sep 02 '19 at 15:35

Hi I think you have 2 problems:

You never echo your $gpsjson;
If you expect a json your else should return a json encode msg too.

Also you should send an associative array so try like this and let me know how it works. php:

<?php
$servername = "localhost";
$username = "db_001";
$password = "12345678";
$dbname = "gpstable";

// Create connection
$conn = new mysqli($servername, $username, $password);

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} 
//declare gps json here
$gpsjson = array();
$sql = "SELECT rider_id, track_time, track_lng, track_lat FROM db_001.gpstable";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    // output data of each row
    while($row = $result->fetch_assoc()) {
       //replace this 
       // $lat = $row['track_lat'];
       // $lng = $row['track_lng'];
       //$gpsjson = json_encode(array($lat, $lng));
       //for this
        $temp = array('lat'=>$row['track_lat'],'lng'=>$row['track_lng']);
        $gpsjson[] = $temp;
    }
    $gpsjson['response'] = 'ok';
    echo json_encode($gpsjson);
} else {
    echo json_encode(array('response'=>'error'));
}

$conn->close();

?>

Javascript:

 $.ajax({
      url: 'gps_tracker.php',
      type: 'GET',
      dataType: 'JSON'
    })
    .done(function(response) {
      console.log(response);
    });

Hope it helps

How to create a javascript variable from MySQL using PHP and AJAX?

2 Answers2