How to Make a search function using Node Express and MYSQL
Last Updated :
30 Dec, 2023
In this article, we will learn how to create a search function using Node with Express framework to search terms inside MYSQL tables.
Prerequisites:
We will discuss the following methods to search:
Approach to make search function:
We’re making a web application using Express.js and connecting it to a MySQL database. When users visit the ‘/search’ route and provide a search term as a parameter, the app looks for that term in the database. If there’s an error, we log it and send a 500 error. If not, we send back the search results in JSON format. We’re using Node.js with Express for all this.
Steps to Create a Express application:
Step 1: Initialize npm (node package manager) using the following command
npm init
Step 2: Install mysql2 package using the following npm install command.
npm install mysql2
Step 3: Create a new JavaScript file with .js extension (Example: app.js ) and write the code.
Step 4: For testing, create a new database and sample records using the following SQL query. Run the query in the MySQL workspace.
insert sample data
Project Structure:
Project Structure
The updated dependencies in package.json file will look like:
"dependencies": {
"express": "^4.18.2",
"mysql2": "^3.6.5",
}
Step 5: Run the application using following command:
node app.js
Step 6: Navigate to ‘http://localhost:3000/search‘ and pass search term in the query using ‘?term= <search term>’ .
Example: http://localhost:3000/search?term=geeksforgeeks
Note:
Important Note:
Replace host, user, password and database with your actual values.
Example:
const db = mysql.createConnection({
host: 'localhost',
user: 'root',
password: 'root',
database: 'mydatabase',
});
Approach 1: Searching term with partial match:
It performs a partial match on ‘name’ column using LIKE and % wildcard in SQL query. Search the term with partial match in ‘name‘ column using the following query. The LIKE operator with search term surrounded by % allows for a partial match ( const searchValue = `%${searchTerm}%`; ).
Replace it with following SQL query:
const query = `
SELECT * FROM items
WHERE name LIKE ?
`;
Example: Below is the code example of Searching term with partial match:
Javascript
const express = require('express');
const mysql = require('mysql2');
const app = express();
const port = 3000;
const db =
mysql.createConnection({
host: 'localhost',
user: 'root',
password: 'root',
database: 'mydatabase',
});
db.connect(err => {
if (err) {
console.error('Error connecting to MySQL:', err);
} else {
console.log('Connected to MySQL');
}
});
app.get('/search', (req, res) => {
const searchTerm = req.query.term;
if (!searchTerm) {
return res.status(400)
.json(
{
error: 'Search term is required'
}
);
}
const query = `
SELECT * FROM items
WHERE name LIKE ?
`;
const searchValue = `%${searchTerm}%`;
db.query(query, [searchValue, searchValue],
(err, results) => {
if (err) {
console
.error('Error executing search query:', err);
return res.status(500)
.json(
{
error: 'Internal server error'
});
}
res.json(results);
});
});
app.listen(port, () => {
console.log(`Server is running on
http:
});
|
Output:
match partially in ‘name’ column
Approach 2: Searching term with exact matching
Search the term with exact match in ‘name‘ column using the following query. The = operator finds the exact match only.
Replace it with following SQL query:
const query = `
SELECT * FROM items
WHERE name = ?
`;
Example: Below is the code example of Searching term with exact matching
Javascript
const express = require('express');
const mysql = require('mysql2');
const app = express();
const port = 3000;
const db = mysql.createConnection({
host: 'localhost',
user: 'root',
password: 'root',
database: 'mydatabase',
});
db.connect(err => {
if (err) {
console.error('Error connecting to MySQL:', err);
} else {
console.log('Connected to MySQL');
}
});
app.get('/search', (req, res) => {
const searchTerm = req.query.term;
if (!searchTerm) {
return res.status(400)
.json(
{
error: 'Search term is required'
});
}
const query = `
SELECT * FROM items
WHERE name = ?
`;
const searchValue = searchTerm;
db.query(query,
[searchValue,
searchValue],
(err, results) => {
if (err) {
console.error('Error executing search query:', err);
return res.status(500)
.json(
{
error: 'Internal server error'
});
}
res.json(results);
});
});
app.listen(port, () => {
console.log(`Server is running
on http:
});
|
Output:
match exact term in ‘name’ column
Approach 3: Searching term in multiple columns
It performs a partial match on both ‘name’ and ‘description’ columns using the % wildcard. Search the term with partial match in both ‘name’ and ‘description’ column using the following query. The LIKE operator with search term surrounded by % allows for a partial match ( const searchValue = `%${searchTerm}%`; ).
Replace it with following SQL query:
const query =
`SELECT *
FROM items
WHERE name LIKE ? OR description LIKE ? `;
Example: Below is the code example of Searching term in multiple columns
Javascript
const express = require("express");
const mysql = require("mysql2");
const app = express();
const port = 3000;
const db = mysql.createConnection({
host: "localhost",
user: "root",
password: "root",
database: "mydatabase",
});
db.connect((err) => {
if (err) {
console.error("Error connecting to MySQL:", err);
} else {
console.log("Connected to MySQL");
}
});
app.get("/search", (req, res) => {
const searchTerm = req.query.term;
if (!searchTerm) {
return res.status(400).json({ error: "Search term is required" });
}
const query = `
SELECT * FROM items
WHERE name LIKE ? OR description LIKE ?
`;
const searchValue = `%${searchTerm}%`;
db.query(query, [searchValue, searchValue], (err, results) => {
if (err) {
console.error("Error executing search query:", err);
return res.status(500).json({ error: "Internal server error" });
}
res.json(results);
});
});
app.listen(port, () => {
console.log(`Server is running on http:
});
|
Output:
search in both ‘name’ and ‘description’ with partial match
Share your thoughts in the comments
Please Login to comment...