# 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:** 1\
**Showing post:** 12

<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;
}

```

---

_[View the full topic](https://discourse.nodered.org/t/mqtt-data-to-a-mysql-to-display-data-on-public-website/60082)._