How can I get the first row of a MySQL query in JavaScript?

Question

So I have this: And I just get an undefined when asking for the Address with getAddress( banned ); I want to save that row and use it into another function, but I can't due to that error.

function getAddress( banned ){
    let ip = [];
    connection.query(`SELECT Ips FROM sc_alts WHERE Name LIKE '${banned}'`, (error, rows) => {
        if (error) {
            throw error;
        }
        ip = rows[0].Ips;
    })
    return ip[0];
}

hi, perhaps check `if (rows.length === 0)` before using the result? — jspcal, May 24 '21 at 20:32
I mean the row has something, for example if I do a console.log(rows[0].Ips), it will show some data, but I can't save that, when I try that it says that its undefined. — BarraR3port, May 24 '21 at 20:34
You miss% in your SQL? `SELECT Ips FROM sc_alts WHERE Name LIKE '%${banned}%'`, Also, shouldn’t it be ip.push( rows[0].Ips) and return ip only. — ikhvjs, May 24 '21 at 20:35
This may be a dumb question but do you need a `LIKE` clause? If you're checking on a specific _ip address_ you'd probably want to check for a specific one such as `WHERE Name = '${banned}'` right? Is `banned` a specific subnet or partial address? — War10ck, May 24 '21 at 20:39
If `rows` is an array is `rows[0].Ips;` the value in that row/column? If so you should just `return ip;`? — 001, May 24 '21 at 20:40
So when I make a console.log("Ip: "+rows[0].Ips); the ip that I want is printed into the console, but I just can't save it in a var or a let to return it. I tried the push() function, but it didn't save or add the string into the let ip = []; — BarraR3port, May 24 '21 at 21:15

score 0 · Accepted Answer · answered May 24 '21 at 22:39

You are running with an async problem. When your function return the value (the line return ip[0]) the connection.query() hasn't been returned, because is an async function. So you are returning the value ip[0] without actually running the MySQL query, so the ip value stills equal to [] (the same when defined), so ip[0] is undefined.

For example, the same happend in here:

function getAddress(banned) {
    let ip = [];
    setTimeout(function() {
        ip = ["hi", "world"];
        console.log("IP value is now:" + ip[0])
    }, 2000);
    console.log("I am returning the IP value: " + ip[0])
    return ip[0];
}

getAddress()

With the following output:

I am returning the IP value: undefined
IP value is now: hi

As you can see, the return statment happens before the callback of the setTimeout which modified the ip variable, the same with your connection query function.

What you could do is to make your function async, and await for the results to complete on a synchronous way. I haven't tried this one in particular, but something like this should work:

async function getAddress( banned ){
    const rows= await connection.query(`SELECT Ips FROM sc_alts WHERE Name LIKE '${banned}'`)
    return rows[0].Ips;
}

See Node.js Using async/await with mysql for some reference

Thanks I end up by replanting the way I code my project, so I query in a different way using createPool();, but your comment was very useful to understand what was I doing! — BarraR3port, May 25 '21 at 01:54

How can I get the first row of a MySQL query in JavaScript?

1 Answers1