Node-RED & MySQL (Insert , Delete , Update , Select )

 Node-RED & MySQL  (Insert , Delete , Update , Select )

Setting MySQL DataBase 

/Template

<table style="width:100%">

  <tr>

    <th>Index</th> 

    <th>Sensor</th>

    <th>Temperature</th> 

    <th>Humidity</th>

  </tr>

  <tr ng-repeat="x in msg.payload | limitTo:20">

    <td>{{$index}}</td>

    <td align="center">{{msg.payload[$index].SensorName}}</td>

    <td align="center">{{msg.payload[$index].Temperature}}</td> 

    <td align="center">{{msg.payload[$index].Humidity}}</td>

  </tr>

</table>

/Insert

INSERT INTO `sensor` (`SensorName`, `Temperature`, `Humidity`) VALUES ('Sensor1', '25', '78');

INSERT INTO `sensor` (`SensorName`, `Temperature`, `Humidity`) VALUES ('Sensor2', '45', '56');

INSERT INTO `sensor` (`SensorName`, `Temperature`, `Humidity`) VALUES ('Sensor3', '38', '88');

SELECT * FROM sensor

DELETE FROM sensor WHERE Sensor_Id =  6

UPDATE sensor SET Temperature = 0 WHERE Sensor_Id = 7

/ PhpMyAdmin  Setting

[{"id":"5a02d23f.8784ec","type":"tab","label":"流程4","disabled":false,"info":""},{"id":"e8de394c.aad598","type":"mysql","z":"5a02d23f.8784ec","mydb":"73a92d28.0c3eb4","name":"Test","x":370,"y":300,"wires":[["3e0abcb2.4bb474","4edf2cfa.0ee964"]]},{"id":"e3ca7c3f.1e92f","type":"inject","z":"5a02d23f.8784ec","name":"Insert Data1","topic":"INSERT INTO `sensor` (`SensorName`, `Temperature`, `Humidity`) VALUES ('Sensor1', '25', '78');","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":150,"y":180,"wires":[["e8de394c.aad598"]]},{"id":"3e0abcb2.4bb474","type":"debug","z":"5a02d23f.8784ec","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":570,"y":300,"wires":[]},{"id":"15998a8.733e176","type":"inject","z":"5a02d23f.8784ec","name":"Insert Data2","topic":"INSERT INTO `sensor` (`SensorName`, `Temperature`, `Humidity`) VALUES ('Sensor2', '45', '56');","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":150,"y":240,"wires":[["e8de394c.aad598"]]},{"id":"e5c239a7.944e78","type":"inject","z":"5a02d23f.8784ec","name":"Insert Data3","topic":"INSERT INTO `sensor` (`SensorName`, `Temperature`, `Humidity`) VALUES ('Sensor3', '38', '88');","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":150,"y":300,"wires":[["e8de394c.aad598"]]},{"id":"595c5780.0c9f98","type":"inject","z":"5a02d23f.8784ec","name":"Select","topic":"SELECT * FROM sensor","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":150,"y":340,"wires":[["e8de394c.aad598"]]},{"id":"d2cc1836.ca4e98","type":"inject","z":"5a02d23f.8784ec","name":"Delete","topic":"DELETE FROM sensor WHERE Sensor_Id =  6","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":150,"y":380,"wires":[["e8de394c.aad598"]]},{"id":"a64a4ae2.71f728","type":"inject","z":"5a02d23f.8784ec","name":"UpDate Record 7","topic":"UPDATE sensor SET Temperature = 0 WHERE Sensor_Id = 7","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":180,"y":420,"wires":[["e8de394c.aad598"]]},{"id":"4edf2cfa.0ee964","type":"ui_template","z":"5a02d23f.8784ec","group":"6c9116b.b62d4e8","name":"","order":9,"width":0,"height":0,"format":"<table style=\"width:100%\">\n  <tr>\n    <th>Index</th> \n    <th>Sensor</th>\n    <th>Temperature</th> \n    <th>Humidity</th>\n  </tr>\n  <tr ng-repeat=\"x in msg.payload | limitTo:20\">\n    <td>{{$index}}</td>\n    <td align=\"center\">{{msg.payload[$index].SensorName}}</td>\n    <td align=\"center\">{{msg.payload[$index].Temperature}}</td> \n    <td align=\"center\">{{msg.payload[$index].Humidity}}</td>\n  </tr>\n</table>","storeOutMessages":true,"fwdInMessages":true,"resendOnRefresh":true,"templateScope":"local","x":560,"y":360,"wires":[[]]},{"id":"73a92d28.0c3eb4","type":"MySQLdatabase","z":"","name":"MySQL_Test2","host":"localhost","port":"3306","db":"mysql_test2","tz":""},{"id":"6c9116b.b62d4e8","type":"ui_group","z":"","name":"Test","tab":"eeb8e179.a47a4","order":1,"disp":true,"width":"6","collapse":false},{"id":"eeb8e179.a47a4","type":"ui_tab","z":"","name":"MySQL","icon":"dashboard","disabled":false,"hidden":false}]