Controlling relays over the internet from mysql i can't figure this out.

Hello sorry for the bother but i really need help I would like to control a couple of relay over the internet from my php page using mysql. thing is i don't want to port forward it and my ip address changes it's a dynamic ip address. I saw some stuff for logging to send to mysql to log stuff but only thing i found for controlling relays wants me to put a ip address to my arduino on my network. Does anyone know where i can find something to control the relays through mysql from the php page?

Joseph

Mysql is a database, it stores data. It cannot control relays. PHP is a scripting language. It can action things when events occur. But PHP cannot run on an Arduino.

Please explain what you really want to achieve, not how you think you can achieve it.

For the non-port forwarding solution, the arduino must reqularly query the mySql database to determine what the state of the relays should be. It then ensures that the relays are on or off as required. Naturally, this means a bit of latency. Design a table in your database with two columns (relayID, Status) and two rows (one for each relay). Design a PHP script1 to be used from a web browser which can be used to set the relay status in the table. Design another PHP script which can be queried from the Arduino to determine what the state of the relays should be.

Hello I'm sorry i didn't explain better i have a hard time saying what i would like to say and typing it.

My goal is a small home automation project for controlling relays and lights when I'm not home. I can't port forward nothing and i can't put my ip address in because it changes all the time. So I'm trying to use mysql database and have a value for each relay and light. A 1 or a 0. And the arduino can go to that database and see if there is a 1 there it turns on the led or relay and uploads a new value of 0 in the database so that way the relay or light control keep turning on and off. Same with it the if the light is on i can put a 0 in there to turn off the relay or light.

Hello 6v6gt i have the mysql and columns as well as the php page to send the values to mysql.

OK. Post the code you have so far.

So it looks like you need a only then (1) a PHP script which the Arduino can call (say using the GET method) and (2) an Arduino sketch which can issue the GET command, interpret the results, and operate the relay (or other devices).

Here is the PHP I created for something very similar (lighting a led) as a demonstration. The comments at the top describe it. It uses a trick so the same code can be used from a web browser to set the values and from the Arduino to query the values, but the Arduino must then use the GET (not POST) method.

<?php
/*
   IoT Demo
   reads mySQL Table indicator (columns id and state)
     id (integer) value 1
     state (varchar) value 'on' or 'off'

   builds form with 2 connected radio buttons for changing state to 'on' or 'off'
   builds an html comment <!-- **on** --> or <!-- **off** --> which is
   for simple parsing by a remote program (ESP8266)

   The form is submitted automatically on change of radio buttons.

   ver 0.10  29.05.2016 consolidated from component scripts
*/
?>

<html>
 <head>
  <title>PHP Indicator</title>
 </head>
 <body>

<?php

$servername = "localhost";
$username = "user";
$password = "*****";
$dbname = "**********";

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

if ($_SERVER["REQUEST_METHOD"] == "POST") {
   // 
   $outStatus = $_POST["state"] ;
    // echo "in Post. Status: $outStatus" ;
    $sql1 ="UPDATE indicator SET state='$outStatus' WHERE id='1'";
    $result1 = $conn->query($sql1);

}


$sql = "SELECT id,state FROM indicator where id = '1' ";
$result = $conn->query($sql);

if ( $result->num_rows == 1 ) {
    // output data of each row
    while($row = $result->fetch_assoc()) {
        // echo "id: " . $row["id"]. " - State: " . $row["state"].  "
";
        $stat1 = $row["state"] ;
    }
} else {
    echo "bad number of results: $result->num_rows ";

}

// builds radio button checked mark plus state flag for ESP8266 to read
if ( $stat1 == 'on' ) {
   $onChecked = 'checked' ;
   $offChecked = '' ;
   echo '<!-- **on** -->' ;
} else {
   $offChecked = 'checked' ;
   $onChecked = '' ;
   echo'<!-- **off** -->' ;
}

$conn->close();
?> 

<form method="post" action="<?php echo htmlspecialchars($_SERVER["PHP_SELF"]);?>" >



State: 

<input type="radio" onchange="this.form.submit()"  name="state" value="on"    <? echo $onChecked; ?> > on

<input type="radio" onchange="this.form.submit()"  name="state" value="off"   <? echo $offChecked; ?> > off

</form>

</body>
</html>

. . . and the corresponding sketch for an ESP8266 which did some other things as well. You are interested in the part “read remote SQL DB and set LED on/off as appropriate” :

/*
 IoT Demonstration

 1. read remote SQL DB and set LED on/off  as appropriate
 2. get pir sensor data and send to remote SQL DB
 3. get tilt switch data and sent to remote SQL DB

 ver v04  30.05.2016  integrate tilt switch

 */

#include <ESP8266WiFi.h>

const char* ssid     = "******";
const char* password = "******";

const char* host = "www.******.ch";

const int indicatorPin = 13 ;
const int pirInPin = 12 ;
const int pirOutPin = 14 ;
const int tiltInPin = 16 ;
const int tiltOutPin = 5 ;


unsigned long pirLastMs = 0 ;
unsigned long radioPollLastMs = 0 ;
unsigned long tiltMs = 0 ;

int tiltStateOld = LOW ;

boolean pirDetectedInInterval = false ;
boolean tiltDetectedInInterval = false ;

int loopType = 1 ;
unsigned long loopCount = 0 ;


void setup() {
  Serial.begin(115200);
  delay(100);

  pinMode( pirInPin, INPUT ) ;
  pinMode( pirOutPin, OUTPUT ) ;
  pinMode( tiltInPin, INPUT ) ;
  pinMode( tiltOutPin, OUTPUT ) ;
  // indicator pinMode set in loop

  // We start by connecting to a WiFi network

  Serial.println();
  Serial.println();
  Serial.print("Connecting to ");
  Serial.println(ssid);

  WiFi.begin(ssid, password);

  while (WiFi.status() != WL_CONNECTED) {
    delay(500);
    Serial.print(".");
  }

  Serial.println("");
  Serial.println("WiFi connected");
  Serial.println("IP address: ");
  Serial.println(WiFi.localIP());
}



void loop() {

  yield() ;

  // record pir sensor data
  if (digitalRead( pirInPin ) == HIGH ) {
    // pirLastMs = millis() ;
    pirDetectedInInterval = true ;
    digitalWrite( pirOutPin  , HIGH ) ;
  }
  else {
    digitalWrite( pirOutPin  , LOW ) ;
  }

  // look for change in tilt sensor value
  if ( digitalRead( tiltInPin ) !=  tiltStateOld  )  {
    tiltDetectedInInterval = true ;
    tiltMs = millis() ;
    tiltStateOld = digitalRead( tiltInPin ) ;
  }

  // if change of tilt state then light led for 1 second.
  // can't use state directly because it could be on all the time.
  if ( millis() - tiltMs  < 1000 ) {
    digitalWrite( tiltOutPin  , HIGH ) ;
  }
  else {
    digitalWrite( tiltOutPin  , LOW ) ;
  }



  if (millis() - radioPollLastMs  > 2000 ) {
    loopCount ++ ;

    // choose if send or get done in this iteratioin

    if ( loopCount % 6 == 0 )  {
      loopType = 1 ;   // send pir  1/6
    }
    else if ( loopCount % 6 == 3 ) {
      loopType = 2 ;   // send tilt 1/6
    }
    else {
      loopType = 0 ;  // get indicator 4/6
    }


    radioPollLastMs = millis() ;

    Serial.print("connecting to ");
    Serial.println(host);

    // Use WiFiClient class to create TCP connections
    WiFiClient client;
    const int httpPort = 80;
    if (!client.connect(host, httpPort)) {
      Serial.println("connection failed");
      return;
    }

    if (loopType == 0 ) {
      // get indicator
      // We now create a URI for the request
      String url = "/IoT/indicator.php";
      Serial.print("Requesting URL: ");
      Serial.println(url);

      // This will send the request to the server
      client.print(String("GET ") + url + " HTTP/1.1\r\n" +
                   "Host: " + host + "\r\n" +
                   "Connection: close\r\n\r\n");
      delay(500);

      // Read all the lines of the reply from server and print them to Serial
      boolean commOk = false ;
      boolean isOn = false ;

      while (client.available()) {
        String line = client.readStringUntil('\r');

        // parse lines to find state inidcator
        if ( line.indexOf("**on**") >= 0 ) {
          Serial.println(line);
          Serial.println("found **on**") ;
          Serial.println( line.indexOf("**on**") ) ;
          commOk = true ;
          isOn = true ;
        }
        if (line.indexOf("**off**") >= 0 ) {
          Serial.println(line);
          Serial.println("found **off**") ;
          Serial.println( line.indexOf("**off**") ) ;
          commOk = true ;
          isOn = false ;
        }
        Serial.print(line);
      }
      // indicator pin is 3 state HIGH/LOW/high impedance
      if ( commOk ) {
        pinMode( indicatorPin, OUTPUT ) ;
        if ( isOn ) {
          digitalWrite(indicatorPin, HIGH);
        }
        else {
          digitalWrite(indicatorPin, LOW);
        }
      }
      else {
        pinMode( indicatorPin, INPUT ) ;
      }
    }

    else if (loopType == 1 ) {
      // send pir sensor data
      // We now create a URI for the request
      String url = "/IoT/sensorLog.php?sensorId=PIR01&activityCode=activity-detected";
      if ( pirDetectedInInterval ) {
        Serial.println ("++++++++++++pir detected" ) ;
        pirDetectedInInterval = false ;  //reset
        Serial.print("Requesting URL: ");
        Serial.println(url);

        // This will send the request to the server
        client.print(String("GET ") + url + " HTTP/1.1\r\n" +
                     "Host: " + host + "\r\n" +
                     "Connection: close\r\n\r\n");
        delay(500);

        // Read all the lines of the reply from server and print them to Serial
        while (client.available()) {
          String line = client.readStringUntil('\r');
          Serial.print(line);
        }
      }
      else {
        Serial.println ("============pir NOT detected" ) ;
      }
    }

    else if (loopType == 2 ) {
      // send tilt sensor data
      // We now create a URI for the request
      String url = "/IoT/sensorLog.php?sensorId=tilt02&activityCode=state-change";
      if ( tiltDetectedInInterval ) {
        Serial.println (">>>>>>>>>tilt detected" ) ;
        tiltDetectedInInterval = false ;  //reset
        Serial.print("Requesting URL: ");
        Serial.println(url);

        // This will send the request to the server
        client.print(String("GET ") + url + " HTTP/1.1\r\n" +
                     "Host: " + host + "\r\n" +
                     "Connection: close\r\n\r\n");
        delay(500);

        // Read all the lines of the reply from server and print them to Serial
        while (client.available()) {
          String line = client.readStringUntil('\r');
          Serial.print(line);
        }
      }
      else {
        Serial.println (">>>>>>>>>tilt NOT detected" ) ;
      }
    }


    Serial.println();
    Serial.println("closing connection");
  }
}

Hello let me rephrase what i said before. I have the mysql database setup. But I'm working on the php page part. I just say your php page code. and esp8266. I don't have one of them right now just arduino uno and Ethernet but it should be okay.

This is my connect.php page.

<?php
 $value1 = filter_input(INPUT_POST, 'value1');
 $value2 = filter_input(INPUT_POST, 'value2');
 if (!empty($value1)){
if (!empty($value2)){
$host = "localhost";
$dbusername = "root";
$dbpassword = "demo!";
$dbname = "arduino";

// Create connection
$conn = new mysqli ($host, $dbusername, $dbpassword, $dbname);

if (mysqli_connect_error()){
  die('Connect Error ('. mysqli_connect_errno() .') '
    . mysqli_connect_error());
}
else{
  $sql = "INSERT INTO arduino (value1, value2)
  values ('$value1','$value2')";
  if ($conn->query($sql)){
    echo "New record is inserted sucessfully";
  }
  else{
    echo "Error: ". $sql ."
". $conn->error;
  }
  $conn->close();
}
}
else{
  echo "value1 should not be empty";
  die();
}
 }
 else{
  echo "value2 should not be empty";
  die();
 }
?>

this is the html code

<!DOCTYPE html>
<html>
<head>
 <title>Arduino post to mysql</title>
</head>
<body>
<form method="post" action="connect.php">
Value1 : <input type="text" name="value1">


Value2 : <input type="text" name="value2">


<input type="submit" value="Submit">
</form>
</body>
</html>

This is code i use for a project a long time ago for php and mysql. But i think i never got it to work. I keep getting a error saying value1 should not be empty

It looks OK apart from:

  1. the two error messages at the end being interchanged, for example:
echo "value1 should not be empty";

should be

echo "value2 should not be empty";
  1. It will put a new row in the table each time you use the form. I would have thought you should instead update the existing row(s) since these rows represent the current state of the relay(s). You can do it with multiple rows, but then you have to write a view which presents the latest one.

Once you get that working, you still have to: (a) create the php/html code which the Arduino can call to discover what state the relays should be in. (b) write the arduino sketch to to do (a) above, interpret the result and switch the relays as needed.

hello 6v6gt they look the same i don’t see a difference. And I’m still getting that error.

josephchrzempiec: hello 6v6gt they look the same i don't see a difference. And I'm still getting that error.

It is true that the difference is subtle, but it is untidy if the field is labeled value1 and the error message refers to value2.

Your code looks OK and if you were putting simple values in the 2 fields before pressing submit should not, as far as I can see, have generated that particular error, but could depend on your particular PHP environment version /default settings etc.

But you could try changing the 2 lines with filter_input eg

$value1 = filter_input(INPUT_POST, 'value1');

to

$value1 = $_POST[ "value1" ] ;

just to see if the error goes away.

Hello i would love to update this post and completed it. Back then i had some person problems i had to deal with.

6v6gt i would love to use your code you have created. What table and fields would i need for mysql. I'm not much of a mysql guy like arduino I'm learning.

Joseph

I’m just looking back over the history of this.
If you have a PHP script and a matching table in the MySQL database, you can actually test the setup without an Arduino. You can simply type the correct URL into a web browser. However, that works only with the HTTP GET method to talk to the web server, but not the HTTP POST method.
Are you using an internet based server for the MySQL/ PHP / Web Server (e.g. Apache) or have you set something up at home ? Sometimes you get simple tools to create and view the contents of databases and general management functions etc. PhpMyAdmin is an example.

I'm usung the code you posted and so far i have got the php and mysql working. But I'm working on the arduino side now. I will post a update.

Joseph