Example select data in mysql with nodejs

 

Selecting From a Table

To select data from a table in MySQL, use the "SELECT" statement.

Example: Select all records from the "customers" table, and display the result object

 

var mysql = require('mysql');

var con = mysql.createConnection({

  host: "localhost",

  user: "root",

  password: "",

  database: "mydb"

});

con.connect(function(err) {

  if (err) throw err;

  //Select all customers and return the result object:

  con.query("SELECT * FROM customers", function (err, result, fields) {

    if (err) throw err;

    console.log(result);

  });

});

 

Run and result:

 

C:\Nodejs>node mysqlSelectAllData.js

[ RowDataPacket { id: 1, name: 'nam1', address: 'Highway 71' },

  RowDataPacket { id: 2, name: 'name2', address: 'Lowstreet 4' },

  RowDataPacket { id: 3, name: 'name3', address: 'Apple st 652' },

  RowDataPacket { id: 4, name: 'name4', address: 'Mountain 21' },

  RowDataPacket { id: 5, name: 'name5', address: 'Valley 345' },

  RowDataPacket { id: 6, name: 'name6', address: 'Ocean blvd 2' },

  RowDataPacket { id: 7, name: 'name7', address: 'Green Grass 1' },

  RowDataPacket { id: 8, name: 'name8', address: 'Sky st 331' },

  RowDataPacket { id: 9, name: 'name9', address: 'One way 98' },

  RowDataPacket { id: 10, name: 'name10', address: 'Yellow Garden 2' } ]

 

Selecting Columns

To select only some of the columns in a table, use the "SELECT" statement followed by the column name.

Example: Select name and address from the "customers" table, and display the return object

 

var mysql = require('mysql');

var con = mysql.createConnection({

  host: "localhost",

  user: "root",

  password: "",

  database: "mydb"

});

 

con.connect(function(err) {

  if (err) throw err;

  con.query("SELECT name, address FROM customers", function (err, result, fields) {

    if (err) throw err;

    console.log(result);

    console.log(result[2].address);

  });

});

 

Run and result:

 

C:\Nodejs>node  mysqlSelectColumnData.js

[ RowDataPacket { name: 'Codewr', address: 'Highway 37' },

  RowDataPacket { name: 'Codewr1', address: 'Highway 37' },

  RowDataPacket { name: 'Codewr2', address: 'Highway 37' },

  RowDataPacket { name: 'nam1', address: 'Highway 71' },

  RowDataPacket { name: 'name2', address: 'Lowstreet 4' },

  RowDataPacket { name: 'name3', address: 'Apple st 652' },

  RowDataPacket { name: 'name4', address: 'Mountain 21' },

  RowDataPacket { name: 'name5', address: 'Valley 345' },

  RowDataPacket { name: 'name6', address: 'Ocean blvd 2' },

  RowDataPacket { name: 'name7', address: 'Green Grass 1' },

  RowDataPacket { name: 'name8', address: 'Sky st 331' },

  RowDataPacket { name: 'name9', address: 'One way 98' },

  RowDataPacket { name: 'name10', address: 'Yellow Garden 2' } ]

Highway 37

 

The Fields Object

The third parameter of the callback function is an array containing information about each field in the result.

Example: Select all records from the "customers" table, and display the fields object

 

var mysql = require('mysql');

var con = mysql.createConnection({

  host: "localhost",

  user: "root",

  password: "",

  database: "mydb"

});

 

con.connect(function(err) {

  if (err) throw err;

  con.query("SELECT name, address FROM customers", function (err, result, fields) {

    if (err) throw err;

    console.log(fields);

    // get name of field

    console.log(fields[1].name);

  });

});

 

Run and result:

 

C:\Nodejs>node mysqlSelectFieldsObjectColumn.js

[ FieldPacket {

    catalog: 'def',

    db: 'mydb',

    table: 'customers',

    orgTable: 'customers',

    name: 'name',

    orgName: 'name',

    charsetNr: 33,

    length: 765,

    type: 253,

    flags: 0,

    decimals: 0,

    default: undefined,

    zeroFill: false,

    protocol41: true },

  FieldPacket {

    catalog: 'def',

    db: 'mydb',

    table: 'customers',

    orgTable: 'customers',

    name: 'address',

    orgName: 'address',

    charsetNr: 33,

    length: 765,

    type: 253,

    flags: 0,

    decimals: 0,

    default: undefined,

    zeroFill: false,

    protocol41: true } ]

address