Display results from SQL query in a HTML table?

General
1

Using node-red-node-mysql, how do I go on about displaying query results in a HTML table using a template node and HTTP Response node (not Node-RED dashboard/UI)?

2

You will need to convert the returned JSON to an HTML table. You can use the template node to help. The template node uses Mustache templating so that you can do loops to get the content into the HTML tags you need.

If you need more interaction between Node-RED and your front-end, you might also want to look at uibuilder. In fact the next release v6.1.0 branch of uibuilder has a node that will convert from input arrays/objects direct to HTML tables via a standardised configuration data format. you send the converted data to a uibuilder node and the uibuilder client library hydrates it into HTML.

In v6.2 of uibuilder, The hydration code will also be made available in a new node so that you could do:

sql query
  -> uib-element (set to produce a table)
    -> uib-html (set to output HTML)
      -> http-out

There will also be a uib-save node that can take the HTML and save it to a file in the right uibuilder instance folder which, if you only need to update the query periodically, would be super efficient since the last result would be loaded as a static file. The static file would be updated when you re-run the query.

3

Can you point me in the right direction to do that?

4

Hi @network_potato

As this is not dashboard driven - you will need to develop the "engine" your self.

Here is the gist (in writing)

HTTP IN -> Your SQL Query -> Template Node -> HTTP RESPONSE

Putting that into a flow?
NOTE: I have substituted the SQL Node for a function node (I don't use SQL in Node RED)

Screenshot 2023-02-03 at 19.04.05
Screenshot 2023-02-03 at 19.04.05981Ă—258 8.6 KB

As for the template Node, this is nothing more than markup mixed with the msg object
The example below will list names in an html <ul> element.

My payload:

{
    payload: ["Node", "Red"]
}

My template

<ul>
  {{#payload}}
  <li>{{.}}</li>
  {{/payload}}
</ul>

Output (sent as payload to the HTTP Response Node)

<ul>
  <li>Node</li>
  <li>RED</li>
</ul>

using only these components , you can achive your goal.
BUT a read up on mustache (the template engine) is recommended.

2 Likes
5

Thanks! I managed to achieve my goal with this code:

Converting the SQL query payload to JSON and then putting {{{payload}}} inside var myArray

6

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