how to fetch multiple tables from multiple tables in a database using nodeJs Mysql ExpressJs?

Question

I need some help to find where the problem is. i want to fetch this tables from different queries (tables in Mysql), gestionEntreprise.ejs:

<tbody>
       <tr>
           <% data1.forEach(entry => { %>
           <th scope="row"><%= entry.IDdirection %></th>
           <td><%= entry.emailCompteDirecteur %></td>
       </tr>
<% }) %>
</tbody>
<tbody>
      <tr>
          <% data2.forEach(entry => { %>
          <th scope="row"><%= entry.IDsousDirection %></th>
          <td><%= entry.IDdirection %></td>
      </tr>
<% }) %>
</tbody>
...

i have this request in app.js:

app.get('/gestionEntreprise',(req,res)=>{
const sql1 = 'SELECT * FROM direction';
const sql2 = 'SELECT * FROM sousdirection';
const sql3 = 'SELECT * FROM departement';
const sql4 = 'SELECT * FROM equipe';
getConn().query(sql1, (err, fetchdirection)=>{
    if(err){
        console.log('Failed to query ', err);
        return;
    }
    getConn().query(sql2, (err, fetchsousdirection)=>{
        if(err){
            console.log('Failed to query ', err);
            return;
        }
        getConn().query(sql3, (err, fetchdepartement)=>{
            if(err){
                console.log('Failed to query ', err);
                return;
            }
            getConn().query(sql4, (err, fetchequipe)=>{
                if(err){
                    console.log('Failed to query ', err);
                    return;
                }
                res.render('gestionEntreprise',{data1: fetchdirection,data2: fetchsousdirection,data3: fetchdepartement,data4: fetchequipe});
            })
        })
    })
})
res.end();
});

but it show me this error:

Error [ERR_HTTP_HEADERS_SENT]: Cannot set headers after they are sent to the client

where is the problem, and how to fix it?

Firmino Changani · Accepted Answer · 2022-09-06 11:55:38Z

1

You are missing a return statement after sending the success response:

app.get("/gestionEntreprise", (req, res) => {
  const sql1 = "SELECT * FROM direction";
  const sql2 = "SELECT * FROM sousdirection";
  const sql3 = "SELECT * FROM departement";
  const sql4 = "SELECT * FROM equipe";
  getConn().query(sql1, (err, fetchdirection) => {
    if (err) {
      console.log("Failed to query ", err);
      return;
    }
    getConn().query(sql2, (err, fetchsousdirection) => {
      if (err) {
        console.log("Failed to query ", err);
        return;
      }
      getConn().query(sql3, (err, fetchdepartement) => {
        if (err) {
          console.log("Failed to query ", err);
          return;
        }
        getConn().query(sql4, (err, fetchequipe) => {
          if (err) {
            console.log("Failed to query ", err);
            return;
          }
          res.render("gestionEntreprise", {
            data1: fetchdirection,
            data2: fetchsousdirection,
            data3: fetchdepartement,
            data4: fetchequipe,
          });
          return; // Always end execution after sending a response
        });
      });
    });
  });
  res.end();
});

You can make your code more readable by making the following changes:

If your getConn() supports Promises, you can replace the callbacks you have with Promises
Given that you need all queries to succeed before sending a success response, you can use Promise.all() to execute/resolve all of your query promises somewhat at once;
Give more meaningful names to your query variables sql1 ---> queryDirections;
Wrap all your code under a try/catch block so that you can handle errors more easily;
Unless you need the data of all your tables columns, you could specify the exact columns you need to show: SELECT id, name FROM direction;;
Unless you need all data at once, you could limit the results to reduce your application load SELECT id, name FROM direction LIMIT 100;;

app.get("/gestionEntreprise", async (req, res) => {
  const queryDirections = "SELECT * FROM direction";
  const querySousDirections = "SELECT * FROM sousdirection";
  const queryDepartments = "SELECT * FROM departement";
  const queryEquipes = "SELECT * FROM equipe";

  try {
    // Get connection once
    const conn = getConn();

    // Techniques: Array destructuring and Promise resolving in batch
    const [directions, sousDirectios, departments, equipes] = await Promise.all(
      [
        conn.query(queryDirections),
        conn.query(querySousDirections),
        conn.query(queryDepartments),
        conn.query(queryEquipes),
      ]
    );

    res.render("gestionEntreprise", {
      data1: directions,
      data2: sousDirectios,
      data3: departments,
      data4: equipes,
    });
  } catch (error) {
    // Handle your error here
    console.log(error);
    res.end();
  }
});

Resources for further reading:

Promises - https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Promise
Array Destructuring - https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Operators/Destructuring_assignment

edited Sep 6, 2022 at 11:55

answered Sep 6, 2022 at 11:37

Firmino Changani

9798 silver badges11 bronze badges

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

5 Comments

Firmino Changani Over a year ago

@BilalBelli Right before the route handler ---> app.get("/gestionEntreprise", async (req, res) => {...

Bilal Belli Over a year ago

it show me this error: data1.forEach is not a function

Firmino Changani Over a year ago

Can you console.log() the results of each query and assure that they are actually arrays?

Bilal Belli Over a year ago

_ended: false, _timeout: undefined, _timer: Timer { _object: [Circular *1], _timeout: null }, sql: 'SELECT * FROM direction', values: undefined, ...... } ;; for just console.log(directions);

Bilal Belli Over a year ago

look, you guide me to the solution. thank you so much!

Bilal Belli · Accepted Answer · 2022-09-06 14:06:47Z

What happens is that data that I select from the database are not ready, and nodeJs is framework asynchronous, so I add setTimeout to sleep the rendering process.

gestionEntreprise.ejs

app.get('/gestionEntreprise',async (req,res)=>{
const queryDirections = "SELECT * FROM direction";
const querySousDirections = "SELECT * FROM sousdirection";
const queryDepartments = "SELECT * FROM departement";
const queryEquipes = "SELECT * FROM equipe";
try {
    // Get connection once
    const conn = getConn();
    // Techniques: Array destructuring and Promise resolving in batch
    let directions, sousDirectios, departments, equipes;
    await Promise.all(
    [
        conn.query(queryDirections,(err, rows)=>{directions=rows;} ),
        conn.query(querySousDirections,(err, rows)=>{sousDirectios=rows;}),
        conn.query(queryDepartments,(err, rows)=>{departments=rows;}),
        conn.query(queryEquipes,(err, rows)=>{equipes=rows;}),
    ]
    );
    setTimeout(() => {
    res.render("gestionEntreprise", {
    data1: directions,
    data2: sousDirectios,
    data3: departments,
    data4: equipes,
    });},100);
} catch (error) {
console.log(error);
res.end();
}
});

Collectives™ on Stack Overflow

how to fetch multiple tables from multiple tables in a database using nodeJs Mysql ExpressJs?

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related