Passing promises with mySQL & nodejs

Question

I am having an issue with accessing a promise from a function containing multiple MySQL queries. I need to ask multiple questions and need the answer choices to update dynamically from what is currently in the DB (this is working).

I need to pass the empId variable to the final query but am getting an error that "empID is not defined". When I console.log "employee" and "newrole" I receive objects { whichemp: '1 Eric Scott' } { newrole: '1 Senior Engineer' }

I've tried all I could think of but am hitting a wall. I appreciate any feedback.

function empUpRole() {
connection.query("SELECT * FROM employee", (err, res) => {
    if (err) throw err;
    inquirer.prompt([
        {
            type: "list",
            message: "Which employee's role would you like to update?",
            name: "whichemp",
            choices: res.map(res => res.id + " " + res.first_name + " " + res.last_name)
        }
    ]).then(employee => {
        let empId = employee.id

        connection.query("SELECT * FROM role", (err, res) => {
            if (err) throw err;
            inquirer.prompt([
                {
                    type: "list",
                    message: "What is the employee's new role?",
                    name: "newrole",
                    choices: res.map(res => res.id + " " + res.title)
                }
            ]).then(newrole => {
                let roleId = newrole.id
                console.log(employee, newrole)
                let query = connection.query("UPDATE employee SET role_id = ? WHERE id = ?",
                    [roleId, empID],
                    (err, res) => {
                        if (err) throw err;
                    }
                );
                start();
            });
        });
    });
});

Solution:

function empUpRole() {
    connection.query("SELECT * FROM employee", (err, res) => {
        if (err) throw err;
        inquirer.prompt([
            {
                type: "list",
                message: "Which employee's role would you like to update?",
                name: "whichemp",
                choices: res.map(res => res.id + " " + res.first_name + " " + res.last_name)
            }
        ]).then(employee => {
            let empId = employee.whichemp.split(' ')[0];

            connection.query("SELECT * FROM role", (err, res) => {
                if (err) throw err;
                inquirer.prompt([
                    {
                        type: "list",
                        message: "What is the employee's new role?",
                        name: "newrole",
                        choices: res.map(res => res.id + " " + res.title)
                    }
                ]).then(newrole => {
                    let roleId = newrole.newrole.split(' ')[0];
                    console.log(empId, roleId)
                    console.log(employee, newrole)
                    console.log(newrole.id, employee.id)

                    let query = connection.query("UPDATE employee SET role_id = ? WHERE id = ?",
                        [roleId, empId],
                        (err, res) => {
                            if (err) throw err;
                        }
                    );
                    start();
                });
            });
        });
    });

user_13675289 · Answer

You need to replace empID with empId. because you are use empID variable and it's not exist.

I think the issue because inquirer return choice string which like${employee.id} ${employee.first_name} ${employee.last_name} so you can't get employee.id. so I'm using employee.selectedValue.split(' ')[0] to get id.

To access inquirer.prompt() you should use result.xxxx which xxxx is the name which you passed it to inquirer.prompt().

employee.selectedValue.split(' ') will split ${employee.id} ${employee.first_name} ${employee.last_name} to array like [employee.id, employee.first_name, employee.last_name] then I select index 0 to get the id.

To know more about inquirer go to here. or see this example.

Example.

const inquirer = require('inquirer');

const main = async () => {
    const result = await inquirer.prompt([
        {
            type: 'list',
            message: "Which employee's role would you like to update?",
            name: 'selectedValue',
            choices: [
                'id1 ahmed1 elmetwally1',
                'id2 ahmed2 elmetwally2',
                'id3 ahmed3 elmetwally3',
            ],
        },
    ]);

    // if you choise 1
    // result will be {selectedValue: "id1 ahmed1 elmetwally1"}

    // to get id 
    const id = result.selectedValue.split(' ')[0];
    console.log(id)
};

main();

Solution.

const updateEmployeeRole = async () => {
  // get employees and choice one
  const employees = await connection.query('SELECT * FROM employee');
  console.log({ employees });
  const employee = await inquirer.prompt([
    {
      type: 'list',
      message: "Which employee's role would you like to update?",
      name: 'selectedValue',
      choices: employees.map(
        employee =>
          `${employee.id} ${employee.first_name} ${employee.last_name}`,
      ),
    },
  ]);
  console.log({ employee });
  const employeeId = employee.selectedValue.split(' ')[0];

  // get roles and choice one
  const roles = await connection.query('SELECT * FROM role');
  console.log({ roles });
  const newRole = inquirer.prompt([
    {
      type: 'list',
      message: "What is the employee's new role?",
      name: 'selectedValue',
      choices: roles.map(role => `${role.id} ${role.title}`),
    },
  ]);
  console.log({ newRole });
  const roleId = newRole.selectedValue.split(' ')[0];

  // update employee role
  const updateResult = await connection.query(
    'UPDATE employee SET role_id = ? WHERE id = ?',
    [roleId, employeeId],
  );
  console.log({ updateResult });

  return Promise.resolve();
};

updateEmployeeRole()
  .then(() => {
    console.log('updateEmployeeRole() is Done. Now will run start()');
    start();
  })
  .catch(err => {
    console.error('Something Error ', { err });
  });

Passing promises with mySQL & nodejs

1 Answers