CODEWITHSUNDEEP
mysqljsonnode.js
Rawan Hamdi
Rawan Hamdi

Reputation: 105

How to output result of a mysql query as a json file in node.js

What i have her is this 

app.post('/login', function (req, res){
  connection.connect();
  email = req.body.email;
  password = req.body.password;
  if(email && password ) {
    console.log(email);
    console.log(password);
   // connection.query('SELECT username FROM user WHERE email =? and password = ?', [email, password], 
   // function (error, rows, fields) { 
    connection.query('SELECT * FROM user ', 
    function (error, rows, fields) {
      var str='';
      for (var i = 0;i < rows.length; i++) {
        str = str + rows[i].username;
        res.end(str);
      }
      connection.end(); 
    }); 
  }
});

so instead of just displaying the result i want it to be something like this:

{
 "username": "ABC",
 "username": "DEF",
 "username": "HIJ"
}

Upvotes: 5

Views: 36603

Answers (2)

Shivkanth
Shivkanth

Reputation: 133

With Express 4.x, the output rows from mysql db is in json format.

For example, look at this

sqlConnect.connection.query('SELECT * from users', function(err, rows, fields){
		if (err)
		{
			console.log("Querying error");
		}
		else
		{
			console.log(rows);
		}
		sqlConnect.connection.end();
	});
});

The output is of the form 

[ RowDataPacket {
    id: 1,
    username: 'shivkanth',
    password: '1q2w3e4r',
    verified: 0 } ]

So now you can get the individual values using the . operator. For example, console.log(rows[0].username) will log the value 'shivkanth'

Upvotes: -3

Mike Perrenoud
Mike Perrenoud

Reputation: 67898

The first problem is you're not actually building the object you want. The second problem is you're not building an array of them. Finally, you need to convert that array into JSON if the res object doesn't have something like res.json.

var objs = [];
for (var i = 0;i < rows.length; i++) {
    objs.push({username: rows[i].username});
}
connection.end();
res.end(JSON.stringify(objs));

Upvotes: 11

Related Questions