mysql - 在 Node.JS 中使用更新查询来执行算术计算（从起点扣除，信用目标）

Question

我有个问题。所以我想实现一些东西，比如我借记一个账户并贷记另一个账户。我Node.js用来继续开发 REST api

我的代码看起来是这样的：

app.post('/api/transfer', function(req,res){
    var originAcc = req.body.originAcc;
    var destAcc = req.body.destAcc;
    var amt = req.body.amt;

    dbConn.query('UPDATE financeplusacct SET bal = bal-'+amt+' WHERE accNum =?',[bal,amt,originAcc],function(error,results,fields){
        if(error) throw error;
    });

    dbConn.query('UPDATE financeplusacct SET bal = bal+'+amt+' WHERE accNum =?',[bal,amt,destAcc],function(error,results,fields){
        if(error) throw error;
        return res.send({ error: false, data: results, message: 'Executed!' }); 
    });
});

它告诉我 Bal 没有定义，我的数据库中有一个列，其中有名称 bal。有什么我想念的吗？

编辑

现在它正确地获得了余额，但它没有正确地借记和贷记

代码如下所示：

var express = require('express');
var app = express();
var bodyParser = require('body-parser');
var mysql = require('mysql');
var path = require('path');
var port = process.env.PORT || 3000;


app.use(bodyParser.json());
app.use(bodyParser.urlencoded({
    extended:true
}));

app.get('/api/',function(req , res){
    return res.send({error: false,message: 'hello'})
});

var dbConn = mysql.createPool({
    host: 'localhost',
    user: 'root',
    password: '',
    database: 'financeplusdb'
});

module.exports = dbConn;

function getAccountBalance(accNum){
    dbConn.query('SELECT * FROM financeplusacct WHERE accNum =?',accNum,function(error,results,fields){
        if (error) throw error;
        var ledgerBal = results[0].bal;
       
        return ledgerBal;
    })
}

//Functions
app.post('/api/createaccount',function(req,res){

var prologue = '00343';
var digits = Math.floor(Math.random() * 9000000000) + 1000000000;
var accNum = prologue + digits;

var fullname = req.body.fullname;
var address = req.body.address;
var city = req.body.city;
var state = req.body.state;
var tel = req.body.tel;
var email = req.body.email;
var nationalID = req.body.nationalID;
var gender = req.body.gender;
var birth_date = req.body.birth_date;
var bal = req.body.bal;
var ccy = req.body.ccy;


dbConn.query("INSERT INTO financeplusacct (fullname, address, city,state, tel,email,nationalID,gender,birth_date, bal, ccy, accNum) VALUES (?,?,?,?,?,?,?,?,?,?,?,?)",[fullname, address, city,state, tel,email,nationalID,gender,birth_date, bal, ccy, accNum], function (error, results, fields){
    if (error) throw error;
    return res.send({error: false,data: results, message: 'Account setup Complete'})
    });
});


app.get('/api/getaccounts',function(req,res){
    dbConn.query('SELECT * FROM financeplusacct',function(error, results, fields){
        if (error) throw error;
        return res.send({ error: false, data: results, message: 'users list' });
    });
});


app.get('/api/getaccounts/:accNum', function(req,res){
    let account_num = req.params.accNum;
    
    if(!account_num){
        return res.status(400).send({error: true, message: 'Please provide accNum'});
    }
    dbConn.query('SELECT * FROM financeplusacct WHERE accNum =?', account_num,function(error, results, fields){
        if(error) throw error;
        return res.send({ error:false, data: results[0], message: 'users List.' });
    });
});



app.post('/api/transfer', function(req,res){
    var originAcc = req.body.originAcc;
    var destAcc = req.body.destAcc;
    var amt = req.body.amt;
    var bal = getAccountBalance(originAcc);

    dbConn.query('UPDATE financeplusacct SET bal = bal-'+amt+' WHERE accNum =?',[bal,amt,originAcc],function(error,results,fields){
        if(error) throw error;
        //return res.send({ error: false, data: results, message: 'Executed!' }); 
    });

    dbConn.query('UPDATE financeplusacct SET bal = bal+'+amt+' WHERE accNum =?',[bal,amt,destAcc],function(error,results,fields){
        if(error) throw error;
        return res.send({ error: false, data: results, message: 'Executed!' }); 
    });
});

/*app.post('/api/updatebal',function(req,res){

    var bal = req.body.bal;
    var accNum = req.body.accNum;

    dbConn.query('UPDATE financeplusacct SET bal =? WHERE accNum =?',[bal,accNum],function(error,results,fields){
        if (error) throw error;
        return res.send({ error: false, data: results, message: 'users list' });
    });
});
*/


app.listen(port,function(){
    console.log('App running on Port: '+port);
});

module.exports = app;

编辑 2

试图在代码中进行少量计算显示 NAN

新代码是这样看的：

app.post('/api/transfer', function(req,res){
    var originAcc = req.body.originAcc;
    var destAcc = req.body.destAcc;
    var amt = req.body.amt;
    var bal = getAccountBalance(originAcc);
    var destBal = getAccountBalance(destAcc);

    let fromBal = bal - amt;
    let newBal = destBal + amt;

    console.log(fromBal);
    console.log(newBal);

    dbConn.query('UPDATE financeplusacct SET bal = bal-'+amt+' WHERE accNum =?',[bal,amt,originAcc],function(error,results,fields){
        if(error) throw error;
        //return res.send({ error: false, data: results, message: 'Executed!' }); 
    });

    dbConn.query('UPDATE financeplusacct SET bal = bal+'+amt+' WHERE accNum =?',[bal,amt,destAcc],function(error,results,fields){
        if(error) throw error;
        return res.send({ error: false, data: results, message: 'Executed!' }); 
    });
});

标签： mysqlsqlnode.js