Question 112710 in Mysql Question posted in
The official MySQL documentation can be found here.

NodeJS/MySQL: Use Results of Previous SELECT Query?

Building a simple ToDo app in React/NodeJS/Express with MySQL. Users join a group ("family" in the code), then tasks are viewable filtered by familyId. To create a task, I first have a query that finds the user’s familyId from the Users table, then I want to include that familyId value in the subsequent INSERT query for creating the task row in the Tasks table. My task.model.js is below:

const sql = require("./db.js");

// constructor
const Task = function(task) {
    this.title = task.title;
    this.familyId = task.familyId;
    this.description = task.description;
    this.completed = task.completed;
    this.startDate = task.startDate;
    this.userId = task.userId;
};

Task.create = (task, result) => {
    sql.query("SELECT familyId FROM users WHERE userId = ?", task.userId, (err, res) => {
        if (err) {
            console.log("Error selecting from USERS: ", err);
            return result(err, null);
        }
        sql.query("INSERT INTO tasks (familyId, title, description, completed, startDate) VALUES (?,?,?,?,?)", [result, task.title, task.description, task.completed, task.startDate], (err, res) => {
            if (err) {
                console.log("Error inserting in TASKS: ", err);
                return result(err, null);
            }
        })
        console.log("created task: ", { id: res.insertId, ...task });
        return result(null, { id: res.insertId, ...task });
    });
};

However, I cannot figure out how to properly use the familyId result of the SELECT query as a parameter in the suqsequent INSERT query. I know the overall syntax works because I can manually plug in an ID value as a parameter and the entire operation completes successfully – I just need to know how to use the resule of the first query in the next.

Tags:

2

Answers


  1. 0

    The way you are using it should work but the problem is you have defined the callback as res but are passing result in the 2nd sql query

    sql.query("SELECT familyId FROM users WHERE userId = ?", task.userId, (err, res) => {
    if (err) {
        console.log("Error selecting from USERS: ", err);
        return result(err, null);
    }
    //res should have the value for the familyId of the given user so in next line pass res not result
    sql.query("INSERT INTO tasks (familyId, title, description, completed, startDate) VALUES (?,?,?,?,?)", [res[0].familyId, task.title, task.description, task.completed, task.startDate], (err, res) => {
        if (err) {
            console.log("Error inserting in TASKS: ", err);
            return result(err, null);
        }
    })
    console.log("created task: ", { id: res.insertId, ...task });
    return result(null, { id: res.insertId, ...task });
});
    Login or Signup to reply.
  2. 0

    SQL returns array in result , so use result[0] to get first Object ,
    and then access the object key by result[0].keyName

    Task.create = (task, result) => {
        sql.query("SELECT familyId FROM users WHERE userId = ?", task.userId, (err, users) => {
            if (err) {
                console.log("Error selecting from USERS: ", err);
                return result(err, null);
            }

            let familyId = users && users[0] ? users[0].familyId : null;

            sql.query("INSERT INTO tasks (familyId, title, description, completed, startDate) VALUES (?,?,?,?,?)", [familyId, task.title, task.description, task.completed, task.startDate], (err, res) => {
                if (err) {
                    console.log("Error inserting in TASKS: ", err);
                    return result(err, null);
                }
            })
            console.log("created task: ", { id: res.insertId, ...task });
            return result(null, { id: res.insertId, ...task });
        });
    };
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search