Import form data into database

Dashboard
1

Hi everyone, I'm new to node red and coding in general. I want to capture form data and import it into my database on mariadb/mysql. But I can't get the syntax correct.

"Error: ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'registration' at line 1"

I'm also struggling to find examples to see where I'm missing the target...

Here is my flow so far:


[{"id":"46233a8e.8bcd24","type":"tab","label":"Login Screen","disabled":false,"info":""},{"id":"419e8217.fbfd3c","type":"tab","label":"Flow 1","disabled":false,"info":""},{"id":"b9ea27cf.5f0e88","type":"ui_tab","z":"","name":"Login","icon":"dashboard","disabled":false,"hidden":false},{"id":"d1985782.d77da8","type":"ui_group","z":"","name":"Login","tab":"b9ea27cf.5f0e88","order":1,"disp":true,"width":"6","collapse":false},{"id":"e2919019.50053","type":"ui_base","theme":{"name":"theme-light","lightTheme":{"default":"#0094CE","baseColor":"#0094CE","baseFont":"-apple-system,BlinkMacSystemFont,Segoe UI,Roboto,Oxygen-Sans,Ubuntu,Cantarell,Helvetica Neue,sans-serif","edited":true,"reset":false},"darkTheme":{"default":"#097479","baseColor":"#097479","baseFont":"-apple-system,BlinkMacSystemFont,Segoe UI,Roboto,Oxygen-Sans,Ubuntu,Cantarell,Helvetica Neue,sans-serif","edited":false},"customTheme":{"name":"Untitled Theme 1","default":"#4B7930","baseColor":"#4B7930","baseFont":"-apple-system,BlinkMacSystemFont,Segoe UI,Roboto,Oxygen-Sans,Ubuntu,Cantarell,Helvetica Neue,sans-serif"},"themeState":{"base-color":{"default":"#0094CE","value":"#0094CE","edited":false},"page-titlebar-backgroundColor":{"value":"#0094CE","edited":false},"page-backgroundColor":{"value":"#fafafa","edited":false},"page-sidebar-backgroundColor":{"value":"#ffffff","edited":false},"group-textColor":{"value":"#1bbfff","edited":false},"group-borderColor":{"value":"#ffffff","edited":false},"group-backgroundColor":{"value":"#ffffff","edited":false},"widget-textColor":{"value":"#111111","edited":false},"widget-backgroundColor":{"value":"#0094ce","edited":false},"widget-borderColor":{"value":"#ffffff","edited":false},"base-font":{"value":"-apple-system,BlinkMacSystemFont,Segoe UI,Roboto,Oxygen-Sans,Ubuntu,Cantarell,Helvetica Neue,sans-serif"}},"angularTheme":{"primary":"indigo","accents":"blue","warn":"red","background":"grey"}},"site":{"name":"Login","hideToolbar":"false","allowSwipe":"true","lockMenu":"false","allowTempTheme":"true","dateFormat":"DD/MM/YYYY","sizes":{"sx":48,"sy":48,"gx":6,"gy":6,"cx":6,"cy":6,"px":0,"py":0}}},{"id":"f7686cf8.19fc9","type":"ui_group","z":"","name":"Registration","tab":"df77a766.73a2a8","order":2,"disp":true,"width":"6","collapse":false},{"id":"df77a766.73a2a8","type":"ui_tab","z":"","name":"Register","icon":"dashboard","disabled":false,"hidden":true},{"id":"982a6755.c150f8","type":"MySQLdatabase","z":"","name":"fleet_management","host":"0.0.0.0","port":"3306","db":"fleet_management","tz":""},{"id":"ce0982dd.9cd1e","type":"ui_button","z":"46233a8e.8bcd24","name":"registerButton","group":"d1985782.d77da8","order":2,"width":0,"height":0,"passthru":false,"label":"Register","tooltip":"Click here to register","color":"","bgcolor":"","icon":"","payload":"1","payloadType":"num","topic":"","x":210,"y":380,"wires":[["4717d738.c44da8","b122435c.a9d7d"]]},{"id":"4717d738.c44da8","type":"debug","z":"46233a8e.8bcd24","name":"","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":440,"y":380,"wires":[]},{"id":"b122435c.a9d7d","type":"ui_ui_control","z":"46233a8e.8bcd24","name":"","events":"change","x":420,"y":420,"wires":[[]]},{"id":"dcb2259c.cef148","type":"ui_form","z":"46233a8e.8bcd24","name":"signIn","label":"Sign In","group":"d1985782.d77da8","order":1,"width":0,"height":0,"options":[{"label":"Employee Number","value":"employeeNumber","type":"number","required":true,"rows":null},{"label":"Password","value":"password","type":"password","required":true,"rows":null}],"formValue":{"employeeNumber":"","password":""},"payload":"","submit":"submit","cancel":"cancel","topic":"","x":180,"y":340,"wires":[[]]},{"id":"48c1adf.a5cd654","type":"mysql","z":"46233a8e.8bcd24","mydb":"982a6755.c150f8","name":"fleet_management","x":460,"y":320,"wires":[[]]},{"id":"4f1a12d3.35c88c","type":"debug","z":"46233a8e.8bcd24","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":660,"y":340,"wires":[]},{"id":"6e2f90d0.b3a46","type":"ui_form","z":"46233a8e.8bcd24","name":"registrationForm","label":"Registration details","group":"f7686cf8.19fc9","order":2,"width":0,"height":0,"options":[{"label":"Full Name","value":"name","type":"text","required":true,"rows":null},{"label":"Employee Number","value":"employeeNumber","type":"text","required":true,"rows":null},{"label":"Contact Number","value":"contactNumber","type":"text","required":true,"rows":null},{"label":"Contact Email","value":"contactEmail","type":"email","required":true,"rows":null},{"label":"Emergency Contact Name","value":"emergencyName","type":"text","required":true,"rows":null},{"label":"Emergency Contact Nuber","value":"emergencyNumber","type":"text","required":true,"rows":null},{"label":"Password","value":"password","type":"password","required":true,"rows":null}],"formValue":{"name":"","employeeNumber":"","contactNumber":"","contactEmail":"","emergencyName":"","emergencyNumber":"","password":""},"payload":"","submit":"submit","cancel":"cancel","topic":"registration","x":200,"y":480,"wires":[["70133aba.71a984","c27eaa8a.ce3e28","a7fded4c.0334"]]},{"id":"70133aba.71a984","type":"debug","z":"46233a8e.8bcd24","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":400,"y":580,"wires":[]},{"id":"c27eaa8a.ce3e28","type":"ui_ui_control","z":"46233a8e.8bcd24","name":"","events":"change","x":400,"y":540,"wires":[[]]},{"id":"a7fded4c.0334","type":"template","z":"46233a8e.8bcd24","name":"Format Data","field":"payload","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"name = (msg.payload.name);\nemployeeNumber = (msg.payload.employeeNumber);\ncontacNumber = (msg.payload.contacNumber);\ncontactEmail = (msg.payload.contactEmail);\nemergencyName = (msg.payload.emergencyName);\nemergencyNumber = (msg.payload.emergencyName);\npassword = (msg.payload.password);\n\nINSERT INTO `users` (`name`,`employeeNumber`,`contactNumber`,`contactEmail`,`emergencyName`,`emergencyNumber`,`password`) \nVALUES ('\" + name + \"','\" + employeeNumber + \"','\" + contactNumber + \"','\" + contactEmail + \"','\" + emergencyName + \"','\" + emergencyNumber + \"','\" + password + \"',)","output":"str","x":410,"y":480,"wires":[["7be3762e.700a88"]]},{"id":"7be3762e.700a88","type":"mysql","z":"46233a8e.8bcd24","mydb":"982a6755.c150f8","name":"","x":610,"y":480,"wires":[["4f1a12d3.35c88c"]]}]

Your input will be appreciated!

2

Hi and welcome to the forum. Could you please wrap your code in back-ticks as per the FAQ otherwise it isn't usable. Thanks.

You will also need to share what db you are using and which node.

3

Apologies, missed that requirement. But it is wrapped in the ticks

I am using mariaDB on PhpMysql on my Raspberry Pi4 and using the form input from node-red-dashboard and feeding into the node-red-node-mysql

5

Look at the info tab of the mysql node. Where does it say to put the query? Now look at your template node - where are you putting the query?

6

I did, and after some tweaking, I managed to sort it out. Thank you for your assistance.

For those wondering what I did here is the flow:

[{"id":"46233a8e.8bcd24","type":"tab","label":"Login Screen","disabled":false,"info":""},{"id":"ce0982dd.9cd1e","type":"ui_button","z":"46233a8e.8bcd24","name":"registerButton","group":"d1985782.d77da8","order":2,"width":0,"height":0,"passthru":false,"label":"Register","tooltip":"Click here to register","color":"","bgcolor":"","icon":"","payload":"1","payloadType":"num","topic":"","x":210,"y":380,"wires":[["4717d738.c44da8","b122435c.a9d7d"]]},{"id":"4717d738.c44da8","type":"debug","z":"46233a8e.8bcd24","name":"","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":440,"y":380,"wires":[]},{"id":"b122435c.a9d7d","type":"ui_ui_control","z":"46233a8e.8bcd24","name":"","events":"change","x":420,"y":420,"wires":[[]]},{"id":"dcb2259c.cef148","type":"ui_form","z":"46233a8e.8bcd24","name":"signIn","label":"Sign In","group":"d1985782.d77da8","order":1,"width":0,"height":0,"options":[{"label":"Employee Number","value":"employeeNumber","type":"number","required":true,"rows":null},{"label":"Password","value":"password","type":"password","required":true,"rows":null}],"formValue":{"employeeNumber":"","password":""},"payload":"","submit":"submit","cancel":"cancel","topic":"","x":180,"y":340,"wires":[[]]},{"id":"48c1adf.a5cd654","type":"mysql","z":"46233a8e.8bcd24","mydb":"982a6755.c150f8","name":"fleet_management","x":460,"y":320,"wires":[[]]},{"id":"4f1a12d3.35c88c","type":"debug","z":"46233a8e.8bcd24","name":"","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":660,"y":340,"wires":[]},{"id":"6e2f90d0.b3a46","type":"ui_form","z":"46233a8e.8bcd24","name":"registrationForm","label":"Registration details","group":"f7686cf8.19fc9","order":2,"width":0,"height":0,"options":[{"label":"Full Name","value":"name","type":"text","required":true,"rows":null},{"label":"Employee Number","value":"employeeNumber","type":"text","required":true,"rows":null},{"label":"Contact Number","value":"contactNumber","type":"text","required":true,"rows":null},{"label":"Contact Email","value":"contactEmail","type":"email","required":true,"rows":null},{"label":"Emergency Contact Name","value":"emergencyName","type":"text","required":true,"rows":null},{"label":"Emergency Contact Nuber","value":"emergencyNumber","type":"text","required":true,"rows":null},{"label":"Password","value":"password","type":"password","required":true,"rows":null}],"formValue":{"name":"","employeeNumber":"","contactNumber":"","contactEmail":"","emergencyName":"","emergencyNumber":"","password":""},"payload":"","submit":"submit","cancel":"cancel","topic":"registration","x":200,"y":480,"wires":[["c27eaa8a.ce3e28","a6904715.9c86b8"]]},{"id":"c27eaa8a.ce3e28","type":"ui_ui_control","z":"46233a8e.8bcd24","name":"","events":"change","x":400,"y":540,"wires":[[]]},{"id":"7be3762e.700a88","type":"mysql","z":"46233a8e.8bcd24","mydb":"982a6755.c150f8","name":"","x":610,"y":480,"wires":[["4f1a12d3.35c88c"]]},{"id":"a6904715.9c86b8","type":"function","z":"46233a8e.8bcd24","name":"","func":"msg.payload.employeeNumber;\nmsg.payload.contactNumber;\nmsg.payload.contactEmail;\nmsg.payload.emergencyName;\nmsg.payload.emergencyNumber;\nmsg.payload.password;\n\n\nmsg.topic=\"INSERT INTO `users` (`name`,`employeeNumber`,`contactNumber`,`contactEmail`,`emergencyName`,`emergencyNumber`,`password`) VALUES (:name, :employeeNumber, :contactNumber, :contactEmail, :emergencyName, :emergencyNumber, :password);\"\nreturn msg;\n","outputs":1,"noerr":0,"x":410,"y":480,"wires":[["7be3762e.700a88"]]},{"id":"d1985782.d77da8","type":"ui_group","z":"","name":"Login","tab":"b9ea27cf.5f0e88","order":1,"disp":true,"width":"6","collapse":false},{"id":"982a6755.c150f8","type":"MySQLdatabase","z":"","name":"fleet_management","host":"0.0.0.0","port":"3306","db":"fleet_management","tz":""},{"id":"f7686cf8.19fc9","type":"ui_group","z":"","name":"Registration","tab":"df77a766.73a2a8","order":2,"disp":true,"width":"6","collapse":false},{"id":"b9ea27cf.5f0e88","type":"ui_tab","z":"","name":"Login","icon":"dashboard","disabled":false,"hidden":false},{"id":"df77a766.73a2a8","type":"ui_tab","z":"","name":"Register","icon":"dashboard","disabled":false,"hidden":false}]
7

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