Node.js – MySQL WHERE

Node.js MySQL WHERE is used to filter the selection of MySQL SELECT FROM statement records based on a condition applied to one or more columns of the TABLE.

In this tutorial, we will learn how to filter the rows of a Query selection using WHERE clause, with the help of examples.

We shall learn to filter records of a table using following Node.js examples

We shall use the following MySQL Table, in the examples of this section [DATABASE : studentsDB, Table: students]

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 Gellar |      8 |    86 |
| Lee           |      9 |    98 |
| Bruce Wane    |     10 |    92 |
| Sukumar       |     11 |    99 |
+---------------+--------+-------+
11 rows in set (0.00 sec)

Example 1 – Node.js MySQL WHERE with filter applied on a column

We shall apply a filter based on marks and fetch only those records with marks greater than 90.

selectFromWhere.js

</>
Copy
// include mysql module
var mysql = require('mysql');

// create a connection variable with the required details
var con = mysql.createConnection({
  host: "localhost",	// ip address of server running mysql
  user: "arjun",	// user name to your mysql database
  password: "password",	// corresponding password
  database: "studentsDB" // use the specified database
});

// make to connection to the database.
con.connect(function(err) {
  if (err) throw err;
  // if connection is successful
  con.query("SELECT * FROM students where marks>90", function (err, result, fields) {
	// if any error while executing above query, throw error
    if (err) throw err;
	// if there is no error, you have the result
    console.log(result);
  });
});

Open a terminal from the location of above .js file and run selectFromWhere.js Node.js MySQL example program.

Output

arjun@arjun-VPCEH26EN:~/workspace/nodejs$ node selectFromWhere.js 
[ RowDataPacket { name: 'Raja', rollno: 5, marks: 94 },
  RowDataPacket { name: 'Lee', rollno: 9, marks: 98 },
  RowDataPacket { name: 'Bruce Wane', rollno: 10, marks: 92 },
  RowDataPacket { name: 'Sukumar', rollno: 11, marks: 99 } ]

Example 2 – Node.js MySQL WHERE to use Escaping Query Values

Records may contain data with escaping values like space characters. Following Node.js MySQL example program helps you to filter records based on values containing escaping characters.

WhereEscapingValues.js

</>
Copy
// include mysql module
var mysql = require('mysql');

// create a connection variable with the required details
var con = mysql.createConnection({
  host: "localhost",	// ip address of server running mysql
  user: "arjun",	// user name to your mysql database
  password: "password",	// corresponding password
  database: "studentsDB" // use the specified database
});

// make to connection to the database.
con.connect(function(err) {
  if (err) throw err;
  // if connection is successful
  var name = "Bruce Wane";
  var query = "SELECT * FROM students where name=" + mysql.escape(name);
  con.query(query, function (err, result, fields) {
	// if any error while executing above query, throw error
    if (err) throw err;
	// if there is no error, you have the result
    console.log(result);
  });
});

Open a terminal from the location of above .js file and run WhereEscapingValues.js Node.js MySQL example program.

Output

arjun@arjun-VPCEH26EN:~/workspace/nodejs$ node WhereEscapingValues.js 
[ RowDataPacket { name: 'Bruce Wane', rollno: 10, marks: 92 } ]

Example 3 – Node.js MySQL WHERE with filter applied on two columns

We shall apply a filter based on marks and fetch only those records with marks greater than 90 and rollno less than 8.

selectFromWhere2.js

</>
Copy
// include mysql module
var mysql = require('mysql');

// create a connection variable with the required details
var con = mysql.createConnection({
  host: "localhost",	// ip address of server running mysql
  user: "arjun",	// user name to your mysql database
  password: "password",	// corresponding password
  database: "studentsDB" // use the specified database
});

// make to connection to the database.
con.connect(function(err) {
  if (err) throw err;
  // if connection is successful
  con.query("SELECT * FROM students where marks>90 && rollno<8", function (err, result, fields) {
	// if any error while executing above query, throw error
    if (err) throw err;
	// if there is no error, you have the result
    console.log(result);
  });
});

Open a terminal from the location of above .js file and run selectFromWhere.js Node.js MySQL example program.

Output

arjun@arjun-VPCEH26EN:~/workspace/nodejs$ node selectFromWhere2.js 
[ RowDataPacket { name: 'Raja', rollno: 5, marks: 94 } ]

Conclusion

In this Node.js Tutorial – Node.js MySQL Module Section – Node.js MySQL WHERE – We have learned to filter the selection of MySQL SELECT FROM statement records based on a condition applied to one or more columns of the MySQL Table.