Turn on/off light using mysql on seperate server

[Brandon222]

Hi there, I am new to Raspberry PI and I am looking for a way in which I can turn on and off a light using MySQL and a web interface which will be hosted on a separate machine since I eventually want to use more than one RPI. I have searched online and can only find servers being run on the same RPI or not using MYSQL at all. Is there any tutorials out there that could send me in the right direction? If so could someone point me to them or give me some information regarding this?

[DougieLawson]

What possible purpose would adding, updating or deleting a row in a database table have when it comes to controlling external hardware?

[Heater]

MySql is a database and as such nothing to do with turning any hardware on or off.

If your web server is on another machine that relays commands from visiting web browsers to many Raspberry Pi then you need some communication from the web server to the Pi's.

I suggest you create that web server using node.js and then simple Javascript, node.js, clients on the Pi's can communicate with it using websockets.

If you need to record what happened, then log it to MySql.

[Brandon222]

I eventually want to have more than one RPI running at a time from one screen. So lets say I have 3 lights bulbs all controlled by differe RPIs in my house. I want to be able to see them all on the same webpage and click on a button to send say a 1 to my database where the RPI will read it and turn the light off while not affecting the other ones.

[Heater]

So you have a web page with a bunch of "on/off" buttons displayed.

Perhaps the number of buttons and their state comes from a database of light bulbs.

When a user clicks an "on" or "off" button an AJAX request will be made to the web server indicating the desired action.

Now, that web server needs to be in communication with all the remote Pi and relay the users on/off commands to them.

The easy way to do this is to write the web server in node.js and have all the Pi "chat" to it from node.js clients. Preferably using web sockets.

That node.js code on the Pi can easily interface to the PI GPIO pins or whatever hardware interface your light bulbs need. Whilst at the same time making it very easy to communicate with your web server over web sockets.

[Brandon222]

Interesting I never thought of that approach. I am not too familiar with Node.js do you know of any tutorials showing this method?

[Heater]

Not really.

Ask Google about "node.js". "express.js", "socket.io". There are tons of blogs and tutorials out there.

Also lot's videos on Youtube.

Or, you can start with the AdaFruit articles: https://learn.adafruit.com/node-embedde ... ode-dot-js

[asandford]

Use Node Red, it's already installed and there are loads of tutorials for it (controlling things via web pages is demonstrated).

You'll never want to touch python again (which can't be a bad thing).

[Brandon222]

amazing! This is exactly what I am looking for I will post my updates in a bit.

[Brandon222]

Ok so after looking into Node-Red it seems to be exactly what I am looking for. However, I am just wondering if anyone knows how to do a simple SELECT statement with this. I have already installed the mysql node.

I have attached an image of what I think it should be. Also I this is what I believe should be my code for the Select Statement:

Code: Select all

msg.topic = "Select status FROM Test1";
return msg;

[asandford]

You need a further node to process the output contained in msg.payload from the DB test node. This could be a simple debug node just to check that you are on the right tracks (when developing a flow, put as many debug nodes in as you can to confirm that things are working as you expect).