MySQL Select From

Why Use Node.js to Select Data from MySQL?

  1. Dynamic Data Retrieval: Fetch data based on user input or API requests.
  2. Asynchronous Execution: Node.js handles database queries without blocking other processes, ensuring fast and responsive applications.
  3. Ease of Integration: MySQL queries can be easily integrated into your Node.js logic.

Prerequisites

MySQL Installed: Ensure MySQL is installed and running on your system.

Node.js Installed: Install Node.js if you haven’t already.

MySQL Package Installed: Install the MySQL module using:

npm install mysql

Setting Up the Node.js Project

Initialize a New Project:

npm init -y

Install the MySQL Package:

npm install mysql

Create a File: Create a file named app.js.

Connecting to the MySQL Database

Before fetching data, establish a connection to your MySQL database.

Example: Connect to MySQL

const mysql = require('mysql');

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

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

Using the SELECT Statement in Node.js

The SELECT statement is used to fetch data from one or more columns in a table.

Example 1: Select All Data from a Table

const sql = 'SELECT * FROM users';

connection.query(sql, (err, results) => {
if (err) {
console.error('Error fetching data:', err);
return;
}
console.log('Data fetched successfully:', results);
});

// Close the connection
connection.end();

Explanation:

  • SELECT *: Retrieves all columns and rows from the users table.
  • results: Contains an array of all fetched rows.

Example 2: Select Specific Columns

If you only need specific columns, list them explicitly in the query.

const sql = 'SELECT name, email FROM users';

connection.query(sql, (err, results) => {
if (err) {
console.error('Error fetching data:', err);
return;
}
console.log('Selected columns:', results);
});

// Close the connection
connection.end();

Explanation:

  • Only the name and email columns are retrieved, reducing unnecessary data.

Filtering Data with WHERE Clause

You can filter results using the WHERE clause to specify conditions.

Example 3: Fetch Data Based on a Condition

const sql = 'SELECT * FROM users WHERE age > ?';
const age = 25;

connection.query(sql, [age], (err, results) => {
if (err) {
console.error('Error fetching data:', err);
return;
}
console.log('Users older than 25:', results);
});

// Close the connection
connection.end();

Explanation:

  • WHERE age > ?: Fetches users older than 25.
  • Parameterized Query: Prevents SQL injection by using placeholders (?).

Sorting Data with ORDER BY

You can sort the fetched data using the ORDER BY clause.

Example 4: Sort Data in Ascending Order

const sql = 'SELECT * FROM users ORDER BY age ASC';

connection.query(sql, (err, results) => {
if (err) {
console.error('Error fetching data:', err);
return;
}
console.log('Users sorted by age:', results);
});

// Close the connection
connection.end();

Explanation:

  • ORDER BY age ASC: Sorts users by age in ascending order. Use DESC for descending order.

Limiting Results with LIMIT

The LIMIT clause restricts the number of rows returned.

Example 5: Fetch Limited Rows

const sql = 'SELECT * FROM users LIMIT 5';

connection.query(sql, (err, results) => {
if (err) {
console.error('Error fetching data:', err);
return;
}
console.log('First 5 users:', results);
});

// Close the connection
connection.end();

Explanation:

  • LIMIT 5: Returns only the first 5 rows.

Combining Multiple Clauses

You can combine clauses like WHERE, ORDER BY and LIMIT in a single query.

Example 6: Fetch and Sort Data with Conditions

const sql = 'SELECT * FROM users WHERE age > ? ORDER BY name ASC LIMIT 3';
const age = 20;

connection.query(sql, [age], (err, results) => {
if (err) {
console.error('Error fetching data:', err);
return;
}
console.log('Filtered and sorted users:', results);
});

// Close the connection
connection.end();

Handling Errors During Data Fetching

Always handle errors to ensure your application remains stable.

Example: Error Handling

connection.query('SELECT * FROM non_existing_table', (err, results) => {
if (err) {
console.error('Error fetching data:', err.message);
return;
}
console.log('Data fetched:', results);
});

Output:

Error fetching data: Table 'nodejs_demo.non_existing_table' doesn't exist

Real-World Use Case

Imagine building an admin dashboard where you need to display user data. You can use the SELECT statement to fetch the required data dynamically.

Example: Fetch Data for Display

const fetchUsers = () => {
const sql = 'SELECT name, email, age FROM users';

connection.query(sql, (err, results) => {
if (err) {
console.error('Error fetching user data:', err);
return;
}
console.log('User data for dashboard:', results);
});
};

// Call the function
fetchUsers();

Leave a Comment

BoxofLearn