forEach Loop not returning Array

Question

So, i seem to be having a problem with returning a value from my forEach MYSQL query loop in Node JS and then inserting this into an ejs template. Currently, these are my pieces of code:

app.js GET Request

app.get("/admin/stock", function(req, res) {
db.query("SELECT * FROM stock", function (err, stock) {
    if (err) {
        console.log("Error with showing SQL")
    } else {
        stock.forEach(function (stock) {
                db.query("SELECT * FROM product WHERE productID = " + 
stock.stock_productID, function (err, product) {
                    if (err) {
                        console.log("Error");
                    }
                    else {
                        stock.stock_productID = product[0].productName
                    }

                });
                db.query("SELECT * FROM currency WHERE currencyID = " + 
stock.stock_currencyID, function (err, currency) {
                    if (err) {
                        console.log("Error");
                    }
                    else {
                        stock.stock_currencyID = currency[0].currencyName
                    }

                });
                db.query("SELECT * FROM customer WHERE customerID = " + 
    stock.stock_customerID, function (err, customer) {
                    if (err) {
                        console.log("Error");
                    }
                    else {
                        stock.stock_customerID = 
    customer[0].customerBusiness;
                        console.log(stock);
                    }
                })
        });
    }
    console.log(stock);
    res.render("admin/stock", {stock:stock, userFName: req.user[1]})
})
});

Stock Page EJS

        <table class="generalTable">
            <tr>
                <th>Product Name</th>
                <th>Quantity</th>
                <th>Currency</th>
                <th>Cost</th>
                <th>Date Bought</th>
                <th>Bought From</th>
            </tr>
            <% stock.forEach(function(stock){ %>
            <tr>
                <td><%=stock.stock_productID%></td>
                <td><%=stock.quantityStockCurrent%></td>
                <td><%=stock.stock_currencyID%></td>
                <td><%=stock.priceBought%></td>
                <td><%=stock.boughtDate%></td>
                <td><%=stock.stock_customerID%></td>
            </tr>
            <% }) %>
        </table>

The First console.log of stock shows the replaced values, the second however, shows the original array values, and therefore the page is loaded in with the original values (IDs etc, instead of names).

How do I send the inner/edited array to the res.render instead of the original array?

Many Thanks, Brad

EDIT//////

By using JOINs, the output was correct

You really need to learn how LEFT JOIN and INNER JOIN work in SQL. Your code is the most inefficient way of retrieving information from a relational database. There is no use fixing this, please read about joins and rewrite your query. — Tomalak
– Tomalak, Commented Apr 27, 2018 at 13:46
THANK YOU SO MUCH, I was being foolish in forgetting about JOINs!! — SomeGenuineGuy
– SomeGenuineGuy, Commented Apr 27, 2018 at 13:58

luksch · Accepted Answer · 2018-04-27 11:23:22Z

0

I guess the database queries have not yet returned when you already render the stock object.

Remember, nodejs is asynchronous. You have to wait for all DB queries to come back before rendering the result.

I did have a similar problem when I first learned about nodejs: JavaScript nodejs mysql with queries in a loop

answered Apr 27, 2018 at 11:23

luksch

11.7k6 gold badges41 silver badges54 bronze badges

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

2 Comments

SomeGenuineGuy Over a year ago

Thanks for the information, i've tried working it similar to how you did and i just get errors, and cant get it to call the res.render

luksch Over a year ago

@SomeGenuineGuy write a new question on StackOverflow then if you get stuck. Don't forget to put in all stack traces and other relevant info. State what you tried and what you think. If you want to thank me, upvote this post!

Ali Asgher Badshah · Accepted Answer · 2018-04-27 13:53:54Z

0

The problem is that javascript is asynchronous and that Javascript doesn't have a block scope, which means that you render file before the loop complete so you should use simple for loop for this.

you should replace your code with this :-

app.get("/admin/stock", function(req, res) {
    db.query("SELECT * FROM stock", function (err, stocks) {
         var stock = JSON.parse(JSON.stringify(stocks));
        if (err) {
            console.log("Error with showing SQL")
        } else {
            for(var i = 0 ; i< stock.length; i++) {
                db.query("SELECT * FROM product WHERE productID = " +
                    stock[i].stock_productID, function (err, product) {
                    if (err) {
                        console.log("Error");
                    }
                    else {
                        stock[i].stock_productID = product[0].productName
                    }   

                });
                db.query("SELECT * FROM currency WHERE currencyID = " +
                    stock[i].stock_currencyID, function (err, currency) {
                    if (err) {
                        console.log("Error");
                    }
                    else {
                        stock[i].stock_currencyID = currency[0].currencyName
                    }

                });
                db.query("SELECT * FROM customer WHERE customerID = " +
                    stock[i].stock_customerID, function (err, customer) {
                    if (err) {
                        console.log("Error");
                    }
                    else {
                        stock[i].stock_customerID =
                            customer[0].customerBusiness;
                        console.log(stock);
                    }
                });
                if(i === stock.length-1){
                    console.log(stock);
                    res.render("admin/stock", {stock:stock, userFName: req.user[1]})
                }
            };



        }

    })
});

edited Apr 27, 2018 at 13:53

answered Apr 27, 2018 at 12:29

Ali Asgher Badshah

8612 gold badges12 silver badges34 bronze badges

7 Comments

SomeGenuineGuy Over a year ago

Thank you for the help, I now get this error: TypeError: Cannot set property 'stock_productID' of undefined Why would this be?

Ali Asgher Badshah Over a year ago

i think the stock which you get from stock table is empty thats ehy you get TypeError so you should console stock first to know what stock actually contain

SomeGenuineGuy Over a year ago

Stock is not empty once the query is finalised, how do I only run the for loop after the stock table is fully queried? Thanks

Ali Asgher Badshah Over a year ago

i edit the code try it out if you get similar problem send me the data which you get after consoling stock

SomeGenuineGuy Over a year ago

TypeError: Cannot set property 'stock_productID' of undefined at Query._callback app.js:292:50) -- This Equates to line 13 in your code snippet above

|

RajputManoj · Accepted Answer · 2018-04-27 14:10:40Z

0

You can use async.map for executing query in loop.

function getResults(id,cb){
  db.query("SELECT * FROM product WHERE productID = " +
            id.stock_productID, function (err, product) {
            if (err) {
                console.log("Error");
            }
            else {
                id.stock_productID = product[0].productName
            }   

        });  
}
async.mapLimit(stock, 5, function(id, callback) {
   getResults(id, function (err, res) {
    if (err) return callback(err);
    callback(null, res);
   })
}, function(err, results) {
// results is an array of names
});

answered Apr 27, 2018 at 14:10

RajputManoj

11 bronze badge

Collectives™ on Stack Overflow

forEach Loop not returning Array

3 Answers 3

2 Comments

7 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

7 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related