This is Node.js tutorial on dynamic dependent select box from MySQL database and under this tutorial, you will step by step learn How to create dynamic dependent country state city select box in Node.js Express Application with MySQL Database.
In the Dynamic Dependent dropdown box feature, when we have select first value from first select box, then second or child select box will be filled with dynamic data based on first or parent dropdown box. This things will be done by Vanilla JavaScript Fetch API. So when we have select option from parent dropdown box then Fetch API has been send Ajax request to Node JS server script for fetch data for child select box and then after Fetch API has receive data in JSON format and after this second dropdown has been filled with dynamic data.
Under this tutorial for define the functionality of Dynamic Dependent Select box, here have use Country State and City dynamic dependent select box example. And under this example when page has been load in the browser, then only Country dropdown box has been filled with data and other state and city select box with blank value. So when we have select country from country select box then that country state data has been filled under state dropdown box which has been fetch from MySQL database using Fetch API and same way when we have select state from dropdown select box then that state city data has been filled under city select box, So in dynamic dependent dropdown box feature child select box data has been based on parent select. So in this tutorial we will make dynamic dropdown select box in Node js express application with MySQL database.
You have to follow below step for build dynamic dependent dropdown in Node JS Express with MySQL Database.
- MySQL Table Structure
- Download and Install Node.js Express framework
- Create MySQL Database Connection
- Create Routes
- Create Views File
- Check Output in the browser
Step 1 - MySQL Table Structure
For Create Dynamic Dropdown Feature in Node.js, first we have to create country_state_city table in your MySQL Database. So for this you have to run following .SQL script, which will create MySQL table and insert sample data into that table.
--
-- Database: `testing`
--
-- --------------------------------------------------------
--
-- Table structure for table `country_state_city`
--
CREATE TABLE `country_state_city` (
`id` int(11) NOT NULL,
`country` varchar(250) NOT NULL,
`state` varchar(250) NOT NULL,
`city` varchar(250) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Dumping data for table `country_state_city`
--
INSERT INTO `country_state_city` (`id`, `country`, `state`, `city`) VALUES
(1, 'USA', 'New York', 'New York city'),
(2, 'USA', 'New York', 'Buffalo'),
(3, 'USA', 'New York', 'Albany'),
(4, 'USA', 'Alabama', 'Birmingham'),
(5, 'USA', 'Alabama', 'Montgomery'),
(6, 'USA', 'Alabama', 'Huntsville'),
(7, 'USA', 'California', 'Los Angeles'),
(8, 'USA', 'California', 'San Francisco'),
(9, 'USA', 'California', 'San Diego'),
(10, 'Canada', 'Ontario', 'Toronto'),
(11, 'Canada', 'Ontario', 'Ottawa'),
(12, 'Canada', 'British Columbia', 'Vancouver'),
(13, 'Canada', 'British Columbia', 'Victoria'),
(14, 'Australia', 'New South Wales', 'Sydney'),
(15, 'Australia', 'New South Wales', 'Newcastle'),
(16, 'Australia', 'Queensland', 'City of Brisbane'),
(17, 'Australia', 'Queensland', 'Gold Coast\r\n');
--
-- Indexes for dumped tables
--
--
-- Indexes for table `country_state_city`
--
ALTER TABLE `country_state_city`
ADD PRIMARY KEY (`id`);
--
-- AUTO_INCREMENT for dumped tables
--
--
-- AUTO_INCREMENT for table `country_state_city`
--
ALTER TABLE `country_state_city`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=18;
Step 2 - Download and Install Node.js Express framework
Under this Node JS Tutorial, we have use Express framework. So first we need to download and install Node Express Application in our computer. So first we have to create dynamic_dependent directory. So we have go into command prompt and run following command.
mkdir dynamic_dependent
After run above command it will create dynamic_dependent directory. Next we have goes into this directory. So for this, we have to run following command in command prompt.
cd dynamic_dependent
Now we want to first download node express generator. So for this in command prompt we have to run following command.
npm install -g express-generator
After this we want to download express framework, so for this in command prompt, we have to run following command.
npx express --view=ejs
So this command will download express framework and it will use EJS template engie in our Node JS Application, and after run above command will display following output in the command prompt.
create : public\
create : public\javascripts\
create : public\images\
create : public\stylesheets\
create : public\stylesheets\style.css
create : routes\
create : routes\index.js
create : routes\users.js
create : views\
create : views\error.ejs
create : views\index.ejs
create : app.js
create : package.json
create : bin\
create : bin\www
install dependencies:
> npm install
run the app:
> SET DEBUG=crud:* & npm start
And lastly we want to install Node.js dependencies or required node module. So for this we have to run following command in command prompt.
npm install
Once we have run all above command, then it will download and install Node.js Express Application in our computer.
Step 3 - Create MySQL Database Connection
In this step, we want to connect our Node.js Application with MySQL database. So first we have to download node mysql modile in our node application. So for this, we have to run following command in command prompt.
npm install mysql
After this, we have to create database.js file in the root directory and under this file, we have to define MySQL database configuration for connect Node Express Application with MySQL database.
database.js
const mysql = require('mysql');
var connection = mysql.createConnection({
host : 'localhost',
database : 'testing',
user : 'root',
password : ''
});
connection.connect(function(error){
if(error)
{
throw error;
}
else
{
console.log('MySQL Database is connected Successfully')
}
});
module.exports = connection;
Step 4 - Create Routes
In this step, we have to open routes/index.js file and under this file, first we have to include database connection file for make mysql database.
After this, we have goes into /index get route which has been load views/index.ejs file in the browser. So with this file, we have to send country data also. So under this route we have to write select query for fetch country data from MySQL database and send to views/index.ejs file.
Under this file, we have to create another get route for handle Fetch API request for fetch state and city data from MySQL table and send data to fetch api in json format which will fill state and city select box with dynamic data.
routes/index.js
var express = require('express');
var router = express.Router();
var database = require('../database');
/* GET home page. */
router.get('/', function(req, res, next) {
database.query('SELECT DISTINCT country FROM country_state_city ORDER BY country ASC', function(error, data){
res.render('index', { title: 'Express', country_data : data });
});
});
router.get('/get_data', function(request, response, next){
var type = request.query.type;
var search_query = request.query.parent_value;
if(type == 'load_state')
{
var query = `
SELECT DISTINCT state AS Data FROM country_state_city
WHERE country = '${search_query}'
ORDER BY state ASC
`;
}
if(type == 'load_city')
{
var query = `
SELECT city AS Data FROM country_state_city
WHERE state = '${search_query}'
ORDER BY city ASC
`;
}
database.query(query, function(error, data){
var data_arr = [];
data.forEach(function(row){
data_arr.push(row.Data);
});
response.json(data_arr);
});
});
module.exports = router;
Step 5 - Create Views File
In Nodex Express Application, we have use Node Express EJS engine for display HTML output in the browser.
Under this file, we will create Country, State and City select box in the HTML code part.
And for send Ajax request to Node script we have use Vanilla JavaScript Fetch API which will send Ajax request to Node.js server script for fetch state and city data from MySQL table and populate state and city dropdown with dynamic data without refresh of web page.
views/index.ejs
<!doctype html>
<html lang="en">
<head>
<!-- Required meta tags -->
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<!-- Bootstrap CSS -->
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.0.2/dist/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-EVSTQN3/azprG1Anm3QDgpJLIm9Nao0Yz1ztcQTwFspd3yD65VohhpuuCOmLASjC" crossorigin="anonymous">
<title>Country State City Dependent Dropdown in Node js</title>
</head>
<body>
<div class="container">
<h1 class="text-center text-primary mt-3 mb-3">Dynamic Dropdown Select Box in Node.js</h1>
<div class="card">
<div class="card-header">Dynamic Dropdown</div>
<div class="card-body">
<div class="mb-3">
<select name="country" id="country" class="form-control">
<option value="">Select Country</option>
<% for(var count = 0; count < country_data.length; count++) { %>
<option value="<%= country_data[count].country %>"><%= country_data[count].country %></option>
<% } %>
</select>
</div>
<div class="mb-3">
<select name="state" id="state" class="form-control">
<option value="">Select State</option>
</select>
</div>
<div class="mb-3">
<select name="city" id="city" class="form-control">
<option value="">Select City</option>
</select>
</div>
</div>
</div>
</div>
</body>
</html>
<script>
function _(element)
{
return document.getElementById(element);
}
function fetch_data(parent_element, child_element, type)
{
fetch('/get_data?type='+type+'&parent_value='+parent_element.value+'').then(function(response){
return response.json();
}).then(function(responseData){
var html = '';
if(type == 'load_state')
{
html = '<option value="">Select State</option>';
}
if(type == 'load_city')
{
html = '<option value="">Select City</option>';
}
for(var count = 0; count < responseData.length; count++)
{
html += '<option value="'+responseData[count]+'">'+responseData[count]+'</option>';
}
child_element.innerHTML = html;
});
}
_('country').onchange = function(){
fetch_data(_('country'), _('state'), 'load_state');
};
_('state').onchange = function(){
fetch_data(_('state'), _('city'), 'load_city');
};
</script>
Step 6 - Check Output in the browser
Once we have follow all above step then our Node JS Dynamic Dependent Select box is ready with MySQL database, now for check output in the browser, so first we need to start node server. So for this, we have go to command prompt and run following command.
npm start
This command will start node server and then after we have goes to browser and hit following command for check Node JS Dynamic Dependent dropdown box output on the web page.
http://localhost:3000/
0 comments:
Post a Comment