# MQTT data to a mySQL to display data on public website

**URL:** <https://discourse.nodered.org/t/mqtt-data-to-a-mysql-to-display-data-on-public-website/60082>\
**Category:** General\
**Created:** [19 March 2022 17:44 UTC](https://discourse.nodered.org/t/mqtt-data-to-a-mysql-to-display-data-on-public-website/60082 "2022-03-19T17:44:53Z")\
**Posts on this page:** 15\
**Page:** 1

<div class="post-metadata">

**Author:** ![astrojav](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/astrojav/32/58738_2.png) [@astrojav](https://discourse.nodered.org/u/astrojav)\
**Post date:** [19 March 2022 17:44 UTC](https://discourse.nodered.org/t/mqtt-data-to-a-mysql-to-display-data-on-public-website/60082/1 "2022-03-19T17:44:54Z")

</div>

Hi folks,

Firstly I'm not a programmer but can follow instructions quite well.  
I have an ESP32 sending sensors data to Node-red on a Raspberry Pi, acting as an MQTT broker. The MQTT broker subscribes to a single topic that outputs the data as "a  
parsed JSON object." The debug look like this:

_{"controllertype":"mySQM+","firmware":127,"uptime":"00:09:04","date":"01/01/1900","time":"00:00:00","sqm":17.74,"nelm":3.75,"lux":0.0087,"ambient":23.67,"humidity":57.2,"dewpoint":14.71,"pressure":1014.22,"slpressure":1015.06,"bme280alt":7,"skyambient":20,"skyobject":20,"skystate":1,"cloudcover":100,"raining":0,"rvout":0,"rainprevhr":0,"raincurrhr":0,"raincurrday":0,"windspd":0,"windavg":0,"windgust":0,"windchill":100,"beaufort":0,"winddir":0,"gpsdate":"01/01/1900","gpstime":"00:00:00","gpslat":"-77 00 00 S","gpslon":"166 00 00 E","gpsalt":0,"gpssat":0,"gpsfix":0,"mac":"78:E3:6D:0A:24:14","makehay":12.52}_

On the domain, the MySQL table will look something like the snippet below which should only contain **"ambient"** , **"humidity"** and **"pressure"** data.

```auto
CREATE TABLE Sensor (
    id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    value1 VARCHAR(10),
    value2 VARCHAR(10),
    value3 VARCHAR(10),
    reading_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)

```

From Node-red I would like to send the data to the MySQL database using a PHP post script fround on the website which looks like this example:

```auto
$servername = "localhost";

// REPLACE with your Database name
$dbname = "REPLACE_WITH_YOUR_DATABASE_NAME";
// REPLACE with Database user
$username = "REPLACE_WITH_YOUR_USERNAME";
// REPLACE with Database user password
$password = "REPLACE_WITH_YOUR_PASSWORD";

// Keep this API Key value to be compatible with the ESP32 code provided in the project page. If you change this value, the ESP32 sketch needs to match
$api_key_value = "tPmAT5Ab3j7F9";

$api_key = $value1 = $value2 = $value3 = "";

if ($_SERVER["REQUEST_METHOD"] == "POST") {
    $api_key = test_input($_POST["api_key"]);
    if($api_key == $api_key_value) {
        $value1 = test_input($_POST["value1"]);
        $value2 = test_input($_POST["value2"]);
        $value3 = test_input($_POST["value3"]);
        
        // Create connection
        $conn = new mysqli($servername, $username, $password, $dbname);
        // Check connection
        if ($conn->connect_error) {
            die("Connection failed: " . $conn->connect_error);
        } 
        
        $sql = "INSERT INTO Sensor (value1, value2, value3)
        VALUES ('" . $value1 . "', '" . $value2 . "', '" . $value3 . "')";
        
        if ($conn->query($sql) === TRUE) {
            echo "New record created successfully";
        } 
        else {
            echo "Error: " . $sql . "<br>" . $conn->error;
        }
        $conn->close();
    }
    else {
        echo "Wrong API Key provided.";
    }
}
else {
    echo "No data posted with HTTP POST.";
}
function test_input($data) {
    $data = trim($data);
    $data = stripslashes($data);
    $data = htmlspecialchars($data);
    return $data;
}

```

I'm not sure how to do this in Node-red, any guidance would be much appreciated.

Jairo

---

<div class="post-metadata">

**Author:** ![E1cid](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/e1cid/32/77971_2.png) [@E1cid](https://discourse.nodered.org/u/E1cid)\
**Post date:** [20 March 2022 09:18 UTC](https://discourse.nodered.org/t/mqtt-data-to-a-mysql-to-display-data-on-public-website/60082/2 "2022-03-20T09:18:30Z")

</div>

I doubt you have to use a php script to enter data to your MYSQL DB, there are plenty of MYSQL nodes available to enter your data  
e.g. [Library - Node-RED](https://flows.nodered.org/search?term=mysql&type=node).

Using the first from the list, here is an low code example of how to create the query in `msg.topic`

```auto
[{"id":"2b1eedce.9a031a","type":"inject","z":"bf9e1e33.030598","name":"incoming MQTT","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"{\"controllertype\":\"mySQM+\",\"firmware\":127,\"uptime\":\"00:09:04\",\"date\":\"01/01/1900\",\"time\":\"00:00:00\",\"sqm\":17.74,\"nelm\":3.75,\"lux\":0.0087,\"ambient\":23.67,\"humidity\":57.2,\"dewpoint\":14.71,\"pressure\":1014.22,\"slpressure\":1015.06,\"bme280alt\":7,\"skyambient\":20,\"skyobject\":20,\"skystate\":1,\"cloudcover\":100,\"raining\":0,\"rvout\":0,\"rainprevhr\":0,\"raincurrhr\":0,\"raincurrday\":0,\"windspd\":0,\"windavg\":0,\"windgust\":0,\"windchill\":100,\"beaufort\":0,\"winddir\":0,\"gpsdate\":\"01/01/1900\",\"gpstime\":\"00:00:00\",\"gpslat\":\"-77 00 00 S\",\"gpslon\":\"166 00 00 E\",\"gpsalt\":0,\"gpssat\":0,\"gpsfix\":0,\"mac\":\"78:E3:6D:0A:24:14\",\"makehay\":12.52}","payloadType":"json","x":170,"y":140,"wires":[["f57147da.fb29"]]},{"id":"f57147da.fb29","type":"template","z":"bf9e1e33.030598","name":"","field":"topic","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"INSERT INTO Sensor (value1, value2, value3) VALUES ('{{payload.ambient}}', '{{payload.humidity}}', '{{payload.pressure}}')","output":"str","x":370,"y":140,"wires":[["709bd9d3.7011e8"]]},{"id":"709bd9d3.7011e8","type":"debug","z":"bf9e1e33.030598","name":"MYSQL query","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"topic","targetType":"msg","statusVal":"","statusType":"auto","x":570,"y":140,"wires":[]}]

```

This will enter your data as strings, you maybe better to change your DB table to have your readings as numeric(floats/decimals).  
[edit] Fixed typo cheers @jbudd

---

<div class="post-metadata">

**Author:** ![dynamicdave](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/dynamicdave/32/96_2.png) [@dynamicdave](https://discourse.nodered.org/u/dynamicdave)\
**Post date:** [20 March 2022 09:41 UTC](https://discourse.nodered.org/t/mqtt-data-to-a-mysql-to-display-data-on-public-website/60082/3 "2022-03-20T09:41:01Z")

</div>

Personally, I would use this one as it well supported.

> **[node-red-node-mysql](https://flows.nodered.org/node/node-red-node-mysql)**
>
> A Node-RED node to read and write to a MySQL database

---

<div class="post-metadata">

**Author:** ![jbudd](https://avatars.discourse-cdn.com/v4/letter/j/5f8ce5/32.png) [@jbudd](https://discourse.nodered.org/u/jbudd)\
**Post date:** [20 March 2022 13:09 UTC](https://discourse.nodered.org/t/mqtt-data-to-a-mysql-to-display-data-on-public-website/60082/4 "2022-03-20T13:09:18Z")

</div>

I agree, use node-red-node-mysql to write to the database, it's very reliable and much simpler than calling an external script.

I think @E1cid's example script has a typo - `{{payload.pressure}}' should be '{{payload.pressure}}' ? (Different quote character)

---

<div class="post-metadata">

**Author:** ![astrojav](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/astrojav/32/58738_2.png) [@astrojav](https://discourse.nodered.org/u/astrojav)\
**Post date:** [20 March 2022 17:27 UTC](https://discourse.nodered.org/t/mqtt-data-to-a-mysql-to-display-data-on-public-website/60082/5 "2022-03-20T17:27:21Z")

</div>

Hi @E1cid ,  
Thanks for the quick response.Some additional questions.  
If I understood correctly, I would need to insert a 'change' node after the MQTT node to prepare the `msg.payload` which will contain the query setup named `msg.topic`, right? After that, the "MySQL" node will send data to the DB as shown in the screenshot below. Does that make sense?  
 ![Screenshot 2022-03-20 130255](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/e/d/ed9c5a08d65e1cf7e98fad29815499147112cfe9.png)

The "change" node shall looked something like this?  
 ![Screenshot 2022-03-20 130524](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/c/2/c2f28ac3337680472e7f9b3c7303da1fd6d26d36.png)

In the MySQL node the database setting should look something like this? Would it connect to my host without needing port forwarding?

 ![Screenshot 2022-03-20 131114](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/5/c/5c4a9f3826da410b10694ea1ec9d02b829ab0137.png)

As for the MySQL DB table, this should work, right?

```auto
CREATE TABLE Sensor (
    id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    value1 FLOAT(3,2),
    value2 FLOAT(3,2),
    value3 FLOAT(3,2),
    reading_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)

```

Thanks in advance for the support.

Jairo

---

<div class="post-metadata">

**Author:** ![E1cid](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/e1cid/32/77971_2.png) [@E1cid](https://discourse.nodered.org/u/E1cid)\
**Post date:** [20 March 2022 17:40 UTC](https://discourse.nodered.org/t/mqtt-data-to-a-mysql-to-display-data-on-public-website/60082/6 "2022-03-20T17:40:04Z")

</div>

Yes it should connect without port forwarding.

The inject node i supplied was just for testing as i do not have access to your mqtt topic paayload.

you would connect the mqtt node to the template node, then the template to the mySQL node.  
The template node would not need the single quotes if you are db now accepts floats.

```auto
INSERT INTO Sensor (value1, value2, value3) VALUES ({{payload.ambient}}, {{payload.humidity}}, {{payload.pressure}})

```

The template node outputs the query which is in the message property `msg.topic`, this is passed to the mySQL node.

---

<div class="post-metadata">

**Author:** ![jbudd](https://avatars.discourse-cdn.com/v4/letter/j/5f8ce5/32.png) [@jbudd](https://discourse.nodered.org/u/jbudd)\
**Post date:** [20 March 2022 17:42 UTC](https://discourse.nodered.org/t/mqtt-data-to-a-mysql-to-display-data-on-public-website/60082/7 "2022-03-20T17:42:35Z")

</div>

This:

> [@astrojav](#):
>
> ![Screenshot 2022-03-20 130524](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/c/2/c2f28ac3337680472e7f9b3c7303da1fd6d26d36.png)

Is not correct!

The code that E1cid posted is a complete flow. Import it to Node-Red using the hamburger menu.

 ![Untitled 3](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/0/e/0efebffdf8a54358ac5a9a37ac6dc99dd2489e9b.jpeg)

It seems a bit odd to use "value1" etc as database field names, why not "ambient", "humidity", "pressure"?

---

<div class="post-metadata">

**Author:** ![astrojav](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/astrojav/32/58738_2.png) [@astrojav](https://discourse.nodered.org/u/astrojav)\
**Post date:** [20 March 2022 18:36 UTC](https://discourse.nodered.org/t/mqtt-data-to-a-mysql-to-display-data-on-public-website/60082/8 "2022-03-20T18:36:27Z")

</div>

@E1cid thanks again for the quick reply.  
And thanks for clarifying the template part. I have now set up the flow like this.  
 ![Screenshot 2022-03-20 142445](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/c/a/caf8db6c867fdf3d3ca20fbe6b108d9fcb135585.png)

However, I'm receiving some errors connecting to the database. Here's the screenshot and settings in the MySQL node.

 ![Screenshot 2022-03-20 142836](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/1/5/15e311878df220d428badd9c3e360c9e4d589506.png)

In phpMyAdmin on the domain, the host appears to be localhost:3306 this should relay to [https://skynstars.com](https://skynstars.com), right?

 ![Screenshot 2022-03-20 143331](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/a/9/a9b760e5ec0654c9b056b7773071291b3f760072.png)

![Screenshot 2022-03-20 143439](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/a/a/aa6c098b3ee4c878f0313d4fceeb90b839727379.png)

---

<div class="post-metadata">

**Author:** ![E1cid](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/e1cid/32/77971_2.png) [@E1cid](https://discourse.nodered.org/u/E1cid)\
**Post date:** [20 March 2022 18:56 UTC](https://discourse.nodered.org/t/mqtt-data-to-a-mysql-to-display-data-on-public-website/60082/9 "2022-03-20T18:56:08Z")

</div>

Where is you DB set up is it on same machine or hosted on the web hosted server?

---

<div class="post-metadata">

**Author:** ![astrojav](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/astrojav/32/58738_2.png) [@astrojav](https://discourse.nodered.org/u/astrojav)\
**Post date:** [20 March 2022 19:00 UTC](https://discourse.nodered.org/t/mqtt-data-to-a-mysql-to-display-data-on-public-website/60082/10 "2022-03-20T19:00:57Z")

</div>

It's on the web-hosted server.

---

<div class="post-metadata">

**Author:** ![E1cid](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/e1cid/32/77971_2.png) [@E1cid](https://discourse.nodered.org/u/E1cid)\
**Post date:** [20 March 2022 19:09 UTC](https://discourse.nodered.org/t/mqtt-data-to-a-mysql-to-display-data-on-public-website/60082/11 "2022-03-20T19:09:44Z")

</div>

Then i think you will have to configure the server to allow remote access to mySQL. Long time since i hosted on web. You may have to ask the hosting company how to achieve this.

---

<div class="post-metadata">

**Author:** ![astrojav](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/astrojav/32/58738_2.png) [@astrojav](https://discourse.nodered.org/u/astrojav)\
**Post date:** [20 March 2022 20:13 UTC](https://discourse.nodered.org/t/mqtt-data-to-a-mysql-to-display-data-on-public-website/60082/12 "2022-03-20T20:13:01Z")

</div>

Ok, here's a solution I found to inject values into the MySQL DB on the web-hosted server.  
I've used the HTTP Request node (POST) that calls in an API key found in a PHP script on the web-hosted server (e.g., URL: [http://example.com/weather/post-data.php](http://example.com/weather/post-data.php)). Once the API key is verified, the PHP script injects the values into the DB. Not sure if this is a safe way to put values in the DB, but as shown on the screenshot below, it's creating the table. What are your thoughts on this?

Here's what the flow looks like:

 ![Screenshot 2022-03-20 154502](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/6/f/6f7f4695d2bd073791c97a7852bb021ae57bb4fc.png)

Here's the change node setting:

 ![Screenshot 2022-03-20 155605_1](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/a/a/aa9fb0cb89256642d896ad8069fa9cedb753c52d.png)

The data being injected into the DB:

 ![Screenshot 2022-03-20 160446](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/3/3/339c030c2a2c2bdcf2e94b6b09b9765c8fed892e.png)

The PHP script used on the web-hosted server to inject values in the DB looks like this:

```auto
<?php

$servername = "localhost";

// REPLACE with your Database name
$dbname = "REPLACE_WITH_YOUR_DATABASE_NAME";
// REPLACE with Database user
$username = "REPLACE_WITH_YOUR_USERNAME";
// REPLACE with Database user password
$password = "REPLACE_WITH_YOUR_PASSWORD";

// Keep this API Key value to be compatible with the ESP32 code provided on the project page. If you change this value, the ESP32 sketch needs to match
$api_key_value = "tPmAT5Ab3j7F9";

$api_key = $value1 = $value2 = $value3 = $value4 = $value5 = $value6 = "";

if ($_SERVER["REQUEST_METHOD"] == "POST") {
    $api_key = test_input($_POST["api_key"]);
    if($api_key == $api_key_value) {
        $value1 = test_input($_POST["value1"]);
        $value2 = test_input($_POST["value2"]);
        $value3 = test_input($_POST["value3"]);
        $value4 = test_input($_POST["value4"]);
        $value5 = test_input($_POST["value5"]);
        $value6 = test_input($_POST["value6"]);
        
        // Create connection
        $conn = new mysqli($servername, $username, $password, $dbname);
        // Check connection
        if ($conn->connect_error) {
            die("Connection failed: " . $conn->connect_error);
        } 
        
        $sql = "INSERT INTO Sensor (value1, value2, value3, value4, value5, value6)
        VALUES ('" . $value1 . "', '" . $value2 . "', '" . $value3 . "', '" . $value4 . "', '" . $value5 . "', '" . $value6 . "')";
        
        if ($conn->query($sql) === TRUE) {
            echo "New record created successfully";
        } 
        else {
            echo "Error: " . $sql . "<br>" . $conn->error;
        }
    
        $conn->close();
    }
    else {
        echo "Wrong API Key provided.";
    }

}
else {
    echo "No data posted with HTTP POST.";
}

function test_input($data) {
    $data = trim($data);
    $data = stripslashes($data);
    $data = htmlspecialchars($data);
    return $data;
}

```

---

<div class="post-metadata">

**Author:** ![E1cid](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/e1cid/32/77971_2.png) [@E1cid](https://discourse.nodered.org/u/E1cid)\
**Post date:** [20 March 2022 20:39 UTC](https://discourse.nodered.org/t/mqtt-data-to-a-mysql-to-display-data-on-public-website/60082/13 "2022-03-20T20:39:01Z")

</div>

Using http over the web with your api key is not very secure, It would be best to use https

---

<div class="post-metadata">

**Author:** ![astrojav](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/astrojav/32/58738_2.png) [@astrojav](https://discourse.nodered.org/u/astrojav)\
**Post date:** [20 March 2022 21:03 UTC](https://discourse.nodered.org/t/mqtt-data-to-a-mysql-to-display-data-on-public-website/60082/14 "2022-03-20T21:03:18Z")

</div>

Understood...

On a slightly different topic. With the same sensor values, I've created a node-red dashboard that I can only access on my local network. The screenshot can be found below.  
Is there a method to display this screen on a public webpage?

 ![Screenshot 2022-03-20 134226](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/7/f/7f7dd3473a590e59ece9e88fd8e388f74ac0364c.png)

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/1X/d073cd938eafa2e558d7c2cd59003b3ef4963033.png) [@system](https://discourse.nodered.org/u/system)\
**Post date:** [3 April 2022 21:03 UTC](https://discourse.nodered.org/t/mqtt-data-to-a-mysql-to-display-data-on-public-website/60082/15 "2022-04-03T21:03:37Z")

</div>

This topic was automatically closed 14 days after the last reply. New replies are no longer allowed.