Sending data from mysql via php to arduino

Projects Programming
1

Hello, I'm trying to receive data from the MySQL server hosted on AwardSpace to my Arduino MEGA2560 using the SIM7600CE, but I'm not receiving any response.

Code MySQL

<?php

// Create connection
$conn = new PDO('sqlite:readingsDB.sqlite');

// Vérifier la connexion
if (!$conn) {
    die("Connection failed");
}

// Requête SQL pour récupérer les données
$sql = "SELECT humidity FROM reading ORDER BY distance DESC LIMIT 1";
$result = $conn->query($sql);


if ($result) {
    // Tableau pour stocker les données
    $data = array();

    // Récupérer chaque ligne de résultat
    while($row = $result->fetch(PDO::FETCH_ASSOC)) {
        // Ajouter les données à $data
        $data[] = $row;
    }

    // Convertir le tableau en format JSON
    $json_data = json_encode($data);

    // Renvoyer les données au format JSON
    echo $json_data;
} else {
    echo "0 results";
}

?>

I have added a new file named "test.json" for testing purposes.

JsonDocument doc;

doc["sensor"] = "gps";
doc["time"] = 1351824120;
doc["data"][0] = 48.756080;
doc["data"][1] = 2.302038;

serializeJson(doc, Serial);
// This prints:
// {"sensor":"gps","time":1351824120,"data":[48.756080,2.302038]}

Code Arduino

/*Ce code utilise la bibliothèque TinyGSM: https://github.com/vshymanskyy/TinyGSM 
 * pour le module GSM SIM7600
 * Code version 0.3 GSM
*/


#define TINY_GSM_MODEM_SIM7600//SIM7600 pour la library GSM fonction network

// Define how you're planning to connect to the internet.
// This is only needed for this example, not in other code.
#define TINY_GSM_USE_GPRS true
#define TINY_GSM_USE_WIFI false
#define TINY_GSM_YIELD() { delay(2); }//reception delay

#define TINY_GSM_RX_BUFFER 650//obligatoir pour les cartes Arduino

// Set serial for debug console (to the Serial Monitor, speed 115200)
#define SerialMon Serial

// Set serial for AT commands (to the module)
// Use Hardware Serial on Mega, Leonardo, Micro
#ifndef __AVR_ATmega328P__
#define SerialAT Serial3

// or Software Serial on Uno, Nano
#else
#include <SoftwareSerial.h>
SoftwareSerial SerialAT(7,8);  // RX, TX
#endif

#include "timer.h" //rajouter le fichier source timer.cpp pour l'utilisation des timers
#include "temperature.h"
#include <TinyGsmClient.h>
#define TINY_GSM_DEBUG SerialMon//pour debuger
#include <ArduinoJson.h>

#include <SPI.h>

TinyGsm SIM7600CE(SerialAT);
TinyGsmClient client(SIM7600CE);

const char apn[]  = "F Free Free "; // Change this to your Provider details
const char gprsUser[] = "";
const char gprsPass[] = "";
const char server[] = "sms-ileo.atwebpages.com"; // Change this to your selection
const char resource[] = "/insert.php";
const char path[] = "/test.json"; // Chemin vers le script PHP
const int  port = 80;
unsigned long timeout;



void setup() {
  timerConfig();//pré-config des timers
  temperatureInit();
  SerialMon.begin(115200);
  delay(10);
  //Debut du boot auto
  pinMode(12,OUTPUT);
  digitalWrite(12,1);
  delay(1000);
  digitalWrite(12,0);
  //fin du boot auto
  SerialMon.println("Wait...");
  SerialAT.begin(115200);//met la com UART 115200 pour le module GSM
  //rajouter une fonction de reboot en cas de non reponse de la part du shield GSM
  delay(600);
  SerialMon.println("Initializing SIM7600CE...");

  // Déverrouillage de la carte SIM avec le code PIN
  SerialMon.println("Unlocking SIM card...");
  if (!unlockSIMCard("1234")) { // code PIN
    SerialMon.println("Failed to unlock SIM card!");
    return;
  }
  SerialMon.println("SIM card unlocked successfully!");
}




//routine ISR pour interruption externe

//routine ISR pour interruption timer
ISR (TIMER4_OVF_vect)
{ 
   nbCycleTimer4_g=nbCycleTimer4_g+1; 
   
    if(nbCycleTimer4_g < (nbCyclePgrm_g+1))
    {
          if(nbCycleTimer4_g == nbCyclePgrm_g )//test passage de 15 à 20
          {
            SerialMon.println("Objectif timer 4 (PGRM) atteint! ");          
            timerPgrm_g=1;   
            nbCycleTimer4_g=0;//raz
          } 
    }
    else nbCycleTimer4_g=0;//raz
  
  TCNT4H=0x00;
  TCNT4L=0x00;
}

ISR (TIMER3_OVF_vect)
{
  nbCycleTimer3_g=nbCycleTimer3_g+1; 
  
    if(nbCycleTimer3_g < (nbCycleGsm_g+1))
    {
          if(nbCycleTimer3_g == nbCycleGsm_g )//test passage de 15 à 20
          {
            sei();        
            SerialMon.println("Objectif timer 3 (GSM) atteint! ");   
            sendDataTempServeur();
            getdatafromserver();
           

            cli();        
            timerGsm_g=1;   
            nbCycleTimer3_g=0;//raz
            
          } 
    }
    else nbCycleTimer3_g=0;//raz
    
  TCNT3H=0x00;
  TCNT3L=0x00; //reset à chaque passage pour l'overflow     
    
}

void loop() {

  //timerGsm_go(60); //cycle total 60 secondes
  //timerPgrm_go(60); //cycle total 60 secondes
  
  SIM7600CE.init();//si beug remplacer par restart
  SerialMon.print("Waiting for network...");

  String modemInfo = SIM7600CE.getModemInfo();
  SerialMon.print("Modem Info: ");
  SerialMon.println(modemInfo);//information du module
  
  if (!SIM7600CE.waitForNetwork()) {
    SerialMon.println(" erreur: La connexion n'a pas pu être établie!");
    // SIM7600CE.sendSMS(SMS_TARGET, String("ILEO erreur: La connexion au réseau mobile n'a pas pu être établie!!"));//fonction pour l'envoi de SMS
    delay(600);
    return;
  }
  SerialMon.println(" success");
  
  if (SIM7600CE.isNetworkConnected()) {SerialMon.println("Network connected!");}

  SerialMon.print(F("Connecting to "));
  SerialMon.print(apn);
  if (!SIM7600CE.gprsConnect(apn, gprsUser, gprsPass)) {
    SerialMon.println(" fail");
    delay(1000);
    return;
  }
  SerialMon.println(" success");

  if (SIM7600CE.isGprsConnected()) {
    SerialMon.println("GPRS connected");
  }
  

  timerGsm_go(30); //cycle total 30 secondes envoi des data sur serveur

  while(1)//program principal 
  {

  }
         
}
//------------------------------------------------------------------------------------------------------
// ---------------------------- Fonction de déverrouillage de la carte SIM -----------------------------
//------------------------------------------------------------------------------------------------------
bool unlockSIMCard(const char* pin) {
  SerialAT.println("AT+CPIN=\"" + String(pin) + "\"");
  delay(1000);
  String response = SerialAT.readString();
  return response.indexOf("OK") != -1;
}


//------------------------------------------------------------------------------------------------------
// -------------------------------------- Fonction d'Envoie sms ----------------------------------------
//------------------------------------------------------------------------------------------------------
void sendSMS(String number, String message) {
      SerialAT.println("AT+CMGF=1"); // Configuration du mode texte
      delay(100);
      SerialAT.print("AT+CMGS=\""); // Commande pour spécifier le numéro de téléphone
      SerialAT.print(number);
      SerialAT.println("\"");
      delay(100);
      SerialAT.print(message); // Contenu du SMS
      delay(100);
      SerialAT.println((char)26); // Envoi du message (Ctrl+Z)
      delay(100);
}


//------------------------------------------------------------------------------------------------------
// ---------------------------------- Fonction de Récupération des données -----------------------------
//------------------------------------------------------------------------------------------------------
void getdatafromserver(void){

  // Send HTTP request
   client.print("GET ");
    client.print(path);
    client.println(" HTTP/1.0");
    client.print("Host: ");
    client.println(server);
    client.println(F("Connection: close"));
  if (client.println() == 0) {
    Serial.println(F("Failed to send request GET"));
    client.stop();
    return;
  }

  // Check HTTP status
  char status[32] = {0};
  client.readBytesUntil('\r', status, sizeof(status));
  // It should be "HTTP/1.0 200 OK" or "HTTP/1.1 200 OK"
  if (strcmp(status + 9, "200 OK") != 0) {
    Serial.print(F("Unexpected response: "));
    Serial.println(status);
    client.stop();
    return;
  }

  // Skip HTTP headers
  char endOfHeaders[] = "\r\n\r\n";
  if (!client.find(endOfHeaders)) {
    Serial.println(F("Invalid response"));
    client.stop();
    return;
  }

  // Allocate the JSON document
  JsonDocument doc;

  // Parse JSON object
  DeserializationError error = deserializeJson(doc, client);
  if (error) {
    Serial.print(F("deserializeJson() failed: "));
    Serial.println(error.f_str());
    client.stop();
    return;
  }

  // Extract values
  Serial.println(F("Response:"));
  Serial.println(doc["sensor"].as<const char*>());
  Serial.println(doc["time"].as<long>());
  Serial.println(doc["data"][0].as<float>(), 6);
  Serial.println(doc["data"][1].as<float>(), 6);

  // Disconnect
  client.stop();
}

 


//------------------------------------------------------------------------------------------------------
// ------------------------------ Fonction d'envoie des données au serveur -----------------------------
//------------------------------------------------------------------------------------------------------
void sendDataTempServeur(void) {
    if (!client.connect(server, port)) {
        SerialMon.println("Impossible de se connecter au serveur !");
        return;
    }

    const float temperature = 80;

    SerialMon.print("Temperature: ");
    SerialMon.println(temperature);
    SerialMon.println("Envoi de la requête HTTP POST...");

    String httpRequestData = "key=a@4K%3&distance=" + String(temperature) + "&temperature=" + String(temperature) + "&temperature=" + String(temperature);

    client.print("POST ");
    client.print(resource);
    client.println(" HTTP/1.1");
    client.print("Host: ");
    client.println(server);
    client.println("Connection: close");
    client.println("Content-Type: application/x-www-form-urlencoded");
    client.print("Content-Length: ");
    client.println(httpRequestData.length());
    client.println();
    client.println(httpRequestData);

    // Attendre la réponse du serveur
    while (client.connected() && !client.available()) {
        delay(1);
    }
    Serial.println();
    SerialMon.println("FIN de la requête HTTP POST...");

    // Fermer la connexion
    client.stop();
}
2

post the full arduino code using code tags properly (please read How to get the best out of this forum as I had to fix your first post).

explain also if you have proper connectivity to your server from MEGA2560 using the SIM7600CE

3

Are you sure your database is a MySQL?
In your PHP script you are using a SQLite driver and it's not the same thing.

4

What do you see when you issue the URL embedded in the GET request to the PHP script from a web browser ? Do you see some representation of this: echo $json_data ?

5

Thank you for your response. I have now published all the Arduino code.

6

I see the value
[{"humidity":60}]

7

but i can post value to the web server