how to get all the rows in recursive function calling in mysql2 and Nodejs?

Question

i am new in Javascript. I am trying to get all row and their parent rows with promise and async await

here is my code

const e = require('express')
const mysql2 = require('mysql2')


const pool = mysql2.createPool({
  host: '127.0.0.1',
  user: 'root',
  password: '',
  database: 'dreamselling',
  waitForConnections: false,
  connectionLimit: 10,
  queueLimit: 0
})

let levels=[];
const getLevel = (id,levels) => {
  return new Promise((resolve, reject) => {
    let sql = `select id,memberName,parent_id from member_table where id=${id}`;
    pool.getConnection((error, connection) => {
      connection.query(sql, (err, rows) => {
        if (rows && rows[0].parent_id != null) {
          // console.log(rows[0]);
          levels.push((rows[0]))
          getLevel(rows[0].parent_id,levels)

        } else {
          pool.end()
        }
      })
    })
  })

}

async function myfunc() {
  let a = await getLevel(7)
  console.log(levels);
  // console.log('level',a);
}
myfunc()

i can see the rows with console.log(rows[0]) but cannot return from the function i need help about this

Why not use MySQL2's promise wrapper so you can use async/await for your queries? That would at least help you get out of callback-hell. — robertklep
– robertklep, Commented Aug 13, 2022 at 6:57

lpizzinidev · Accepted Answer · 2022-08-13 07:05:15Z

1

Try to use async await as suggested by @robertklep.
Also, make sure that you use a prepared statement:

const e = require('express');
const mysql2 = require('mysql2');

const pool = mysql2.createPool({
  host: '127.0.0.1',
  user: 'root',
  password: '',
  database: 'dreamselling',
  waitForConnections: false,
  connectionLimit: 10,
  queueLimit: 0,
});

let levels = [];

const getLevel = async (id, levels) => {
  const promisePool = pool.promise();
  const [rows, fields] = await promisePool.query(
    'select id,memberName,parent_id from member_table where id = ?',
    [id]
  );
  if (rows && rows[0].parent_id !== null) {
    levels.push(rows[0]);
    getLevel(rows[0].parent_id, levels);
  } else {
    pool.end();
  }
};

function myfunc() {
  getLevel(7);
  console.log(levels);
}

myfunc();

answered Aug 13, 2022 at 7:05

lpizzinidev

13.3k2 gold badges15 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

how to get all the rows in recursive function calling in mysql2 and Nodejs?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related