CODEWITHSUNDEEP
javascriptmysqlnode.js
Jaskirat Singh
Jaskirat Singh

Reputation: 82

A simple Nodejs MySQL query

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.

Upvotes: 0

Views: 1303

Answers (3)

Shilly
Shilly

Reputation: 8589

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.
} );

Upvotes: 0

Guillaume Georges
Guillaume Georges

Reputation: 4020

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;

Upvotes: 1

acdcjunior
acdcjunior

Reputation: 135862

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);
});

Upvotes: 2

Related Questions