Node.js MySQL IN

In this post, we will discuss how to return rows based on the values specified inside the IN operator from an MYSQL table from XAMPP Server using Node.js Script.

It is important to install mysql package in node.js.

Command to install the mysql package:

npm install mysql
				

IN Operator:

IN Operator in MySQL is used to return rows based on values specified inside in it. IN is specified after WHERE Clause as a condition. In other words, it will filter the records from the table. So each value is separated by comma - (value1,value2,....)

Steps:

Now let's see steps

1. First start your XAMPP Server (Both Apache and MySQL).
2. Open Notepad or any text-editor and write the Node.js script
3. In that script, first we have to load the mysql package using the below syntax
var mysql_package = require('mysql');
4. Create the connection using the server,username and password.
Copied

var connection_data = mysql_package.createConnection({
  host: "localhost",
  user: "root",
  password: "",
  database:"database_name"
});

5. Write the sql query that uses IN Operator. It will take two parameters. The first parameter is the SQL Query and the second parameter will handle the result
Copied

connection_data.connect(function(error) {
connection_data.query("SELECT column/s FROM table_name 
WHERE column IN (value1,value2,...))", 
function (error, result) {
 
    console.log(result);
  });
});

6. Now type the following command in your command prompt to run the script.
node file_name.js

It will return the records one by one in the following format:

[
  RowDataPacket {
    column1: value,
    column2: value,
    .....,
	.....
},

 RowDataPacket {
    column1: value,
    column2: value,
    .....,
	.....
},
.....
.....

Consider the village table with the following records:

IN Example 1:-

Node.js Script

1. To select rows which are delhi and Kakumanu from village_name column.
2. To select rows which are delhi and Kakumanu from village_name column.

// Load the mysql package
var mysql_package = require('mysql');

// Create the connection using the server,username and password.
//In my scenario - server is the localhost,
//username is root,
//password is empty.
//database is facility
var connection_data = mysql_package.createConnection({
  host: "localhost",
  user: "root",
  password: "",
  database:"facility"
});


connection_data.connect(function(error) {
	

//Query to select rows which are delhi and Kakumanu from village_name column.
  connection_data.query("SELECT * FROM village 
  WHERE village_name IN ('delhi','Kakumanu')", 
  function (error, result) {
 
 console.log("Rows with delhi and Kakumanu Villages");
 
//Display the records one by one
    console.log(result);
  });
  
	//Query to select rows which are delhi and patna from distcict column.
  connection_data.query("SELECT * FROM village 
  WHERE distcict IN ('delhi','patna')", 
  function (error, result) {
 
console.log("Rows with delhi and patna Districts");
 
//Display the records one by one
    console.log(result);
  });
});

Output:

Rows with delhi and Kakumanu Villages
[
  RowDataPacket {
    village_name: 'Kakumanu',
    distcict: 'Guntur',
    people: 110
  },
  RowDataPacket {
    village_name: 'delhi',
    distcict: 'delhi',
    people: 30
  }
]
Rows with delhi and patna Districts
[
  RowDataPacket {
    village_name: 'delhi',
    distcict: 'delhi',
    people: 30
  },
  RowDataPacket {
    village_name: 'patna',
    distcict: 'patna',
    people: 100
  }

So we can see that rows were selected based on the values specified inside IN operator.

IN Example 2:-

Node.js Script to select rows with values 30,110 from people column.

// Load the mysql package
var mysql_package = require('mysql');
// Create the connection using the server,username and password.
//In my scenario - server is the localhost,
//username is root,
//password is empty.
//database is facility
var connection_data = mysql_package.createConnection({
  host: "localhost",
  user: "root",
  password: "",
  database:"facility"
});
connection_data.connect(function(error) {
//Query to select rows with values 30,110 from people column.
  connection_data.query("SELECT * FROM village 
  WHERE people IN (30,110)", 
  function (error, result) {
 
 console.log("Rows with delhi and Kakumanu Villages");
 
//Display the records one by one
    console.log(result);
  }); 
});

Output:

Rows with delhi and Kakumanu Villages
[
  RowDataPacket {
    village_name: 'Kakumanu',
    distcict: 'Guntur',
    people 110
  },
  RowDataPacket {
    village_name: 'delhi',
    distcict: 'delhi',
    people: 30
  }
]

So we can see that rows were selected based on the values specified inside IN operator based on people column.

Summary

So we seen how to use IN Operator on a MySQL Table in XAMPP Server using Node.js.