Sending Rpi local data to an online Server (MySQL

Question

I'm using a raspberry Pi zero w with temperature sensor, i don't have a continuous connection that's why i'm saving the temperature data on a local MySQL database using the following command

    <?php

exec("/usr/bin/python /home/pi/temperatura/Adafruit_Python_DHT/examples/AdafruitDHT.py 11 4",$salida);
$escribir = implode('',$salida);
$escribir = trim($escribir);

$t1 = explode('emp=',$escribir);
$t2 = explode('*  Humidity=',$t1[1]);

$temperatura = trim($t2[0]);

$hostname = exec("hostname");

$date = date("Y:m:d H:m:s");

$servername = "localhost";
$username = "root";
$password = "root";
$dbname = "datos";

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

$sql = "INSERT INTO temperatura (name, fecha, valor)
VALUES ('$hostname', '$date', '$temperatura')";

if ($conn->query($sql) === TRUE) {
    echo "New record created successfully";
} else {
    echo "Error: " . $sql . "<br>" . $conn->error;
}

$conn->close();

?>

and now i'm trying to send the local data to an online server every hour or 2 hours so i need a PHP or Python code to send my local MySQL Database to my online server, on the server side i have two files, first is for connecting with the online database and the other is for writing the data to the online database as shown below:

<?php
function conectar_bd(){
    $link = @mysqli_connect('localhost', '*****', '*****', '*****');
    if(!$link){
        die('Connect Error: ' . mysqli_connect_errno());
    }
    return($link);
}
?>

and:

<?php

$temperatura = ($_POST['t'])?$_POST['t']:$_GET['t'];
$nombre = ($_POST['n'])?$_POST['n']:$_GET['n'];
$humedad = ($_POST['h'])?$_POST['h']:$_GET['h'];

include ("libnube.inc");

$link = conectar_bd();

$date = time();

$querytemp = "INSERT INTO temperatura (name, fecha, valor) VALUES ('.$nombre.', '.$date.', '.$temperatura.')";
if (mysqli_query($link, $querytemp) === false) {
  die ('Error al ejecutar la consulta de temperatura: '.$querytemp.' - Error: '.mysqli_connect_errno()."\n");
}

?>

I hope that my question is clear, so i need help with the codes on both sides, from the Rpi to send the data and the server data to connect to the cloud and add the data, my online 2 files are basic and most likely that i need to modify them or create a new file and delete these 2.

It is very complicated for me and i couldn't find any source that explain a case like this. I hope someone here could help me. Thank you very much!!

UPDATE:

so far i managed to write this code to send the local saved data to the cloud but it is only sending the last result (the last row in the table), but i need to send the whole table at once:

<?php

$servername = "localhost";
$username = "root";
$password = "ribosoma1970";
$dbname = "datos";


$conn = mysqli_connect($servername, $username, $password, $dbname);
if ($conn === false) {
    die("Connection failed: " . mysqli_connect_error());
}

$sql = "SELECT * from temperatura";

if ($result = mysqli_query($conn,$sql)){
     if (mysqli_num_rows($result) > 0){

      while($row = mysqli_fetch_assoc($result)) {

        $nombre = $row["name"];
        $date = $row["fecha"];
        $temperatura = $row["valor"];
     }
     }
}

mysqli_close($conn);

$datos = array();
$datos['d'] = $date;
$datos['t'] = $temperatura;
$datos['n'] = $nombre;


$conexion = curl_init();
curl_setopt($conexion, CURLOPT_URL, "http://cyclesmartcity.com/dispositivos/escribirbd.php");
curl_setopt($conexion, CURLOPT_POST, 1);
curl_setopt($conexion, CURLOPT_POSTFIELDS, $datos);


$salidacurl = curl_exec($conexion);
curl_close($conexion);


?>

Any ideas on how to modify the code to send all the results in the table at once ??

Answer 1

I'm not sure that this question should be in the RaspberryPi StackExchange because it is more of a general programming question. However, I think I did see why you're only saving the last row.

1. You open the database.
2. You have a while loop which reads each row of the database and you put the results of each row into three variables. Each time through the while loop, you writer over the previous row's data.
3. You close the database
4. Then you put the latest values of these three variables into an array and send that array elsewhere.

Saving the last row is what I'd expect given the code you have. If you want to save all of it, you could use a two-dimensional array rather than just normal variables.

In other words, you want the data to live beyond the while loop.

Another way to do this is to send each row's data one at a time inside the while loop.

I hope this helps.