insert html table data into mysql database in node js

212
August 17, 2019, at 07:40 AM

I am trying to retrieve data from a html table and insert this in MySQL database. I am already able to do the reverse, which is retrieve the information from the database and display it in same table using ejs template. I can also insert raw/JSON data into MySQL, but I'm unable to extract the data from same table because I am unable to reference that table from the server side (the same way body parser does with form data).

I have searched the web, all tutorials just do an insert to database using json data, nobody is retrieving data first from html table.

With below code I can loop through table data using plain javascript.

var table = document.getElementById('vehiclesTB');
for (var i = 1; i < table.rows.length; i++) {
  if (table.rows[i].cells.length) {
    var vehicleTag = (table.rows[i].cells[0].textContent.trim());    
  }
}

How do I pass retrieve data from html table to my controller(server side)? I am unable to reference html table directly from my server file (app.js).

My app.js file:

var express = require('express')
  , routes = require('./routes')
  , controls = require('./routes/controls')
  , http = require('http')
  , path = require('path');
var app = express();
var mysql = require('mysql');
var bodyParser =require("body-parser");
var pool = mysql.createConnection({
    connectionLimit: 100,
    host: 'localhost',
    database: 'vehicluster',
    user: 'motor',
    password: '',
    debug: false  
});
pool.connect();

// all environments
app.set('port', process.env.PORT || 8080);
app.set('views', __dirname + '/views');
app.set('view engine', 'ejs');
app.use(bodyParser.urlencoded({ extended: false }));
app.use(bodyParser.json());
app.use(express.static(path.join(__dirname, 'public')));
app.post('/vehicle', controls.vehicle);
//Middleware
app.listen(8080)

My controls.js file.

exports.vehicle = function(req, res){                   
var table = document.getElementById('vehiclesTB');//how to read this 
table in ejs
for (var i = 1; i < table.rows.length; i++) {
if (table.rows[i].cells.length) {
var vehicleTag = (table.rows[i].cells[0].textContent.trim());
var vehicleMake = (table.rows[i].cells[1].textContent.trim());
var vehicleModel = (table.rows[i].cells[2].textContent.trim());
var price = (table.rows[i].cells[3].textContent.trim());
var quantity = (table.rows[i].cells[4].textContent.trim());                
     }
}
var sql = 'insert into Vehicle(make, model, price, quantity) values 
(?,?,?,?,?)';
pool.query(sql,[vehicleMake, vehicleModel, price, quantity], 
(err,data)=>{
if(err){
    console.log(err);            
    return
 }else{
    console.log(data);                        
     }      
 };

The HTML table is showing relevant item (table), place table data into mysql database. I can already retrieve to table:

<div style="overflow-x: auto;">                       
    <table id="customers">
        <tbody id="mytbody">
            <tr>                                  
                <th>Make</th> 
                <th>Model</th>
                <th>price</th>
                <th>quantity</th>                                                                   
            </tr>
            <tr>
                <th>toyota</th> 
                <th>camry</th>
                <th>200</th>
                <th>5</th>                                     
            </tr>
            <tr>
                <th>honda</th> 
                <th>civic</th>
                <th>400</th>
                <th>7</th>                                     
            </tr>
        </tbody>
    </table>                            
</div>

I am getting errors as you would expect, getElementById is client side, null value etc, database is not updated. Most tutorials online show the reverse, which is to insert database values into html table, instead of the other way around. Any help with real table example/route will be appreciated.

Rent Charter Buses Company
READ ALSO
How can I avoid using hardcoded credentials in Javascript

How can I avoid using hardcoded credentials in Javascript

I am working on a Javascript application in which a password has been hardcoded ie var key ='333666'

146
cypress: comparing information in 2 sites

cypress: comparing information in 2 sites

I'm starting with cypress and need to compare 2 different environments

239
How can I reference an external javascriptfile in angular?

How can I reference an external javascriptfile in angular?

I am trying to create a sliding menu for my aplication in angular, I would like to reference a javascript fileI have referenced css files in the angular

143