help need: upload GPS data to MySQL server with Sim900

Hi,

I wonder if anyone can help me with this one, please. And, yes, it’s a programming question I have.

My environment is as follows:
Arduino MEGA2560
Ultimate GPS breakout boad, with GPS connected to Serial3
Sim900 module, with Sim900 connected to Serial2
Micro-SD card module with 4GB micro-SD card.
PHP+MySQL web server to store and view GPS data

The idea is to have a GPS tracking device in a vehicle, which can sometimes to out of 3G/GPRS coverage, but keep saving the data on a micro-SD card, and then upload to a PHP web page, in order to save to a MySQL server.

Right now, the Arduino is saving GPS information (lat, lon, date, time, speed, direction) to a micro-sd card every 2 minutes, into a .csv.

The vehicle operates in the bushes and the driver doesn’t know much about this kind of technology. But I don’t want him to even know it there’s, so the “GPS tracker” should work autonomously.

I need to be able to view the data in real-time, or as close to real-time as possible. It won’t always be possible if there’s no GPRS/3G coverage, but that’s fine, as long as I could at least get the last-known location and direction.

Now, onto the code:

The Arduino MEGA2560 saves the GPS data into a .CSV file, every 2 minutes, as follows:

void loop()
{

while (!Serial1.available()) {
unsigned long currentMillis = millis();

if(currentMillis - previousMillis > interval) {
// save the last time you blinked the LED
previousMillis = currentMillis;

// if the LED is off turn it on and vice-versa:
if (ledState == 0)
ledState = 255;
else
ledState = 0;
// set the LED with the ledState of the variable:
analogWrite(GPS_Error, ledState);
}
}
// read the OptoCoupler input pin:
if (digitalRead(Opto1) == LOW)
{
analogWrite(Opto_Enable, 255);
Serial.println(“Opto Enabled.”);
bool newdata = false;
unsigned long start = millis();
// Every second we print an update
do
{
if (feedgps())
newdata = true;
} while (millis() - start < LOG_INTERVAL*1000);

gpsdump(gps);

}
analogWrite(Opto_Enable, 0);
}

static void gpsdump(TinyGPS &gps)
{
float flat, flon;
unsigned long age, date, time, chars = 0;
gps.f_get_position(&flat, &flon, &age);
// Serial.print("Latitude: “); print_float(flat, TinyGPS::GPS_INVALID_F_ANGLE, 9, 5);
// Serial.print(” Longitude: “); print_float(flon, TinyGPS::GPS_INVALID_F_ANGLE, 10, 5);
// Serial.print(” Date: “); print_date(gps);
// Serial.print(” Speed: "); print_float(gps.f_speed_kmph(), TinyGPS::GPS_INVALID_F_SPEED, 6, 2);
// Serial.println();

// SD Card logging
dataFile = SD.open(“gps_log.csv”, FILE_WRITE);

int year;
byte month, day, hour, minute, second, hundredths;
// unsigned long age;
gps.crack_datetime(&year, &month, &day, &hour, &minute, &second, &hundredths, &age);
if (age == TinyGPS::GPS_INVALID_AGE)
{
Serial.println(“No GPS Fix”);
analogWrite(GPS_Error, 255);

// Activate GPS_Error Debug LED
/*
unsigned long currentMillis = millis();
if(currentMillis - previousMillis > interval) {
// save the last time you blinked the LED
previousMillis = currentMillis;
// if the LED is off turn it on and vice-versa:
if (ledState == 0)
ledState = 255;
else
ledState = 0;
// set the LED with the ledState of the variable:
analogWrite(GPS_Error, ledState);
} // END Activate GPS_Error Debug LED */
}
else {
analogWrite(GPS_Error, 0);
if(dataFile) // if datafile is available, write to it
{ // otherwise just write to pc terminal
// Serial.println(“SD data file opened ok”);
dataFile.print(flat,5);
dataFile.print(",");
dataFile.print(flon,5);
dataFile.print(",");
dataFile.print(year);
dataFile.print("-");
dataFile.print(month);
dataFile.print("-");
dataFile.print(day);
dataFile.print(",");
dataFile.print(hour+2);
dataFile.print(":");
dataFile.print(minute);
dataFile.print(":");
dataFile.print(second);
dataFile.print(",");
if(gps.f_speed_kmph()>1) {
dataFile.println(gps.f_speed_kmph());
} else {
dataFile.println(“0”);
}
}
Serial.print(“Data written to SD Card: “);
Serial.print(flat,5);
Serial.print(”, “);
Serial.print(flon,5);
Serial.print(”, “);
Serial.print(year);
Serial.print(”-”);
Serial.print(month);
Serial.print("-");
Serial.print(day);
Serial.print(",");
Serial.print(hour+2);
Serial.print(":");
Serial.print(minute);
Serial.print(":");
Serial.print(second);
Serial.print(",");
Serial.println(gps.f_speed_kmph());
// Serial.print(" ,");
// Serial.println(age);
dataFile.close();

// Activate SD_Write Debug LED
unsigned long currentMillis = millis();
if(currentMillis - previousMillis > interval) {
// save the last time you blinked the LED
previousMillis = currentMillis;
// if the LED is off turn it on and vice-versa:
if (ledState == 0)
ledState = 255;
else
ledState = 0;
// set the LED with the ledState of the variable:
analogWrite(SD_Write, ledState);
}
// END SD_Write Debug LED
}
}

I simply don’t know where to begin with this, and have searched the forums and google for a sample but couldn’t find anything.

Can anyone please help me with this?
I just need some sample code, or even a tutorial / wiki / blog article, showing how a .CSV file is opened and the data is posted to a PHP file on a webserver, sequentially.

I know how to to submit a single string to a web page, but not a few hundred, or thousand stings.
Lastly, I need a way to know where the data submission has stopped.

Let’s say the .CSV file has 500 lines, and it submits strings to the PHP webpage, but the GRPS signal is lost during transmission (i.e. data plan has run out, vehicle moves into non-coverage area, problems with GRPS provider, etc.) how will I know where it stopped, and where to continue?

I suppose the data would need to be submitted, say every 5 or so minutes?

Can anyone help me with this, please?

Try to use a server-side daemon written in autoit. He tinuation take the bag and put into the database. This is the real way.

Server:

#Region Includes
#Include <Array.au3>
#EndRegion Includes
#NoTrayIcon

#include<mysql.au3>

$ip = “192.168.0.3”
$port = 400
$max_connections = 30

TCPStartup ()

;Создаем сокет для прослушивания входящих соединений.
$TCPListen = TCPListen (@IPAddress1, $port, $max_connections)
;Если не получилось, то отваливаемся
If $TCPListen = -1 Then Exit ()
MsgBox(0, ‘TCPListen’ , “ok”,1)

;Бесконечный цикл опроса и ответа
While 1
_Accept_Connection ()
Sleep (100)
WEnd

;Функция ожидание запроса клиента
Func _Accept_Connection ()

;Разрешает входящие попытки подключения к сокету.
$Accept = TCPAccept ($TCPListen)

;Принимает входящее соединение
If $Accept = -1 Then Return

;MsgBox(0, ‘TCPAccept’ , “ok”,1)
Do
$Recv = TCPRecv ($Accept, 4096) ;Получает данные с подключенного сокета.
sleep(100)
Until $Recv <> ‘’

$Recv = StringSplit ($Recv, ‘^’);Примитивный парсинг по символу ^ (@UserName & ‘^EXAMPLE DATA’)
$LogIn = $Recv[1]
$Pass = $Recv[2]
$Command = $Recv[3]
if $Command = “select” then
;проверим пользователя по базе
_MySQL_InitLibrary()
If @error Then return
$MysqlConn = _MySQL_Init()
$connected = _MySQL_Real_Connect($MysqlConn, “192.168.0.3”,“barmer”,“merlin”,“test”)
If $connected = 0 Then Exit MsgBox(16, ‘Ошибка подключения’, _MySQL_Error($MysqlConn))
;собираем запрос в базу
$query = “SELECT id FROM login_pass WHERE login = ““test”” "
MsgBox(0, ‘Запрос в базу’ , $query,0)
$zapros=_MySQL_Real_Query($MysqlConn, $query)
;проверяем ошибку в запросе пароля логина
if $zapros <> 0 then
TCPSend ($Accept, ‘Ошибка запроса’)
_MySQL_Close($MysqlConn)
TCPCloseSocket ($Accept)
Return
endif
$res = _MySQL_Store_Result($MysqlConn)
$aResult = _MySQL_Fetch_Result_StringArray($res)
$fields = _MySQL_Num_Fields($res)
;проверяем наличие в базе пользователя
if $fields = 0 then
TCPSend ($Accept, ‘Пользователя с таким именем или паролем нет’)
_MySQL_Close($MysqlConn)
TCPCloseSocket ($Accept)
Return
endif
;примем из базы id пользователя
$idUser = $aResult[1][0]
MsgBox(0, ‘Номер пользователя’ , $idUser,0)
;считаем состояние датчиков из двух баз sensor и rele
$query = “SELECT s_1, s_2, s_3, s_4 FROM sensor WHERE id=””"&$idUser&""""
MsgBox(0, ‘Запрос в базу sensor’ , $query,0)
$zapros=_MySQL_Real_Query($MysqlConn, $query)
if $zapros <> 0 then
TCPSend ($Accept, ‘Ошибка запроса’)
_MySQL_Close($MysqlConn)
TCPCloseSocket ($Accept)
Return
endif
$res = _MySQL_Store_Result($MysqlConn)
$aResult = _MySQL_Fetch_Result_StringArray($res)
$fields = _MySQL_Num_Fields($res);определили количество записей (строк)
$rows = _MySQL_Num_Rows($res);определили количество столбцов
if $rows = 0 then
TCPSend ($Accept, ‘У пользователя пока нет данных’)
_MySQL_Close($MysqlConn)
TCPCloseSocket ($Accept)
Return
endif
;вернем пользователю состояние

MsgBox(0, ‘Число солбцов из sensor’ , $fields,0)
MsgBox(0, ‘Число строк из sensor’ , $rows,0)
$answer=""
for $i=0 to $fields-2
$answer=$answer&$aResult[0]

  • if i<> $rows-1 then $answer=$answer&"^"*
  • next*
  • TCPSend ($Accept, 'Данные клиенту обратно - ’ & $answer)*
  • endif*
    ; Закрывает соединение с базой
  • _MySQL_Close($MysqlConn)*
  • TCPCloseSocket ($Accept)*
  • Return*
    EndFunc
    Client
    TCPStartup()
    $IP = TCPNameToIP("******.dyndns.org") ;получаем ip-адрес
    $port = 400
    ;$DataTransmit=InputBox(‘Ввод данных’, ‘Пожалуйста введите передаваемые данные’, “”)
    $DataTransmit=“test^test^select”;команда обновления сенсоров
    ;$DataTransmit=“test^test^update^0^1^2^3”;команда обновления сенсоров
    $Socket = TCPConnect($ip, $port)
    ;Connects to an open socket on the server…
    If $Socket = -1 Then
    MsgBox(0, “error - TCPConnect”, “”,3)
    TCPCloseSocket($Socket)
    TCPShutdown()
    Exit
    endif
    TCPSend($Socket, $DataTransmit)
    do
    $DataTransmit = TCPRecv($Socket, 1000000)
    Until $DataTransmit <> ‘’
    If $DataTransmit = ‘’ Then
    MsgBox(0, "Error ", “No Data recived”,3)
    TCPCloseSocket($Socket)
    TCPShutdown()
    Exit
    endif
    MsgBox(0, "Data recived - ", $DataTransmit)
    TCPCloseSocket($Socket)
    *TCPShutdown() *
    The client side to make similar only to SIM900.

Please read this.

Please “Modify” your post.

Put "[code]" before a section of code, and "[/code]" after that section of code. You will have two code sections, one for “server” and one for the “client”.

Example:


Try to use a server-side daemon ...

[code]#Region Includes
#Include <Array.au3>
#E...
[/code]

Client:

[code]TCPStartup()


$IP = TCPNameToIP("******.dyndns.org") ;получаем ip-адрес
$por...
[/code]

The client side t...

When you Save it, it will look like this:

Try to use a server-side daemon …
**__ <strong>**#Region Includes #Include <Array.au3> #E...**</strong> __**
Client:
```
**TCPStartup()

$IP = TCPNameToIP(".dyndns.org") ;получаем ip-адрес
$por…

__
```__
The client side t…

Cheers,
/dev

Not sure if this will help you or not but you should try ajax instead of php, I found this a lot easier to upload my data to a page. Check out this tutorial.