Show the state of switches via web browser

Projects General Guidance
1

Hi everyone,

First of all, I'm a beginner. I don't have programming experience, but I'm an advanced (Linux, openwrt) user. I can run/configure a web server and I have experience compiling from source, applying patches, etc.
I'm trying to find the best suitable hardware for the following project:

I want to pick up the state (open/close) of a couple of switches through the input terminals of an I/O board. This I/O board has to be network capable, because it will be far from the pc that will host the database. I want to update a database via sql as soon as the state of any of the switches changes.
Then I want to show these states on a excel sheet alike table via a browser. I was thinking to use google charts (table charts) for this, because I don't know how to code in PHP, but I find google charts fairly easy to use and it is a well documented project.

I have spent a lot of time trying to find a hardware that would pick up the open/close signal and would do the update in the database via sql. I was hoping that there is an off the shelf solution already which I can configure via the webif of the hardware. I'm not lucky so far, that is why I ended up here.

Does anyone know of any proprietary hardware that is capable of doing what I'm trying to achieve?

If not, what is the most simple way of doing this with an arduino board?

Many thanks ahead!

Peter

Update: I have created a connection diagram for the project. This will help further explain what I'm trying to achive. What hardware do you guys recommend for the project? Keep in mind that I can only do basic changes to an existing program, so I prefer to use the hardware that already has a script/program written for it that performs very similar task to what I'm trying to do here.

https://creately.com/jupiter/diagram/image/iwxxu1cq1(image larger than 4 MB)

Click here for zooming functions: https://creately.com/diagram/example/iwxxu1cq1/Switch+status+data+logger

2

This should do what you want, just plug and go with the "Web Server" software.

Just need to be slightly modified for a simple switch input rather than the analogue.

3

WiFi or cat5?

How will the remote switch sensor be powered? PoE, batteries, 5V adaptor... If batteries, how often are you prepared to re-charge?

I use Wemos Mini for my WiFi projects (temp/humidity sensors, weather station) which update remote mySql databases. Very cheap, small and convenient and can be programmed with the Arduino IDE. Its based on the ESP8266 chip, so there is lots of example code around.

Example

4

PaulRB:
WiFi or cat5?

How will the remote switch sensor be powered? PoE, batteries, 5V adaptor... If batteries, how often are you prepared to re-charge?

I use Wemos Mini for my WiFi projects (temp/humidity sensors, weather station) which update remote mySql databases. Very cheap, small and convenient and can be programmed with the Arduino IDE. Its based on the ESP8266 chip, so there is lots of example code around.

Example

I prefer POE, but could be ethernet with PSU or wifi with PSU as well. Battery is not an option. Where can I read about how it can update a remote mysql database?

5

ricky101:
This should do what you want, just plug and go with the "Web Server" software.

Just need to be slightly modified for a simple switch input rather than the analogue.

https://www.arduino.cc/en/Tutorial/WebServer

The google chart has to run on a separate web server (preferably on a PC), because I plan to use more than one of these data acquisition devices at the same time at different locations. I was hoping for it to have a built in webif just for the ease of configuration (normally proprietary hardware do have this feature). If I go with the arduino board, I have to get familiar with the programming of it, so the having a webif for configuration will not be important anymore. However the capability of being able to update a remote sql database is essential.

6

blaudp79:
Where can I read about how it can update a remote mysql database?

The link I gave you is an example. It does the database updates via a php script. I have also seen examples of updating sql databases directly, without a php script.

7

I have edited my first post and included a diagram which gives you guys a little more details. What hardware would you recommend? Both arduino and RPI seem to be capable, but I'm not sure which one is a better fit and which one is easier to setup for a beginner.

8

blaudp79:
What hardware would you recommend?

Did you have a look at my link? I buy Wemos Minis from the Wemos shop on AliExpress. They come in around 12 days from China to UK. If you need more WiFi range, there is also the Mini Pro, which has an external antenna connector so you can attach a higher gain antenna.

9

I've found a commercial unit that does what I want: DI-161 Event Data Logger

Page 26 of the manual shows how to set it up to record state change only (they refer to it as event)

It costs over $600US, so I stay with the DIY option for now :slight_smile:

I have purchased these boards online:

1 pc - arduino UNO + ethernet shield
1 pc - Wemos D1 mini pro + antenna with pigtail
1 pc - generic ESP8266 (with integrated CP2104 TTL chip) board with integrated pcb antenna

I have read many projects online that push sensor state to a database via sql. This one for example is well documented: Arduino: Save data to database - iCreateProject

What I see is that these are running in a loop (sampling rate is set by ms delay) and will read and update the db repeatedly at a fixed time interval. However I only want to send an update when the status of my switch changes. Otherwise I will have many instances of the same record. Is it possible to achieve this?
So instead of saving the data at every loop cycle...

  1. Read the data at every loop
  2. Compare with previous reading
  3. Do nothing if matching
  4. Update SQL if NOT matching

Would this be doable?

10

Yes, it's a simple matter of programming. You seem to have identified the steps required to achieve it, and you seem to be able to read and interpret code, so I think you should give it a try yourself. Post your sketch here if you get stuck.

11

The arduino UNO has arrived today, so I gave it a try. I managed to get repeated pushes to the database. I was using the code given in the tutorial I've linked in one of my earlier post and just modified the input type from analog to digital. It seems to work fine. Here is the code:

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

byte mac[] = {
  0x00, 0xAA, 0xBB, 0xCC, 0xDE, 0x02
};

int pushButton = 2;
int pushButtonReading;

char server[] = "192.168.66.223";

EthernetClient client;

void setup() {
 
  Serial.begin(9600);
  pinMode(pushButton, INPUT);
  Ethernet.begin(mac);
    
}

void loop() {
 
  pushButtonReading = digitalRead(pushButton);
 
  if (client.connect(server, 80)) {
    client.print("GET /write_data.php?");
    client.print("value=");
    client.print(pushButtonReading);
    client.println(" HTTP/1.1");
    client.println("Host: 192.168.66.223");
    client.println("Connection: close");
    client.println();
    client.println();
    client.stop();

  }

  else {
    Serial.println("--> connection failed\n");
  }
 
  delay(10000);
}

This is what I'm getting:

Push Button readings

ID	Timestamp	Value
0	2016-12-24 01:03:54	1
0	2016-12-24 01:04:04	1
0	2016-12-24 01:04:15	0
0	2016-12-24 01:04:25	0
0	2016-12-24 01:04:35	0

So far it was simple. Now I have to find the way to get it to send an update only when there is a change in the state of the button. I have to do a lot of reading for that to happen, because there is no project that I can copy/paste from :).

12

You only need to add about 3 lines of code to detect button changes. It's much simpler than you think. You just need to read the digital pin into a new variable, instead of updating your pushButtonReading variable directly.

int newReading = digitalRead(pushButton);

If the new reading is different from pushButtonReading, then you update pushButtonReading and send the update to the server.

if (newReading != pushButtonReading) {
  pushButtonReading = newReading;
  // update server
  ...
  }

You will then no longer need to use the delay().

13

Thanks a lot for your help! This is the final working code:

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

byte mac[] = {
  0x00, 0xAA, 0xBB, 0xCC, 0xDE, 0x02
};

int pushButton = 2;
int pushButtonReading;
int Reading;
int newReading;

char server[] = "192.168.66.223";

EthernetClient client;

void setup() {
 
  Serial.begin(9600);
  pinMode(pushButton, INPUT);
  Ethernet.begin(mac);
    
}

void loop() {

Reading = digitalRead(pushButton);
delay(5000);
newReading = digitalRead(pushButton);
 
if (newReading != Reading) {
  pushButtonReading = newReading;

if (client.connect(server, 80)) {
    client.print("GET /write_data.php?");
    client.print("value=");
    client.print(pushButtonReading);
    client.println(" HTTP/1.1");
    client.println("Host: 192.168.66.223");
    client.println("Connection: close");
    client.println();
    client.println();
    client.stop();
  }
  else {
    Serial.println("--> connection failed\n");
  }
 }
}
14

That won't work for button presses of less than 5 seconds. Why did you not simply follow my suggested changes?

15

The reason why I put that 5 sec delay there is that these switches will not change state within less than 10 minutes of the last state change, so a 5s resolution is all right. The 5s delay acts as a saftey feature to filter out errors. The switch will be a relay that will be hooked up to a 24V status indicator green light. This green light sometimes blinks a couple before it fully changes state from unlit to lit. I consider this an error to be filtered out. Probably I will use timer relays and I will eliminate that delay from the code later on...

I tried your suggestion but it did not work. Probably I did not apply it correctly. Therefore I went ahead and started to play around with it and it worked this way. Do you think I should still try the code you were suggesting or it should be fine as is?

16

I will show you what I meant exactly if you want. But you are correct to say that the code I had in mind would react more quickly to short term changes. Not all that much more quickly, though, because of the 2~3 second delay caused by sending the GET request. You did not really explain before how quickly the code needed to react to changes.

Do these lights ever flicker on for less than 5s but then stay off, causing a "false alarm" to be recorded in the database?

What would the timer relays you mentioned do? It would be a waste to add more hardware for something that could easily be done in code.

How are you interfacing these 24V signals to the Arduino? A direct connection would damage it because the maximum input is 5V.

17

If you could edit the code to show what exactly you meant, I would really appreciate it.

The green light might flicker only during state change for 1-2s. The purpose of the timer relay is:

  1. To isolate the 24V circuit from the gpio pins and it would only pull the pin to 5V. Basically when the bulb of the green light receives 24V, the relay will pull the gpio pin to 5V.
  2. I can set a delay on it so that the flicker will not be picked up. I prefer this over the code because it can manually be adjusted later if the delay is not enough.
18

blaudp79:
If you could edit the code to show what exactly you meant, I would really appreciate it.

Just this. But as you pointed out, it may react too quickly to "false alarms".

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

byte mac[] = {
  0x00, 0xAA, 0xBB, 0xCC, 0xDE, 0x02
};

int pushButton = 2;
int pushButtonReading;

char server[] = "192.168.66.223";

EthernetClient client;

void setup() {

  Serial.begin(9600);
  pinMode(pushButton, INPUT);
  Ethernet.begin(mac);

}

void loop() {

  int newReading = digitalRead(pushButton);

  if (newReading != pushButtonReading) {
    pushButtonReading = newReading;

    if (client.connect(server, 80)) {
      client.print("GET /write_data.php?");
      client.print("value=");
      client.print(pushButtonReading);
      client.println(" HTTP/1.1");
      client.println("Host: 192.168.66.223");
      client.println("Connection: close");
      client.println();
      client.println();
      client.stop();
    }
    else {
      Serial.println("--> connection failed\n");
    }
  }
}

blaudp79:

  1. To isolate the 24V circuit from the gpio pins and it would only pull the pin to 5V. Basically when the bulb of the green light receives 24V, the relay will pull the gpio pin to 5V.

You can do that with a simple voltage divider made from two resistors (costing pennies).

blaudp79:
2. I can set a delay on it so that the flicker will not be picked up. I prefer this over the code because it can manually be adjusted later if the delay is not enough.

You can manually adjust it in the code for... zero cost!

19

Thank you for all your help!

I have one more question if you don't mind:

I'm thinking to move to move to the arduino Yun, because I need additional features, like Set up a LAMP stack on OpenWrt [Old OpenWrt Wiki]
How would this code change on a YUN?

I know that I can't call #include ethernet.h directly, because the LAN IC is attached to the IC that runs the linux portion of the board. I know I have to call these libraries, but I don't know how exatly:

#include <Process.h>
#include <Bridge.h>

and use parameters similar to this instead of the "client.print" parameters:

  Process p;              
  p.begin("php-cli");      
  p.addParameter("/mnt/sda1/write_data.php"); //assuming that lighttpd is running on the linux side of Yun
  p.addParameter(String(pushbuttonreading));
  p.run();