将mqtt转换的结果插入mysql

Question

EDIT: I should add i am trying to enter the data into mysql everytime it comes into the system.

the javascript runs on the server, previously i managed to do it all via node-red GUI but im trying to make it pure code, collectors send the data to mosquitto on the server then i use the javascript to intercept the mqtt strings and convert them to location,beacon,mac I would then like to insert this data into MYSQL

I have successfully managed to convert my MQTT string to how i wanted it displayed but now im having issue inputting the data into MYSQL instead of inputting data

EXAMPLE DATA

location   beacon     mac
Yellow,      52,    DBECEE45JD77

it enters the data as

'+location+','+beacon+', '+data.mac+'

the location,beacon and mac display correctly in my chrome console but i think i may be missing the link between the javascript function and inserting the results using php but im not sure on which bit

  <script>
<meta charset="utf-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<title>Mosquitto test client</title>
<script type="text/javascript" src="js/mqttws31.js"></script>
<script type="text/javascript">
    // Parameters
    var hostname = "127.0.0.1";
    var port = 1884;
    // Create a client instance
    var client = new Paho.MQTT.Client(hostname, Number(port), "clientId");

    // set callback handlers
    client.onConnectionLost = onConnectionLost;
    client.onMessageArrived = onMessageArrived;

    // connect the client
    client.connect({
        onSuccess: onConnect
    });


    // called when the client connects
    function onConnect() {
        // Once a connection has been made, make a subscription and send a message.
        console.log("onConnect");
        client.subscribe("test");
    }

    // called when the client loses its connection
    function onConnectionLost(responseObject) {
        if (responseObject.errorCode !== 0) {
            console.log("onConnectionLost:" + responseObject.errorMessage);
        }
    }

    // called when a message arrives
    function onMessageArrived(message) {        

var raw = message.payloadString;
message.length = raw.length;
message.raw = raw;
var data = {};

data.major = raw.slice(-10,-4);
data.mac = raw.substring(5,17);
data.hostname = raw.substring(18,30);
data.minor = raw.slice(-8,-4);


var str = data.hostname;
var location = str.replace(`C129E53D0F45`, `PINK`)
            .replace(`FD139CD46385`, `DARKORANGE`)
            .replace(`EB72F2609789`, `DEEPSKYBLUE`)
            .replace(`DBECEE45AE6F`, `RED`)
            .replace(`E47D6D760A7F`, `WHITE`)
            .replace(`C3D7EDB7EF28`, `WHITE`)
            .replace(`E3A3BD636EA3`, `GREEN`)
            .replace(`D6F579FC35C9`, `YELLOW`);




var beacon = parseInt(data.minor ,16);




 var topic2 = ('[value="'+location+','+beacon+', '+data.mac+'"]')
        console.log("Message arrived: topic=" + message.destinationName + ",  message=" + topic2);

 }

</script>
 <?php
 $servername = "127.0.0.1";
 $username = "user";
 $password = "password";
 $dbname = "test";

 // Create connection
 $conn = new mysqli($servername, $username, $password, $dbname);
 // Check connection
 if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
 }

 $sql = "INSERT INTO test.test (`location`,`beacon`, `mac`) VALUES ('+location+','+beacon+', '+data.mac+')";

 if ($conn->query($sql) === TRUE) {
echo "New record created successfully";
 } else {
echo "Error: " . $sql . "<br>" . $conn->error;
 }

 $conn->close();
 ?>

Answer 1

The javascript Paho MQTT client runs in the browser, the PHP will be running on the server at the point the page is loaded, you can't pass messages from one to the other in page since they are running on different machines at different times.

You will need to make a HTTP request from the page back to a separate PHP endpoint to do the insert into the database.