[compactbrain]

I'm using my laptop as a server with lamp. this is an academic project so its not going live. I'll just have to google cron as i've never used it before. I've posted the code in case you want to have a look. I'll have to change it to add a time-stamp as well

refmySQL.php

<?php
require("dbinfo.php");
if($ssh = ssh2_connect('127.0.0.1', 22))//If it connects 
{
    if(ssh2_auth_password($ssh, 'user', 'pass'))//User and password to use 
    {
        $stream = ssh2_exec($ssh, 'cat /home/jason/Desktop/test');//command to execute
        stream_set_blocking($stream, true);
        $data = '';
        while($buffer = fread($stream, 30))//read from stream 
	{
        $data = $buffer;
        }
	$latlng = explode(";", $data);
	echo $latlng[0]; //output for testing
	echo "</br>";
	echo $latlng[1];
	$lat = explode(",", $latlng[0]);
	$lng = explode(",", $latlng[1]);
	echo "</br>";
	$Declat = round($lat[0]+((($lat[1]*60)+($lat[2]))/3600),6);
	$Declng = round($lng[0]+((($lng[1]*60)+($lng[2]))/3600),6);
	echo $Declat;  //output for testing
	echo "</br>";
	echo $Declng;
        fclose($stream);//close stream
    }
	// Opens a connection to a MySQL server
	$connection=mysql_connect (localhost, $username, $password);
	if (!$connection) {
	  die('Not connected : ' . mysql_error());
	}

	// Set the active MySQL database
	$db_selected = mysql_select_db($database, $connection);
	if (!$db_selected) {
	  die ('Cant find db : ' . mysql_error());
	}
	// Insert new row into table
	$query = "INSERT INTO markers(lat,lng) VALUES ($Declat,$Declng)";
	$result = mysql_query($query);
	if (!$result) {
	  die('Invalid query: ' . mysql_error());
	}
}
?>

[thrca]

For your table...

ALTER TABLE markers ADD COLUMN creation_timestamp timestamp DEFAULT current_timestamp;

This makes it so you dont have to change your insert statement at all, as the records will automagically be stamped with the current_timestamp.

[thrca]

You should be able to use a cronjob like this...

* * * * * /path/to/script.sh

But since cron only schedule a minimum of 1 minute, you need to make a small script if you want more frequent updates then that...

#!/bin/bash
for i in {1..11} 
do
  php -q /path/to/poller.php
  sleep 5
done

One thing I noticed is that your php script collects any data in the ~/test file and inserts it into the db, but does not delete the data from the ~/test file.. If you had 20 records in the test file, and polled it every 5 seconds for a minute, you would then have 12*20=240 records, which I am sure is not what you want.

You could delete the data after inserting it, or add a unique constraint to your database with an "ON CONFLICT IGNORE"... but there is probably a good reason not to do this (like if your gps track takes you to the SAME spot later in the day)

If its simply for demonstration purposes that you can poll the server via SSH, and its OK to run it from the client side, you could just make a little popup window that calls the poller.php and has a meta-refresh of 5 seconds.. This way, while you had the popup open, it would be polling the ssh server, but closing the popup would cause that to stop happening.

[compactbrain]

I'd rather not have a pop up, i hate those things. I'll have a look around online and see if i can find anything.

This post has been edited by compactbrain: 21 January 2012 - 08:21 PM