CODEWITHSUNDEEP
javascripthtmlnode.js
Kyung Lee
Kyung Lee

Reputation: 72

How to display query results from a database in HTML using node.js

I am currently creating a webpage with node.js that makes queries to a MySQL database and displays the results in a table.

I want the page to display all the data in the database upon initial loading, but I also want to implement a filter where the user can fill in some fields and when the submit button is pressed, a new query will be made using the inputs and the results displayed on the page.

Here is the code I have so far:

In "app.js", I make a connection to the database and render it to the page:

var connection = mysql.createConnection({
    // make connection
    host: ...,
    user: ...,
    password: ...,
    database: ...
});

connection.connect();

//routes
app.get("/", function(req, res){

    var cmd = `SELECT * FROM myTable`;
    connection.query(cmd, function(err, result, fields) {
        if (err) throw err;
        res.render('home', {result: result});
    });
});

And in home.ejs, I iterate over each entry in result to create a row in the table:

<table>
    <tr>
        <th> ... </th>
        .
        .
        .
    </tr>
    <% result.forEach(function(entry) { %>
    <tr>
        <td><%= entry.column1 %></td>
        .
        .
        .
    </tr>
    <% }) %>
</table>

So the first part seems to be working (displaying all the data in the DB upon initial loading), but I am struggling to do the second part as I have no prior web development experience.

I currently have a in the page to accept user input and a associated with the form. However, I am not sure how I can use the user input inside myFunction() to perform another query to the database, and how to replace the current content of the table with the result of the new query.

Any help would be appreciated!

Upvotes: 3

Views: 6363

Answers (1)

John Abraham
John Abraham

Reputation: 18771

Ok so what you want to do is make client side filter of the data you already have in the view.

The problem is that the table is rendered server side, so how will you mutate the data on the client side after initial load?

The solution: set the data to a string on the window/global on render

<table id="results-table">
    <tr>
        <th> ... </th>
        .
        .
        .
    </tr>
    <% result.forEach(function(entry) { %>
    <tr>
        <td><%= entry.column1 %></td>
        .
        .
        .
    </tr>
    <% }) %>
</table>
<script>
  window.results = results;
</script>

after that lets make a search box and submit button inside your template:

<input type="search" id="filter-searchbar"/>
<button id="filter-submit-btn"> submit</button>

Let's add an event to listen to search and a handler to filter out the data:

const filterSearchBar = document.querySelector('#filter-searchbar');
const filterSubmitBtn = document.querySelector('#filter-submit-btn');

filterSubmitBtn.addEventListener('click', () => {
  // Next step.
})

Let's lets filter the window.results from the user's search input:

const filterSearchBar = document.querySelector('#filter-searchbar');
const filterSubmitBtn = document.querySelector('#filter-submit-btn');

filterSubmitBtn.addEventListener('click', () => {
  const filteredResults = window.results.filter(result => {
    // Not sure what you wanna filter by but imagine that it's by a property name.
    return result.name === filterSearchBar.value;
  });
  // Final step
});

Final step is to rerender that data client side inplace of the table template:

const filterSearchBar = document.querySelector('#filter-searchbar');
const filterSubmitBtn = document.querySelector('#filter-submit-btn');

filterSubmitBtn.addEventListener('click', () => {
  const filteredResults = window.results.filter(result => {
    // Not sure what you wanna filter by but imagine that it's by a property name.
    return result.name === filterSearchBar.value;
  });
  const resultsTable = document.querySelector('#results-table');
  results.innerHTML = `<pre>${JSON.stringify(resultsTable)}</pre>`

});

Hopefully you understand that I used JSON.stringify to just show you the output is filtered. You'll need to do your own table templating at this point.

Upvotes: 1

Related Questions