SOLVED!
I am trying to use node to update a MySQL table without using express.
What I want to do is query my table. Then loop through every item and then convert several of the serving sizes into a JSON object.
After that I want to update the table with the new JSON object, but I want to do that in the loop so I am sure I am updating it for the right item.
Everything is working well so far, except I can’t seem to be able to call the UPDATE statement from the loop. I tried to put it in a function, but it doesn’t work.
I tried the update statement outside of the function and the loop and it works. (But it won’t work in the loop either)
How can I both select and update to a table with Node and MySQL?
Here is what I have so far:
require(“dotenv”).config();
const mysql = require(“mysql”);
// Prepare to connect to MySQL with your secret environment variables
const connection = mysql.createConnection({
host: process.env.MYSQL,
user: process.env.MYSQL_USER,
password: process.env.MYSQL_PASSWORD,
database: process.env.MYSQL_DB,
});
const sql = "SELECT * FROM MYTABLE where fdc_id=170567";
connection.query(sql, function (err, results, fields) {
if (err) throw err;
for (i=0; i < results.length; i++)
{
console.log(results[i].fdc_id);
servingsize={};
if (results[i].GmWt_1) {servingsize["wt1"] = [results[i].GmWt_1, results[i].GmWt_Desc1];}
if (results[i].GmWt_2) {servingsize["wt2"] = [results[i].GmWt_2, results[i].GmWt_Desc2];}
servingsize["100g"] = [100, "100 grams"];
console.log(servingsize);
servUpdate(servingsize);
} // End loop
});
function servUpdate(servingsize) {
const sqlupdate = "UPDATE MAIN set servingsizes='{ \"wt5\": [500,\"500c\"] }' where fdc_id=170567";
connection.query(sqlupdate, function (err, result) {
if (err) throw err;
console.log(result.affectedRows + " record(s) updated");
});
}
connection.end();
Thanks for any help!