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