Nodejs Express Question: Update WHERE question

Question

Okay, I'm struggling so much to understand this concept right now. I'm using a script to feed curls to my program running on server 3000 through node. I want to create a new entry in my mySQL database each time any previously filled data is updated with something new, but also append any old data from the previous entry that didn't get updated.

So for example, after my program hits the line

curl localhost:3000/register?name=bob\&width=14\&time=0

in the script, the first database entry will look like this:

Name: Bob
Width: 14
time: 0
left: 0
right: 0
dist: 0

My script then goes to the second line where it says this:

curl localhost:3000/wheels?left=1\&right=1\&time=1

which creates ANOTHER entry (separate from the first) that looks like this:

Name: Bob
Width: 14
time: 1
left: 1
right: 1
dist: 0

And then the third line from the script looks like this:

curl localhost:3000/echo?dist=9\&time=10

which creates a third entry in the database that has updated time and dist columns but also the old unchanged data from the previous entry. The third entry should look like this:

Name: Bob
Width: 14
time: 10
left: 1
right: 1
dist: 9

.... etc

And so on... I guess the best way to explain it would be, I want to create a new entry with each app.get(/whatever) that appends the previous entry's data to it but also updates any columns that may have changed after getting a curl from the script..

Here's a snippet of the script:

curl localhost:3000/register?name=bob\&width=13.970000\&time=0
curl localhost:3000/wheels?left=0.000000\&right=0.000000\&time=0 --cookie "USER=bob"
curl localhost:3000/echo?dist=9.220000\&time=10 --cookie "USER=bob"
curl localhost:3000/line?l1=1\&l2=1\&l3=1\&time=20 --cookie "USER=bob"
curl localhost:3000/other?ir=0\&time=30 --cookie "USER=bob"
curl localhost:3000/wheels?left=3.000000\&right=3.000000\&time=100 --cookie "USER=bob"
curl localhost:3000/echo?dist=9.220000\&time=110 --cookie "USER=bob"
curl localhost:3000/line?l1=1\&l2=1\&l3=1\&time=120 --cookie "USER=bob"
curl localhost:3000/other?ir=0\&time=130 --cookie "USER=bob"
curl localhost:3000/wheels?left=3.000000\&right=3.000000\&time=200 --cookie "USER=bob"
curl localhost:3000/echo?dist=9.220000\&time=210 --cookie "USER=bob"
curl localhost:3000/line?l1=1\&l2=1\&l3=1\&time=220 --cookie "USER=bob"

During my latest attempt today, I tried using an auto incrementing uniqueID column and a counter variable to only UPDATE WHERE uniqueID>counter but this didn't work.. This ended up overwriting every entry's column in the database.

Here's my overly complicated and bloated code atm...

const express = require('express');
//const session = require('express-session');
const mysql = require('mysql');

let app = express();


var pool = mysql.createPool( {
        host: 'localhost',
        user: 'root',
        password: 'password',
        database: 'cars',
        connectionLimit: 10,
        multipleStatements : true
});

var helper1 = [];
var helper2 = [];
var helper3 = [];
var helper4 = [];
var counter = 0;

app.get('/register', function(req, res) {
    pool.query("INSERT INTO cars (`name`, `width`, `time`) VALUES (?,?,?)", [req.query.name, req.query.width, req.query.time]);
    helper1.push(req.query.name);
    helper1.push(req.query.width);


    res.redirect('localhost:3000/wheels');
});


app.get('/wheels', function (req, res) {
        pool.query("INSERT INTO cars (`name`, `width`) VALUES ('"+helper1[0]+"', '"+helper1[1]+"')");

    console.log(counter);
    console.log(req.query.time);

    helper2.push(req.query.left);
    helper2.push(req.query.right);

    pool.query("UPDATE `cars` SET left1 = '"+req.query.left+"' WHERE `uniqueID`>'"+counter+"")
    pool.query("UPDATE `cars` SET right1 = '"+req.query.right+"' WHERE `uniqueID`>"+counter+"")
    pool.query("UPDATE `cars` SET time= '"+req.query.time+"' WHERE `uniqueID`>"+counter+"")
    counter++;


    res.redirect('localhost:3000/echo');
});



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

            pool.query("INSERT INTO cars (`name`, `width`) VALUES ('"+helper1[0]+"', '"+helper1[1]+"')");
        helper3.push(req.query.dist);

        pool.query("UPDATE `cars` SET left1= '"+helper2[0]+"' WHERE `uniqueID`>"+counter+"")
        pool.query("UPDATE `cars` SET right1= '"+helper2[1]+"' WHERE `uniqueID`>"+counter+"")
        pool.query("UPDATE `cars` SET dist= '"+req.query.dist+"' WHERE `uniqueID`>"+counter+"")
        pool.query("UPDATE `cars` SET time= '"+req.query.time+"' WHERE `uniqueID`>"+counter+"")
        counter++;


        res.redirect('localhost:3000/line');
});


app.get('/line', function (req, res) {
                    pool.query("INSERT INTO cars (`name`, `width`) VALUES ('"+helper1[0]+"', '"+helper1[1]+"')");

        pool.query("UPDATE `cars` SET left1= '"+helper2[0]+"' WHERE `uniqueID`>"+counter+"")
                pool.query("UPDATE `cars` SET right1= '"+helper2[1]+"' WHERE `uniqueID`>"+counter+"")
                pool.query("UPDATE `cars` SET dist= '"+helper3[0]+"' WHERE `uniqueID`>"+counter+"")
        pool.query("UPDATE `cars` SET l1= '"+req.query.l1+"' WHERE `unqiueID`>"+counter+"")
                pool.query("UPDATE `cars` SET l2= '"+req.query.l2+"' WHERE `unqiueID`>"+counter+"")
                pool.query("UPDATE `cars` SET l3= '"+req.query.l3+"' WHERE `unqiueID`>"+counter+"")
                pool.query("UPDATE `cars` SET time= '"+req.query.time+"' WHERE `uniqueID`>"+counter+"")
        counter++;


    helper4.push(req.query.l1);
    helper4.push(req.query.l2);
    helper4.push(req.query.l3);


    res.redirect('localhost:3000/other');
});

app.get('/other', function(req, res) {
                    pool.query("INSERT INTO cars (`name`, `width`) VALUES ('"+helper1[0]+"', '"+helper1[1]+"')");

                pool.query("UPDATE cars SET left1 =?, right1 = ?",
                        [helper2[0], helper2[1]])
                pool.query("UPDATE cars SET dist =?", [helper3[0]])
        pool.query("UPDATE cars SET l1 = ?, l2 = ?, l3 = ?",
            [helper4[0], helper4[1], helper4[2]])



            pool.query("UPDATE `cars` SET left1= '"+helper2[0]+"' WHERE `uniqueID`>"+counter+"")
                pool.query("UPDATE `cars` SET right1= '"+helper2[1]+"' WHERE `uniqueID`>"+counter+"")
                pool.query("UPDATE `cars` SET dist= '"+helper3[0]+"' WHERE `uniqueID`>"+counter+"")
                pool.query("UPDATE `cars` SET l1= '"+helper4[0]+"' WHERE `unqiueID`>"+counter+"")
                pool.query("UPDATE `cars` SET l2= '"+helper4[1]+"' WHERE `unqiueID`>"+counter+"")
                pool.query("UPDATE `cars` SET l3= '"+helper4[2]+"' WHERE `unqiueID`>"+counter+"")
                pool.query("UPDATE `cars` SET time= '"+req.query.time+"' WHERE `uniqueID`>"+counter+"")
        pool.query("UPDATE `cars` SET ir = '"+req.query.ir+"' WHERE `uniqueID`>"+counter+"")
        counter++;

    console.log("One script loop done, 5 new database entries should be made");
    //res.redirect('localhost:3000/wheels');
});

Is what I'm trying to accomplish even possible? It seems like it would be rather easy, and I have a feeling it is, but I also feel like I'm going down a rabbit hole with it. I feel like I was closer at achieving what I was trying to do yesterday than I am now. Any help or insight would be awesome, thanks.

I feel this is what you are looking after: https://stackoverflow.com/q/6107752/1499476 — Rvy Pandey, May 17 '20 at 02:28
why don't you just simplify everything by just getting the latest row (sort by id in descending order, limit 1), then patch in the updated values to form a new payload, and persist into the db. — Calvintwr, May 17 '20 at 04:16
@Rvy_Pandey, Yes that's very similar. But how would I make it work for Node js / express? I've been trying " pool.query("INSERT INTO cars (`dist`, `time`) VALUES (?, ?) ON DUPLICATE KEY UPDATE dist=VALUES(req.query.dist), time=VALUES(req.query.time)", [req.query.dist, req.query.time]); " And similar commands but it's not updating anything. — JStone, May 17 '20 at 04:56
@Calvintwr, how would this work? pool.query("SELECT * FROM customers ORDER BY uniqueID DESC, UPDATE `cars` SET newestSet = newSet) or something along those lines? — JStone, May 17 '20 at 05:00

score 1 · Accepted Answer · answered May 17 '20 at 11:18

1

Suppose

curl localhost:3000/wheels?left=1\&right=1\&time=1

You then insert those values in your nested SELECT statements. Those that were not changed, leave them as the name of the column.

INSERT INTO Cars (Name, Width, time, left, right, dist)
    SELECT Name, Width, 1, 1, 1, dist
    FROM Cars
    ORDER BY uniqueID DESC
    LIMIT 1

The effect of ORDER BY uniqueID DESC and LIMIT 1 will cause the most recent row to be selected.

answered May 17 '20 at 11:18

Calvintwr

8,308
5
28
42

that's great to hear. you can accept the answer and upvote it? – Calvintwr May 18 '20 at 05:05

Nodejs Express Question: Update WHERE question

1 Answers1