Node.js MySQL BETWEEN

In this post, we will discuss how to return the rows in the given range from an MYSQL table using BETWEEN 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
				

BETWEEN Operator:

BETWEEN Operator in MySQL is used to return rows within the specified range. So we need to specify the start and end ranges. AND clause is used to separate these two ranges. Both the ranges takes values. So in any column values present in these range, the corresponding rows will be returned. It is specified after WHERE Clause.

1. value1 Specify the start range
2. value2 Specify the end range

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 BETWEEN 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 BETWEEN value1 and 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,
    .....,
	.....
},
.....
.....

Here, each RowDataPacket represent a row.

Consider the village table with the following records:

BETWEEN Example 1:-

Let's select the rows from the village table with values in people column in between 50 to 100.

// 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 in between 50 and 100 values in people column.
  connection_data.query("SELECT * FROM village WHERE people BETWEEN 50 and 100", 
  function (error, result) {
  
//Display the records one by one
    console.log(result);
 
  

  });
});

Output:

[
  RowDataPacket {
    village_name: 'patna',
    distcict: 'patna',
    people: 100
  },
  RowDataPacket {
    village_name: 'gogulamudi',
    distcict: 'guntur',
    people: 67
  }
]

So we can see that there are only two rows present in the given range.

BETWEEN Example 2:-

Let's select the rows from the village table with values in people column in between 30 to 50.

// 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 in between 30 and 50 values in people column.
  connection_data.query("SELECT * FROM village WHERE people BETWEEN 30 and 50", 
  function (error, result) {
  
//Display the records one by one
    console.log(result);
 
  

  });
});

Output:

[
  RowDataPacket {
    village_name: 'delhi',
    distcict: 'delhi',
    people: 30
  },
  RowDataPacket {
    village_name: 'bapatla',
    distcict: 'guntur',
    people: 40
  }
]

So we can see that there are only two rows present in the given range.

Summary

So we seen how to use BETWEEN Operator on a MySQL Table in XAMPP Server using Node.js. Make sure that it will work on numeric column (Condition specified on numeric column).