Gridview with CRUD Options (MySQL)

Hi,

I'm creating a grid view with CRUD options using MySQL. The following link consists of the example:

https://flows.nodered.org/flow/e32f2b942bce77ef6079c0642b93c036

I had tried a part of it here and I'm unable to display the data. In short, the page went blank. Debug node showing all the data in JSON format.

image

The following is the flow:

[{"id":"b571d0de.e819e","type":"template","z":"2cc812cc.02d06e","name":"Web Template","field":"payload","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"<!DOCTYPE html>\n<html lang=\"en\">\n\n<head>\n    <title>Licence Plate</title>\n    <meta charset=\"utf-8\">\n    <meta name=\"viewport\" content=\"width=device-width, initial-scale=1\">\n    <link rel=\"stylesheet\" href=\"https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css\">\n    <link rel=\"stylesheet\" href=\"https://cdnjs.cloudflare.com/ajax/libs/bootstrap-timepicker/0.5.2/css/bootstrap-timepicker.min.css\" />\n    <script src=\"https://ajax.googleapis.com/ajax/libs/jquery/3.1.1/jquery.min.js\"></script>\n    <script src=\"https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js\"></script>\n    <link type=\"text/css\" rel=\"stylesheet\" href=\"https://cdnjs.cloudflare.com/ajax/libs/jsgrid/1.5.3/jsgrid.min.css\" />\n    <link type=\"text/css\" rel=\"stylesheet\" href=\"https://cdnjs.cloudflare.com/ajax/libs/jsgrid/1.5.3/jsgrid-theme.min.css\" />\n    <script type=\"text/javascript\" src=\"https://cdnjs.cloudflare.com/ajax/libs/jsgrid/1.5.3/jsgrid.min.js\"></script>\n\n    <script type=\"text/javascript\">\n        $(function() {\n                    var db = {\n                        {#\n                            payload\n                        }\n                    } {\n                        {\n                            {.\n                            }\n                        }\n                    } {\n                    {\n                        /payload}};\n                        $(\"#jsgrid\").jsGrid({\n                            width: \"100%\",\n                            inserting: true,\n                            editing: true,\n                            sorting: true,\n                            paging: true,\n\n                            data: db,\n\n                            fields: [{\n                                title: \"Name\",\n                                name: {{msg.payload[$index].name}},\n                                type: \"text\",\n                                width: 50\n                            }, {\n                                title: \"Last Name\",\n                                name: {{msg.payload[$index].lastname}},\n                                type: \"text\",\n                                width: 50\n                            }, {\n                                title: \"Code\",\n                                name: {{msg.payload[$index].code}},\n                                type: \"text\",\n                                width: 50\n                            }, {\n                                type: \"control\"\n                            }],\n\n                            controller: {\n                                insertItem: function(item) {\n                                    return $.ajax({\n                                        type: \"POST\",\n                                        url: \"/insert\",\n                                        data: item\n                                    });\n                                },\n                                updateItem: function(item) {\n                                    return $.ajax({\n                                        type: \"PUT\",\n                                        url: \"/update\",\n                                        data: item\n                                    });\n                                },\n                                deleteItem: function(item) {\n                                    return $.ajax({\n                                        type: \"DELETE\",\n                                        url: \"/delete\",\n                                        data: item\n                                    });\n                                }\n                            }\n                        });\n                    });\n    </script>\n</head>\n\n<body class=\"container\">\n    <section class=\"row\">\n\n        <div class=\"col-md-6\"></div>\n        <div class=\"col-md-6\" id=\"jsgrid\">\n        </div>\n    </section>\n</body>\n\n</html>","x":785,"y":75,"wires":[["3d9d2c67.cf5a24"]]},{"id":"e2a598a1.414398","type":"http in","z":"2cc812cc.02d06e","name":"","url":"/demo","method":"get","upload":false,"swaggerDoc":"","x":105,"y":75,"wires":[["c272c04f.820d8"]]},{"id":"3d9d2c67.cf5a24","type":"http response","z":"2cc812cc.02d06e","name":"","statusCode":"","headers":{},"x":965,"y":75,"wires":[]},{"id":"e519b59d.7593b8","type":"mysql","z":"2cc812cc.02d06e","mydb":"24d38c58.3fc104","name":"database","x":450,"y":75,"wires":[["eabacfef.a471f"]]},{"id":"a439e24.5152d2","type":"debug","z":"2cc812cc.02d06e","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":775,"y":150,"wires":[]},{"id":"c272c04f.820d8","type":"function","z":"2cc812cc.02d06e","name":"select-query","func":"msg.topic=\"SELECT name,lastname,code FROM test\";\nreturn msg;","outputs":1,"noerr":0,"x":280,"y":75,"wires":[["e519b59d.7593b8"]]},{"id":"eabacfef.a471f","type":"json","z":"2cc812cc.02d06e","name":"","property":"payload","action":"","pretty":false,"x":605,"y":75,"wires":[["b571d0de.e819e","a439e24.5152d2"]]},{"id":"24d38c58.3fc104","type":"MySQLdatabase","z":"","host":"192.168.3.10","port":"3306","db":"ram_test","tz":""}]

Thanks for helping.

1 Like

I suggest you start by looking at the page using chrome and viewing the console to see if there are any errors.
You might add some debug nodes and a catch node to your flow

All mainstream browsers have good developer views. F12 is usually the quick way to activate them.

I would also add another debug node onto the output of the web template node, that way you can see what (if anything) it is producing.

That is a nice little GridView example -- I just had to make a few small tweaks to the Web Template node to get it to display properly:

<!DOCTYPE html>
<html lang="en">

<head>
    <title>License Plate</title>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css">
    <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/bootstrap-timepicker/0.5.2/css/bootstrap-timepicker.min.css" />
    <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.1.1/jquery.min.js"></script>
    <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js"></script>
    <link type="text/css" rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/jsgrid/1.5.3/jsgrid.min.css" />
    <link type="text/css" rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/jsgrid/1.5.3/jsgrid-theme.min.css" />
    <script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/jsgrid/1.5.3/jsgrid.min.js"></script>

    <script type="text/javascript">
        $(function() {
            var db = {{{payload}}};
            $("#jsgrid").jsGrid({
                width: "100%",
                inserting: true,
                editing: true,
                sorting: true,
                paging: true,

                data: db,

                fields: [{
                    title: "Name",
                    name: "name",
                    type: "text",
                    width: 50
                }, {
                    title: "Last Name",
                    name: "lastname",
                    type: "text",
                    width: 50
                }, {
                    title: "Code",
                    name: "code",
                    type: "text",
                    width: 50
                }, {
                    type: "control"
                }],

                controller: {
                    insertItem: function(item) {
                        return $.ajax({
                            type: "POST",
                            url: "/insert",
                            data: item
                        });
                    },
                    updateItem: function(item) {
                        return $.ajax({
                            type: "PUT",
                            url: "/update",
                            data: item
                        });
                    },
                    deleteItem: function(item) {
                        return $.ajax({
                            type: "DELETE",
                            url: "/delete",
                            data: item
                        });
                    }
                }
            });
        });
    </script>
</head>

<body class="container">
    <section class="row">
        <div class="col-md-6"></div>
        <div class="col-md-6" id="jsgrid">
        </div>
    </section>
</body>

</html>

I'm not sure what is supposed to happen when trying to save changes to the data, but you probably already have the /update endpoint somewhere else in your flows. When you get it all working, please update and document it in the flows library -- I know others have searched for similar solutions in the past.

5 Likes

This is what I got:

I would like to do the same for insert, update and delete. The following is the flow:

[{"id":"8e691a3.4be01e8","type":"http in","z":"2cc812cc.02d06e","name":"","url":"/update","method":"put","swaggerDoc":"","x":250,"y":645,"wires":[["9310b6dd.4b10d8"]]},{"id":"30c87678.d8c56a","type":"mongodb2 in","z":"2cc812cc.02d06e","service":"_ext_","configNode":"f6c122ad.621c98","name":"","collection":"LicencePlate","operation":"update","x":1026.9998779296875,"y":645.13330078125,"wires":[["b9ecdd0e.5d6a7"]]},{"id":"cc4faa2e.a5d5a8","type":"http response","z":"2cc812cc.02d06e","name":"","x":1495.9998779296875,"y":643.7332763671875,"wires":[]},{"id":"7468298e.ee1a88","type":"function","z":"2cc812cc.02d06e","name":"update request","func":"/*\nmsg.result correspond à l'objet mis à jour,\nle delete permet de supprimer la propriété\n_id de l'objet, en effet l'_id est géré directement\npar le noued OjectId\npour la requète update  \non doit fournir un tableau \n[\n<query>,\n<valeur mises à jour>\n]\n\nici ce sera de la forme:\n[\n    {_id:ObjectId(\"xxx\")},\n    {\"nom\": \"toto\", \"prenom\": \"tutu\", \n        \"immatriculation\": \"AB123CD\", \n        \"heure\": null, \"minute\": null\n    }\n]\n*/\nmsg.result=msg.req.body;\ndelete msg.req.body._id;\nmsg.payload=[\n   msg.payload,\n   msg.req.body\n    ];\nreturn msg;\n\n","outputs":"1","noerr":0,"x":786.7666015625,"y":645.3665771484375,"wires":[["30c87678.d8c56a"]]},{"id":"9310b6dd.4b10d8","type":"function","z":"2cc812cc.02d06e","name":"extract _id","func":"msg._id=msg.payload._id;\nreturn msg;","outputs":1,"noerr":0,"x":437.88336181640625,"y":645.7999267578125,"wires":[["3bb45bbd.66e854"]]},{"id":"3bb45bbd.66e854","type":"objectid","z":"2cc812cc.02d06e","name":"","x":591.8833312988281,"y":645.4666748046875,"wires":[["7468298e.ee1a88"]]},{"id":"b9ecdd0e.5d6a7","type":"function","z":"2cc812cc.02d06e","name":"callback response","func":"/*\npour que la jsGrid soit rafraichie, \nil faut lui envoyer l'objet qui a été mis à jour\n*/\nmsg.payload=msg.result;\nreturn msg;","outputs":1,"noerr":0,"x":1294.7666015625,"y":644.5166015625,"wires":[["cc4faa2e.a5d5a8"]]},{"id":"9297af6f.1e84c","type":"mongodb2 in","z":"2cc812cc.02d06e","service":"_ext_","configNode":"f6c122ad.621c98","name":"","collection":"LicencePlate","operation":"insert","x":1033.8831787109375,"y":741.4332275390625,"wires":[["2b09fc00.531954"]]},{"id":"11fbcda8.460f62","type":"http response","z":"2cc812cc.02d06e","name":"","x":1504.8831787109375,"y":740.033203125,"wires":[]},{"id":"850ba02f.20311","type":"http in","z":"2cc812cc.02d06e","name":"","url":"/insert","method":"post","swaggerDoc":"","x":249.76666259765625,"y":735.25,"wires":[["9297af6f.1e84c"]]},{"id":"90419dc8.f06f4","type":"http in","z":"2cc812cc.02d06e","name":"","url":"/delete","method":"delete","swaggerDoc":"","x":262.76666259765625,"y":832.2499694824219,"wires":[["cb8ba207.ca182"]]},{"id":"589a277d.fdc278","type":"mongodb2 in","z":"2cc812cc.02d06e","service":"_ext_","configNode":"f6c122ad.621c98","name":"","collection":"LicencePlate","operation":"deleteOne","x":1044.88330078125,"y":831.2998962402344,"wires":[["ef000c12.8c265"]]},{"id":"cb8ba207.ca182","type":"function","z":"2cc812cc.02d06e","name":"extract _id","func":"msg._id=msg.payload._id;\nreturn msg;","outputs":1,"noerr":0,"x":459.8833312988281,"y":832.2998962402344,"wires":[["c800466a.fa33d8"]]},{"id":"c800466a.fa33d8","type":"objectid","z":"2cc812cc.02d06e","name":"","x":631.88330078125,"y":831.9666442871094,"wires":[["589a277d.fdc278"]]},{"id":"ef000c12.8c265","type":"http response","z":"2cc812cc.02d06e","name":"","x":1504.7666015625,"y":826.8332214355469,"wires":[]},{"id":"2b09fc00.531954","type":"function","z":"2cc812cc.02d06e","name":"callback response","func":"/*\npour que la jsGrid soit rafraichie, \nil faut lui envoyer l'objet qui a été mis à jour\n*/\nmsg.payload=msg.req.body;\nreturn msg;","outputs":1,"noerr":0,"x":1301.88330078125,"y":741.2999267578125,"wires":[["11fbcda8.460f62"]]}]

Thanks for helping.

Your HTML output from the template node is too long to see all of it. You should save it to a file or increase the size of the debug output temporarily (in settings.js) or maybe temporarily delete all the < head > parts so that you can actually see the grid.

Hi,

I somehow managed to insert, update and delete the data from the Gridview, but I'm stuck at search function.

Hope to hear from you soon. Thanks for helping.

Hi,

Currently I'm stuck at search function. I also would like to insert and update data shown in the following link:
http://js-grid.com/demos/data-manipulation.html

How to do this in Node-RED?

Thanks for helping.

Hi all,

Is anyone there to help? Thanks for helping.

Providing your flow would be helpful

Hi,

I'm now able to perform CRUD operations. However, I'm unable to display the data and perform CRUD operations with dropdown fields.

I'm only seeing a blank page with "Machine Management" heading once I changed two fields type from "text" to "select". If I set the fields type to "text" back and remove other dropdown properties, everything is working perfectly.

The following is the flow:

Here is the code:

[{"id":"2cebe15.5254a1e","type":"http in","z":"3f384dee.a0ca22","name":"","url":"/app","method":"get","upload":false,"swaggerDoc":"","x":105,"y":60,"wires":[["127b16b9.f62b19"]]},{"id":"127b16b9.f62b19","type":"function","z":"3f384dee.a0ca22","name":"select-query","func":"//var msg1={};\n//msg.topic=\"SELECT id AS mac_id, name AS mac_name FROM machine_type\";\n//msg1.topic=\"SELECT id AS loc_id, location AS loc_name FROM location\";\nif (msg.payload.name===\"\" && msg.payload.description===\"\" && msg.payload.location===\"\" && msg.payload.type===\"\" && msg.payload.ip_address===\"\" && msg.payload.ip_port===\"\" && msg.payload.process_code===\"\")\n{\n    msg.topic=\"SELECT machine2.id AS id, machine2.name AS name, machine2.description AS description, location.location AS location, machine_type.name AS type, users.name AS owner_id, machine2.ip_address AS ip_address, machine2.ip_port AS ip_port, machine2.process_code AS process_code, machine2.enabled AS enabled FROM machine2 INNER JOIN machine_type ON machine_type.id = machine2.type INNER JOIN location ON location.id = machine2.location INNER JOIN users ON users.id = machine2.owner_id ORDER BY machine2.id DESC\";\n}\nelse\n{\n    msg.topic=\"SELECT machine2.id AS id, machine2.name AS name, machine2.description AS description, location.location AS location, machine_type.name AS type, users.name AS owner_id, machine2.ip_address AS ip_address, machine2.ip_port AS ip_port, machine2.process_code AS process_code, machine2.enabled AS enabled FROM machine2 INNER JOIN machine_type ON machine_type.id = machine2.type INNER JOIN location ON location.id = machine2.location INNER JOIN users ON users.id = machine2.owner_id WHERE machine2.name='\" + msg.payload.name + \"' OR machine2.description='\" + msg.payload.description + \"' OR location.id='\" + msg.payload.location + \"' OR machine_type.id='\" + msg.payload.type + \"' OR machine2.ip_address='\" + msg.payload.ip_address + \"' OR machine2.ip_port='\" + msg.payload.ip_port + \"' OR machine2.process_code='\" + msg.payload.process_code + \"' ORDER BY machine2.id DESC\";\n}\nreturn msg;","outputs":1,"noerr":0,"x":325,"y":60,"wires":[["98180f70.264e2"]]},{"id":"bc7be8bb.430798","type":"json","z":"3f384dee.a0ca22","name":"","property":"payload","action":"","pretty":false,"x":650,"y":60,"wires":[["edc26dc.5f20e9"]]},{"id":"edc26dc.5f20e9","type":"template","z":"3f384dee.a0ca22","name":"web-template","field":"payload","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"<!DOCTYPE html>\n<html lang=\"en\">\n<head>\n    <title>Machine Management</title>\n    <meta charset=\"utf-8\">\n    <meta name=\"viewport\" content=\"width=device-width, initial-scale=1\">\n    <link rel=\"stylesheet\" href=\"https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css\">\n    <link rel=\"stylesheet\" href=\"https://cdnjs.cloudflare.com/ajax/libs/bootstrap-timepicker/0.5.2/css/bootstrap-timepicker.min.css\" />\n    <script src=\"https://ajax.googleapis.com/ajax/libs/jquery/3.1.1/jquery.min.js\"></script>\n    <script src=\"https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js\"></script>\n    <link type=\"text/css\" rel=\"stylesheet\" href=\"https://cdnjs.cloudflare.com/ajax/libs/jsgrid/1.5.3/jsgrid.min.css\" />\n    <link type=\"text/css\" rel=\"stylesheet\" href=\"https://cdnjs.cloudflare.com/ajax/libs/jsgrid/1.5.3/jsgrid-theme.min.css\" />\n    <script type=\"text/javascript\" src=\"https://cdnjs.cloudflare.com/ajax/libs/jsgrid/1.5.3/jsgrid.min.js\"></script>\n    <style>\n        h1 { text-align: center }\n    </style>\n    <script type=\"text/javascript\">\n        $(function() {\n            var db = {{{payload}}};\n            $(\"#jsgrid\").jsGrid({\n                width: \"100%\",\n\n                filtering: true,\n                inserting: true,\n                editing: true,\n                sorting: true,\n                paging: true,\n                autoload: true,\n\n                deleteConfirm: function(item) {\n                    return \"Machine \" + item.name + \" information will be deleted. Are you sure?\";\n                },\n                \n                pageSize: 30,\n\n                /*\n                rowClick: function(args) {\n                    showDetailsDialog(\"Edit\", args.item);\n                },\n                */\n\n                data: db,\n\n                fields: [{\n                    type: \"control\"\n                }, {\n                    title: \"Enabled\",\n                    name: \"enabled\",\n                    type: \"checkbox\",\n                    width: 60,\n                    filtering: false\n                }, {\n                    title: \"ID\",\n                    name: \"id\",\n                    type: \"number\",\n                    filtering: false,\n                    inserting: false,\n                    editing: false,\n                    width: 50,\n                    align: \"left\"\n                }, {\n                    title: \"Code\",\n                    name: \"name\",\n                    type: \"text\",\n                    width: 70\n                }, {\n                    title: \"Description\",\n                    name: \"description\",\n                    type: \"text\"\n                }, {\n                    title: \"Location\",\n                    name: \"location\",\n                    //type: \"text\",\n                    type: \"select\",\n                    items: db.location,\n                    valueField: \"id\",\n                    textField: \"location\",\n                    width: 75\n                }, {\n                    title: \"Type\",\n                    name: \"type\",\n                    //type: \"text\",\n                    type: \"select\",\n                    items: db.machine_type,\n                    valueField: \"id\",\n                    textField: \"name\",\n                    width: 75\n                }, {\n                    title: \"Owner\",\n                    name: \"owner_id\",\n                    type: \"text\",\n                    filtering: false,\n                    inserting: false,\n                    editing: false\n                }, {\n                    title: \"IP Address\",\n                    name: \"ip_address\",\n                    type: \"text\",\n                    width: 70\n                }, {\n                    title: \"IP Port\",\n                    name: \"ip_port\",\n                    type: \"text\",\n                    width: 50\n                }, {\n                    title: \"Process Code\",\n                    name: \"process_code\",\n                    type: \"text\",\n                    width: 60\n                }],\n\n                controller: {\n                    loadData: function(filter) {\n                        return $.ajax({\n                            type: \"GET\",\n                            url: \"/search\",\n                            data: filter\n                        });\n                    },\n                    insertItem: function(item) {\n                        return $.ajax({\n                            type: \"POST\",\n                            url: \"/insert\",\n                            data: item\n                        });\n                    },\n                    updateItem: function(item) {\n                        return $.ajax({\n                            type: \"PUT\",\n                            url: \"/update\",\n                            data: item\n                        });\n                    },\n                    deleteItem: function(item) {\n                        return $.ajax({\n                            type: \"DELETE\",\n                            url: \"/delete\",\n                            data: item\n                        });\n                    }\n                }\n            });\n        });\n    </script>\n</head>\n\n<body class=\"container\">\n    <section class=\"row\">\n        <h1>Machine Management</h1>\n        <div class=\"col-lg-12\"></div>\n        <div class=\"col-lg-12\" id=\"jsgrid\">\n        </div>\n    </section>\n</body>\n</html>","x":830,"y":60,"wires":[["63708ec.2e7617"]]},{"id":"63708ec.2e7617","type":"http response","z":"3f384dee.a0ca22","name":"","statusCode":"","headers":{},"x":1010,"y":60,"wires":[]},{"id":"d81d4a4d.77f1c8","type":"http in","z":"3f384dee.a0ca22","name":"","url":"/update","method":"put","upload":false,"swaggerDoc":"","x":115,"y":210,"wires":[["70ecf663.6b2108"]]},{"id":"bd5710a6.0b1e5","type":"function","z":"3f384dee.a0ca22","name":"update-response","func":"msg.payload=msg.result;\nreturn msg;","outputs":1,"noerr":0,"x":690,"y":210,"wires":[["dd875985.366d78"]]},{"id":"4fe0652c.1beb6c","type":"http in","z":"3f384dee.a0ca22","name":"","url":"/insert","method":"post","upload":false,"swaggerDoc":"","x":115,"y":135,"wires":[["4228be9a.47891"]]},{"id":"1d3429af.0d5b66","type":"http in","z":"3f384dee.a0ca22","name":"","url":"/delete","method":"delete","upload":false,"swaggerDoc":"","x":125,"y":285,"wires":[["88d03b1e.8f8408"]]},{"id":"102f6c93.a2f413","type":"function","z":"3f384dee.a0ca22","name":"insert-response","func":"msg.payload=msg.result;\nreturn msg;","outputs":1,"noerr":0,"x":680,"y":135,"wires":[["e09d5fa1.472d"]]},{"id":"4228be9a.47891","type":"function","z":"3f384dee.a0ca22","name":"insert-query","func":"if (msg.payload.enabled===\"true\")\n{\n    msg.topic=\"INSERT INTO machine2 (enabled,id,name,description,ip_address,ip_port,process_code) VALUES ('1','\" + msg.payload.name + \"','\" + msg.payload.description + \"','\" + msg.payload.ip_address + \"','\" + msg.payload.ip_port + \"','\" + msg.payload.process_code + \"')\";\n}\nelse\n{\n    msg.topic=\"INSERT INTO machine2 (enabled,id,name,description,ip_address,ip_port,process_code) VALUES ('0','\" + msg.payload.name + \"','\" + msg.payload.description + \"','\" + msg.payload.ip_address + \"','\" + msg.payload.ip_port + \"','\" + msg.payload.process_code + \"')\";   \n}\nreturn msg;","outputs":1,"noerr":0,"x":325,"y":135,"wires":[["2cd887e8.423148"]]},{"id":"70ecf663.6b2108","type":"function","z":"3f384dee.a0ca22","name":"update-query","func":"if (msg.payload.enabled===\"true\")\n{\n    msg.topic=\"UPDATE machine2 SET enabled='1', name='\" + msg.payload.name + \"', description='\" + msg.payload.description + \"', ip_address='\" + msg.payload.ip_address + \"', ip_port='\" + msg.payload.ip_port + \"', process_code='\" + msg.payload.process_code + \"' WHERE id='\" + msg.payload.id + \"'\";\n}\nelse\n{\n    msg.topic=\"UPDATE machine2 SET enabled='0', name='\" + msg.payload.name + \"', description='\" + msg.payload.description + \"', ip_address='\" + msg.payload.ip_address + \"', ip_port='\" + msg.payload.ip_port + \"', process_code='\" + msg.payload.process_code + \"' WHERE id='\" + msg.payload.id + \"'\";    \n}\nreturn msg;","outputs":1,"noerr":0,"x":325,"y":210,"wires":[["65b8e1cd.c37d8"]]},{"id":"6aa64c7d.45aa44","type":"function","z":"3f384dee.a0ca22","name":"delete-response","func":"msg.payload=msg.result;\nreturn msg;","outputs":1,"noerr":0,"x":680,"y":285,"wires":[["ea5e155b.decbc8"]]},{"id":"88d03b1e.8f8408","type":"function","z":"3f384dee.a0ca22","name":"delete-query","func":"msg.topic=\"DELETE FROM machine2 WHERE id='\" + msg.payload.id + \"'\";\nreturn msg;","outputs":1,"noerr":0,"x":325,"y":285,"wires":[["627bcf35.3281c"]]},{"id":"ea5e155b.decbc8","type":"http response","z":"3f384dee.a0ca22","name":"","x":1010,"y":285,"wires":[]},{"id":"dd875985.366d78","type":"http response","z":"3f384dee.a0ca22","name":"","x":1010,"y":210,"wires":[]},{"id":"e09d5fa1.472d","type":"http response","z":"3f384dee.a0ca22","name":"","x":1010,"y":135,"wires":[]},{"id":"160f4157.f6e32f","type":"http in","z":"3f384dee.a0ca22","name":"","url":"/search","method":"get","upload":false,"swaggerDoc":"","x":115,"y":360,"wires":[["8f7020f9.e1f22"]]},{"id":"8f7020f9.e1f22","type":"function","z":"3f384dee.a0ca22","name":"search-query","func":"if (msg.payload.name===\"\" && msg.payload.description===\"\" && msg.payload.location===\"\" && msg.payload.type===\"\" && msg.payload.ip_address===\"\" && msg.payload.ip_port===\"\" && msg.payload.process_code===\"\")\n{\n    msg.topic=\"SELECT machine2.id AS id, machine2.name AS name, machine2.description AS description, location.location AS location, machine_type.name AS type, users.name AS owner_id, machine2.ip_address AS ip_address, machine2.ip_port AS ip_port, machine2.process_code AS process_code, machine2.enabled AS enabled FROM machine2 INNER JOIN machine_type ON machine_type.id = machine2.type INNER JOIN location ON location.id = machine2.location INNER JOIN users ON users.id = machine2.owner_id ORDER BY machine2.id DESC\";\n}\nelse\n{\n    msg.topic=\"SELECT machine2.id AS id, machine2.name AS name, machine2.description AS description, location.location AS location, machine_type.name AS type, users.name AS owner_id, machine2.ip_address AS ip_address, machine2.ip_port AS ip_port, machine2.process_code AS process_code, machine2.enabled AS enabled FROM machine2 INNER JOIN machine_type ON machine_type.id = machine2.type INNER JOIN location ON location.id = machine2.location INNER JOIN users ON users.id = machine2.owner_id WHERE machine2.name='\" + msg.payload.name + \"' OR machine2.description='\" + msg.payload.description + \"' OR location.id='\" + msg.payload.location + \"' OR machine_type.id='\" + msg.payload.type + \"' OR machine2.ip_address='\" + msg.payload.ip_address + \"' OR machine2.ip_port='\" + msg.payload.ip_port + \"' OR machine2.process_code='\" + msg.payload.process_code + \"' ORDER BY machine2.id DESC\";\n}\nreturn msg;","outputs":1,"noerr":0,"x":325,"y":360,"wires":[["2a6cfcbd.f00d84"]]},{"id":"1041cd5a.7d8463","type":"http response","z":"3f384dee.a0ca22","name":"","statusCode":"","headers":{},"x":1010,"y":360,"wires":[]},{"id":"577755ce.4f4b6c","type":"function","z":"3f384dee.a0ca22","name":"search-response","func":"return msg;","outputs":1,"noerr":0,"x":690,"y":360,"wires":[["1041cd5a.7d8463"]]},{"id":"36e8e622.a1946a","type":"debug","z":"3f384dee.a0ca22","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","x":670,"y":435,"wires":[]},{"id":"98180f70.264e2","type":"mysql","z":"3f384dee.a0ca22","mydb":"8429c6f1.4909c8","name":"database","x":495,"y":60,"wires":[["bc7be8bb.430798"]]},{"id":"65b8e1cd.c37d8","type":"mysql","z":"3f384dee.a0ca22","mydb":"8429c6f1.4909c8","name":"database","x":495,"y":210,"wires":[["bd5710a6.0b1e5"]]},{"id":"2a6cfcbd.f00d84","type":"mysql","z":"3f384dee.a0ca22","mydb":"8429c6f1.4909c8","name":"database","x":495,"y":360,"wires":[["36e8e622.a1946a","577755ce.4f4b6c"]]},{"id":"627bcf35.3281c","type":"mysql","z":"3f384dee.a0ca22","mydb":"8429c6f1.4909c8","name":"database","x":495,"y":285,"wires":[["6aa64c7d.45aa44"]]},{"id":"2cd887e8.423148","type":"mysql","z":"3f384dee.a0ca22","mydb":"8429c6f1.4909c8","name":"database","x":495,"y":135,"wires":[["102f6c93.a2f413"]]},{"id":"8429c6f1.4909c8","type":"MySQLdatabase","z":"","host":"10.21.11.15","port":"3306","db":"i4inari","tz":""}]

Thanks for helping.

And in which node are you making that change?

Template node

Please provide the code that created the database and a sample of the data

The following link consists of an example which meet my requirement. In the example, country names are being displayed on Country column. When click on any row, Country column field of the row will become dropdown (with options from another table of the database) and we are able to perform CRUD.

For example, I have country table with id and country columns. I only would like to add/edit id of country table on country column of machine2 table.

Link: http://js-grid.com/demos/basic.html

That is a great demo of the front end, but doesn't answer my request. If you want help you have to provide the date. I'm not going to spend the time creating the SQL to create the tables and load that data, it is not worth my time to do that.

If you provide it so I can create the database and tables YOU use to help YOU figure out an issue YOU have, I'll be happy to spend a little of MY time helping you. But if You don't want to do the work to provide the data needed to debug YOUR issue, why should I or anyone else?

Hi,

I had uploaded the the sample data here. Thanks for helping.

queries.txt (725 Bytes)
Sample Data (tab delimited).txt (2.0 KB)

1 Like

The sql and data (not in a format that will allow someont to load it into a database!!) do not match the table you are using in your queries.

To move forward with helping you, you have two choices:
(1) provide the sql that will create the table and load the data you are using in your flow
or
(2) change your flow to use the table you just created

Hi,

I had created a demo flow for this issue. The following is the flow:

[{"id":"20582d3.3df2dd2","type":"http in","z":"2cc812cc.02d06e","name":"","url":"/demo","method":"get","upload":false,"swaggerDoc":"","x":105,"y":60,"wires":[["9e77f24a.5ce11"]]},{"id":"c0c901fe.fd2a","type":"mysql","z":"2cc812cc.02d06e","mydb":"d2f8d5d0.cabda8","name":"database","x":495,"y":60,"wires":[["c076bfc0.dd124"]]},{"id":"9e77f24a.5ce11","type":"function","z":"2cc812cc.02d06e","name":"select-query","func":"msg.topic=\"SELECT test.id AS id,test.name AS name,lastname.lastname AS lastname,test.code AS code FROM test INNER JOIN lastname ON lastname.id = test.lastname ORDER BY test.id DESC\";\nreturn msg;","outputs":1,"noerr":0,"x":325,"y":60,"wires":[["c0c901fe.fd2a"]]},{"id":"c076bfc0.dd124","type":"json","z":"2cc812cc.02d06e","name":"","property":"payload","action":"","pretty":false,"x":650,"y":60,"wires":[["2a7e57e8.672488"]]},{"id":"2a7e57e8.672488","type":"template","z":"2cc812cc.02d06e","name":"web-template","field":"payload","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"<!DOCTYPE html>\n<html lang=\"en\">\n   <head>\n      <title>Gridview with CRUD Options (MySQL)</title>\n      <meta charset=\"utf-8\">\n      <meta name=\"viewport\" content=\"width=device-width, initial-scale=1\">\n      <link rel=\"stylesheet\" href=\"https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css\" integrity=\"sha384-BVYiiSIFeK1dGmJRAkycuHAHRg32OmUcww7on3RYdg4Va+PmSTsz/K68vbdEjh4u\" crossorigin=\"anonymous\">\n      <link rel=\"stylesheet\" href=\"https://cdnjs.cloudflare.com/ajax/libs/bootstrap-timepicker/0.5.2/css/bootstrap-timepicker.min.css\" />\n      <script src=\"https://ajax.googleapis.com/ajax/libs/jquery/3.1.1/jquery.min.js\"></script>\n      <script src=\"https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js\" integrity=\"sha384-Tc5IQib027qvyjSMfHjOMaLkfuWVxZxUPnCJA7l2mCWNIpG9mGCD8wGNIcPD7Txa\" crossorigin=\"anonymous\"></script>\n      <link type=\"text/css\" rel=\"stylesheet\" href=\"https://cdnjs.cloudflare.com/ajax/libs/jsgrid/1.5.3/jsgrid.min.css\" />\n      <link type=\"text/css\" rel=\"stylesheet\" href=\"https://cdnjs.cloudflare.com/ajax/libs/jsgrid/1.5.3/jsgrid-theme.min.css\" />\n      <script type=\"text/javascript\" src=\"https://cdnjs.cloudflare.com/ajax/libs/jsgrid/1.5.3/jsgrid.min.js\"></script>\n      <script type=\"text/javascript\">\n         $(function() {\n             var db = {{{payload}}};\n             $(\"#jsGrid\").jsGrid({\n                 width: \"100%\",\n         \n                 filtering: true,\n                 inserting: true,\n                 editing: true,\n                 sorting: true,\n                 paging: true,\n                 autoload: true,\n         \n                 deleteConfirm: function(item) {\n                     return item.name + \" information will be deleted. Are you sure?\";\n                 },\n         \n                 data: db,\n         \n                 fields: [ {\n                     title: \"ID\",\n                     name: \"id\",\n                     type: \"text\",\n                     width: 50,\n                     filtering: false,\n                     inserting: false,\n                     editing: false,\n                     align: \"left\"\n                 }, {\n                     title: \"Name\",\n                     name: \"name\",\n                     type: \"text\",\n                     width: 50,\n                     filtering: true\n                 }, {\n                     title: \"Last Name\",\n                     name: \"lastname\",\n                     type: \"text\",\n                     width: 50,\n                     filtering: false\n                 }, {\n                     title: \"Code\",\n                     name: \"code\",\n                     type: \"text\",\n                     width: 50,\n                     filtering: false\n                 }, {                                                   \n                     type: \"control\"\n                 }],\n                \n                 controller: {\n                     loadData: function(filter) {\n                         return $.ajax({\n                         type: \"GET\",\n                         url: \"/search\",\n                         data: filter\n                         });\n                     },\n                     insertItem: function(item) {\n                         return $.ajax({\n                             type: \"POST\",\n                             url: \"/insert\",\n                             data: item\n                         });\n                     },\n                     updateItem: function(item) {\n                         return $.ajax({\n                             type: \"PUT\",\n                             url: \"/update\",\n                             data: item\n                         });\n                     },\n                     deleteItem: function(item) {\n                         return $.ajax({\n                             type: \"DELETE\",\n                             url: \"/delete\",\n                             data: item\n                         });\n                     }\n                 }\n             });\n         });\n      </script>\n   </head>\n   <body class=\"container\">\n      <section class=\"row\">\n         <div class=\"col-12\"></div>\n         <div class=\"col-12\" id=\"jsGrid\">\n         </div>\n      </section>\n   </body>\n</html>","x":830,"y":60,"wires":[["6d1e29fa.fd0458"]]},{"id":"6d1e29fa.fd0458","type":"http response","z":"2cc812cc.02d06e","name":"","statusCode":"","headers":{},"x":1010,"y":60,"wires":[]},{"id":"1ddd63f8.61ab9c","type":"http in","z":"2cc812cc.02d06e","name":"","url":"/update","method":"put","upload":false,"swaggerDoc":"","x":115,"y":210,"wires":[["3175e6d2.006daa"]]},{"id":"d92b8c59.13e73","type":"function","z":"2cc812cc.02d06e","name":"update-response","func":"msg.payload=msg.result;\nreturn msg;","outputs":1,"noerr":0,"x":690,"y":210,"wires":[["cf5afe03.ebb75"]]},{"id":"5b5fdd49.a07bb4","type":"http in","z":"2cc812cc.02d06e","name":"","url":"/insert","method":"post","upload":false,"swaggerDoc":"","x":115,"y":135,"wires":[["7f6528eb.3e9738"]]},{"id":"dd0a1ed7.24618","type":"http in","z":"2cc812cc.02d06e","name":"","url":"/delete","method":"delete","upload":false,"swaggerDoc":"","x":125,"y":285,"wires":[["4d8e5cf3.121764"]]},{"id":"e5f54aa9.4f4d28","type":"function","z":"2cc812cc.02d06e","name":"insert-response","func":"msg.payload=msg.result;\nreturn msg;","outputs":1,"noerr":0,"x":680,"y":135,"wires":[["3a762524.f0cdfa"]]},{"id":"7f6528eb.3e9738","type":"function","z":"2cc812cc.02d06e","name":"insert-query","func":"msg.topic=\"INSERT INTO test (name,lastname,code,timestamp) VALUES ('\" + msg.payload.name + \"','\" + msg.payload.lastname + \"','\" + msg.payload.code + \"',CURRENT_TIMESTAMP)\";\nreturn msg;","outputs":1,"noerr":0,"x":325,"y":135,"wires":[["fa0939b3.ead6f8"]]},{"id":"3175e6d2.006daa","type":"function","z":"2cc812cc.02d06e","name":"update-query","func":"msg.topic=\"UPDATE test SET name='\" + msg.payload.name + \"', lastname='\" + msg.payload.lastname + \"', code='\" + msg.payload.code + \"' WHERE id='\" + msg.payload.id + \"'\";\nreturn msg;","outputs":1,"noerr":0,"x":325,"y":210,"wires":[["b99fc447.73e1d8"]]},{"id":"6877696.f3b8c98","type":"function","z":"2cc812cc.02d06e","name":"delete-response","func":"msg.payload=msg.result;\nreturn msg;","outputs":1,"noerr":0,"x":680,"y":285,"wires":[["228d6714.d97398"]]},{"id":"4d8e5cf3.121764","type":"function","z":"2cc812cc.02d06e","name":"delete-query","func":"msg.topic=\"DELETE FROM test WHERE id='\" + msg.payload.id + \"'\";\nreturn msg;","outputs":1,"noerr":0,"x":325,"y":285,"wires":[["16b60cbd.673883"]]},{"id":"228d6714.d97398","type":"http response","z":"2cc812cc.02d06e","name":"","x":1010,"y":285,"wires":[]},{"id":"cf5afe03.ebb75","type":"http response","z":"2cc812cc.02d06e","name":"","x":1010,"y":210,"wires":[]},{"id":"3a762524.f0cdfa","type":"http response","z":"2cc812cc.02d06e","name":"","x":1010,"y":135,"wires":[]},{"id":"16b60cbd.673883","type":"mysql","z":"2cc812cc.02d06e","mydb":"d2f8d5d0.cabda8","name":"database","x":495,"y":285,"wires":[["6877696.f3b8c98"]]},{"id":"b99fc447.73e1d8","type":"mysql","z":"2cc812cc.02d06e","mydb":"d2f8d5d0.cabda8","name":"database","x":495,"y":210,"wires":[["d92b8c59.13e73"]]},{"id":"fa0939b3.ead6f8","type":"mysql","z":"2cc812cc.02d06e","mydb":"d2f8d5d0.cabda8","name":"database","x":495,"y":135,"wires":[["e5f54aa9.4f4d28"]]},{"id":"98468cae.15157","type":"http in","z":"2cc812cc.02d06e","name":"","url":"/search","method":"get","upload":false,"swaggerDoc":"","x":115,"y":360,"wires":[["b907ac2e.d8ced"]]},{"id":"b907ac2e.d8ced","type":"function","z":"2cc812cc.02d06e","name":"search-query","func":"if (msg.payload.name===\"\")\n{\n    msg.topic=\"SELECT test.id AS id,test.name AS name,lastname.lastname AS lastname,test.code AS code FROM test INNER JOIN lastname ON lastname.id = test.lastname ORDER BY test.id DESC\";\n}\nelse\n{\n    msg.topic=\"SELECT test.id AS id,test.name AS name,lastname.lastname AS lastname,test.code AS code FROM test INNER JOIN lastname ON lastname.id = test.lastname WHERE test.name='\" + msg.payload.name + \"' ORDER BY test.id DESC\";\n\n}\nreturn msg;","outputs":1,"noerr":0,"x":325,"y":360,"wires":[["a9f5f112.25584"]]},{"id":"a9f5f112.25584","type":"mysql","z":"2cc812cc.02d06e","mydb":"d2f8d5d0.cabda8","name":"database","x":495,"y":360,"wires":[["c2103f30.99397"]]},{"id":"408e7dd.768de84","type":"http response","z":"2cc812cc.02d06e","name":"","statusCode":"","headers":{},"x":1010,"y":360,"wires":[]},{"id":"c2103f30.99397","type":"function","z":"2cc812cc.02d06e","name":"search-response","func":"return msg;","outputs":1,"noerr":0,"x":690,"y":360,"wires":[["408e7dd.768de84"]]},{"id":"d2f8d5d0.cabda8","type":"MySQLdatabase","z":"","host":"192.168.3.10","port":"3306","db":"ram_test","tz":""}]

The following file consists of two tables and data:
data.txt (5.2 KB)

Expected result: I want lastname field to appear as dropdown (with options Rishan and Login) once I click on add and edit button.