CODEWITHSUNDEEP
mysqlsqlnode.js
Bu Fahad
Bu Fahad

Reputation: 92

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

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

Upvotes: 1

Views: 168

Answers (1)

Serg
Serg

Reputation: 22811

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.

Upvotes: 2

Related Questions