Node.js: Unable to store SQL query results in an array

Question

I'm trying to push the results of a SQL query to an array. However, it doesn't seem to work. I can't find a solution online. If anyone can help me solve this problem I would be very grateful.

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
                            });
                        }
                    });
                }
            });
        }
    });

Answer 1

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

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 eliminates internal asynchronous methods in js code.