Node-REDとMySQLを使ってシンプルなウェブサイトを作ってみます。

1. データベースから読み取った情報を表示する

以下のフローをコピペします。

データベースから読み取った情報を表示する

[{"id":"c078ee5a.c19ac","type":"comment","z":"1158278.35414d9","name":"入力文字列を取得","info":"","x":130,"y":60,"wires":[]},{"id":"2a5130b.ccaa75","type":"template","z":"1158278.35414d9","name":"css","field":"payload.css","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"#basic {\n  font-family: meiryo;\n}\n#a1 {\n  background-color:  #98d98e;\n  padding: 20px;\n  height: 100px;\n}\n#b1 {\n  background-color:  #e4de8a;\n  padding: 20px;\n  height: 100px;\n  text-align: center;\n  font-size: 18pt\n}\n#c1 {\n  background-color:  lightskyblue;\n  padding: 20px;\n  height: 100px;\n  text-align: left;\n  font-size: 18pt\n}\n","x":270,"y":100,"wires":[["ca5a9f3.cf7286"]]},{"id":"ca5a9f3.cf7286","type":"template","z":"1158278.35414d9","name":"test18.html 入力用","field":"payload","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"<html>\n  <head>\n    <style type=\"text/css\">\n      {{{payload.css}}}\n    </style>\n  </head>\n  <body style=\"background-color: #EAE5E3\">\n    <div id='basic'>\n      <div id='a1'>\n        <h1>\n          省略語を検索するサンプル\n        </h1>\n      </div>\n    <div id='b1'>\n      これは省略語の元の語を検索・表示させるサンプルです。\n    </div>\n    <div id='c1'>\n      <b>検索する省略語を入力します</b>\n      <form action=\"http://localhost:1880/test19\" method=\"get\">\n        <textarea name=\"msg\" cols=20 rows=1 style=\"font-size:24pt;\">HTTP</textarea>\n      <button type=\"submit\" name=\"submit\" value=\"送信\">\n        <font size=\"2\">表示要求を</font><font size=\"5\" color=\"red\">送信！</font>\n      </button>\n\n    </div>\n  </div>\n</body>\n</html>\n","x":470,"y":100,"wires":[["4e3cf580.d15f2c","c6d4b3c2.a2609"]]},{"id":"64e581bf.b6073","type":"http in","z":"1158278.35414d9","name":"","url":"/test18","method":"get","swaggerDoc":"","x":110,"y":100,"wires":[["2a5130b.ccaa75"]]},{"id":"4e3cf580.d15f2c","type":"http response","z":"1158278.35414d9","name":"","x":650,"y":100,"wires":[]},{"id":"c6d4b3c2.a2609","type":"debug","z":"1158278.35414d9","name":"","active":false,"console":"false","complete":"false","x":660,"y":60,"wires":[]},{"id":"9318d252.cabbb","type":"debug","z":"1158278.35414d9","name":"","active":true,"console":"false","complete":"payload","x":670,"y":260,"wires":[]},{"id":"72897409.0a63cc","type":"function","z":"1158278.35414d9","name":"入力値の取り出し","func":"msg.data1 = msg.payload.msg;\nmsg.topic = \"select name from table01 where c1 = \\u0027\";\nmsg.topic += msg.payload.msg;\nmsg.topic += \"\\u0027;\";\n//msg.topic = \"select name from table01 where c1 = 'http';\";\nreturn msg;","outputs":1,"noerr":0,"x":290,"y":200,"wires":[["fb018013.e2721","faece602.8d4148"]]},{"id":"fb018013.e2721","type":"debug","z":"1158278.35414d9","name":"","active":true,"console":"false","complete":"topic","x":470,"y":260,"wires":[]},{"id":"94248172.f166f8","type":"http in","z":"1158278.35414d9","name":"","url":"/test19","method":"get","swaggerDoc":"","x":110,"y":200,"wires":[["72897409.0a63cc"]]},{"id":"a7479d2d.efbd18","type":"comment","z":"1158278.35414d9","name":"文字列を取り出してみました","info":"","x":160,"y":160,"wires":[]},{"id":"5a92fb8f.a6e4b4","type":"template","z":"1158278.35414d9","name":"test19.html 出力用","field":"payload","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"<html>\n  <head>\n    <style type=\"text/css\">\n      {{{payload.css}}}\n    </style>\n  </head>\n  <body style=\"background-color: #EAE5E3\">\n    <div id='basic'>\n      <div id='a1'>\n        <h1>\n          省略語を検索するサンプル\n        </h1>\n      </div>\n    <div id='b1'>\n      DBからの情報：『<b><font color=\"blue\">{{ data1 }}</font></b>』は、『<b><font color=\"red\">{{ data2 }}</font></b>』を省略したものです。\n    </div>\n    <div id='c1'>\n      <b>検索する省略語を入力します</b>\n      <form action=\"http://localhost:1880/test19\" method=\"get\">\n        <textarea name=\"msg\" cols=20 rows=1 style=\"font-size:24pt;\">HTTP</textarea>\n      <button type=\"submit\" name=\"submit\" value=\"送信\">\n        <font size=\"2\">表示要求を</font><font size=\"5\" color=\"red\">送信！</font>\n      </button>\n\n    </div>\n  </div>\n</body>\n</html>\n","x":910,"y":200,"wires":[["8b064005.c9e498"]]},{"id":"8b064005.c9e498","type":"http response","z":"1158278.35414d9","name":"","x":1070,"y":200,"wires":[]},{"id":"79c7d264.fbd554","type":"template","z":"1158278.35414d9","name":"css","field":"payload.css","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"#basic {\n  font-family: meiryo;\n}\n#a1 {\n  background-color:  #98d98e;\n  padding: 20px;\n  height: 100px;\n}\n#b1 {\n  background-color:  #e4de8a;\n  padding: 20px;\n  height: 100px;\n  text-align: center;\n  font-size: 18pt\n}\n#c1 {\n  background-color:  lightskyblue;\n  padding: 20px;\n  height: 100px;\n  text-align: left;\n  font-size: 18pt\n}\n","x":570,"y":200,"wires":[["f880e5ab.87c53"]]},{"id":"faece602.8d4148","type":"mysql","z":"1158278.35414d9","mydb":"9758a03.7a54be","name":"","x":450,"y":200,"wires":[["79c7d264.fbd554","9318d252.cabbb"]]},{"id":"f880e5ab.87c53","type":"function","z":"1158278.35414d9","name":"返却値をセット","func":"msg.data2 = msg.payload[0].name;\nreturn msg;","outputs":1,"noerr":0,"x":720,"y":200,"wires":[["5a92fb8f.a6e4b4"]]},{"id":"9758a03.7a54be","type":"MySQLdatabase","z":"","host":"127.0.0.1","port":"3306","db":"DB01","tz":""}]

-ブラウザを開き、http://localhost:1880/test18を開きます

httpを検索してみます

2. データベースへ省略語を登録する

下記のフローをコピペします。

データベースへ登録する


[{"id":"39869d89.3e8422","type":"function","z":"dffe860a.3dd5b8","name":"入力値の取り出し","func":"msg.index = msg.payload.index;\nmsg.original = msg.payload.original;\nmsg.abbreviation = msg.payload.abbreviation;\n\nmsg.topic = \"insert into table01 (id, name, c1) values (\";\nmsg.topic += msg.index;\nmsg.topic += \", \\u0027\";\nmsg.topic += msg.original;\nmsg.topic += \"\\u0027, \\u0027\";\nmsg.topic += msg.abbreviation;\nmsg.topic += \"\\u0027);\";\n//msg.topic = \"insert into table01 (id, name, c1) values (2, 'hyper text transfer protocol', 'http')\";\nreturn msg;\n","outputs":1,"noerr":0,"x":320,"y":200,"wires":[["3896dcc.ec47924","5a1866ed.0c56e8"]]},{"id":"6f13aff0.80fa98","type":"http in","z":"dffe860a.3dd5b8","name":"","url":"/test21","method":"get","swaggerDoc":"","x":140,"y":200,"wires":[["39869d89.3e8422"]]},{"id":"ce775936.32481","type":"comment","z":"dffe860a.3dd5b8","name":"DBへ書き込み","info":"","x":140,"y":160,"wires":[]},{"id":"45b632d9.d43a0c","type":"template","z":"dffe860a.3dd5b8","name":"test21.html 出力用","field":"payload","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"<html>\n  <head>\n    <style type=\"text/css\">\n      {{{payload.css}}}\n    </style>\n  </head>\n  <body style=\"background-color: #EAE5E3\">\n    <div id='basic'>\n      <div id='a1'>\n        <h1>\n          省略語を登録するサンプル\n        </h1>\n      </div>\n    <div id='b1'>\n     DBへの登録：登録番号：『<b><font color=\"blue\">{{ index }}</font>』で、『<b><font color=\"blue\">{{ abbreviation }}</font></b>』を、『<b><font color=\"red\">{{ original }}</font></b>』の省略語として登録しました。\n    </div>\n    <div id='c1'>\n      <b>検索する省略語を入力します</b>\n      <form action=\"http://localhost:1880/test19\" method=\"get\">\n        <textarea name=\"msg\" cols=20 rows=1 style=\"font-size:24pt;\">{{ abbreviation }}</textarea>\n      <button type=\"submit\" name=\"submit\" value=\"送信\">\n        <font size=\"2\">表示要求を</font><font size=\"5\" color=\"red\">送信！</font>\n      </button>\n\n    </div>\n  </div>\n</body>\n</html>\n","x":430,"y":280,"wires":[["8cbcd330.a9d37"]]},{"id":"8cbcd330.a9d37","type":"http response","z":"dffe860a.3dd5b8","name":"","x":590,"y":280,"wires":[]},{"id":"b904b249.42ee9","type":"template","z":"dffe860a.3dd5b8","name":"css","field":"payload.css","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"#basic {\n  font-family: meiryo;\n}\n#a1 {\n  background-color:  #98d98e;\n  padding: 20px;\n  height: 100px;\n}\n#b1 {\n  background-color:  #e4de8a;\n  padding: 20px;\n  height: 100px;\n  text-align: center;\n  font-size: 18pt\n}\n#c1 {\n  background-color:  lightskyblue;\n  padding: 20px;\n  height: 100px;\n  text-align: left;\n  font-size: 18pt\n}\n","x":270,"y":280,"wires":[["45b632d9.d43a0c"]]},{"id":"84a55ce3.5814a8","type":"comment","z":"dffe860a.3dd5b8","name":"DBへ書き込むデータを入力","info":"","x":190,"y":60,"wires":[]},{"id":"aebab1f3.bca3e8","type":"template","z":"dffe860a.3dd5b8","name":"css","field":"payload.css","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"#basic {\n  font-family: meiryo;\n}\n#a1 {\n  background-color:  #98d98e;\n  padding: 20px;\n  height: 100px;\n}\n#b1 {\n  background-color:  #e4de8a;\n  padding: 20px;\n  height: 100px;\n  text-align: center;\n  font-size: 18pt\n}\n#c1 {\n  background-color:  lightskyblue;\n  padding: 20px;\n  height: 300px;\n  text-align: left;\n  font-size: 18pt\n}\n","x":300,"y":100,"wires":[["9a3b66dc.45cfb"]]},{"id":"9a3b66dc.45cfb","type":"template","z":"dffe860a.3dd5b8","name":"test20.html 入力用","field":"payload","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"<html>\n  <head>\n    <style type=\"text/css\">\n      {{{payload.css}}}\n    </style>\n  </head>\n  <body style=\"background-color: #EAE5E3\">\n    <div id='basic'>\n      <div id='a1'>\n        <h1>\n          省略語を登録するサンプル\n        </h1>\n      </div>\n    <div id='b1'>\n      これは省略語と元の語をデータベースへ登録するサンプルです。\n    </div>\n    <div id='c1'>\n      <b>登録する省略語と元の語を入力します</b>\n      <form action=\"http://localhost:1880/test21\" method=\"get\">\n        <textarea name=\"index\" cols=5 rows=1 style=\"font-size:24pt;\">2</textarea><br><br>\n        <textarea name=\"abbreviation\" cols=20 rows=1 style=\"font-size:24pt;\">HTML</textarea><br><br>\n        <textarea name=\"original\" cols=80 rows=1 style=\"font-size:24pt;\">Hyper Text Markup Language</textarea><br><br>\n      <button type=\"submit\" name=\"submit\" value=\"送信\">\n        <font size=\"2\">表示要求を</font><font size=\"5\" color=\"red\">送信！</font>\n      </button>\n\n    </div>\n  </div>\n</body>\n</html>\n","x":500,"y":100,"wires":[["f4f748a3.13da88","aec44345.328338"]]},{"id":"e43d62c8.437548","type":"http in","z":"dffe860a.3dd5b8","name":"","url":"/test20","method":"get","swaggerDoc":"","x":140,"y":100,"wires":[["aebab1f3.bca3e8"]]},{"id":"f4f748a3.13da88","type":"http response","z":"dffe860a.3dd5b8","name":"","x":680,"y":100,"wires":[]},{"id":"aec44345.328338","type":"debug","z":"dffe860a.3dd5b8","name":"","active":true,"console":"false","complete":"false","x":690,"y":60,"wires":[]},{"id":"3896dcc.ec47924","type":"debug","z":"dffe860a.3dd5b8","name":"","active":true,"console":"false","complete":"topic","x":530,"y":200,"wires":[]},{"id":"5a1866ed.0c56e8","type":"mysql","z":"dffe860a.3dd5b8","mydb":"9758a03.7a54be","name":"","x":140,"y":280,"wires":[["b904b249.42ee9"]]},{"id":"9758a03.7a54be","type":"MySQLdatabase","z":"","host":"127.0.0.1","port":"3306","db":"DB01","tz":""}]

ブラウザでlocalhost:1880/test20を開きます
新たに登録したい省略語を指定し、データベースへ登録してみます

3. データベースから省略語を削除する

下記のフローをコピペします。

データベースから削除する


[{"id":"a7f83d6d.c8d17","type":"function","z":"36386679.da703a","name":"入力値の取り出し","func":"msg.del = msg.payload.delete;\n\nmsg.topic = \"delete from table01 where c1 = \\u0027\";\nmsg.topic += msg.del;\nmsg.topic += \"\\u0027\";\n//msg.topic = \"delete from table01 where c1 = 'NHK'\";\nreturn msg;\n","outputs":1,"noerr":0,"x":320,"y":180,"wires":[["a8968263.e88c68","26ff6e64.73540a"]]},{"id":"5ad029b7.4780d","type":"http in","z":"36386679.da703a","name":"","url":"/test23","method":"get","swaggerDoc":"","x":140,"y":180,"wires":[["a7f83d6d.c8d17"]]},{"id":"39cd45a7.da09da","type":"comment","z":"36386679.da703a","name":"DBから削除","info":"","x":140,"y":140,"wires":[]},{"id":"fce789e2.284f2","type":"template","z":"36386679.da703a","name":"test23.html 出力用","field":"payload","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"<html>\n  <head>\n    <style type=\"text/css\">\n      {{{payload.css}}}\n    </style>\n  </head>\n  <body style=\"background-color: #EAE5E3\">\n    <div id='basic'>\n      <div id='a1'>\n        <h1>\n          省略語を削除するサンプル\n        </h1>\n      </div>\n    <div id='b1'>\n     DBから削除：『<b><font color=\"blue\">{{ del }}</font></b>』を削除しました。\n    </div>\n    <div id='c1'>\n      <b>検索する省略語を入力します</b>\n      <form action=\"http://localhost:1880/test19\" method=\"get\">\n        <textarea name=\"msg\" cols=20 rows=1 style=\"font-size:24pt;\">{{ del }}</textarea>\n      <button type=\"submit\" name=\"submit\" value=\"送信\">\n        <font size=\"2\">表示要求を</font><font size=\"5\" color=\"red\">送信！</font>\n      </button>\n\n    </div>\n  </div>\n</body>\n</html>\n","x":450,"y":240,"wires":[["e5c0cdee.12b2c"]]},{"id":"e5c0cdee.12b2c","type":"http response","z":"36386679.da703a","name":"","x":630,"y":240,"wires":[]},{"id":"db4adaf4.d450b","type":"template","z":"36386679.da703a","name":"css","field":"payload.css","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"#basic {\n  font-family: meiryo;\n}\n#a1 {\n  background-color:  #98d98e;\n  padding: 20px;\n  height: 100px;\n}\n#b1 {\n  background-color:  #e4de8a;\n  padding: 20px;\n  height: 100px;\n  text-align: center;\n  font-size: 18pt\n}\n#c1 {\n  background-color:  lightskyblue;\n  padding: 20px;\n  height: 100px;\n  text-align: left;\n  font-size: 18pt\n}\n","x":270,"y":240,"wires":[["fce789e2.284f2"]]},{"id":"ff293090.8ae93","type":"comment","z":"36386679.da703a","name":"DBから削除するデータを入力","info":"","x":190,"y":40,"wires":[]},{"id":"aaef9f72.b7c988","type":"template","z":"36386679.da703a","name":"css","field":"payload.css","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"#basic {\n  font-family: meiryo;\n}\n#a1 {\n  background-color:  #98d98e;\n  padding: 20px;\n  height: 100px;\n}\n#b1 {\n  background-color:  #e4de8a;\n  padding: 20px;\n  height: 100px;\n  text-align: center;\n  font-size: 18pt\n}\n#c1 {\n  background-color:  lightskyblue;\n  padding: 20px;\n  height: 300px;\n  text-align: left;\n  font-size: 18pt\n}\n","x":300,"y":80,"wires":[["13a4ce89.7296f1"]]},{"id":"13a4ce89.7296f1","type":"template","z":"36386679.da703a","name":"test22.html 入力用","field":"payload","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"<html>\n  <head>\n    <style type=\"text/css\">\n      {{{payload.css}}}\n    </style>\n  </head>\n  <body style=\"background-color: #EAE5E3\">\n    <div id='basic'>\n      <div id='a1'>\n        <h1>\n          省略語を削除するサンプル\n        </h1>\n      </div>\n    <div id='b1'>\n      これは省略語と元の語をデータベースから削除するサンプルです。\n    </div>\n    <div id='c1'>\n      <b>削除する省略語を入力します</b>\n      <form action=\"http://localhost:1880/test23\" method=\"get\">\n        <textarea name=\"delete\" cols=20 rows=1 style=\"font-size:24pt;\">HTML</textarea><br><br>\n      <button type=\"submit\" name=\"submit\" value=\"送信\">\n        <font size=\"2\">表示要求を</font><font size=\"5\" color=\"red\">送信！</font>\n      </button>\n\n    </div>\n  </div>\n</body>\n</html>\n","x":500,"y":80,"wires":[["60b6f2b2.57b9fc","14feaae0.ba24fd"]]},{"id":"b34cf2b6.c3da88","type":"http in","z":"36386679.da703a","name":"","url":"/test22","method":"get","swaggerDoc":"","x":140,"y":80,"wires":[["aaef9f72.b7c988"]]},{"id":"60b6f2b2.57b9fc","type":"http response","z":"36386679.da703a","name":"","x":680,"y":80,"wires":[]},{"id":"14feaae0.ba24fd","type":"debug","z":"36386679.da703a","name":"","active":false,"console":"false","complete":"false","x":690,"y":40,"wires":[]},{"id":"a8968263.e88c68","type":"debug","z":"36386679.da703a","name":"","active":true,"console":"false","complete":"topic","x":510,"y":180,"wires":[]},{"id":"26ff6e64.73540a","type":"mysql","z":"36386679.da703a","mydb":"9758a03.7a54be","name":"","x":130,"y":240,"wires":[["db4adaf4.d450b"]]},{"id":"9758a03.7a54be","type":"MySQLdatabase","z":"","host":"127.0.0.1","port":"3306","db":"DB01","tz":""}]

ブラウザでhttp://localhost:1880/test22を開き、削除したい省略語を指定します
データベースから該当の省略語に関するエントリーが削除されたことを確認します