0

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 ??

1
  • I have edited my question with what i have done so far but that is not completely what i want. How is it possible now to send a complete table at once ? show i write what i get from the saved data as a table or an array ? any ideas ? Thank you! Jun 29, 2018 at 9:42

1 Answer 1

0

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.

2
  • Thank you for your help, your second suggestion solved the problem, i just included sending the data code part inside the while loop and all the data was sent together at once. Jun 29, 2018 at 17:21
  • Now i will try your first suggestion to create a two-dimensional array, but it is a little complicated for me. Thanks! Jun 29, 2018 at 17:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.