MY SQL 的查询问题
mysql.query('SELECT accountID,userID,notes FROM User WHERE notes = "'+info.uid+'"', function(err, fields) {
var res = fields[0];
if (fields.length > 0){
var acc = res['accountID'];
var user = res['userID'];
//query grouplist
mysql.query('SELECT accountID,userID,groupID FROM GroupList WHERE accountID = "'+acc+'" AND userID = "'+user+'"', function(err, fields) {
var res = fields[0];
if (fields.length > 0){
var acc = res['accountID'];
var grouplist = res['groupID'];
//console.log(acc)
//console.log(grouplist)
//query devicelist
mysql.query('SELECT accountID,deviceID FROM DeviceList WHERE accountID = "'+acc+'" AND groupID = "'+grouplist+'"', function(err,rows,fields) {
if (rows.length > 0){
for(var i=0;i<rows.length;i++){
var deviceID = rows[i].deviceID
mysql.query('SELECT accountID,deviceID,statusCode,longitude,latitude,speedKPH,heading,timestamp FROM EventData WHERE accountID = "'+acc+'" AND deviceID = "'+deviceID+'" ORDER BY timestamp DESC ', function(err,rows,fields) {
if (rows.length > 0){
for(var i=0;i<rows.length;i++){
var accountID = rows[i].accountID;
var deviceID = rows[i].deviceID;
var statusCode = rows[i].statusCode;
var longitude = rows[i].longitude;
var latitude = rows[i].latitude;
var speedKPH = rows[i].speedKPH;
var heading = rows[i].heading;
var timestamp = rows[i].timestamp;
var accountID1 = rows[i].accountID;
var deviceID1 = rows[i].deviceID;
var statusCode1 = rows[i].statusCode;
var longitude1 = rows[i].longitude;
var latitude1 = rows[i].latitude;
var speedKPH1 = rows[i].speedKPH;
var heading1 = rows[i].heading;
var timestamp1 = rows[i].timestamp
re2.push({
title: deviceID,
pic: 'http://ditu.google.cn/maps/api/staticmap?markers='+latitude+','+longitude+'&zoom=15&size=512x320',
url: 'https://github.com/node-webot/webot-example',
description: accountID+ '\n' +
deviceID+ '\n' +
statusCode+ '\n' +
unixtime2YYMMDD(timestamp)+ '\n' +
unixtime2hhmmss(timestamp)
});
console.log(re2)
console.log("aaaaaaaaaaaaaaaaaaaa")
return next(null,re2)
}
}
}
)// end query eventdata mysql
}//deviceID end
}
})// end query devicelist
}
})// end query grouplist
}
else{
next(null, "你未绑定帐号,请先绑定你")
}
}) //mysql
======================================= 以上是NODE 的微信公众号查询GPS 监控系统数据的一部分, 1:查询 User 表格,获得 accountID userID notes 2:查询 Grouplist 获得groupid 3:查询Devicelist,获得deviceID 4:查询Eventdata,获得GPS的定位数据 由于在
mysql.query('SELECT accountID,deviceID FROM DeviceList WHERE accountID = "'+acc+'" AND groupID = "'+grouplist+'"', function(err,rows,fields) {
if (rows.length > 0){
for(var i=0;i<rows.length;i++){
var deviceID = rows[i].deviceID
是历遍 DeviceList 所以最后一步,获得的数据是
[{titel:device1,pic:.... }]
aaaaaaaaaaaaaaaaaaaa
[{titel:device2,pic:.... }]
aaaaaaaaaaaaaaaaaaaa
[{titel:device3,pic:.... }]
但我需要获得
[{titel:device1,pic:.... },
{titel:device2,pic:.... },
{titel:device3,pic:.... }]
aaaaaaaaaaaaaaaaaaaa