How to send GPS location to mysql table every x minutes

38
February 11, 2019, at 09:30 AM

I'm working on a bit of code to save the user's current GPS coordinates to a mysql table every 5 minutes. I'm familiar with PHP and learning javascript at the moment so I need some assistance with the javascript portion of the code.

My current code sample will get the user's GPS position every 5 seconds and set it as the javascript variable lat and lon. The javascript function runs every 5 seconds for now, but will eventually be updated to run every 5 minutes.

What do I need to add to the code to insert the lat and lon variables into a mysql table each time the function is called?

Here is my current code example:

<!DOCTYPE html> 
<html> 
<body> 
 
<p>Get GPS Location Every 5 seconds</p> 
 
<p id="coordinates"></p> 
 
<body onload="getLocation()">  
 
<script> 
var x = document.getElementById("coordinates"); 
 
function getLocation() { 
  if (navigator.geolocation) { 
    navigator.geolocation.getCurrentPosition(showPosition); 
	setTimeout(getLocation, 5000); 
  } else {  
    x.innerHTML = "Geolocation is not supported by this browser."; 
  } 
} 
 
function showPosition(position) { 
  x.innerHTML = "Latitude: " + position.coords.latitude +  
  "<br>Longitude: " + position.coords.longitude; 
} 
</script> 
 
</body> 
</html>

Answer 1

In Short:

You should use a server-side instead of using direct access from client-side JS to your database

In Depth:

First, let's agree that your DB is valuable to you and you want to keep it as secured as possible.

Second, let's also agree that client-side code (especially JS) is vulnerable and may be a perfect place of potential attackers to hit.

With all that set up, lets' define how we create a connection to one's database:

  • Define a username and password to access the DB (usually DB pre-define an admin user during installation)
  • Creating a connection string in your code to connect the DB using the credentials (e.g. username and password) mentioned above
  • Performing operations (mainly querying) against the DB

So, exposing your connection string in client-side JS, will cause (eventually) to someone having access rights to your DB.

In the "good case scenario" (which is pretty bad) the user will not have admin rights so the potential damage will be limited (reading sensitive information, deleting records, or just create fake records to screw up your data).

In the "bad case scenario", the attacker will be able to access your DB with admin privileges, change the user password (thus locking you out) and Take the entire db to itself or "just" listen to your DB and get updated data (like a leech)

And that is why we are using server-side code to deal with DB connections and operations

Answer 2
            //HTML file
            <!DOCTYPE html>
            <html>
            <head> 
    <script src="https://ajax.googleapis.com/ajax/libs/jquery/1.12.0/jquery.min.js"></script>
            </head>
            <body>
            <p>Get GPS Location Every 5 seconds</p>
            <p id="coordinates"></p>
            <body onload="getLocation()"> 
            <script>
            var x = document.getElementById("coordinates");
            function getLocation() {
              if (navigator.geolocation) {
                navigator.geolocation.getCurrentPosition(showPosition);
                setTimeout(getLocation, 5000);
              } else { 
                x.innerHTML = "Geolocation is not supported by this browser.";
              }
            }
            function showPosition(position) {
            $.ajax({
                    url: "./path/to/php/file.php",
                    type: "post",
                    data: { lat: position.coords.latitude, long: position.coords.longitude},
                    success: function (data) {
                      var dataParsed = JSON.parse(data);
                      console.log(dataParsed);
                    }
                  });
            }
            </script>
            </body>
            </html>
        //Php File
<?php
    //Create connection
  $connection = mysqli_connect('localhost', 'root', '', 'dbase');
    if($_POST['lat'] && $_POST['long']){
      $lat = $_POST['lat'];
      $long = $_POST['long'];
      $q = "INSERT INTO location (lat, long) VALUES ('$lat', '$long')";
      $query = mysqli_query($connection, $q);
      if($query){
          echo json_encode("Data Inserted Successfully");
          }
      else {
          echo json_encode('problem');
          }
      }
?>
READ ALSO
Efficient DB calls to update recycler view

Efficient DB calls to update recycler view

I have a MYSQL database holding my dataIt consists of various texts and a couple of image urls for each row

19
MySQL: Calculate width date in column

MySQL: Calculate width date in column

I want to define a cron that freqently delete specific data from a MySQL column if date in date_added column is older than 60 days, but don't find any infomation if MySQL supports calculation width dateIs that possible and if yes how to do?

24
column count doesn&#39;t match the value count at row 1_database insert error

column count doesn't match the value count at row 1_database insert error

Been working on this for a whileFor some reason even though I have enough values and columns, I get "column count doesn't match value count at row 1" error

90
cannot get id from ajax

cannot get id from ajax

I can't get the id from the js file, I don't know if the js code or the php code is wrongCan someone help? problem seems to be from index

21