Link to home
Start Free TrialLog in
Avatar of Seham Hammad
Seham Hammad

asked on

Insert into mysql table after the last row with javascript and nodejs

Hi
I am new to javascript and I am writing a program with nodejs and javascript to get the ip address and hostname of my computer and log them to mysql table, I need to insert a new row each time the ipp address changes with the time stamp. the problem that how to insert the new row at the end of the table if the ip address changes..
can anyone help me please as I was trying for few days. i have used the for loop to loop through each row and insert at after the last row, but when the insert statement executes it inserts many rows at the same time with the loop.

#!/usr/bin/env node
const publicIP = require('public-ip');
const pool = require('./database/config');
const moment = require('moment');
const os = require ('os');
const ip = require("ip");
var async = require('async');

setInterval(function () {

    (async () => {
        try {
            const IPv4 = await publicIP.v4();
            const hostname = os.hostname();
            const timestamp = moment().format('YYYYY:MM:DD, HH:mm:ss');

            //const sql = "SELECT id, ip_add, host_name,active FROM ipaddresses WHERE host_name = host_name" ;
            //const sql = "SELECT id, ip_add, hostname,timestamp FROM ipaddress" ;
            const sql = "SELECT  * FROM ipaddress order by id desc limit 1";    
           //async.forEachOf(rows, function(value, key, callback){
               var result =[];
                pool.query(sql,IPv4,(error, rows, fields) => {
                         console.log(hostname);
                                if (error) {
                                    console.log(error)
                                }else {
                                    if(rows.length){      
                                       let row = rows.length;                                                                                        
                                         for(var i=0; i < rows.length; i++ ) {
                                            result.push(row[i]);
                                            console.log(row[i]);
                                               if (row[i].ip_add !== IPv4 ) {
                                                  const sql = "INSERT INTO ipaddress(ip_add, hostname, timestamp) VALUES (?,?,?)";
                                                    pool.query(sql, [IPv4,hostname,timestamp], (error, result) => {
                                                     if (error) {
                                                        console.log(error);
                                                     } else{
                                                         console.log('sucess');
                                                       }
                                                  }); }
                                               // });
                                        }
                                  //});    
                                           }
                                         }
                            //}
                           
                            });
                       
   
                       
          //  console.log(IPv4);
            }catch (err) {
            console.log(err);
        }

    })();
}, 5000);
Avatar of Julian Hansen
Julian Hansen
Flag of South Africa image

What DB are you using?
If you are using MySQL you can use an ON DUPLICATE KEY statement. Make your IP Address+Hostname a unique key - then run
INSERT INTO ipaddress (ip_add, hostname) VALUES (?,?)  
ON DUPLICATE KEY UPDATE UPDATE ip_add=VALUES(ip_add)

Open in new window


What is the rational behind using this query?
SELECT  * FROM ipaddress order by id desc limit 1

Open in new window

Why not search for a specific hostname / ipaddress combo and if not found add it?
There is no 'last row' in SQL.  It is not like a spreadsheet.  If you want a specific order, you have to use the ORDER BY clause to create it.
Avatar of Seham Hammad
Seham Hammad

ASKER

Thanks guys, will try that today and will let you know.
This question needs an answer!
Become an EE member today
7 DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform.
View membership options
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.