How to send data from arduino to database (Mysql-PHP-Arduino)

Hi All
i have a project and using PIR sensor
i want to make arduino can send data of PIR sensor status to my database
the values of data are “MOVEMENT” and “NO MOVEMENT”

i have 2 file for this project
connec.php and ardu.php

connec.php

<?php
function Connection(){
   if (!($link=mysql_connect("localhost","root","")))  {
      exit();
   }
   if (!mysql_select_db("arduino",$link)){
      exit();
   }
   return $link;
}
?>

ardu.php

<?php

   include("connec.php");
   $link=Connection();

   if(isset($_GET['stpump1'])) {
     $statp1=$_GET['stpump1'];
     echo ("status=$statp1 \r\n" );

         echo "\r\n";
    $Sql="insert into 'tblevel' ('stpump1')  values ('".$statp1."')";
     mysql_query($Sql,$link);

   }
    else {

          $result=mysql_query("select * from tblevel order by id desc",$link);
    }

?>

sensorpir.ino

#include <Ethernet.h>
#include <SPI.h>
#include <Client.h>

 
byte mac[]         = { 0xDE, 0xAD, 0xBE, 0xEF, 0xFE, 0xED }; // Mac Address Default
  IPAddress ip(192, 168, 0, 99);
  IPAddress subnet(255, 255, 255, 0);
 
  // change to your server
  IPAddress server(192,168,0,111); 
 
  //Change to your domain name for virtual servers
  char serverName[] = "192.168.0.111";//"http://localhost/";

String data;

int PIR = 2;          // PIR sensor is connected to digital pin 2
int PIRstate = 0;     // variable for PIR sensor status
int serverPort = 80;
 
EthernetClient client;
char buffer [256];
char c1[13];
char* cp[]={"statuspir="};
char* cs[]={"NO MOVEMENT","MOVEMENT"};
char pageAdd [128];
#define delayMillis 30000UL
 
  unsigned long thisMillis = 0;
  unsigned long lastMillis = 0;

void setup(void)
{
  Serial.begin(9600);
  pinMode(PIR, INPUT);

     Ethernet.begin(mac, ip, subnet);
    digitalWrite(10,HIGH);
 
    Serial.println(Ethernet.localIP());
 
    delay(2000);
    Serial.println(F("Ready"));
 }
 
 
void loop(void) {
  thisMillis = millis();
 
    if(thisMillis - lastMillis > delayMillis)
    {
      lastMillis = thisMillis;
 
PIRstate = digitalRead(2);
          if (PIRstate==0){
        strcpy (c1, cp[0]); //pir sensor
        strcat (c1, cs[0]);  //NO MOVEMENT
      } else {
        strcpy (c1, cp[0]);
      strcat (c1, cs[1]);
      }
      sprintf(pageAdd,"/ardu.php?status=%s",c1);
      
      if(!getPage(server,serverPort,pageAdd)) Serial.print(F("Fail "));
      else Serial.print(F("Pass "));
      Serial.println(c1);                      
      Serial.println(pageAdd);
      delay(1000);
}
}
byte getPage(IPAddress ipBuf,int thisPort, char *page)
  {
    int inChar;
    char outBuf[128];
 
    Serial.print(F("connecting..."));
 
    if(client.connect(ipBuf,thisPort))
    {
      Serial.println(F("connected"));
 
      sprintf(outBuf,"GET %s HTTP/1.1",page);
      client.println(outBuf);
      sprintf(outBuf,"Host: %s",serverName);
      client.println(outBuf);
      client.println(F("Connection: close\r\n"));
           
     
    }
    else
    {
      Serial.println(F("failed"));
      return 0;
    }
 
    // connectLoop controls the hardware fail timeout
    int connectLoop = 0;
 
    while(client.connected())
    {
      while(client.available())
      {
        inChar = client.read();
        Serial.write(inChar);
        // set connectLoop to zero if a packet arrives
        connectLoop = 0;
      }
 
      connectLoop++;
 
      // if more than 10000 milliseconds since the last packet
      if(connectLoop > 10000)
      {
        // then close the connection from this end.
        Serial.println();
        Serial.println(F("Timeout"));
        client.stop();
      }
      // this is a delay for the connectLoop timing
      delay(1);
    }
 
    Serial.println();
 
    Serial.println(F("disconnecting."));
    // close client end
    client.stop();
 
    return 1;}

database

ardu.php

the problem is,how to send and insert data of the sensor status to my databases because my database still empty when i upload the program and move a sensor(to change PIRstate values)
and ardu.php not shown an error but it still clear and not show the values of c1

Sorry about my english
i hope anyone can help me
thank you all :slight_smile:

You have a problem on the php end. The Arduino is sending a variable "status", and the php code is looking for "stpump1". These variable names must match.

Thanks for your response SurferTim :slight_smile:

i have changed my code and make it same

ardu.php

<?php
  
   include("connec.php");
   $link=Connection();

   if(isset($_GET['stpump1'])) {
    $statp1=$_GET['stpump1'];
     echo ("stpump1=$statp1 \r\n" );

         echo "\r\n";
    $Sql="insert into 'tblevel' ('stpump1')  values ('".$statp1."')";
     mysql_query($Sql,$link);

   }
    else {

          $result=mysql_query("select * from tblevel order by id desc",$link);
    }

?>

sensor.ino

#include <Ethernet.h>
#include <SPI.h>
#include <Client.h>

 
byte mac[] = { 0xDE, 0xAD, 0xBE, 0xEF, 0xFE, 0xED }; // Mac Address Default
  IPAddress ip(192, 168, 0, 99);
  IPAddress subnet(255, 255, 255, 0);
 
  // change to your server
  IPAddress server(192,168,0,111); 
 
  //Change to your domain name for virtual servers
  char serverName[] = "192.168.0.111";//"http://localhost/";

String data;

int PIR = 2;          // PIR sensor is connected to digital pin 2
int PIRstate = 0;     // variable for PIR sensor status
int serverPort = 80;
 
EthernetClient client;
char buffer [256];
char c1[13];
char* cp[]={"stpump1="};
char* cs[]={"NO MOVEMENT","MOVEMENT};
char pageAdd [128];
#define delayMillis 30000UL
 
  unsigned long thisMillis = 0;
  unsigned long lastMillis = 0;

void setup(void)
{
  Serial.begin(9600);
  pinMode(PIR, INPUT);

     Ethernet.begin(mac, ip, subnet);
    digitalWrite(10,HIGH);
 
    Serial.println(Ethernet.localIP());
 
    delay(2000);
    Serial.println(F("Ready"));
 }
 
 
void loop(void) {
  thisMillis = millis();
 
    if(thisMillis - lastMillis > delayMillis)
    {
      lastMillis = thisMillis;
 
PIRstate = digitalRead(2);
          if (PIRstate==0){
        strcpy (c1, cp[0]); //pump1
        strcat (c1, cs[0]);  //status ON
      } else {
        strcpy (c1, cp[0]);
      strcat (c1, cs[1]);
      }
      sprintf(pageAdd,"/ardu.php?stpump1=%s",c1);
      
      if(!getPage(server,serverPort,pageAdd)) Serial.print(F("Fail "));
      else Serial.print(F("Pass "));
      Serial.println(c1);                      
      Serial.println(pageAdd);
      delay(1000);
}
}
byte getPage(IPAddress ipBuf,int thisPort, char *page)
  {
    int inChar;
    char outBuf[128];
 
    Serial.print(F("connecting..."));
 
    if(client.connect(ipBuf,thisPort))
    {
      Serial.println(F("connected"));
 
      sprintf(outBuf,"GET %s HTTP/1.1",page);
      client.println(outBuf);
      sprintf(outBuf,"Host: %s",serverName);
      client.println(outBuf);
      client.println(F("Connection: close\r\n"));
           
     
    }
    else
    {
      Serial.println(F("failed"));
      return 0;
    }
 
    // connectLoop controls the hardware fail timeout
    int connectLoop = 0;
 
    while(client.connected())
    {
      while(client.available())
      {
        inChar = client.read();
        Serial.write(inChar);
        // set connectLoop to zero if a packet arrives
        connectLoop = 0;
      }
 
      connectLoop++;
 
      // if more than 10000 milliseconds since the last packet
      if(connectLoop > 10000)
      {
        // then close the connection from this end.
        Serial.println();
        Serial.println(F("Timeout"));
        client.stop();
      }
      // this is a delay for the connectLoop timing
      delay(1);
    }
 
    Serial.println();
 
    Serial.println(F("disconnecting."));
    // close client end
    client.stop();
 
    return 1;}

But my database still empty like before :frowning:

What does the serial monitor show? If not enough, add some debug stuff.

        strcpy (c1, cp[0]); //pump1
        strcat (c1, cs[0]);  //status ON

cp[0] is "stpump1=" cs[0] is "NO MOVEMENT"

How can you reasonably expect all that, and a terminating NULL to fit in:

char c1[13];

hello can i ask? what if im using mq135 gas sensor to send data to database, is it have a source code or reference? thank you so much