EN
Node.js - MySQL BETWEEN clause
3 points
In this article, we would like to show you how to use SQL BETWEEN clause in Node.js.
Note: at the end of this article you can find database preparation SQL queries, you will also find an example of a more parameterized query there.
xxxxxxxxxx1
const mysql = require('mysql');2
3
const connection = mysql.createConnection({ // gets connection with database4
host: 'localhost', // '127.0.0.1'5
user: 'root',6
password: 'password',7
database: 'database_name',8
});9
10
connection.connect((error) => {11
if (error) throw error;12
const query = 'SELECT * FROM `users` ' +13
'WHERE `id` BETWEEN ? AND ?';14
const values = [2, 4]; // id >= 2 AND id <= 415
16
connection.query(query, values, (error, result) => { // sends queries17
connection.end(); // closes connection 18
if (error) throw error;19
console.log(result);20
});21
});Result:
xxxxxxxxxx1
[2
{ id: 2, name: 'Chris', role: 'moderator' },3
{ id: 3, name: 'Kate', role: 'user' },4
{ id: 4, name: 'Denis', role: 'moderator' }5
]Note:
BETWEEN clause is used very often for dates.
create_tables.sql file:
xxxxxxxxxx1
CREATE TABLE `users` (2
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,3
`name` VARCHAR(100) NOT NULL,4
`role` VARCHAR(15) NOT NULL,5
PRIMARY KEY (`id`)6
)7
ENGINE=InnoDB;insert_data.sql file:
xxxxxxxxxx1
INSERT INTO `users`2
(`name`, `role`)3
VALUES4
('John', 'admin'),5
('Chris', 'moderator'),6
('Kate', 'user'),7
('Denis', 'moderator');xxxxxxxxxx1
// ...2
connection.connect((error) => {3
if (error) throw error;4
const query = `SELECT * 5
FROM ??6
WHERE ?? BETWEEN ? AND ?`;7
const values = ['users', 'id', 2, 4]; // id >= 2 AND id <= 48
9
connection.query(query, values, (error, result) => { // sends queries10
connection.end(); // closes connection 11
if (error) throw error;12
console.log(connection.sql); // SELECT * 13
}); // FROM `users`14
}); // WHERE `id` BETWEEN '2' AND '4'The names of columns and tables can be placed in the query by a double question mark ??.