1

I'm making a little web interface with nodejs, express and sqlite3. There's a table with a fixed number of rows and columns, which can be updated through a form. The user isn't required to fill out all of the fields, so there's a lot of empty values in the post request. I only want to write back the the populated fields to the database and ignore the empty ones. I can think of a number of ugly and tedious ways to achieve this, but I'm sure there's some really simple solution I don't know of.

app.post('/updatevals', (req, res) => {

        for (const m in req.body) {

            const val = req.body[m]; // val = ['value1', '', '', 5]
                                     // user left index 1 + 2 empty
                                     // last value is primary key

            // Overwrites val2 + val3 with empty strings...
            const sql = "UPDATE mytable SET val1=?, val2=?, val3=? WHERE id=?"; 

            db.run(sql, val, (err) => {});

        }

});
user1854611
  • 121
  • 8
  • Curious about some of your "ugly and tedious ways" to do it. – TGrif Feb 11 '19 at 16:48
  • val itself won't be empty, but the array values inside of it may be empty strings. I could do a separate query for each of the columns and put them in conditionals -> pretty ugly and tedious – user1854611 Feb 11 '19 at 16:54
  • You could try [this](https://stackoverflow.com/a/9814128/5156280). – TGrif Feb 11 '19 at 17:01
  • Yes, I've already seen this post, but the tedious thing about it is that I'd have to apply this case/then/when/else construct to every single field.. – user1854611 Feb 11 '19 at 17:07
  • 2
    To eliminate the tedious case/then/when/else construct, iterate val and replace `''` with `null` before sql is executed. Then it is the less tedious `val1 = COALESCE(?,val1)`. – DinoCoderSaurus Feb 11 '19 at 22:21

0 Answers0