Arduino with PHP and mySQL. {Noob seeks direction}

Projects General Guidance
1

STUMBLING BLOCK
Get Arduino to read returned variables from a PHP script.

I stopped programming in the 80's, mostly Perl. When I recently discovered all things Arduino I was excited to get back into coding. Small devices that have about the same horsepower as any of my desktops from three+ decades ago.

As a first project I am trying to control multiple strings of addressable LEDs. Each string controlled by a NodeMCU ESP8266 V1.0 via WIFI. They'll each send a request a couple times a second and update when a change is made to the database.

The idea is to coordinate them all with mySQL. Each string will connect to a LOCALHOST PHP script that queries the mySQL database and then returns relevant data.

So far I've managed to figure out how to:

  • control addressable LEDs to do whatever I want. (Just pixels, not rocket science.)
  • create a mySQL database with RGB values for each LED.
  • echo the required variables from mySQL to a local http page using PHP

Where I am falling down is getting the returned variables back to the Arduino (ESP8266).

Pixel ID and 0-255 for Red, Green and Blue.
Tiny integers are all I need.

example:

Pixel Red Green Blue
1 100 100 23
2 0 0 255
3 200 100 50

From PHP I can spit the numbers out in whatever format is easiest to use.

Any suggestions welcome. Thus far I haven't managed to get an Arduino example working that loads variables from a PHP script. Once I do that I can manage to glue it all together.

Thanks!

2

In my footer are some examples that may be of interest.. but also searching my nic + WAMP or PHP will yield some others.

That being said...

I think you/your set-up would benefit greatly from using a Raspberry PI (or whatever) as the BRAIN/central station.

Configured as an MQTT server. This will help in your consistent 'polling' approach.. as an MQTT server have a subscribe/publish platform.. so your 'nodes' dont have to constantly 'check' for a change.. they are alerted when a change happens.

The PI can also have the Apache, MySQL and PHP set up there as well.. (thats how I have my configured as both an MQTT broker and a LAMP server)

As for the PHP side of things.. I'm not quite clear how you are trying/wanting to get the vars? from a web request? or directly from a serial command/dump?

3

I chose the ESP8266 because of the compact size. I want as small a footprint as possible so that the controller can be easily hidden. The localhost server running PHP and mySQL can be dedicated. An old laptop with no other use is the idea. Found the one I am using now for under $50.

As for the PHP side of things, I am also not quite clear how I am wanting/ trying to get the variables. I guess that is the gist of my question.

4

I have never set up an MQTT broker on just a 'PC' before..

But it is still something I recommend.. whether it runs of a RPi or a 'laptop'.. the point was NOT to use a 'polling' type approach.

nobody said/mentioned anything about getting rid of the ESP8266 modules.. (wouldnt make any sense it there is no one to 'publish' changes to)

Regarding the PHP side of things... which again really depends on if you stay with the current inefficient approach of polling or not)

In my many post/examples here.. I guess there both types to choose from (non using an MQTT broker approach, direct PHP connection through either serial or returned data from DB query).. although I'm not sure if they will lend themselves well/scale to your current approach/project.

Once you settle on something and have a question back so someone can help.

5

Thanks. I'll read about the MQTT broker.

I'm about a week into playing with Arduino and it's been 30 years since I wrote a single line of code. I'm learning by doing and trying to figure out the easiest way to get variables from a database to my Arduino.

The help I seek is on settling on a way that I can get that working. Perhaps MQTT is reasonably uncomplicated. It's a direction to look in which is what I need.

6

MQTT Broker is still a bit daunting. The approach I am trying right now is to open simple PHP page on localhost that echos a string of eight letters and then try to get that data into Arduino.

  • Connect to WIFI #Works.
  • test.PHP returns eight random characters #Works
  • Read eight characters from localhost/test.php #Doesn't work

Yesterday I couldn't connect to mySQL with PHP. Two days ago I managed to get addressable LEDs to do what I wanted. Four days ago I finally got WIFI to work. Earlier in the week I spent two days figuring out the Arduino IDE serial monitor. Now those things are all pretty simple.

Here's my code. I stood on the shoulders of giants to piece it together a la Frankenstein. MQTT seems like week three or four level. Totally lost. Probably a better solution, but the idea is to learn and have fun. Right now the goal is blinking lights, WIFI and a database.

#include <ESP8266WiFi.h>
#include <ESP8266WiFiMulti.h>


byte mac[] = { 0xDE, 0xAD, 0xBE, 0xEF, 0xFE, 0xED };

char inString[32]; // string for incoming serial data
int stringPos = 0; // string index counter
boolean startRead = false; // is reading?


ESP8266WiFiMulti WiFiMulti;

IPAddress server(192, 168, 1, 69);  
WiFiClient client;

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

    // We start by connecting to a WiFi network
    WiFiMulti.addAP("ANGMAR", "Becker50");

    Serial.println();
    Serial.println();
    Serial.print("Wait for WiFi... ");

    while(WiFiMulti.run() != WL_CONNECTED) {
        Serial.print(".");
        delay(500);
    }

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

    delay(500);

// Everything above seems to do what it is meant to. 
// Next I try to get eight characters from PHP and print them in the serial monitor. 

    if (client.connect(server, 80)) {
    Serial.println("connected");
    // Make a HTTP request:
    client.println("Host: localhost/test.php");
    client.println("Connection: close");
    client.println();
  } else {
    // if you didn't get a connection to the server:
    Serial.println("connection failed");
  }



    
}


void loop() {

  
  // if there are incoming bytes available
  // from the server, read them and print them:
  if (client.available()) {
    char c = client.read();
    Serial.print(c);
  }

  // if the server's disconnected, stop the client:
  if (!client.connected()) {
    Serial.println();
    Serial.println("disconnecting.");
    client.stop();

    // do nothing forevermore:
    while (true);
  }
}
7

I dont want to mislead you... as I was in the same boat when introduce to MQTT broker stuff..

I didnt fully understand it.. nor the benefit.

You will still need the PHP scripts and what not for talking to the database..etc..

What the MQTT broker does for you is stop the un-needed (and frankly just bad practice) of consistently querying the database to see if a 'change' has been made.. and then act accordingly.. (or more so.. not do anything because there was no change.. and hence wasted time/resources..etc..)

The general 'idea' behind it is..

You set up one of your 'nodes' to subscribe to a 'topic' (or 'topics').... we'll call this 'topic' 'led_change'

Whenever this topic changes (ie: gets a new value or something).. it 'publishes' this change to all 'subscribed' devices..

so it sits and waits patiently not wasting time or resources.. until it thre is a change that the devices changes about (its subscribed topic)..

All that being said, here are some of the examples I noted above.

This one use an Adobe Flash front end/GUI... to send data to a PHP script.. that ultimately send the data via serial port to the connected Arduino:
http://forum.arduino.cc/index.php?topic=124394.0

Link to thread about using the ESP to host a WEB PAGE to be used as a GUI to control the connected devices:

This link is a thread where I post files for a working example:
https://forum.arduino.cc/index.php?topic=452765.30

I believe the user is using a ethernet shield and not wi-fi.. but the set-up and PHP s (even though the example IS NOT MQTT based)

http://forum.arduino.cc/index.php?topic=454390.0

Link to post where user wanted to use fingerprint logging to gain entry via php & database... but had some requirements of a 'timed thing'... (didnt make much sense.. but the project code was posted for him more or less)

https://forum.arduino.cc/index.php?topic=396991.msg2737396#msg2737396

From all of the above.. you should have no problem throwing something together that works..

I see your project as have to 'entry points' (more or less)

1.) something changes the database
2.) getting the change out to node/led controllers.

*1-
How is your database being updated? What activity or behavior/event changes this 'data' in your database?

*2-
I'm not 100% clear on your set-up..

(as in how the led strips work...etc)..

Sync'ing the strips might be rather difficult if you are awaiting on an HTTP response...

but again.. I'm not clear on what you have in your mind.. on the effects/patterns..etc..

The Flash example has some RGB values I pass in from a PHP script.. but that was only for the 1 led.

Are you using individually addressable LEDS? Like a Neopixel led strip or something?

** I'm actually leaving on vacation in several hours.. (out of country for a bit)..

but if you go through the links.. (You'll probably be able to figure out the parsing stuff on your own).. and have any questions.. post back I can walk through any questions with the stuff I posted.

Seems like you got a pretty good understanding.

I will caution though.. that Neopixel strips have a VERY SPECIFIC TIMING requirement..

Which keeps the main CPU pretty busy. You need to code the patterns in such a way that it has time to check for other events.. (such as a button push or in your case a serial event update letting the Arduino know it needs to change the led behavior)

8

If, however, you are still going the route of getting information returned from PHP to an ESP8266 acting as a HTTP client, in principle you define a PHP page which delivers just the data items you want in a nice format. On the ESP you invoke that web page then parse the stream you get back and pick out your data items. You've got some of this already in your code.

If you have only a few data items, you can probably pack them together somehow in PHP so they are easy enough to parse on the ESP side.
If you have more, it well worth using JSON, which is a simple format "":"","":"", . . . etc.

You can generate JSON in PHP very easily using json_encode. Be aware that nulls may cause problems on the ESP side:

<html>
<head>
<title>View Sensor Log</title>
</head>
<body>
<p>
<?php 

/*
     usage:   ?id=0     returns latest record
              ?id=x     x >=1 returns record x

*/
 
// echo "getCall 
" ;

if ( is_numeric( $_GET["id"] ) ) {
   $id = $_GET["id"] ;
}
else {
   $id = "0" ;
}

 // ============================================================================ 
      $servername = "localhost";
      $username = "xxxx";
      $password = "xxxx";
      $dbname = "xxxx";

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

if ( $id == "0" ) {
   $sql = "SELECT * FROM  callExp  ORDER BY id DESC  LIMIT 1" ;  // get newest row
}
else { 
   $sql = "SELECT * FROM  callExp  WHERE id = $id" ;  // get specific row
}   

$result = $conn->query($sql);

echo "***Json: "  ; // marker - this is searched for on the ESP

if ( $result->num_rows == 1 ) {
    // output data of each row
    $row = $result->fetch_assoc() ;
    
    $row = array("ErrorCode" => "0" ) + array("ErrorText" => "" ) + $row ;

    if ( $row["name"] == NULL ) { 
        $row["name"] = "" ; //      json_encode returns a NULL which is not handled by the Arduino lib.
    }
    echo json_encode( $row ) ;  // create JSON output.

    // var_dump( $row ) ;

/*
    echo "id = " . $row["id"] . "
" ;
    echo "timeStampSystem = " . $row["timeStampSystem"] . "
" ;
    echo "telNo = " . $row["telNo"] . "
" ;  
    echo "nameNet = " . $row["nameNet"] . "
" ; 
    echo "name = " . $row["name"] . "
" ; 
    echo "dateStampNet = " . $row["dateStampNet"] . "
" ; 

    echo "checkDigitOk = " . $row["checkDigitOk"] . "
" ; 
    echo "numberInRun = " . $row["numberInRun"] . "
" ; 
    echo "runNumber = " . $row["runNumber"] . "
" ; 
*/



} 
else {
    $myObj->ErrorCode = "1" ;
    $myObj->ErrorText = "record {$id}. {$result->num_rows} row(s) found"  ;  
    echo json_encode( $myObj ) ;
}


?> 
</p>
</body>
</html>

To get that and parse it on the ESP, here is an example code snippet which I had to trim to get with in the 9000 characters here allowed here.

#include <ArduinoJson.h>
#include <ESP8266WiFi.h>
#include "WebClient.h"
. . . 
. . .


void getCallFromDb( byte par ) {
  //
  // gets call based on par from db and displays it as a history record
  //
 
  WiFiClient client;
  swSerial.print(F("connecting to "));
  swSerial.println( config.remoteHost );

  if (!client.connect( config.remoteHost , 80 )) {
    swSerial.println(F("connection failed"));
    dbOnline = false ;
  }
  else {

    // We now create a URI for the request

    // String url = "/tel/getCallJson.php" + String( "?id=" ) + String( currentId  ) ;
    String url = String( config.phpRetrievePath )  + String( "?id=" ) + String( currentId  ) ;
    swSerial.print(F("Requesting URL: "));
    swSerial.println(url);

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


    swSerial.println(F("Skip to beginning of html"));
    char endOfHeaders[] = "***Json:";
    if ( ! client.find(endOfHeaders) ) {
      swSerial.println(F("Json marker not found"));
      dbOnline = false ;
    }
    else {

      swSerial.println(F("Allocate JsonBuffer"));
      // ver 0.44a
      const size_t BUFFER_SIZE = 512 ;
      DynamicJsonBuffer jsonBuffer(BUFFER_SIZE);

      swSerial.println(F("Parse JSON object"));

      JsonObject& root = jsonBuffer.parseObject(client);
      if (root.success()) {

        swSerial.println(F("Extract values:"));
        swSerial.println(root["ErrorCode"].as<char*>());
        swSerial.println(root["ErrorText"].as<char*>());

        if ( strcmp(  ( root["ErrorCode"].as<char*>() ) , "0" ) == 0 ) {

          if ( par == 0 ) {
            // set / reset if we are getting the latest
            maxIdFound = atol(root["id"].as<char*>()) ;
            currentId  = maxIdFound ;
          }

          swSerial.print("\nmaxIdFound: ");
          swSerial.println( maxIdFound );

          swSerial.println(root["id"].as<char*>());
          swSerial.println(root["timeStampSystem"].as<char*>());
          swSerial.println(root["telNo"].as<char*>());
          swSerial.println(root["nameNet"].as<char*>());
          swSerial.println(root["dateStampNet"].as<char*>());
          swSerial.println(root["checkDigitOk"].as<char*>());
          swSerial.println(root["numberInRun"].as<char*>());
          swSerial.println(root["runNumber"].as<char*>());
          swSerial.println(root["name"].as<char*>());

          swSerial.println();

          swSerial.println(F("Preparing to write to tft:"));

          // delay(100) ;

          dbOnline = true ;

          // create screen for dbRecord


          tft.fillScreen( ILI9341_BLACK );

          tft.setCursor( 0, 10 );   // x, y
          tft.setTextSize(4);

          String telNoTrunc = root["telNo"].as<char*>() ;

          tft.println ( telNoTrunc.substring( 0 , 12 )  ) ;

          tft.setTextSize(2);
          tft.println( ) ;

          tft.setTextSize(3);
          if ( strlen(  root["name"].as<char*>()  ) > 0 ) {
            tft.println ( root["name"].as<char*>() ) ;
          }
          else if ( strlen(  root["nameNet"].as<char*>()  ) > 0 ) {
            tft.println ( root["nameNet"].as<char*>() ) ;
          }
          else tft.println (F( "UNKNOWN" )) ;

          tft.setTextSize(2);
          tft.println( ) ;

          tft.setTextSize(2);
          tft.println ( root["timeStampSystem"].as<char*>() ) ;
          tft.println( ) ;

          if ( strlen(  root["name"].as<char*>()  ) > 0 ) {
            tft.println ( root["nameNet"].as<char*>() ) ;       // maybe blank
          }
          tft.println( ) ;

          String statusLine = "" ;

          if ( strcmp( root["checkDigitOk"].as<char*>()  , "1" ) == 0 )   statusLine += "(OK) " ;
          else statusLine += "(NOK)" ;

        }
        else {
          // error found
          swSerial.println(F("Error found"));
 
        }

      }
      else {
        swSerial.println (F("Parsing failed!"));
        dbOnline = false ;
      }
    }
  }
  delay( 10 ) ;

  swSerial.println(F("exiting getCallFromDb() "));
}
9

There is a mySQL Arduino library available which will let your esp connect to and query the database directly. I have not used it myself yet.

My concern with your design is that if you are running animated patterns on your strips, the mySQL database will be swamped with query requests and won't be fast enough.

10

Is there any particular reason why you chose to use a central server, and using the ESP as a client. Why not use the ESP as a server? Clients can then send requests to the ESP directly, without polling a database.

I did something similar with a web interface with three sliders for controlling a single RGB LED, but it can be easily expanded:
https://tttapa.github.io/ESP8266/Chap14 - WebSocket.html

I did an updated version here, that can be used from different clients at the same time, and is a little more optimized:
https://github.com/tttapa/Projects/tree/master/ESP8266/Control Panel/Control-Panel-WebSocket-Sliders

To come back to your original question: I sent the RGB data as hexadecimal strings. This makes it extremely easy to parse, because two hexadecimal characters are exactly one byte. Each color is represented by one byte (0 - 255), and the order is just RGB.
For example "6600AA" would be a purple-ish color.

I used the following functions to parse it:

uint8_t hex_to_byte(char* str) {  // parse a string of 2 hexadecimal characters to an 8-bit byte
  return (hex_to_nibble(str[0]) << 4) | hex_to_nibble(str[1]);
}

uint8_t hex_to_nibble(char hex) {  // convert a hexadecimal character to a 4-bit nibble
  return hex < 'A' ? hex - '0' : hex - 'A' + 10;
}

char text[] = "6600AA";
uint8_t red   = hex_to_byte(&text[0]); // 0x66 or 102
uint8_t green = hex_to_byte(&text[2]); // 0x00
uint8_t blue  = hex_to_byte(&text[4]); // 0xAA or 170

If you need more pixels, you can simply use a for loop:

char text[] = "6600AA001020";
size_t nb_pixels = strlen(text) / 6;
for (size_t i = 0; i < nb_pixels; i++) {
  uint8_t red   = hex_to_byte(&text[0 + 6*i]);
  uint8_t green = hex_to_byte(&text[2 + 6*i]);
  uint8_t blue  = hex_to_byte(&text[4 + 6*i]);
  setPixel(i, red, green, blue);
}

Pixel 0 will be #6600AA (rgb(102, 0, 170)) and pixel 1 will be #001020 (rgb(0, 16, 32)).

Pieter