A simple Nodejs MySQL query

Question

I am trying to use mysql with nodejs: trying to run the query: select count(*) from TABLE. The result comes out as

[ RowDataPacket { 'count(*)': 33 } ]

I only want 33 as the answer, the javascript skeleton code is as follows

var express = require('express');
var router = express.Router();
var mysql = require('mysql');

var con = mysql.createConnection({
  host: "localhost",
  user: "honolulu",
  password: "honolulu",
  database: "db"
});


con.connect(function(err) {
  if (err) throw err;
  console.log("Connected to question database file 1!");
});
var but=20
con.query('select count(*) from qpsc',function (err, result) {
    if (err) throw err;
    but=result;
    console.log(but);
  });
/* GET home page. */
router.get('/', function(req, res, next) {
  res.render('index', { title: 'Express' });
});
router.get('/file', function(req, res, next) {
  res.render('index2',{ num_buttons: '20'});
  // console.log("RENDERED FROM INDEX.js");
});

module.exports = router;

So, what should I do ? Please help.

acdcjunior · Accepted Answer · 2017-12-27 16:11:06Z

2

Since the result is:

[ RowDataPacket { 'count(*)': 33 } ]

You can get 33 by:

var theCount = result[0]['count(*)'];

So, your code adapted, it would be:

con.query('select count(*) from qpsc',function (err, result) {
  if (err) throw err;
  but = result[0]['count(*)'];
  console.log(but);
});

answered Dec 27, 2017 at 16:11

acdcjunior

136k37 gold badges340 silver badges312 bronze badges

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

Comments

Guillaume Georges · Accepted Answer · 2017-12-27 16:13:21Z

1

Your result variable is an array of objects, with a key for each field selected in your query.

You will probably find your count in the result[0]["count(*)"] entry but that's kind of ugly.

I would suggest using an alias in your SQL query to make it more elegant :

select count(*) as many from qpsc

That way, you can retrieve your count like this :

var yourCount = result[0].many;

answered Dec 27, 2017 at 16:13

Guillaume Georges

4,0204 gold badges17 silver badges34 bronze badges

Comments

Shilly · Accepted Answer · 2017-12-27 16:19:58Z

If you rename the fields in the SQL query, you can access those as properties. So if you use "select count(*) AS 'result' from TABLE";, you can access 33 as row.result or any other name you use inside the SQL query.

So what I usually do, is alias all the SQL fields I request, then push all the rows to an array, and then just map over that array with Object.assign to turn all the RowDataPackets into plain objects.

I use the following structure to use the mysql module:

const NOOP = () => {};
const server = {
  "host" : "localhost",
  "user" : "honolulu",
  "password" : "honolulu",
  "database" : "db"
};
const connection = MYSQL.createConnection( server );
connection.connect( error => {
    if ( error ) {
        // handle error
    }
    // else NOOP();
} );
const sql = "select count(*) AS 'result' from TABLE";
const query = connection.query( sql );
const resultSet = [];
query.on( 'error', error => {
    // handle query error
} );
// Incoming field packets, if any, preceding the resultSet rows. No operation.
query.on( 'fields', NOOP );
// Every time a row gets returned, this handler is triggered.
query.on( 'result', row => resultSet.push( row ) );
// All rows have been returned
query.on( 'end', () => {
    // do something with the resultSet.
} );

Collectives™ on Stack Overflow

A simple Nodejs MySQL query

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related