Node.js MySQL WHERE

In this post, we will discuss how to select rows from an MySQL Table with WHERE Clause in 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
				

WHERE Clause

WHERE Clause in MySQL is used as a filter to return only particular records from the specified table which is used along with SELECT Clause. It can also be possible to select particular or all columns from the table.

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 to select records from the table using WHERE Clause. 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 condition", 
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:

WHERE Example 1:-

Let's select all the columns from village table with people greater than 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) {
//Write SQL query to chose all fields from village table with people greater than 50
connection_data.query("SELECT * FROM village WHERE people>50",
function (error, result) {
//Display the records one by one
    console.log(result);
  });
});

Output:

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

So we can see that records with people column greater than 50 were returned.

WHERE Example 2:-

Let's select all the columns from village table with village_name as bapatla.

// 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 chose all the fields from village table with villagename as bapatla.
  connection_data.query("SELECT * FROM village WHERE village_name='bapatla'", 
  function (error, result) {
    console.log(result);
  });
});

Output:

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

So we can see that there is only one record with village_name as bapatla.

WHERE Example 3:-

// 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 chose all fields from village table with people less than or equal to 50.
  connection_data.query("SELECT * FROM village WHERE people<=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 records with people column less-than or equal-to 50 were returned.

Summary

So we seen how to apply WHERE wirh SELECT clause to filter records in MySQL XAMPP Server using Node.js. Make sure that you have to install mysql package and xampp server.