Automated Chicken Coop pt. 10 – Remotely Controlling the Coop pt. 9 “Storing Data w MySQL”

Storing the Data with MySQL

So far you should see the temperature reported in the right debug pane of Node-RED once when the NodeMCU loads, and then periodically to the time you have set for reporting. Now we store the data in a MySQL database.

When you set up your mysql installation, you were asked for a password to provide to the root user.  I hope you remember this password!

INSTALLING PHPMYADMIN

But for now – we need to install “PHPMyAdmin”.  Now strictly speaking, PHPMyAdmin isnt a pre-requisite to continue.  If you are comfortable working inside the terminal you can easily look up information on how to create a database and table in your MySQL installation from there.  PHPMyAdmin will allow us to manage the database from a web browser.

Open a terminal window on your Raspberry PI and type the following command.

sudo apt-get install phpmyadmin

It will now begin to install. You will be presented with a screen asking the type of web server you want it to run off. Select apache2 as this is the server we installed earlier.

Next we will need to configure PHPMyAdmin to connect a database. To do this select yes at the next prompt.
PHPMyAdmin setup

It will now ask for a password, enter the one we set previously when we set up MYSQL.

Next it will ask you to set a password for PHPMyAdmin. You can keep it the same as the password to the MYSQL database or something separate. Make sure you remember it as this is the password you will need to access it.  Personally I keep the password the same.

If you are a database admin and reading this – yes I know this isnt the most secure installation of MySQL.  If somebody is that interested in reading the data from my coop – they can have at it.

Logging In To phpMyAdmin & Creating a Database

Once the installation is finished, on your Raspberry-PI open a browser and type the following address into the address bar

http://localhost/phpmyadmin

You should be brought to a page that looks like this.

Login with the root user and the password you set for it.

When the page loads look to the left, here is a list of all databases in your mysql installation.  Click new to create a new database.

 

Now in the main pane, provide a name for your database and click Create.  Look back at the list of databases and your new database should be visible.

Click on your newly created database.  You’ll be asked to create a table.  Give the table a descriptive name such as “TemperatureLogs” set the columns to 3 and click “Go”

On the next page you will be asked to define the various columns and the data they will contain.  Don’t worry about what you need to do – this is one of those cases I am going to go into depth on setting up the table.

After clicking go you will be presented with this:

Fill out the information in these fields as shown in the following image:

Lets look at what we did here, we defined three columns, named “id”, “date”, & “temp”.

the id column was defined with the type INT (remember INTs are whole numbers) and we checked the A/I (autoincrement) checkbox.

The date column was defined with the type DATETIME and no other fields editted.

The temp column was defined with the type text and no other fields editted.

Click Save and you will have created your table that we will store our temperature data to.

We’ve now created our chicken coop database, and created a table to store the temperature data.  As we continue with our project, we will add more tables over time.  Each time I will provide the information needed for the table, but I encourage you to learn a little about MySQL databases and tables so you can expand in your own way.

Before we leave PHPMyAdmin, there is one last thing for us to do.  Create a user and assign that user to the database.  While we could use the root user to add data to the database tables, it is not a good practice to do so.  With the ChickenCoop Database selected in the Database List, click the “Privileges” Tab at the top of the page.

You’ll see a list of users that have access to this database, many of them will be root.

Click the “Add User” link at the bottom (shown in picture above) to create a new user for the database.

Fill out the text fields above, writing down the database user and password for later user.  Ignore the “host” field, and make sure the checkbox next to

is selected.  Click Go to create the New user for this database.

BACK TO NODE-RED

Open the Node-Red webpage and click the flow we created earlier, here we will be adding some more Nodes to record the temperature of our coop to the database.

Add 1 TimeStamp Node,  2 Function Nodes, 1 Moment Node, and a MySQL node and arrange them as shown in the following image.

You may notice I moved the second debug node to follow the SQL query node.  You can move nodes by clicking the line that connects two nodes and pressing the Delete Key to release their connection.

The Moment Node

In the Moment Node you’ll fine tune the responding date/time.  As UNIX time may not match your actual time because of TimeZones, we can adjust our time /- hours I ended up needing to adjust mine backwards -6 hours for the correct time.

To figure out this adjustment, leave the adjustment value at 0 for now.  After we post the data to our Table, we can go inside the table and see what date is posted then return here and edit the moment node to correct the time.

Function Nodes

Now for our first function.  Type the following code in the function edit window.

msg.timestamp_mysql2 = msg.timestamp_mysql.slice(0, 19).replace('T', ' ');
return msg;

Here we are converting the timestamp that has been passed thru so far into a date/time format that can be handled by MySQL.

On to our second function.

msg.topic = "INSERT INTO `temperatureLogs` (`Date`,`Temp`) VALUES ('"   msg.timestamp_mysql2  "', '"   msg.payload   "');"

return msg;

We just created our very first MySQL Statement.  Lets read thru it in plain English.

Insert the values of msg.timestamp_mysql2 and msg.payload(which still holds our temperature) into the columns Date & Temp of the “temperatureLogs” table respectively.  Of course, you may need to change the name of the table, and the names of the columns in the above code to exactly (case sensitive) match your own Table.

The MySQL Node

This Node contains the information needed to connect to our MySQL server.  Since we’ve never added information for a mysql database before, click the pencil shown in the first image, then fill out the remaining information about the server, using the user we created earlier.  Leave the host to 127.0.0.1 – this is called the “Home Address” and is a special address every computer has.  Its equivalent to the “Localhost” we have used before. The port number of 3306 is also a correct number – this is the default port that MySQL runs on.

 

Hit deploy after you have finished setting up the MySQL Node, then open a terminal and send a message on your Command topic for temperature.  Go into your table in PHPMyAdmin, you should have data about the reported temperature and time.

If the time is off, return to the Moment Node and adjust the time forwards or backwards to match your own time.  Remember, if you adjust the Moment Node you will need to ReDeploy the Node-Red Flow and resend a message on your Command topic (or unplug and plug in your NodeMCU) to restart normal periodic data collection.

Conclusion

As an aside – if anyone has noticed a reference to a particular culturally relevant TV Show, feel free to post it in the comments.

 

Leave a Reply

Your email address will not be published. Required fields are marked *