CODEWITHSUNDEEP
mysqlnode.jsejs
williswin
williswin

Reputation: 133

Reading data from MySQL to create HTML table (with filter)

I am trying to create an HTML page that will display data as a table from MySql table. I am able to get the data from Mysql into JSON but not able to get it to display on the HTML page. It can either read from the database directly or a JSON page, whichever is better practice. I am stuck getting it to display as a table, once it is displayed I would like to be able to add more data such as age and have a sort/filter function. The code I have is very basic and from a tutorial I saw so thank you for any assistance!

table_routes.js

module.exports = function(app, passport) {
    app.get('/', function(req, res){
     res.render('index.ejs');
    });



    //-SQL QUERY
    var express = require('express')
     , http = require('http')
     , mysql = require('mysql'); // <---- HERE

    var app = express();

    app.use(function(req, res, next) {
       res.header("Access-Control-Allow-Origin", "http://localhost:8080"); 
       res.header("Access-Control-Allow-Headers", "Origin, X-Requested-With, Content-Type, Accept");
       next();
     });

    var connection = mysql.createConnection({
       host: 'localhost',
       user: 'root',
       password: "password",
       database: 'db'
    });

    connection.connect(); // <---- AND HERE

    // all environments
    app.set('port', process.env.PORT || 7003);


   connection.query('SELECT * FROM testtable', function(err, rows, fields){  
      if(err) {
            console.log(err); 
            res.json({"error":true});
      }
      else { 
           //    console.log(result); 
          console.log(JSON.stringify(rows));
        //   response.writeHead(200, {'Content-Type': 'application/json'});
          res.json(rows); 
        //   response.end(JSON.stringify(rows));

      }

      });



   http.createServer(app).listen(app.get('port'), function(){
    console.log('Express server listening on port ' + app.get('port'));
   });

    };

admintable.ejs

<!DOCTYPE html>
<head>
<title>Home | Table</title>
<link type="text/css" rel="stylesheet" href="css/styles.css" />
</head>
<body>
  <h1>Dashboard</h1>
  <table id="servers">
    <thead>
      <tr>
        <th width="150">ID</th>
        <th width="150">Name</th>
        <th width="150">Last Name</th>
        <th width="150">Row</th>
      </tr>
    </thead>
    <tbody>
    </tbody>
  </table>

  <div class="log"></div>
  <script src="https://code.jquery.com/jquery-2.2.0.min.js"></script>
  <script type="text/javascript">
    $(document).ready(function() {
        // Code here
          $.get('/getTable', function(result,fields) {
          var rows = JSON.parse(result);
          var row = rows[i];
          console.log(rows);
            for (i=0 ; i < rows.length ; i++){
                $('#servers tbody').append('<tr><td>'+row.id+'</td><td>'+row.name+'</td><td>'+row.lastname+'</td><td>'+row.testtablecol+'</td></tr>');
            }
          });
    });
    </script>
  <script type="text/javascript" src="js/script.js" />
</body>

enter image description here enter image description here enter image description here

Upvotes: 0

Views: 1581

Answers (1)

williswin
williswin

Reputation: 133

I was able to get the data to display with the help of this example. Now I just need assistance with filtering.

https://github.com/sam-suresh/JSON-URL-to-HTML-Table

JSON to HTML from url

<html>
<table id="personDataTable">
    <tr>
        <th>id</th>
        <th>name</th>
        <th>lastname</th>
    </tr>

</table>

<style>
table {
  border: 2px solid #666;
    width: 100%;
}
th {
  background: #f8f8f8;
  font-weight: bold;
    padding: 2px;
}
</style>
     <script src="https://code.jquery.com/jquery-3.2.1.min.js"></script>

<script>
$.ajax({
    url: 'ENTER YOUR URL',
    type: "get",
    dataType: "json",

    success: function(data) {
        drawTable(data);
    }
});

function drawTable(data) {
    for (var i = 0; i < data.length; i++) {
        drawRow(data[i]);
    }
}

function drawRow(rowData) {
    var row = $("<tr />")
    $("#personDataTable").append(row);
        row.append($("<td>" + rowData.id + "</td>"));
        row.append($("<td>" + rowData.name + "</td>"));
        row.append($("<td>" + rowData.lastname + "</td>"));
    }

</script>
</html>

Upvotes: 1

Related Questions