Node.js MySQL Where

In Node.js, when working with MySQL, the WHERE clause is used to filter records based on specific conditions. You can use it to retrieve data that meets certain criteria from the database.

 

Key Features of Node.js MySQL Where

1. Filter Records: Use WHERE clause to retrieve records that meet specific conditions.
2. Multiple Conditions: Combine conditions using AND or OR for more complex filtering.
3. Comparison Operators: Use operators like =, >, <, BETWEEN, LIKE, etc.
4. Wildcards with LIKE: Use wildcards to perform pattern matching.
5. Prepared Statements: Use placeholders (?) to safely insert values into the query.

 

Step 1 Prerequisites

Make sure MySQL is installed and the mysql2 package is added to your Node.js project.

npm install mysql2

Step 2 Basic Query with WHERE Clause

You can use the WHERE clause to filter records based on a single condition.

Example Code

const mysql = require('mysql2');

// Create a connection to the database
const connection = mysql.createConnection({
  host: 'localhost',
  user: 'root',         // Replace with your MySQL username
  password: '',         // Replace with your MySQL password
  database: 'testdb'     // Replace with your database name
});

// Connect to MySQL
connection.connect((err) => {
  if (err) {
    console.error('Error connecting to MySQL:', err.stack);
    return;
  }

  // Select records where name is 'John Doe'
  const selectQuery = 'SELECT * FROM users WHERE name = ?';
  const values = ['John Doe'];

  connection.query(selectQuery, values, (err, results) => {
    if (err) {
      console.error('Error fetching records:', err.stack);
      return;
    }
    console.log('Filtered records:', results);
  });

  // Close the connection
  connection.end();
});

Output:

Filtered records: [
  { id: 1, name: 'John Doe', email: '[email protected]' }
]

Step 3 Using Multiple Conditions with AND/OR

You can combine multiple conditions using AND or OR to create more complex filters.

Example Code with AND

const mysql = require('mysql2');

// Create a connection to the database
const connection = mysql.createConnection({
  host: 'localhost',
  user: 'root',         // Replace with your MySQL username
  password: '',         // Replace with your MySQL password
  database: 'testdb'     // Replace with your database name
});

// Connect to MySQL
connection.connect((err) => {
  if (err) {
    console.error('Error connecting to MySQL:', err.stack);
    return;
  }

  // Select records where name is 'John Doe' and email is '[email protected]'
  const selectQuery = 'SELECT * FROM users WHERE name = ? AND email = ?';
  const values = ['John Doe', '[email protected]'];

  connection.query(selectQuery, values, (err, results) => {
    if (err) {
      console.error('Error fetching records:', err.stack);
      return;
    }
    console.log('Filtered records:', results);
  });

  // Close the connection
  connection.end();
});

Output:

Filtered records: [
  { id: 1, name: 'John Doe', email: '[email protected]' }
]

Example Code with OR

const mysql = require('mysql2');

// Create a connection to the database
const connection = mysql.createConnection({
  host: 'localhost',
  user: 'root',         // Replace with your MySQL username
  password: '',         // Replace with your MySQL password
  database: 'testdb'     // Replace with your database name
});

// Connect to MySQL
connection.connect((err) => {
  if (err) {
    console.error('Error connecting to MySQL:', err.stack);
    return;
  }

  // Select records where name is 'John Doe' or email is '[email protected]'
  const selectQuery = 'SELECT * FROM users WHERE name = ? OR email = ?';
  const values = ['John Doe', '[email protected]'];

  connection.query(selectQuery, values, (err, results) => {
    if (err) {
      console.error('Error fetching records:', err.stack);
      return;
    }
    console.log('Filtered records:', results);
  });

  // Close the connection
  connection.end();
});

Output:

Filtered records: [
  { id: 1, name: 'John Doe', email: '[email protected]' },
  { id: 2, name: 'Jane Doe', email: '[email protected]' }
]

Step 4 Using Comparison Operators

You can use various comparison operators with the WHERE clause to filter data, such as =, >, <, >=, <=, <>, BETWEEN, etc.

Example Code with Greater Than

const mysql = require('mysql2');

// Create a connection to the database
const connection = mysql.createConnection({
  host: 'localhost',
  user: 'root',         // Replace with your MySQL username
  password: '',         // Replace with your MySQL password
  database: 'testdb'     // Replace with your database name
});

// Connect to MySQL
connection.connect((err) => {
  if (err) {
    console.error('Error connecting to MySQL:', err.stack);
    return;
  }

  // Select records where age is greater than 25
  const selectQuery = 'SELECT * FROM users WHERE age > ?';
  const values = [25];

  connection.query(selectQuery, values, (err, results) => {
    if (err) {
      console.error('Error fetching records:', err.stack);
      return;
    }
    console.log('Filtered records:', results);
  });

  // Close the connection
  connection.end();
});

Output:

Filtered records: [
  { id: 2, name: 'Jane Doe', email: '[email protected]', age: 30 },
  { id: 3, name: 'Sam Smith', email: '[email protected]', age: 35 }
]

Step 5 Using LIKE for Pattern Matching

You can use the LIKE operator with wildcards to filter records based on pattern matching.

Example Code with LIKE

const mysql = require('mysql2');

// Create a connection to the database
const connection = mysql.createConnection({
  host: 'localhost',
  user: 'root',         // Replace with your MySQL username
  password: '',         // Replace with your MySQL password
  database: 'testdb'     // Replace with your database name
});

// Connect to MySQL
connection.connect((err) => {
  if (err) {
    console.error('Error connecting to MySQL:', err.stack);
    return;
  }

  // Select records where the name starts with 'John'
  const selectQuery = 'SELECT * FROM users WHERE name LIKE ?';
  const values = ['John%'];

  connection.query(selectQuery, values, (err, results) => {
    if (err) {
      console.error('Error fetching records:', err.stack);
      return;
    }
    console.log('Filtered records:', results);
  });

  // Close the connection
  connection.end();
});

Output:

Filtered records: [
  { id: 1, name: 'John Doe', email: '[email protected]' },
  { id: 2, name: 'John Smith', email: '[email protected]' }
]

Step 6 Using Prepared Statements

Prepared statements allow you to safely insert user input into SQL queries, avoiding SQL injection.

Example Code with Prepared Statements

const mysql = require('mysql2');

// Create a connection to the database
const connection = mysql.createConnection({
  host: 'localhost',
  user: 'root',         // Replace with your MySQL username
  password: '',         // Replace with your MySQL password
  database: 'testdb'     // Replace with your database name
});

// Connect to MySQL
connection.connect((err) => {
  if (err) {
    console.error('Error connecting to MySQL:', err.stack);
    return;
  }

  // Prepared statement to filter records where name is 'John Doe'
  const selectQuery = 'SELECT * FROM users WHERE name = ?';
  const values = ['John Doe'];

  connection.execute(selectQuery, values, (err, results) => {
    if (err) {
      console.error('Error fetching records:', err.stack);
      return;
    }
    console.log('Filtered records:', results);
  });

  // Close the connection
  connection.end();
});

Output:

Filtered records: [
  { id: 1, name: 'John Doe', email: '[email protected]' }
]

 

Summary

The WHERE clause in MySQL is powerful for filtering data based on conditions. You can combine multiple conditions with AND/OR, use comparison operators like =, >, <, and perform pattern matching with LIKE. Prepared statements help secure your queries by avoiding SQL injection.