use nodejs to query mysql database

Question

I use mysql module nodejs-mysql I have two tables, Their struct is like this:

Table nicks

id   |nick   |
--------------
1    |Arnold |
2    |Bob    |

Table order

nick   |money |
---------------
Arnold |12    |
Arnold |43    |
Arnold |3     |
Bob    |32    |
Bob    |2     |

I want get a json object whose struct is like this:

[
   {id:1, nick:'Arnold', order:[{money:12},{money:43},{money:3}]},
   {id:2, nick:'Bob', order[{money:32},{money:2}]}
]

so what should I do?I use nodejs

what I have try:

   var mysql      = require('mysql');
   var connection = mysql.createConnection({
      host     : 'example.org',
      db       : 'db'
      user     : 'user',
      password : 'secret'
   });
   connection.connect();
   connection.query('select * from nicks',function(err,data){
       //here I travese the data array,and select the order table to get the money filed data.

   });

I know how to create a query with node.js, I just don't know a method to get the results I want.I don't know how to make a proper query.

You can't declare such object {money:12,money:43,money:3} in Javascript. Each of member a object should be unique. — user1902830
– user1902830, Commented Dec 26, 2013 at 7:01
What have you done? You say you use node-mysql but you just want the code without saying your problem. — Mustafa
– Mustafa, Commented Dec 26, 2013 at 8:55

robertklep · Accepted Answer · 2013-12-26 07:45:54Z

4

Here's another solution:

var mysql = require('mysql');
var conn  = mysql.createConnection({
  host      : 'localhost',
  database  : 'test',
});

var query = ' \
SELECT id, nicks.nick, GROUP_CONCAT(money) AS money \
FROM nicks, orders \
WHERE orders.nick = nicks.nick \
GROUP BY id';

conn.query(query, function(err, rows, fields) {
  rows.forEach(function(row) {
    row.order = row.money.toString().split(',').map(function(value) {
      return { money : Number(value) };
    });
    delete row.money;
  });
  // as an example, we'll print the object as JSON
  console.log(JSON.stringify(rows, null, 2));
});

answered Dec 26, 2013 at 7:45

robertklep

205k38 gold badges419 silver badges412 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Arnold Over a year ago

Thank you, It's a feasible method, but is there a way I can do it completely?

robertklep Over a year ago

What do you mean by 'completely'?

Arnold Over a year ago

I mean 'directly', is there a method that I can query the stucts I want without split strings?

robertklep Over a year ago

@Arnold I don't think MySQL supports that, so in that case you'd have to leave out the GROUP BY id clause and parse the results yourself.

kaushikb9 · Accepted Answer · 2013-12-26 07:36:07Z

Follow the documentation at https://github.com/felixge/node-mysql You need to setup a connection and query the db

var mysql      = require('mysql');
var connection = mysql.createConnection({
  host     : 'example.org',
  user     : 'bob',
  password : 'secret'
});

var queryString = "SELECT * FROM nicks n JOIN order o ON n.nick=o.nick";
connection.query(queryString, function(err, rows) {
  var outputJSON = [];
  for row in rows{
    outputJSON.push(row);
  }
  return outputJSON.toJSON()
});

You need to implement the function toJSON that formats your output by picking only the desired fields you need for you JSON

Did not answer the question of trying to get the joined rows into the single record row.

Collectives™ on Stack Overflow

use nodejs to query mysql database

2 Answers 2

4 Comments

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Related