如何从 node.js 中的 mysql SELECT 查询返回值

How to return a value from a mysql SELECT query in node.js

提问by redAce

I'm still very new to Node.js, and i'm trying to understand how callbacks work. So, here is my problem :

我对 Node.js 还是很陌生，我正在尝试了解回调是如何工作的。所以，这是我的问题：

I should've put more code :

我应该放更多的代码：

POST :

邮政 ：

app.post('/register', function(req, res) {

    //get data from the request
    var data = {
        username: req.body.username,
        email: req.body.email,
        password: req.body.password
    };

    function fetchID(callback) {
        connection.query('SELECT id_user FROM USERS WHERE username = ?', data.username, function(err, rows) {
            if (err) {
                callback(err, null);
            } else 
                callback(null, rows[0].id_user);
        });
    }
    var user_id;
    fetchID(function(err, content) {
        if (err) {
            console.log(err);
            return next("Mysql error, check your query");
        } else {
            user_id = content;
            console.log(user_id); //undefined
        }
    });

    console.log(user_id); //undefined
    var payload = {
        iss: req.hostname,
        sub: user_id
    }
    console.log(payload.sub); //correct id
})

GET :

得到 ：

app.get('/todos', function(req, res) {
    if (!req.headers.authorization) {
        return res.status(401).send({
            message: 'You are not authorized !'
        });
    }

    var token = req.headers.authorization.split(' ')[1];
    var payload = jwt.decode(token, "shhh..");

    //additional level of security
    console.log('sub id is : ' + payload.sub); //undefined
    if (!payload.sub) {
        return res.status(401).send({
            message: 'Authentication failed !'
        });
    }

})

I commented each console.log to be more clear. I need to get the correct id when i check for if (!payload.sub)in app.get()

我评论了每个 console.log 以使其更加清晰。当我if (!payload.sub)在 app.get() 中检查时，我需要获得正确的 id

回答by Piyas De

Your two functions should be something like -

你的两个功能应该是这样的 -

function fetchID(data, callback) {
        connection.query('SELECT id_user FROM USERS WHERE username = ?', data.username, function(err, rows) {
            if (err) {
                callback(err, null);
            } else 
                callback(null, rows[0].id_user);
        });
}

and then

进而

var user_id;

fetchID(data, function(err, content) {
    if (err) {
        console.log(err);
        // Do something with your error...
    } else {
        user_id = content;
    }
});

Here in the callback function, the returned variable contentwill hold the value for user_id.

在回调函数中，返回的变量content将保存 的值user_id。

EDIT

编辑

I have not solved the exact problem as you had described above.

我还没有解决你上面描述的确切问题。

But in following example, I have shown that, the callback mechanism is working -

但在下面的例子中，我已经表明，回调机制是有效的 -

First (Table creation and insert some dummy data)-

首先（创建表并插入一些虚拟数据）-

use test;
create table users (id int(11) primary key,username varchar(100));
insert into users values(1, "John");
insert into users values(2, "Sham");

Now I have made your post method as get and tested in browser.

现在我已将您的 post 方法设置为 get 并在浏览器中进行了测试。

Following is the full class tested in my localhost -

以下是在我的本地主机中测试的完整课程 -

var application_root = __dirname,
    express = require("express"),
    mysql = require('mysql');
var app = express();
var connection = mysql.createConnection({
  host : 'localhost',
  user : 'root',
  password : 'admin',
  database: "test"
});
app.get('/getuser', function(req, res) {
  //get data from the request
    var data = {
        username: req.query.username
    };
    function fetchID(data, callback) {
        connection.query('SELECT id FROM users WHERE username = ?',        
               data.username, function(err, rows) {
            if (err) {
                callback(err, null);
            } else 
                callback(null, rows[0].id);
        });
    }
    var user_id;
    fetchID(data, function(err, content) {
        if (err) {
        console.log(err);
        res.send(err);  
        // Do something with your error...
        } else {
        user_id = content;
        console.log(user_id);
        res.send("user id is -" + user_id);
        }
    });
})
app.listen(1212);

Now these requests will produce this output - http://127.0.0.1:1212/getuser?username=john=> user id is -1and http://127.0.0.1:1212/getuser?username=sham=> user id is -2

现在这些请求将产生这个输出 - http://127.0.0.1:1212/getuser?username=john=>user id is -1和 http://127.0.0.1:1212/getuser?username=sham=>user id is -2

Hope this code example will help you to understand the callback in node.js.

希望这个代码示例能帮助你理解 node.js 中的回调。

Thanks

谢谢