Not able to push SQL Query result to an array in node js

Question

I'm trying to push the results of an SQL query to an array. However, it doesn't seem to be working. There was no solution online that I could find. If anyone could help me with this, I would greatly appreciate it.

        let data = [];
        connection.query("SELECT DISTINCT referer FROM publisher", (error, results) => {
        if (error) {
            console.log(error);
        } else {
            results.forEach(async (result) => {
                let referer = result.referer;
                let today = new Date();
                for (let i = 0; i < 24; i++) {
                    let hour = i;
                    let start = new Date(today);
                    start.setHours(hour, 0, 0, 0);
                    let end = new Date(today);
                    end.setHours(hour, 59, 59, 999);
                    connection.query("SELECT COUNT(*) AS count FROM publisher WHERE referer = ? AND first_seen >= ? AND first_seen <= ?", [referer, start, end], (error, results) => {
                        if (error) {
                            console.log(error);
                        } else {
                            let count = results[0].count;
                            data.push({
                                referer: referer,
                                hour: hour,
                                count: count
                            });
                        }
                    });
                }
            });
        }
    });

when I log results inside query. It works fine. I'm not able to bring those results out of query. — Bu Fahad
– Bu Fahad, Commented Sep 29, 2022 at 8:17
okay, so count remain Undefined? have you tried using console.table it helps a lot with arrays and structures — Polpetta.-
– Polpetta.-, Commented Sep 29, 2022 at 8:22

Serg · Accepted Answer · 2022-09-29 08:34:28Z

2

You can try to use a single query to get data from MySql DB

select referer, hour(first_seen) h, count(*) n
from publisher
where date(first_seen) = curdate()
group by referer, hour(first_seen)
order by referer, hour(first_seen)

This way eliminating inner async method in js code.

answered Sep 29, 2022 at 8:34

Serg

22.8k5 gold badges23 silver badges50 bronze badges

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

3 Comments

Bu Fahad Over a year ago

Hi. Thank you. I now get error in console TypeError: Converting circular structure to JSON

Bu Fahad Over a year ago

let data = connection.query("SELECT referer, HOUR(first_seen) AS hour, COUNT(*) AS count FROM publisher WHERE date(first_seen) = CURDATE() GROUP BY referer, HOUR(first_seen)");         res.send(data);

Serg Over a year ago

Not sure about JS but my guess is you should use the .query method the way similar to OP, connection.query("SELECT ..", (error, results) => {..} and send result within a callback.

Collectives™ on Stack Overflow

Not able to push SQL Query result to an array in node js

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related