How do I display the table data (HTML) of a database through JSON Express?

Question

How do I display the table data (HTML) of a database through JSON Express?

Navigation

#1 by (0 votes)

0

Hello! I would like to know a way to present to the user in an HTML table, data stored in a MYSQL database with connection through JSON Express.

const express = require('express');
const app = express();         
const bodyParser = require('body-parser');
const port = 3309;
const mysql = require('mysql');
var formidable = require("formidable");


var obj = {};
var path = require('path');


    app.use(bodyParser.urlencoded({ extended: true }));
    app.use(bodyParser.json());

const router = express.Router();


app.get('/', function(req, res) {
    res.sendFile(path.join(__dirname + '/index.html'));
});



    app.get('/lista', function(req, res) {

        execSQLQuery("SELECT Nome, modeloCarro, status FROM Clientes",res);
        });

    app.post('/dados', function(req, res) {
        execSQLQuery("INSERT INTO Clientes(Nome,CPF,dtNascimento,modeloCarro,status) VALUES ('"+req.body.nomeMotorista+"','"+req.body.CPF+"','"
        +req.body.dataNascimento+"','"+req.body.modeloCarro+"','"+req.body.Ativo+"')", res);
    });



    app.listen(port);
    console.log('API funcionando!');

/ ***************************** Connection Code ************** *********************** /

function execSQLQuery(sqlQry, res){
    const connection = mysql.createConnection({
  host     : 'localhost',
  port     : 3308,
  user     : 'root',
  password : 'teste',
  database : 'mysql'
    });

 connection.query(sqlQry, function(error, results, fields){
      if(error) 
        res.json(error);
      else
        res.json(results);
      connection.end();
      console.log('executou!');
  });
}

mysql html json express

asked by anonymous 03.11.2017 / 06:07

1 answer

vb.net- An exception of type 'System.Data.Entity.Infrastructure.DbUpdateException' ASP.MVC ENTITY Code First (strange problem)

score 0 · Answer 1

You can use the ejs engine, use a call passing the array of objects straight to the view and then display each one with the jquery's DataTables.

app.get('/lista', (req, resp) => {
execSQLQuery(sqlQry, (results) => {
    resp.render("Home", { lista: results });
   });
});

In your execSQLQuery add a parameter to the return function, then you have to create a variable that will get your result and soon after you do it passes it as parameter to the function.

The example below is for a SQL Server database, but the idea is the same.

function request(Query, func_Retorno) {
var retorno = undefined;
//Executa a query e enche o objeto que será usado
var request = new sql.Request();
(request.query(Query).then((recordset) => {

    console.log("Carrega objeto");

    sql.close();
    //Preenche a variáveç retorno com um array de objetos
    retorno = recordset.recordset;
}).catch((error) => {
    console.error(error);
    sql.close();
})).then(() => {
    //Retorna o array de objetos para a função de callback
    if (func_Retorno != undefined)
        func_Retorno(retorno);
    console.log("Retorno do objeto concluido");
    });
}

In your view you create a barrel and the jquery function has to use the <% -% > to be able to use what comes from your backend.

<script>
    //Atribui a variável Lista o array de objetos que retorna do backend
    var Lista= <%- JSON.stringify(lista) %>;

    var TableDados = null;
    $(function () {

        TableDados = $('#tblDados').DataTable({
            "serverSide": false,
            "drawCallback": function (settings) {},
            "scrollX": true,
            "processing": true,
            "paging": true,
            "lengthChange": false,
            "searching": true,
            "ordering": true,
            "info": true,
            "autoWidth": true,
            "deferRender": true,
            "language":
            {
                "url": "/scripts/plugins/dataTables/languagePT-BR.json"
            },
            "columns": [
                { "data": "Nome" },
                { "data": "Sobrenome" },
                { "data": "Numero" },
                { "data": "Idade" },
            ],
            "order": [0, "asc"]
        });
        console.log(Lista);
        TableDados.rows.add(Lista)
        TableDados.draw();
    });
</script>

To better understand how DataTables works, take a look at this link