Node.js MySQL SELECT FROM query

Node.js MySQL SELECT FROM query

MySQL SELECT Query is used to select certain records from the table (and some properties if needed).

In this Node.js tutorial, we will use the following Node.js examples to learn how to access table data

• Example of MySQL SELECT FROM query

• Example of selecting only some columns

• Using MySQL SELECT FROM queryResultObject example

• Using MySQL SELECT FROM queryFieldsObject example

In this section's examples, we will use the following MySQL table [DATABASE: studentDB, table: studends]

studentsDB.students table

mysql> select　*　from students;　
+----------+--------+-------+
| name | rollno | marks |
+----------+--------+-------+
| John |　1　|　74　|
| Arjun |　2　|　74　|
| Prasanth |　3　|　77　|
| Adarsh |　4　|　78　|
| Raja |　5　|　94　|
| Sai |　6　|　84　|
| Ross |　7　|　54　|
| Monica |　8　|　86　|
| Lee |　9　|　98　|
| Bruce |　10　|　92　|
| Sukumar |　11　|　99　|
+----------+--------+-------+
11　rows in set (0.01　sec)

Example of MySQL SELECT FROM query

//　Node.js MySQL SELECT FROM query example
//　Include the mysql module
var mysql = require('mysql');　
　
//　Create a connection variable with the required detailed information
var con = mysql.createConnection({　
　　host: "localhost",　//　The IP address of the MySQL server running
　　user: "arjun"　//　The username for the mysql database
　　password: "password",　//　The corresponding password
　　database: "studentsDB"　//　Use the specified database
　});　
　
//　Establish a connection with the database.
con.connect(function(err) {　
　　if (err) throw err;　
　　//　If the connection is successful
　　con.query("SELECT　*　FROM students", function (err, result, fields) {　
　　　　//　If any error occurs during the execution of the above query, throw an error
　　　　if (err) throw err;　
　　　　//　If there is no error, you will get the result
　　　　console.log(result);　
　　});　
　});

//　Node.js MySQL SELECT FROM query example
//　Include the mysql module
var mysql = require('mysql');　
　
//　Create a connection variable with the required detailed information
var con = mysql.createConnection({　
　　host: "localhost",　//　The IP address of the MySQL server running
　　user: "arjun"　//　The username for the mysql database
　　password: "password",　//　The corresponding password
　　database: "studentsDB"　//　Use the specified database
　});　
　
//　Establish a connection with the database.
con.connect(function(err) {　
　　if (err) throw err;　
　　//　If the connection is successful
　　con.query("SELECT name,marks FROM students", function(err, result, fields) {　
　　　　//　If any error occurs during the execution of the above query, throw an error
　　　　if (err) throw err;　
　　　　//　If there is no error, you will get the result
　　　　console.log(result);　
　　});　
　});

selectUseResultObject.js-Access the row and column data of the result object

//　Node.js MySQL SELECT FROM query example
//　Include the mysql module
var mysql = require('mysql');　
　
//　Create a connection variable with the required detailed information
var con = mysql.createConnection({　
　　host: "localhost",　//　The IP address of the MySQL server running
　　user: "arjun"　//　The username for the mysql database
　　password: "password",　//　The corresponding password
　　database: "studentsDB"　//　Use the specified database
　});　
　
//　Establish a connection with the database.
con.connect(function(err) {　
　　if (err) throw err;　
　　//　If the connection is successful
　　con.query("SELECT　*　FROM students", function (err, result, fields) {　
　　　　//　If any error occurs during the execution of the above query, throw an error
　　　　if (err) throw err;　
　　　　//　If there is no error, you will get the result
　　　　//　Iterate over all rows in the result
　　　　Object.keys(result).forEach(function(key) {　
　　　　　　var row = result[key];　
　　　　　　console.log(row.name)　
　　　　});　
　　});　
　});

selectUseFieldsObject.js-Field usage example

　
//　Node.js MySQL SELECT FROM query example
//　Include the mysql module
var mysql = require('mysql');　
　
//　Create a connection variable with the required detailed information
var con = mysql.createConnection({　
　　host: "localhost",　//　The IP address of the MySQL server running
　　user: "arjun"　//　The username for the mysql database
　　password: "password",　//　The corresponding password
　　database: "studentsDB"　//　Use the specified database
　});　
　
//　Establish a connection with the database.
con.connect(function(err) {　
　　if (err) throw err;　
　　//　If the connection is successful
　　con.query("SELECT　*　FROM students", function (err, result, fields) {　
　　　　//　If any error occurs during the execution of the above query, throw an error
　　　　if (err) throw err;　
　　　　//　If there is no error, it means the field object
　　　　//　Traverse all rows in the field object
　　　　Object.keys(fields).forEach(function(key) {　
　　　　　　var field = fields[key];　
　　　　　　console.log(field)　
　　　　});　
　　});　
　});

$ node selectUseFieldsObject.js　
FieldPacket {　
　　catalog: 'def',　
　　db: 'studentsDB',　
　　table: 'students',　
　　orgTable: 'students',　
　　name: 'name',　
　　orgName: 'name',　
　　charsetNr:　33,　
　　length:　150,　
　　type:　253,　
　　flags: 0,　
　　decimals: 0,　
　　default: undefined,　
　　zeroFill: false,　
　　protocol41: true }　
FieldPacket {　
　　catalog: 'def',　
　　db: 'studentsDB',　
　　table: 'students',　
　　orgTable: 'students',　
　　name: 'rollno',　
　　orgName: 'rollno',　
　　charsetNr:　63,　
　　length:　11,　
　　type:　3,　
　　flags: 0,　
　　decimals: 0,　
　　default: undefined,　
　　zeroFill: false,　
　　protocol41: true }　
FieldPacket {　
　　catalog: 'def',　
　　db: 'studentsDB',　
　　table: 'students',　
　　orgTable: 'students',　
　　name: 'marks',　
　　orgName: 'marks',　
　　charsetNr:　63,　
　　length:　11,　
　　type:　3,　
　　flags: 0,　
　　decimals: 0,　
　　default: undefined,　
　　zeroFill: false,　
　　protocol41: true }

You can use the dot operator to access elements of the field object. For example, field.catalog, field.name, field.type, etc.

Conclusion:

In this Node.js tutorial – Node.js MySQL – Node.js MySQL SELECT FROM query, we learned how to retrieve records from the MySQL database and how to use the result object and field object.