Nodejs and SQL – Issue with inserting data to SQL using multiple select statements

Error image
While inserting the data in SQL database table user_recipe_consumption by using multiple select statements i am facing error as – throw err; // Rethrow non-MySQL errors ^ Error: ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘Mushroom pasta’);’ , ‘( select vegEmission from RecipeEmissions where RecipeName’ at line 1

for (var dataVal = 0; dataVal < req.body.length; dataVal++) {
    var recipeInfo = req.body[dataVal].RecipeName;
    var deviceID = req.body[dataVal].deviceID;
    var totEmission = req.body[dataVal].totalEmission;
    var sql = "INSERT INTO user_recipe_consumption (deviceID, totalEmission, recipeID , vegEmission,date_of_entry) VALUES ('" + deviceID + "','" + totEmission + "', '( select RecipeID, from RecipeEmissions where RecipeName = ?);' , '( select vegEmission from RecipeEmissions where RecipeName = ? );' ,'" + now + "')";              
    con.query(sql, recipeInfo, function(err, result) {
            if (err) throw err;
            console.log("Number of records inserted: " + result.affectedRows);
        });
    }

Answer

You have too many semicolons in your SQL statement. You’re also putting single-quotes around a subquery, which effectively turns it into a string literal. And you’re using NOW() incorrectly. Try this:

var sql = "INSERT INTO user_recipe_consumption (deviceID, totalEmission, recipeID , vegEmission,date_of_entry) VALUES ('" + deviceID + "','" + totEmission + "', ( select RecipeID, from RecipeEmissions where RecipeName = ?) , ( select vegEmission from RecipeEmissions where RecipeName = ? ) , NOW())";

If you mean now to be a JS variable then you can revert that part of the query to what you had originally, but it’s not clear what now is supposed to contain.