Node.js MySQL: Using WHERE Clause

Welcome to The Coding College! In this tutorial, you’ll learn how to use the WHERE clause with MySQL queries in Node.js. The WHERE clause helps filter data to retrieve or manipulate specific rows from your database table.

Prerequisites

Ensure you have:

  1. Node.js Installed: Download Node.js.
  2. MySQL Installed: Download MySQL.
  3. A Populated Table: Use our Node.js MySQL Insert Into tutorial to add data to your table.

Step 1: Install MySQL Module

Install the MySQL package if not already installed:

npm install mysql

Step 2: Connect to MySQL Database

Create a file named selectWhere.js and set up the database connection:

const mysql = require('mysql');

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

// Connect to the database
connection.connect((err) => {
  if (err) {
    console.error('Error connecting to MySQL:', err.message);
    return;
  }
  console.log('Connected to the MySQL database!');
});

Step 3: Write a Query with WHERE Clause

The WHERE clause filters records based on conditions. Here’s an example to fetch data for a specific user from a users table:

const whereQuery = 'SELECT * FROM users WHERE id = 1';

Step 4: Execute the Query

Run the query using the query() method and process the result:

connection.query(whereQuery, (err, results) => {
  if (err) throw err;
  console.log('Filtered Data:', results);
});

Step 5: Use Placeholders for Dynamic Data

To filter data dynamically, use placeholders (?) to prevent SQL injection:

const userId = 2;
const dynamicWhereQuery = 'SELECT * FROM users WHERE id = ?';

connection.query(dynamicWhereQuery, [userId], (err, results) => {
  if (err) throw err;
  console.log('Filtered Data:', results);
});

Step 6: Close the Database Connection

Always close the connection when done:

connection.end((err) => {
  if (err) {
    console.error('Error closing the connection:', err.message);
    return;
  }
  console.log('Connection closed.');
});

Full Code Example

Here’s the complete script for fetching filtered data:

const mysql = require('mysql');

// Create a connection to the database
const connection = mysql.createConnection({
  host: 'localhost',
  user: 'root',
  password: 'your-password',
  database: 'mydatabase',
});

// Connect to the database
connection.connect((err) => {
  if (err) {
    console.error('Error connecting to MySQL:', err.message);
    return;
  }
  console.log('Connected to the MySQL database!');
});

// Filter data using WHERE clause
const userId = 2;
const dynamicWhereQuery = 'SELECT * FROM users WHERE id = ?';

connection.query(dynamicWhereQuery, [userId], (err, results) => {
  if (err) throw err;
  console.log('Filtered Data:', results);
});

// Close the connection
connection.end((err) => {
  if (err) {
    console.error('Error closing the connection:', err.message);
    return;
  }
  console.log('Connection closed.');
});

Examples of WHERE Clause Usage

1. Multiple Conditions with AND

To fetch data that matches multiple conditions:

const whereQuery = 'SELECT * FROM users WHERE name = ? AND email = ?';
const params = ['John Doe', '[email protected]'];
connection.query(whereQuery, params, (err, results) => {
  if (err) throw err;
  console.log('Filtered Data:', results);
});

2. Using OR Condition

To fetch data that matches at least one condition:

const whereQuery = 'SELECT * FROM users WHERE id = ? OR name = ?';
const params = [1, 'Jane Doe'];
connection.query(whereQuery, params, (err, results) => {
  if (err) throw err;
  console.log('Filtered Data:', results);
});

3. Using LIKE for Pattern Matching

To search for data that matches a pattern:

const whereQuery = 'SELECT * FROM users WHERE name LIKE ?';
const params = ['%Doe%'];
connection.query(whereQuery, params, (err, results) => {
  if (err) throw err;
  console.log('Filtered Data:', results);
});

4. Using IN for Multiple Values

To filter data based on a list of values:

const whereQuery = 'SELECT * FROM users WHERE id IN (?)';
const params = [[1, 3, 5]];
connection.query(whereQuery, params, (err, results) => {
  if (err) throw err;
  console.log('Filtered Data:', results);
});

Best Practices

  1. Sanitize Inputs: Always use placeholders to prevent SQL injection.
  2. Index Your Columns: Optimize the database performance for frequently queried columns.
  3. Handle Errors Gracefully: Ensure the application doesn’t crash due to unhandled errors.

Conclusion

Using the WHERE clause in Node.js with MySQL is crucial for filtering data and making your application dynamic. By following this guide, you can query specific data securely and efficiently.

Visit The Coding College for more tutorials on Node.js and database integration!

Leave a Comment