Friday, 1 July 2022

Node.js Autocomplete Search with MySQL Database


In this tutorial, we are going to learn Live Autocomplete Search Textbox in Node.js Express by using Vanilla JavaScript Fetch API with MySQL Database. Here we will show you very simple and best example of Auto Suggest Textbox in Node.js using Vanilla JavaScript Fetch API. In this tutorial for send Ajax request we will use JavaScript Fetch API for getting data from MySQL database table. Under this post, we will create Autocomplete feature in Node.js by using Vanilla JavaScript Fetch API with MySQL Database.

For learn Autocomplete Search Textbox in Node.js, so here we have use Country Search Example. So In the MySQL Table we will insert all country name and then after from Node.js Application, when we have start type Country name in textbox then it will send Fetch API Ajax request to Node.js Express Application routes for search country name data from MySQL table and get list of country name which has been start with character, which we have type in the textbox and we can get the list of Country suggestion at below textbox and then after we can select particular country name from the list of Auto suggest country list. So this is the main benefits of Autocomplete Search Textbox feature in our web based application and we do not want to type complete country name but we can select country name from the list of country suggestion.


Node.js Autocomplete Search with MySQL Database


So in this tutorial, we will make Autocomplete Textbox in Node.js Express Application by using Vanilla JavaScript Fetch API with MySQL Database. Below you can find step by step process for create autocomplete textbox feature in Node.js by using JavaScript Fetch API with MySQL Database.

  1. MySQL Table Structure
  2. Download and Install Node.js Express framework
  3. Create MySQL Database Connection
  4. Create Routes
  5. Create Views File
  6. Check Output in the browser

Step 1 - MySQL Table Structure


For start this tutorial, first we have to create table in MySQL table for create Autocomplete Search Textbox in Node.js, so for create table in MySQL database, we have to run following script in your phpmyadmin area and it will create apps_countries table with pre inserting of country data.


--
-- Table structure for table `apps_countries`
--

CREATE TABLE `apps_countries` (
  `id` int(11) NOT NULL,
  `country_code` varchar(2) NOT NULL DEFAULT '',
  `country_name` varchar(100) NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Dumping data for table `apps_countries`
--

INSERT INTO `apps_countries` (`id`, `country_code`, `country_name`) VALUES
(1, 'AF', 'Afghanistan'),
(2, 'AL', 'Albania'),
(3, 'DZ', 'Algeria'),
(4, 'DS', 'American Samoa'),
(5, 'AD', 'Andorra'),
(6, 'AO', 'Angola'),
(7, 'AI', 'Anguilla'),
(8, 'AQ', 'Antarctica'),
(9, 'AG', 'Antigua and Barbuda'),
(10, 'AR', 'Argentina'),
(11, 'AM', 'Armenia'),
(12, 'AW', 'Aruba'),
(13, 'AU', 'Australia'),
(14, 'AT', 'Austria'),
(15, 'AZ', 'Azerbaijan'),
(16, 'BS', 'Bahamas'),
(17, 'BH', 'Bahrain'),
(18, 'BD', 'Bangladesh'),
(19, 'BB', 'Barbados'),
(20, 'BY', 'Belarus'),
(21, 'BE', 'Belgium'),
(22, 'BZ', 'Belize'),
(23, 'BJ', 'Benin'),
(24, 'BM', 'Bermuda'),
(25, 'BT', 'Bhutan'),
(26, 'BO', 'Bolivia'),
(27, 'BA', 'Bosnia and Herzegovina'),
(28, 'BW', 'Botswana'),
(29, 'BV', 'Bouvet Island'),
(30, 'BR', 'Brazil'),
(31, 'IO', 'British Indian Ocean Territory'),
(32, 'BN', 'Brunei Darussalam'),
(33, 'BG', 'Bulgaria'),
(34, 'BF', 'Burkina Faso'),
(35, 'BI', 'Burundi'),
(36, 'KH', 'Cambodia'),
(37, 'CM', 'Cameroon'),
(38, 'CA', 'Canada'),
(39, 'CV', 'Cape Verde'),
(40, 'KY', 'Cayman Islands'),
(41, 'CF', 'Central African Republic'),
(42, 'TD', 'Chad'),
(43, 'CL', 'Chile'),
(44, 'CN', 'China'),
(45, 'CX', 'Christmas Island'),
(46, 'CC', 'Cocos (Keeling) Islands'),
(47, 'CO', 'Colombia'),
(48, 'KM', 'Comoros'),
(49, 'CG', 'Congo'),
(50, 'CK', 'Cook Islands'),
(51, 'CR', 'Costa Rica'),
(52, 'HR', 'Croatia (Hrvatska)'),
(53, 'CU', 'Cuba'),
(54, 'CY', 'Cyprus'),
(55, 'CZ', 'Czech Republic'),
(56, 'DK', 'Denmark'),
(57, 'DJ', 'Djibouti'),
(58, 'DM', 'Dominica'),
(59, 'DO', 'Dominican Republic'),
(60, 'TP', 'East Timor'),
(61, 'EC', 'Ecuador'),
(62, 'EG', 'Egypt'),
(63, 'SV', 'El Salvador'),
(64, 'GQ', 'Equatorial Guinea'),
(65, 'ER', 'Eritrea'),
(66, 'EE', 'Estonia'),
(67, 'ET', 'Ethiopia'),
(68, 'FK', 'Falkland Islands (Malvinas)'),
(69, 'FO', 'Faroe Islands'),
(70, 'FJ', 'Fiji'),
(71, 'FI', 'Finland'),
(72, 'FR', 'France'),
(73, 'FX', 'France, Metropolitan'),
(74, 'GF', 'French Guiana'),
(75, 'PF', 'French Polynesia'),
(76, 'TF', 'French Southern Territories'),
(77, 'GA', 'Gabon'),
(78, 'GM', 'Gambia'),
(79, 'GE', 'Georgia'),
(80, 'DE', 'Germany'),
(81, 'GH', 'Ghana'),
(82, 'GI', 'Gibraltar'),
(83, 'GK', 'Guernsey'),
(84, 'GR', 'Greece'),
(85, 'GL', 'Greenland'),
(86, 'GD', 'Grenada'),
(87, 'GP', 'Guadeloupe'),
(88, 'GU', 'Guam'),
(89, 'GT', 'Guatemala'),
(90, 'GN', 'Guinea'),
(91, 'GW', 'Guinea-Bissau'),
(92, 'GY', 'Guyana'),
(93, 'HT', 'Haiti'),
(94, 'HM', 'Heard and Mc Donald Islands'),
(95, 'HN', 'Honduras'),
(96, 'HK', 'Hong Kong'),
(97, 'HU', 'Hungary'),
(98, 'IS', 'Iceland'),
(99, 'IN', 'India'),
(100, 'IM', 'Isle of Man'),
(101, 'ID', 'Indonesia'),
(102, 'IR', 'Iran (Islamic Republic of)'),
(103, 'IQ', 'Iraq'),
(104, 'IE', 'Ireland'),
(105, 'IL', 'Israel'),
(106, 'IT', 'Italy'),
(107, 'CI', 'Ivory Coast'),
(108, 'JE', 'Jersey'),
(109, 'JM', 'Jamaica'),
(110, 'JP', 'Japan'),
(111, 'JO', 'Jordan'),
(112, 'KZ', 'Kazakhstan'),
(113, 'KE', 'Kenya'),
(114, 'KI', 'Kiribati'),
(115, 'KP', 'Korea, Democratic People\'s Republic of'),
(116, 'KR', 'Korea, Republic of'),
(117, 'XK', 'Kosovo'),
(118, 'KW', 'Kuwait'),
(119, 'KG', 'Kyrgyzstan'),
(120, 'LA', 'Lao People\'s Democratic Republic'),
(121, 'LV', 'Latvia'),
(122, 'LB', 'Lebanon'),
(123, 'LS', 'Lesotho'),
(124, 'LR', 'Liberia'),
(125, 'LY', 'Libyan Arab Jamahiriya'),
(126, 'LI', 'Liechtenstein'),
(127, 'LT', 'Lithuania'),
(128, 'LU', 'Luxembourg'),
(129, 'MO', 'Macau'),
(130, 'MK', 'Macedonia'),
(131, 'MG', 'Madagascar'),
(132, 'MW', 'Malawi'),
(133, 'MY', 'Malaysia'),
(134, 'MV', 'Maldives'),
(135, 'ML', 'Mali'),
(136, 'MT', 'Malta'),
(137, 'MH', 'Marshall Islands'),
(138, 'MQ', 'Martinique'),
(139, 'MR', 'Mauritania'),
(140, 'MU', 'Mauritius'),
(141, 'TY', 'Mayotte'),
(142, 'MX', 'Mexico'),
(143, 'FM', 'Micronesia, Federated States of'),
(144, 'MD', 'Moldova, Republic of'),
(145, 'MC', 'Monaco'),
(146, 'MN', 'Mongolia'),
(147, 'ME', 'Montenegro'),
(148, 'MS', 'Montserrat'),
(149, 'MA', 'Morocco'),
(150, 'MZ', 'Mozambique'),
(151, 'MM', 'Myanmar'),
(152, 'NA', 'Namibia'),
(153, 'NR', 'Nauru'),
(154, 'NP', 'Nepal'),
(155, 'NL', 'Netherlands'),
(156, 'AN', 'Netherlands Antilles'),
(157, 'NC', 'New Caledonia'),
(158, 'NZ', 'New Zealand'),
(159, 'NI', 'Nicaragua'),
(160, 'NE', 'Niger'),
(161, 'NG', 'Nigeria'),
(162, 'NU', 'Niue'),
(163, 'NF', 'Norfolk Island'),
(164, 'MP', 'Northern Mariana Islands'),
(165, 'NO', 'Norway'),
(166, 'OM', 'Oman'),
(167, 'PK', 'Pakistan'),
(168, 'PW', 'Palau'),
(169, 'PS', 'Palestine'),
(170, 'PA', 'Panama'),
(171, 'PG', 'Papua New Guinea'),
(172, 'PY', 'Paraguay'),
(173, 'PE', 'Peru'),
(174, 'PH', 'Philippines'),
(175, 'PN', 'Pitcairn'),
(176, 'PL', 'Poland'),
(177, 'PT', 'Portugal'),
(178, 'PR', 'Puerto Rico'),
(179, 'QA', 'Qatar'),
(180, 'RE', 'Reunion'),
(181, 'RO', 'Romania'),
(182, 'RU', 'Russian Federation'),
(183, 'RW', 'Rwanda'),
(184, 'KN', 'Saint Kitts and Nevis'),
(185, 'LC', 'Saint Lucia'),
(186, 'VC', 'Saint Vincent and the Grenadines'),
(187, 'WS', 'Samoa'),
(188, 'SM', 'San Marino'),
(189, 'ST', 'Sao Tome and Principe'),
(190, 'SA', 'Saudi Arabia'),
(191, 'SN', 'Senegal'),
(192, 'RS', 'Serbia'),
(193, 'SC', 'Seychelles'),
(194, 'SL', 'Sierra Leone'),
(195, 'SG', 'Singapore'),
(196, 'SK', 'Slovakia'),
(197, 'SI', 'Slovenia'),
(198, 'SB', 'Solomon Islands'),
(199, 'SO', 'Somalia'),
(200, 'ZA', 'South Africa'),
(201, 'GS', 'South Georgia South Sandwich Islands'),
(202, 'ES', 'Spain'),
(203, 'LK', 'Sri Lanka'),
(204, 'SH', 'St. Helena'),
(205, 'PM', 'St. Pierre and Miquelon'),
(206, 'SD', 'Sudan'),
(207, 'SR', 'Suriname'),
(208, 'SJ', 'Svalbard and Jan Mayen Islands'),
(209, 'SZ', 'Swaziland'),
(210, 'SE', 'Sweden'),
(211, 'CH', 'Switzerland'),
(212, 'SY', 'Syrian Arab Republic'),
(213, 'TW', 'Taiwan'),
(214, 'TJ', 'Tajikistan'),
(215, 'TZ', 'Tanzania, United Republic of'),
(216, 'TH', 'Thailand'),
(217, 'TG', 'Togo'),
(218, 'TK', 'Tokelau'),
(219, 'TO', 'Tonga'),
(220, 'TT', 'Trinidad and Tobago'),
(221, 'TN', 'Tunisia'),
(222, 'TR', 'Turkey'),
(223, 'TM', 'Turkmenistan'),
(224, 'TC', 'Turks and Caicos Islands'),
(225, 'TV', 'Tuvalu'),
(226, 'UG', 'Uganda'),
(227, 'UA', 'Ukraine'),
(228, 'AE', 'United Arab Emirates'),
(229, 'GB', 'United Kingdom'),
(230, 'US', 'United States'),
(231, 'UM', 'United States minor outlying islands'),
(232, 'UY', 'Uruguay'),
(233, 'UZ', 'Uzbekistan'),
(234, 'VU', 'Vanuatu'),
(235, 'VA', 'Vatican City State'),
(236, 'VE', 'Venezuela'),
(237, 'VN', 'Vietnam'),
(238, 'VG', 'Virgin Islands (British)'),
(239, 'VI', 'Virgin Islands (U.S.)'),
(240, 'WF', 'Wallis and Futuna Islands'),
(241, 'EH', 'Western Sahara'),
(242, 'YE', 'Yemen'),
(243, 'ZR', 'Zaire'),
(244, 'ZM', 'Zambia'),
(245, 'ZW', 'Zimbabwe');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `apps_countries`
--
ALTER TABLE `apps_countries`
  ADD PRIMARY KEY (`id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `apps_countries`
--
ALTER TABLE `apps_countries`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=246;





Step 2 - Download and Install Node.js Express framework


In this Node.js tutorial, we have use Express.js Framework, So first we need to download and install Node.js Express framework. So for this, first we have to go into directory in which we have run our node code and under that directory, we have to create autocomplete directory and then after we have goes into that directory, so for this, we have to run following command.


mkdir autocomplete
cd autocomplete


Once we have goes into autocomplete directory, now first we wan to download and install node express genderator, so for this, in the command prompt we have to run following command, which will download and install node.js express generator.


npm install -g express-generator


Next we want to download and install node.js express framework, so for this in the command prompt we have to run following command which will download and install node.js express framework under autocomplete directory.


npx express --view=ejs


In this downloaded Node.js Express framework, we have use ejs template engine for display HTML output in the browser and once we have run this command then it will display following node.js express directory structure which you can seen below.


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 under node.js express download and install process we have to run following command for install required node.js default module for express framework.


npm install


After run above command, so here our Node.js Express download and install process is completed.

Step 3 - Create MySQL Database Connection


After download and install Node.js Express framework, now we want to connect this applicatioin with MySQL Database, so for make MySQL database connection, first we have to download node mysql module. So for this, we have to run following command in command prompt.


npm install mysql


Once Node Mysql module has been install in our node express application, next we need to create one database.js file for create mysql database connection, and under this file, we have to define MySQL database configuration for connection 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


Generally in routes directory, we have to create server side script, and here we have use Node Express framework, so by default index.js and user.js file has been created by default. In this tutorial, we will use index.js file.

Under this file, first we will include MySQL database connection file for make mysql database.

Under this file, we have create two route, one is for display country name textbox on the web page and another route is for handle fetch API ajax request for search data in mysql table and send back data to ajax request in json format.

routes/index.js

var express = require('express');
var router = express.Router();

var database = require('../database');

/* GET home page. */
router.get('/', function(req, res, next) {
    res.render('index', { title: 'AutoComplete Search in Node.js with MySQL' });
});

router.get('/get_data', function(request, response, next){

    var search_query = request.query.search_query;

    var query = `
    SELECT country_name FROM apps_countries 
    WHERE country_name LIKE '%${search_query}%' 
    LIMIT 10
    `;

    database.query(query, function(error, data){

        response.json(data);

    });

});

module.exports = router;


Step 5 - Create Views File


In the Node.js Express framework, views directory file has been used for display HTML output in the browser. Here we have use ejs template engine for display HTML output in the browser in Node.js Express Application.

In this tutorial, we have use views/index.ejs and first will remove default code of this file and put our HTML code with Bootstrap 5 library.

Under this file, first we will create one textbox for enter country name details and then after we will write vanilla javascript code for make Autocomplete Search Textbox in Node.js.

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>AutoComplete Search in Node.js with MySQL</title>
    </head>
    <body>
        <div class="container">
            <h1 class="text-center text-primary mt-3 mb-3">AutoComplete Search in Node.js with MySQL</h1>

            <!--<div class="card">
                <div class="card-header">Autocomplete Search Example</div>
                <div class="card-body">!-->
                        
                    <input type="text" id="autocomplete_search" class="form-control form-control-lg" placeholder="Type Country Name Here" />
                    <span id="search_result"></span>

                <!--</div>
            </div>!-->
        </div>
  </body>
</html>

<script>

    function load_data(query = '')
    {
        fetch('/get_data?search_query='+query+'').then(function(response){

            return response.json();

        }).then(function(responseData){

            var html = '<ul class="list-group">';

            if(responseData.length > 0)
            {
                for(var count = 0; count < responseData.length; count++)
                {
                    var regular_expression = new RegExp('('+query+')', 'gi');

                    html += '<a href="#" class="list-group-item list-group-item-action" onclick="get_text(this)">'+responseData[count].country_name.replace(regular_expression, '<span class="text-primary fw-bold">$1</span>')+'</a>';
                }
            }
            else
            {
                html += '<a href="#" class="list-group-item list-group-item-action disabled">No Data Found</a>';
            }

            html += '</ul>';

            document.getElementById('search_result').innerHTML = html;

        });
    }

    var search_element = document.getElementById("autocomplete_search");

    search_element.onkeyup = function(){

        var query = search_element.value;

        load_data(query);

    };

    search_element.onfocus = function(){

        var query = search_element.value;

        load_data(query);

    };

    function get_text(event)
    {
        var country_name = event.textContent;

        console.log(country_name);

        document.getElementById('autocomplete_search').value = country_name;

        document.getElementById('search_result').innerHTML = '';
    }

</script>


Step 6 - Check Output in the browser

Once we have follow all above step, so we are able to check output in the browser. So for check output in the browser, first we have goes to command prompt and run following command.


npm start


This command will start Node.js server and then after we can goes to browser and following URL.


http://localhost:3000/


So once we have hit above url then it will display one textbox for type country name and once we have start write country name, then after type character then it will display auto search result at below textbox and when we have type more character then it will narrow filter data and display exact country name. So here in this tutorial, we have make Autocomplete textbox in Node.js Express framework using Vanilla JavaScript Fetch API with MySQL database.





Friday, 24 June 2022

Country State City Dynamic Dependent Dropdown in Node js with MySQL


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.


Country State City Dynamic Dependent Dropdown in Node js with MySQL






You have to follow below step for build dynamic dependent dropdown in Node JS Express with MySQL Database.

  1. MySQL Table Structure
  2. Download and Install Node.js Express framework
  3. Create MySQL Database Connection
  4. Create Routes
  5. Create Views File
  6. 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/






Saturday, 18 June 2022

How to use jQuery DataTables in Nodejs


In this tutorial, we will show you How to use jQuery DataTables in Node.js Express framework and you can learn How to populating MySQL table data in jQuery DataTables with Node.js. In this tutorial we will use DataTables Server-side processing of data in Node.js Express Application with MySQL table. So if you are looking for tutorial on Server side jQuery DataTable with MySQL Database and Node.js then this Node.js tutorial will help you to understand server side jQuery DataTable in Node JS by using Ajax.

Here jQuery DataTables has been used Ajax request for Server-side process in Node.js Express application. In jQuery DataTable all feature like live searching of MySQL table data, sorting of data pagination link will be generated without writing of code. Because jQuery DataTables has been convert our HTML table into advanced table with feature like pagination, instant search, multi column ordering. So when we have type in search box then jQuery DataTables has send Ajax request to Node JS script for instant search in MySQL table and sample way we have click on pagination link of jQuery DataTable then it has send Ajax request to Node JS script for fetch next page data from MySQL table and send response to jQuery DataTables in JSON format. So this way it has perform all process at server side with Node.js.


How to use jQuery DataTables in Nodejs


In this tutorial, we have use following web technology.

Client-side


  • jQuery DataTables
  • jQuery
  • Bootstrap 5

Server-side


  • Node.js
  • Express Framework
  • MySQL

Steps for Use jQuery DataTables in Node.js


You have to follow below steps for implement jQuery DataTables Server-side processing in your Node.js Express Application.

  1. MySQL Database Structure
  2. Install Node.js Express Application
  3. Connection Node.js Express Application with MySQL Database
  4. Create Views Template File
  5. Create Routes File
  6. Check Output in the browser

MySQL Database Structure


In the first steps we have show you MySQL database structure, so first we want to create one customer_table in mysql database. So for create MySQL table, we have to run following command which will create MySQL table in your MySQL database.


--
-- Database: `testing`
--

-- --------------------------------------------------------

--
-- Table structure for table `customer_table`
--

CREATE TABLE `customer_table` (
  `customer_id` int(11) NOT NULL,
  `customer_first_name` varchar(200) NOT NULL,
  `customer_last_name` varchar(200) NOT NULL,
  `customer_email` varchar(300) NOT NULL,
  `customer_gender` varchar(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `customer_table`
--
ALTER TABLE `customer_table`
  ADD PRIMARY KEY (`customer_id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `customer_table`
--
ALTER TABLE `customer_table`
  MODIFY `customer_id` int(11) NOT NULL AUTO_INCREMENT;


Install Node.js Express Application


In tis tutorial, we have use Node.js with Express framework, so first we have want to download and install Node.js Express framework. So first we have to go into directory in which we have run our node application.

After this, we have to create node_datatable directory, so for this, we have to run following command.


mkdir node_datatable


After this, we have goes into this directory, so for this, we have to run following command, so after run this command, we will goes into node_datatable directory.


cd node_datatable


After goes into node_datatable directory, now we want to download and install Node.js Express framework. So here first we want to install express generator, so for this, we have to run following command.


npm install -g express-generator


Once we have install express generator, next we need to download and install Node.js Express framework, with EJS template engine, so for this we have to run following command in command prompt.


npx express --view=ejs


This command will download Node.js Express framework and here we have use EJS template engine under this Node.js Express application and after run this command you will get following output in your command prompt window.



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


Next we want to install node.js dependencies, so for this in command prompt we have to run following command.


npm install


So after run this command here our Node.js Express application has been downloaded and installed in our computer.

Connection Node.js Express Application with MySQL Database


After download and install Node.js Express Application, now we want to make database connection. So for this, first we have to download and install node mysql module. So for this, we have go into command prompt and run following command.


npm install mysql


This command will download and install node mysql module in our Node.js Express application. Now for make database connection, here first we need to create database.j file in the root directory and under this file, first we need to include node mysql module under this file and then after we have to define mysql database configuration under this file which will make MySQL database connection under this Node.js Express application.

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;


Create Views Template File


In the Node.js Express framework, Views directory file has been used for display HTML output in the browser. So for this, we have to open views/index.ejs file.

Under this file, we have to include jQuery, Boostrap 5 library link.

After this, we have to create HTML table with id customer_data and we will initialize jQuery DataTable plugin on the table and convert HTML table adavanced table.

Next, we have to write jQuery code for initialize jQuery Datatables plugin by using DataTable() with required option which you can seen below.

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">

        <link href="https://cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/5.1.3/css/bootstrap.min.css" rel="stylesheet">

        <link href="https://cdn.datatables.net/1.12.1/css/dataTables.bootstrap5.min.css" rel="stylesheet">

        <title><%= title %></title>
    </head>
    <body>
        <div class="container">
            <h1 class="text-center text-primary mt-3 mb-3">jQuery DataTables with Node.js Express & MySQL</h1>

            <div class="card">
                <div class="card-header">Customer Data</div>
                <div class="card-body">
                    <div class="table-responsive">
                        
                        <table id="customer_data" class="table table-bordered">
                            <thead>
                                <tr>
                                    <th>First Name</th>
                                    <th>Last Name</th>
                                    <th>Email</th>
                                    <th>Gender</th>
                                </tr>
                            </thead>
                            <tbody></tbody>
                        </table>
                        
                    </div>
                </div>
            </div>
        </div>

        <script src="https://code.jquery.com/jquery-3.5.1.js"></script>
        <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.0.2/dist/js/bootstrap.bundle.min.js" integrity="sha384-MrcW6ZMFYlzcLA8Nl+NtUVF0sA7MsXsP1UyJoMp4YLEuNSfAP+JcXn/tWtIaxVXM" crossorigin="anonymous"></script>
        <script src="https://cdn.datatables.net/1.12.1/js/jquery.dataTables.min.js"></script>
        <script src="https://cdn.datatables.net/1.12.1/js/dataTables.bootstrap5.min.js"></script>
  </body>
</html>

<script>

$(document).ready(function(){

    var dataTable = $('#customer_data').DataTable({
        'processing' : true,
        'serverSide' : true,
        'serverMethod' : 'get',
        'ajax' : {
            'url' : '/get_data'
        },
        'aaSorting' : [],
        'columns' : [
            { data : 'customer_first_name' },
            { data : 'customer_last_name' },
            { data : 'customer_email' },
            { data : 'customer_gender' }
        ]
    });

});

</script>


Create Routes File


In the routes file, we have mainly write Node.js Server side script under this Node.js Express framework. So routes file has bee stored under routes directory. So by default under this directory index.js file has been created.

So we have to open views/index.js file and under this file, first we want to make database connection by including database.js file.

After this, we have goes into get('/') route and this file has been load views/index.ejs file in the browser for display HTML output on the web page.

Next we have to create another get('/get_data') route for handle Ajax request which has been send by jQuery DataTable.

And under this route, it has received data from jQuery Datatable Ajax request and based on that data, under this route it has calculate total records in MySQL table, total records with filtering and fetch data from MySQL customer_table and then after it has send data to Ajax request in JSON format which has been load in jQuery DataTable without refresh of web page.

routes/index.js

var express = require('express');
var router = express.Router();

var database = require('../database');

/* GET home page. */
router.get('/', function(req, res, next) {
    res.render('index', { title: 'Express' });
});

router.get('/get_data', function(request, response, next){

    var draw = request.query.draw;

    var start = request.query.start;

    var length = request.query.length;

    var order_data = request.query.order;

    if(typeof order_data == 'undefined')
    {
        var column_name = 'customer_table.customer_id';

        var column_sort_order = 'desc';
    }
    else
    {
        var column_index = request.query.order[0]['column'];

        var column_name = request.query.columns[column_index]['data'];

        var column_sort_order = request.query.order[0]['dir'];
    }

    //search data

    var search_value = request.query.search['value'];

    var search_query = `
     AND (customer_first_name LIKE '%${search_value}%' 
      OR customer_last_name LIKE '%${search_value}%' 
      OR customer_email LIKE '%${search_value}%' 
      OR customer_gender LIKE '%${search_value}%'
     )
    `;

    //Total number of records without filtering

    database.query("SELECT COUNT(*) AS Total FROM customer_table", function(error, data){

        var total_records = data[0].Total;

        //Total number of records with filtering

        database.query(`SELECT COUNT(*) AS Total FROM customer_table WHERE 1 ${search_query}`, function(error, data){

            var total_records_with_filter = data[0].Total;

            var query = `
            SELECT * FROM customer_table 
            WHERE 1 ${search_query} 
            ORDER BY ${column_name} ${column_sort_order} 
            LIMIT ${start}, ${length}
            `;

            var data_arr = [];

            database.query(query, function(error, data){

                data.forEach(function(row){
                    data_arr.push({
                        'customer_first_name' : row.customer_first_name,
                        'customer_last_name' : row.customer_last_name,
                        'customer_email' : row.customer_email,
                        'customer_gender' : row.customer_gender
                    });
                });

                var output = {
                    'draw' : draw,
                    'iTotalRecords' : total_records,
                    'iTotalDisplayRecords' : total_records_with_filter,
                    'aaData' : data_arr
                };

                response.json(output);

            });

        });

    });

});

module.exports = router;


Check Output in the browser


Once we have process all above code, so we have to save all files and then after we have go to command prompt and run following command.


npm start


This command will start Node.js server and now we are ready for check output in the browser. So for this we have to hit following url in the browser.


http://localhost:3000


So once you have follow all above steps then you can able to use jQuerh DataTables in Node.js Express Application.





Monday, 13 June 2022

How to Create Login System in Node.js Express with MySQL


In this Node.js Tutorial, we will step by step show you how to create simple login system in Node.js, Express and MySQL Database. So by using Login System only authenticated users on your web application can view or access restricted web pages of your website.

Under this post, you will learn how to make login with MySQL in Node.js using Express and we will create Node.js Express Login with MySQL Database and for validate user login we will use Session in Node.js Express application and store user login data in Session variable for authenticate user login access whole website, this is because we can access session data from any web page.

As we have know in any Software or Web based application Login page is required, so only authenticated user can access some restricted web page which we do not want to access general public. So for this we have to build Login page in our application and login is the passport for access our web application and only login user or authenticate user can access our web application. So login is the required feature of any web application. So for this reason we have build login page in Node JS.


How to Create Login System in Node.js Express with MySQL


Step for Create Node JS Express Login with MySQL Database


  1. MySQL Database Structure
  2. Install Node Express Application
  3. Make MySQL Database Connection with Node Express Application
  4. Configure Session
  5. Create Route
  6. Create Views
  7. Start Node Server & Check Output in browser

Step 1 - MySQL Database Structure


Before build Login System in Node js, first we have to create user_login table in your MySQL database. So for this you have to run following .sql script which will create user_login table in your MySQL Database.


<pre class="line-numbers language-sql">
<code class="language-sql">
--
-- Database: `testing`
--

-- --------------------------------------------------------

--
-- Table structure for table `user_login`
--

CREATE TABLE `user_login` (
  `user_id` int(11) NOT NULL,
  `user_email` varchar(100) NOT NULL,
  `user_password` varchar(100) NOT NULL,
  `user_session_id` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `user_login`
--

INSERT INTO `user_login` (`user_id`, `user_email`, `user_password`, `user_session_id`) VALUES
(1, 'johnsmith@gmail.com', 'password', ''),
(2, 'peterparker@gmail.com', 'password', '');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `user_login`
--
ALTER TABLE `user_login`
  ADD PRIMARY KEY (`user_id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `user_login`
--
ALTER TABLE `user_login`
  MODIFY `user_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;
</code>
</pre>

<script src="https://cdnjs.cloudflare.com/ajax/libs/prism/1.15.0/prism.js" type="text/javascript"></script>
<link href="https://cdnjs.cloudflare.com/ajax/libs/prism/1.15.0/themes/prism.css" rel="stylesheet" type="text/css"/>



So in this MySQL table, we can see two user login details already inserted, so we will use this data for build login system in Node js and we will validate this user login data under this Node js login application.

Step 2 - Install Node Express Application


For Create Login system in Node.js, here we have use Node Express framework. So first we need to download and install Node Express Framework Application.

So first we have to go directory in which we have run Node js. So for this we have to run following command.


f:
cd node


This command will first we have goes into f: drive and then after we have goes into node directory. Now under this directory, we have to create login directory. So for this, we have to run following command.


mkdir login


After creating this login directory, now we have goes into this directory, so for this we have to run following command.


cd login


After goes into this directory, we have to first download and install node express generator, so for this we have to run following command in your command prompt.


npm install -g express-generator


In the next process we have to install Express JS Application. So for this, we have to run following command which will download and install Node Express JS Application.


npx express --view=ejs


In this above command we have use ejs which template engine which we will for layout support in Express application, and once we have run this command then it will display complete directory structure of your Node Express JS Application directory structure which you can seen below.


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


In the next step, we need to install required default Node JS modules in your Express JS Application, so we have to run following command which will download required Node js modules in module directory.


npm install


After run this command here our download and install of Node JS Express Application process has been completed and we are ready to build simplete login application in Node js.





Step 3 - Make MySQL Database Connection with Node Express Application


Once we have download and install Node JS Express application in our computer, now first for build login system with mysql database in Node.js, we have need to make mysql database connection. So for make database connection Node.js Express application first we have to download and install node js mysql module. So for this we have to run following command.


npm install mysql


After download and install Node JS MySQL module, now for make MySQL database connection, we have to create database.js file in the root directory and under this file we have to define MySQL database configuration for make database connection in Node.js Express Application. You can see, we have to define following MySQL database configuration details under database.js file.

database.js

const mysql = require('mysql');

const 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 - Configure Session


In this tutorial, we have use Session for store user login detail under session variable. So here we have use express-session module has been used for building a user session. So first we need to download express-session module in our Node JS Express Application. So we have go to command prompt and run following command, which will download and install express-session module in our Node JS Express Application.


npm install express-session --save


After download and Install express-session module, now for use session in our Node JS Express application, we have to configure session in app.js file. So under this file, we have to add following session configuration details in app.js file after var app = express() code.

app.js

app.use(session({
  secret : 'webslesson',
  resave : true,
  saveUninitialized : true
}));


So after add session configuration in app.js file, now we can see session in our Node JS Express Application.

Step 5 - Create Route


In this step, we have to create route for load login web page and handle login form data for validate user login details. So for create route, we have to open routes/index.js file.

Under this file for load login page in the browser, we have to create get route, which will load views/index.ejs file in the browser and while loading this page, we have render session data also with this route. So we can validate user login status.

After this for handle login form data for authenticate user login. Here we have create post(/login) route. Under this route first it has store login form data in local variable and after this it has validate user login form data with MySQL database data. If both data match then it will store user id in session variable and redirect web page to index route on which user can view welcome message with logout button for logout from system.

And after this, we have create get route for handle logout request. Under this route, it has delete all session data and user will redirect to login page.

routes/index.js

var express = require('express');
var router = express.Router();

var database = require('../database');

/* GET home page. */
router.get('/', function(req, res, next) {
  res.render('index', { title: 'Express', session : req.session });
});

router.post('/login', function(request, response, next){

    var user_email_address = request.body.user_email_address;

    var user_password = request.body.user_password;

    if(user_email_address && user_password)
    {
        query = `
        SELECT * FROM user_login 
        WHERE user_email = "${user_email_address}"
        `;

        database.query(query, function(error, data){

            if(data.length > 0)
            {
                for(var count = 0; count < data.length; count++)
                {
                    if(data[count].user_password == user_password)
                    {
                        request.session.user_id = data[count].user_id;

                        response.redirect("/");
                    }
                    else
                    {
                        response.send('Incorrect Password');
                    }
                }
            }
            else
            {
                response.send('Incorrect Email Address');
            }
            response.end();
        });
    }
    else
    {
        response.send('Please Enter Email Address and Password Details');
        response.end();
    }

});

router.get('/logout', function(request, response, next){

    request.session.destroy();

    response.redirect("/");

});

module.exports = router;




Step 6 - Create Views


In this step, we have to create ejs template file for display HTML output in the browser. So in this tutorial, we have create views/index.ejs template file. This file has been receive data from route and it has receive session data also.

This session data has been used to check user is login into system or not. If user is login then on this template file user will see welcome message with logout link and if user is not login into system then user will sess login form on the web page. So below you can find source code of views template file.

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>Login System in Node.js Express</title>
    </head>
    <body>

        <div class="container">
            <h1 class="mt-5 mb-5 text-center text-primary"><b>Login System in Node.js Express</b></h1>

            <div class="row mt-5">
                <div class="col-md-3">&nbsp;</div>
                <div class="col-md-6">
                <% if(session.user_id) { %>

                    <h1>Hi User, Welcome</h1>

                    <a href="/logout" class="btn btn-primary">Logout</a>

                <% } else { %>

                    <div class="card">
                        <div class="card-header">Login</div>
                        <div class="card-body">
                            <form method="post" action="/login">
                                <div class="mb-3">
                                    <label>Email Address</label>
                                    <input type="email" name="user_email_address" class="form-control" />
                                </div>
                                <div class="mb-3">
                                    <label>Password</label>
                                    <input type="password" name="user_password" class="form-control" />
                                </div>
                                <div class="mb-3">
                                    <input type="submit" class="btn btn-primary" value="Login" />
                                </div>
                            </form>
                        </div>
                    </div>

                <% } %>
                </div>
            </div>
        </div>
    </body>
</html>


Step 7 - Start Node Server & Check Output in browser


Once you have follow all above step, so you have almost build Login System in Node JS Exppress framework. Now you need to check output in the browser. So first we have to start Node JS server, so we have go into command prompt and run following command.


npm start


This command will start node js server and for check output in the browser, you have to hit following url in the browser which will display node js express application login page on the web page.


http://localhost:3000/


So if you have following all above step and check output in the browser. So in this tutorial you have learn How to create simple Login system in Node js Express application with MySQL database. So if you have use MySQL with Node js then this tutorial will help to create Login page in Node js using Express framework.





Wednesday, 1 June 2022

Ajax with Node JS CRUD - Create, Read, Update Delete MySQL Data using Express JS

Ajax with Node JS CRUD - Create, Read, Update Delete MySQL Data using Express JS


In this tutorial, we will learn Ajax CRUD Operation Insert Update and delete data from MySQL Database in Node JS using Express JS framework. In previous our one of the Node JS tutorial, in which we have already show you how can we perform simple CRUD (Create, Read, Update and Delete) operation with MySQL Database in Node JS Express Application.

This Node JS Tutorial will help you to understand how to use Ajax in Node JS Express Application. So for learn in Web Development for any things first you learn CRUD operation before start learn new things, this is because in CRUD Operation, we can learn all database operation like data listing on table, add data, edit data and delete data from database. So by using CRUD Operation we can manage dynamic data.

So for this things, here we will make single page CRUD Application in Node JS using Ajax with MySQL Database. And under this Single Page Node Express JS CRUD Application using Ajax will mainly perform four operations like Create, Read, Update and Delete and all this operation are the most important operation for build any web application.

If you want to better Understand CRUD Operation, you should see the following points -

  • C : CREATE - Insert or Add new data into Database
  • R : READ - Fetch or Select Data from Database and display on the web page
  • U : UPDATE - Update or Edit Existing data into database
  • D : DELETE - Delete or Remove Existing data from database

For Build Ajax CRUD Application in Node Express JS, you should follow below step.

  1. MySQL Database Structure
  2. Download & Install Node JS Express Application
  3. Create MySQL Database Connection
  4. Fetch & Display Data on Web Page using Ajax
  5. Insert Data in Node JS using Ajax
  6. Update Data in Node JS using Ajax
  7. Delete Data in Node JS using Ajax

1 - MySQL Database Structure


For build any CRUD Application with MySQL database, then we need mysql table. So run following .sql script in your local phpmyadmin database and this script will create sample_data table in your define database.


--
-- Database: `testing`
--

-- --------------------------------------------------------

--
-- Table structure for table `sample_data`
--

CREATE TABLE `sample_data` (
  `id` int(10) NOT NULL,
  `first_name` varchar(250) NOT NULL,
  `last_name` varchar(250) NOT NULL,
  `age` varchar(30) NOT NULL,
  `gender` varchar(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `sample_data`
--

INSERT INTO `sample_data` (`id`, `first_name`, `last_name`, `age`, `gender`) VALUES
(1, 'John', 'Smith', '26', 'Male'),
(2, 'Donna', 'Hubber', '24', 'Female'),
(3, 'Peter', 'Parker', '28', 'Male'),
(4, 'Tom ', 'Muddy', '28', 'Male');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `sample_data`
--
ALTER TABLE `sample_data`
  ADD PRIMARY KEY (`id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `sample_data`
--
ALTER TABLE `sample_data`
  MODIFY `id` int(10) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;


2 - Download & Install Node JS Express Application


For Build Ajax CRUD Application in Node JS, here we have use Express JS framework. So first we need to download Node JS Express Application and by using this framework we can fastly build Node JS single page CRUD Application using Ajax.

Now for Install Node JS Express framework first we have to download and install Express generator. So for this, first you have go to command prompt and you have to create one ajax-crud directory and then after we need to goes into that directory by run following command.


mkdir ajax-crud



cd ajax-crud


Once we have goes into ajax-crud directory, so after this, we have to run following command for install Express generator.


npm install -g express-generator


After install Express generator, next we have to install Express application. So for this, we have to run following command command and this command will download and install express application.


npx express --view=ejs


So here we can see that in above command, ejs is the template engine which we have use in Node JS Application and once Node Express Application has been downloaded then it will display following directory structure of Node JS Express framework.


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


Once our Node Express JS application has been downloaded next we have to install required Node JS module so for this, we have to run following command which will download and install required Node JS Module.


npm install


So this is complete process for install Node Express JS Application and now we can proceed for build Ajax CRUD Application Node JS.

3 - Create MySQL Database Connection


In next step we have to make MySQL Database connection with Node JS Express Application. So for use MySQL Database with Node Express JS Application first we need to download node mysql module, So for this we have go to command prompt and run following command.


npm install mysql


So this command will download Node MySQL module in our Node Express JS application after this for make database connection, we have to create database.js file in our root directory, and under this file, we have to define mysql database configuration details which you can seen below and lastly export mysql database connection file.

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;


So after creating this file, we can make database connection in any routes folder file by simply include this file into that server script file which we can seen in next step.

4 - Fetch & Display Data on Web Page using Ajax



In Node JS Ajax CRUD Application tutorial, now we want to show you how to fetch data from MySQL table and display on web page in HTML table format by using Ajax in Node JS Express Application.

So, first we need to create sample_data.js file in routes directory. Under this file we will write server side script for perform CRUD database operation. Under this file we will create routes for load web page in the browser and then after create another routes for handle Ajax post data request.

routes/sample_data.js

var express = require('express');

var router = express.Router();

var database = require('../database');

router.get("/", function(request, response, next){

	response.render('sample_data', {title : 'Node JS Ajax CRUD Application'});

});

router.post("/action", function(request, response, next){

	var action = request.body.action;

	if(action == 'fetch')
	{
		var query = "SELECT * FROM sample_data ORDER BY id DESC";

		database.query(query, function(error, data){

			response.json({
				data:data
			});

		});
	}

});

module.exports = router;


After this, we have define routes in app.js file which you can seen below.

app.js

var createError = require('http-errors');
var express = require('express');
var path = require('path');
var cookieParser = require('cookie-parser');
var logger = require('morgan');

var indexRouter = require('./routes/index');
var usersRouter = require('./routes/users');

var sampleDataRouter = require('./routes/sample_data');

var app = express();

// view engine setup
app.set('views', path.join(__dirname, 'views'));
app.set('view engine', 'ejs');

app.use(logger('dev'));
app.use(express.json());
app.use(express.urlencoded({ extended: false }));
app.use(cookieParser());
app.use(express.static(path.join(__dirname, 'public')));

app.use('/', indexRouter);
app.use('/users', usersRouter);

app.use('/sample_data', sampleDataRouter);

// catch 404 and forward to error handler
app.use(function(req, res, next) {
  next(createError(404));
});

// error handler
app.use(function(err, req, res, next) {
  // set locals, only providing error in development
  res.locals.message = err.message;
  res.locals.error = req.app.get('env') === 'development' ? err : {};

  // render the error page
  res.status(err.status || 500);
  res.render('error');
});

module.exports = app;



After this, for display HTML output in the browser, we need to create sample_data.ejs file in views directory. In this file, we have to create HTML code and jQuery Ajax Code. Under this step, we have make one JavaScript function which will send Ajax request to Node JS Script for fetch data from MySQL database and it will receive data from server in JSON format which will be display on web page in HTML table format.

views/sample_data.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">

        <link href="https://cdn.datatables.net/1.12.0/css/dataTables.bootstrap5.min.css" rel="stylesheet">

        <script src="https://code.jquery.com/jquery-3.6.0.js" integrity="sha256-H+K7U5CnXl1h5ywQfKtSj8PCmoN9aaq30gDh27Xc0jk=" crossorigin="anonymous"></script>
        <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.0.2/dist/js/bootstrap.bundle.min.js" integrity="sha384-MrcW6ZMFYlzcLA8Nl+NtUVF0sA7MsXsP1UyJoMp4YLEuNSfAP+JcXn/tWtIaxVXM" crossorigin="anonymous"></script>

        <script src="https://cdn.datatables.net/1.12.0/js/jquery.dataTables.min.js"></script>

        <script src="https://cdn.datatables.net/1.12.0/js/dataTables.bootstrap5.min.js"></script>

        <title></title>
    </head>
    <body>

        <div class="container">
            <h1 class="mt-4 mb-4 text-center text-primary"><b><%= title %></b></h1>

            <span id="message"></span>
            <div class="card">
                <div class="card-header">
                    <div class="row">
                        <div class="col col-sm-9">Sample Data</div>
                        <div class="col col-sm-3">
                        </div>
                    </div>
                </div>
                <div class="card-body">
                    <div class="table-responsive">
                        <table class="table table-striped table-bordered" id="sample_data">
                            <thead>
                                <tr>
                                    <th>First Name</th>
                                    <th>Last Name</th>
                                    <th>Gender</th>
                                    <th>Age</th>
                                    <th>Action</th>
                                </tr>
                            </thead>
                            <tbody></tbody>
                        </table>
                    </div>
                </div>
            </div>
        </div>
    </body>
</html>



<script>

$(document).ready(function(){

    load_data();

    function load_data()
    {
        $.ajax({
            url:"http://localhost:3000/sample_data/action",
            method:"POST",
            data:{action:'fetch'},
            dataType : "JSON",
            success:function(data)
            {
                var html = '';

                if(data.data.length > 0)
                {
                    for(var count = 0; count < data.data.length; count++)
                    {
                        html += `
                        <tr>
                            <td>`+data.data[count].first_name+`</td>
                            <td>`+data.data[count].last_name+`</td>
                            <td>`+data.data[count].gender+`</td>
                            <td>`+data.data[count].age+`</td>
                            <td></td>
                        </tr>
                        `;
                    }
                }

                $('#sample_data tbody').html(html);
            }
        });
    }

});

</script>


4 - Insert Data in Node JS using Ajax



In next step we have to proceed for how to insert data into MySQL table in Node JS using Ajax. So here for Insert data into MySQL table in Node js, we have use Ajax, Bootstrap 5 Modal and Express JS Application.

First we will make One HTML button, so when we have click on that button then Bootstrap Modal must be pop up on the web page, with HTML Form.

After this, we will Create Bootstrap Modal and under that Modal we will create HTML Form for get data from user. This Modal will be pop up when we have click on Add button by using jQuery.

Next we will write jQuery script for pop up Bootstrap modal on the web page.

And after this, we will write jQuery script for submit form data, so when form has been submitted then Ajax response will be send to Node JS script for Insert data into MySQL table and this Ajax request will receive response from Node js server in JSON format.

views/sample_data.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">

        <link href="https://cdn.datatables.net/1.12.0/css/dataTables.bootstrap5.min.css" rel="stylesheet">

        <script src="https://code.jquery.com/jquery-3.6.0.js" integrity="sha256-H+K7U5CnXl1h5ywQfKtSj8PCmoN9aaq30gDh27Xc0jk=" crossorigin="anonymous"></script>
        <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.0.2/dist/js/bootstrap.bundle.min.js" integrity="sha384-MrcW6ZMFYlzcLA8Nl+NtUVF0sA7MsXsP1UyJoMp4YLEuNSfAP+JcXn/tWtIaxVXM" crossorigin="anonymous"></script>

        <script src="https://cdn.datatables.net/1.12.0/js/jquery.dataTables.min.js"></script>

        <script src="https://cdn.datatables.net/1.12.0/js/dataTables.bootstrap5.min.js"></script>

        <title></title>
    </head>
    <body>

        <div class="container">
            <h1 class="mt-4 mb-4 text-center text-primary"><b><%= title %></b></h1>

            <span id="message"></span>
            <div class="card">
                <div class="card-header">
                    <div class="row">
                        <div class="col col-sm-9">Sample Data</div>
                        <div class="col col-sm-3">
                            <button type="button" id="add_data" class="btn btn-success btn-sm float-end">Add</button>
                        </div>
                    </div>
                </div>
                <div class="card-body">
                    <div class="table-responsive">
                        <table class="table table-striped table-bordered" id="sample_data">
                            <thead>
                                <tr>
                                    <th>First Name</th>
                                    <th>Last Name</th>
                                    <th>Gender</th>
                                    <th>Age</th>
                                    <th>Action</th>
                                </tr>
                            </thead>
                            <tbody></tbody>
                        </table>
                    </div>
                </div>
            </div>
        </div>
    </body>
</html>

<div class="modal" tabindex="-1" id="action_modal">
    <div class="modal-dialog">
        <div class="modal-content">
            <form method="post" id="sample_form">
                <div class="modal-header" id="dynamic_modal_title">
                    <h5 class="modal-title"></h5>
                    <button type="button" class="btn-close" data-bs-dismiss="modal" aria-label="Close"></button>
                </div>
                <div class="modal-body">
                    <div class="mb-3">
                        <label class="form-label">First Name</label>
                        <input type="text" name="first_name" id="first_name" class="form-control" />
                    </div>
                    <div class="mb-3">
                        <label class="form-label">Last Name</label>
                        <input type="text" name="last_name" id="last_name" class="form-control" />
                    </div>
                    <div class="mb-3">
                        <label class="form-label">Gender</label>
                        <select name="gender" id="gender" class="form-control">
                            <option value="Male">Male</option>
                            <option value="Female">Female</option>
                        </select>
                    </div>
                    <div class="mb-3">
                        <label class="form-label">Age</label>
                        <input type="nummber" name="age" id="age" class="form-control" />
                    </div>
                </div>
                <div class="modal-footer">
                    <input type="hidden" name="id" id="id" />
                    <input type="hidden" name="action" id="action" value="Add" />
                    <button type="button" class="btn btn-secondary" data-bs-dismiss="modal">Close</button>
                    <button type="submit" class="btn btn-primary" id="action_button">Add</button>
                </div>
            </form>
        </div>
    </div>
</div>

<script>

$(document).ready(function(){

    load_data();

    function load_data()
    {
        $.ajax({
            url:"http://localhost:3000/sample_data/action",
            method:"POST",
            data:{action:'fetch'},
            dataType : "JSON",
            success:function(data)
            {
                var html = '';

                if(data.data.length > 0)
                {
                    for(var count = 0; count < data.data.length; count++)
                    {
                        html += `
                        <tr>
                            <td>`+data.data[count].first_name+`</td>
                            <td>`+data.data[count].last_name+`</td>
                            <td>`+data.data[count].gender+`</td>
                            <td>`+data.data[count].age+`</td>
                            <td></td>
                        </tr>
                        `;
                    }
                }

                $('#sample_data tbody').html(html);
            }
        });
    }

    $('#add_data').click(function(){

        $('#dynamic_modal_title').text('Add Data');

        $('#sample_form')[0].reset();

        $('#action').val('Add');

        $('#action_button').text('Add');

        $('#action_modal').modal('show');

    });

    $('#sample_form').on('submit', function(event){

        event.preventDefault();

        $.ajax({
            url:"http://localhost:3000/sample_data/action",
            method:"POST",
            data:$('#sample_form').serialize(),
            dataType:"JSON",
            beforeSend:function(){
                $('#action_button').attr('disabled', 'disabled');
            },
            success:function(data)
            {
                $('#action_button').attr('disabled', false);

                $('#message').html('<div class="alert alert-success">'+data.message+'</div>');

                $('#action_modal').modal('hide');

                load_data();

                setTimeout(function(){
                    $('#message').html('');
                }, 5000);
            }
        });

    });
});

</script>


routes/sample_data.js

var express = require('express');

var router = express.Router();

var database = require('../database');

router.get("/", function(request, response, next){

	response.render('sample_data', {title : 'Node JS Ajax CRUD Application'});

});

router.post("/action", function(request, response, next){

	var action = request.body.action;

	if(action == 'fetch')
	{
		var query = "SELECT * FROM sample_data ORDER BY id DESC";

		database.query(query, function(error, data){

			response.json({
				data:data
			});

		});
	}

	if(action == 'Add')
	{
		var first_name = request.body.first_name;

		var last_name = request.body.last_name;

		var age = request.body.age;

		var gender = request.body.gender;

		var query = `
		INSERT INTO sample_data 
		(first_name, last_name, age, gender) 
		VALUES ("${first_name}", "${last_name}", "${age}", "${gender}")
		`;

		database.query(query, function(error, data){

			response.json({
				message : 'Data Added'
			});

		});
	}

});

module.exports = router;


5 - Update Data in Node JS using Ajax



In the Web Development, if we have develop any web application and in that Web based online application, for manage data, CRUD Operation has been used for Insert Update Delete Data from Database. So in last step we have show you How to Insert data, so after inserting of data, so suppose if we want to make any change in existing data then Update or Edit data crud operation has been used.

So now for Update existing data, first we will create edit button in each of data. So when dynamic edit button has been created in each of data. Now under this Node JS Ajax CRUD Application, we will write JavaScript code on edit button event. So when we have click on edit button then it will fetch single row of data from MySQL database and then after it will be display on Bootstrap Modal with fill form data.

After this, we have write JavaScript code for submit form data and which will send form data to Node JS Server Script using Ajax and at Node JS Server side script it will update or edit existing MySQL data based on primary key which has been stored under form hidden field.

views/sample_data.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">

        <link href="https://cdn.datatables.net/1.12.0/css/dataTables.bootstrap5.min.css" rel="stylesheet">

        <script src="https://code.jquery.com/jquery-3.6.0.js" integrity="sha256-H+K7U5CnXl1h5ywQfKtSj8PCmoN9aaq30gDh27Xc0jk=" crossorigin="anonymous"></script>
        <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.0.2/dist/js/bootstrap.bundle.min.js" integrity="sha384-MrcW6ZMFYlzcLA8Nl+NtUVF0sA7MsXsP1UyJoMp4YLEuNSfAP+JcXn/tWtIaxVXM" crossorigin="anonymous"></script>

        <script src="https://cdn.datatables.net/1.12.0/js/jquery.dataTables.min.js"></script>

        <script src="https://cdn.datatables.net/1.12.0/js/dataTables.bootstrap5.min.js"></script>

        <title></title>
    </head>
    <body>

        <div class="container">
            <h1 class="mt-4 mb-4 text-center text-primary"><b><%= title %></b></h1>

            <span id="message"></span>
            <div class="card">
                <div class="card-header">
                    <div class="row">
                        <div class="col col-sm-9">Sample Data</div>
                        <div class="col col-sm-3">
                            <button type="button" id="add_data" class="btn btn-success btn-sm float-end">Add</button>
                        </div>
                    </div>
                </div>
                <div class="card-body">
                    <div class="table-responsive">
                        <table class="table table-striped table-bordered" id="sample_data">
                            <thead>
                                <tr>
                                    <th>First Name</th>
                                    <th>Last Name</th>
                                    <th>Gender</th>
                                    <th>Age</th>
                                    <th>Action</th>
                                </tr>
                            </thead>
                            <tbody></tbody>
                        </table>
                    </div>
                </div>
            </div>
        </div>
    </body>
</html>

<div class="modal" tabindex="-1" id="action_modal">
    <div class="modal-dialog">
        <div class="modal-content">
            <form method="post" id="sample_form">
                <div class="modal-header" id="dynamic_modal_title">
                    <h5 class="modal-title"></h5>
                    <button type="button" class="btn-close" data-bs-dismiss="modal" aria-label="Close"></button>
                </div>
                <div class="modal-body">
                    <div class="mb-3">
                        <label class="form-label">First Name</label>
                        <input type="text" name="first_name" id="first_name" class="form-control" />
                    </div>
                    <div class="mb-3">
                        <label class="form-label">Last Name</label>
                        <input type="text" name="last_name" id="last_name" class="form-control" />
                    </div>
                    <div class="mb-3">
                        <label class="form-label">Gender</label>
                        <select name="gender" id="gender" class="form-control">
                            <option value="Male">Male</option>
                            <option value="Female">Female</option>
                        </select>
                    </div>
                    <div class="mb-3">
                        <label class="form-label">Age</label>
                        <input type="nummber" name="age" id="age" class="form-control" />
                    </div>
                </div>
                <div class="modal-footer">
                    <input type="hidden" name="id" id="id" />
                    <input type="hidden" name="action" id="action" value="Add" />
                    <button type="button" class="btn btn-secondary" data-bs-dismiss="modal">Close</button>
                    <button type="submit" class="btn btn-primary" id="action_button">Add</button>
                </div>
            </form>
        </div>
    </div>
</div>

<script>

$(document).ready(function(){

    load_data();

    function load_data()
    {
        $.ajax({
            url:"http://localhost:3000/sample_data/action",
            method:"POST",
            data:{action:'fetch'},
            dataType : "JSON",
            success:function(data)
            {
                var html = '';

                if(data.data.length > 0)
                {
                    for(var count = 0; count < data.data.length; count++)
                    {
                        html += `
                        <tr>
                            <td>`+data.data[count].first_name+`</td>
                            <td>`+data.data[count].last_name+`</td>
                            <td>`+data.data[count].gender+`</td>
                            <td>`+data.data[count].age+`</td>
                            <td><button type="button" class="btn btn-warning btn-sm edit" data-id="`+data.data[count].id+`">Edit</button></td>
                        </tr>
                        `;
                    }
                }

                $('#sample_data tbody').html(html);
            }
        });
    }

    $('#add_data').click(function(){

        $('#dynamic_modal_title').text('Add Data');

        $('#sample_form')[0].reset();

        $('#action').val('Add');

        $('#action_button').text('Add');

        $('#action_modal').modal('show');

    });

    $('#sample_form').on('submit', function(event){

        event.preventDefault();

        $.ajax({
            url:"http://localhost:3000/sample_data/action",
            method:"POST",
            data:$('#sample_form').serialize(),
            dataType:"JSON",
            beforeSend:function(){
                $('#action_button').attr('disabled', 'disabled');
            },
            success:function(data)
            {
                $('#action_button').attr('disabled', false);

                $('#message').html('<div class="alert alert-success">'+data.message+'</div>');

                $('#action_modal').modal('hide');

                load_data();

                setTimeout(function(){
                    $('#message').html('');
                }, 5000);
            }
        });

    });

    $(document).on('click', '.edit', function(){

        var id = $(this).data('id');

        $('#dynamic_modal_title').text('Edit Data');

        $('#action').val('Edit');

        $('#action_button').text('Edit');

        $('#action_modal').modal('show');

        $.ajax({
            url:"http://localhost:3000/sample_data/action",
            method:"POST",
            data:{id:id, action:'fetch_single'},
            dataType:"JSON",
            success:function(data)
            {
                $('#first_name').val(data.first_name);
                $('#last_name').val(data.last_name);
                $('#gender').val(data.gender);
                $('#age').val(data.age);
                $('#id').val(data.id);
            }
        });

    });
});

</script>


routes/sample_data.js

var express = require('express');

var router = express.Router();

var database = require('../database');

router.get("/", function(request, response, next){

	response.render('sample_data', {title : 'Node JS Ajax CRUD Application'});

});

router.post("/action", function(request, response, next){

	var action = request.body.action;

	if(action == 'fetch')
	{
		var query = "SELECT * FROM sample_data ORDER BY id DESC";

		database.query(query, function(error, data){

			response.json({
				data:data
			});

		});
	}

	if(action == 'Add')
	{
		var first_name = request.body.first_name;

		var last_name = request.body.last_name;

		var age = request.body.age;

		var gender = request.body.gender;

		var query = `
		INSERT INTO sample_data 
		(first_name, last_name, age, gender) 
		VALUES ("${first_name}", "${last_name}", "${age}", "${gender}")
		`;

		database.query(query, function(error, data){

			response.json({
				message : 'Data Added'
			});

		});
	}

	if(action == 'fetch_single')
	{
		var id = request.body.id;

		var query = `SELECT * FROM sample_data WHERE id = "${id}"`;

		database.query(query, function(error, data){

			response.json(data[0]);

		});
	}

	if(action == 'Edit')
	{
		var id = request.body.id;

		var first_name = request.body.first_name;

		var last_name = request.body.last_name;

		var gender = request.body.gender;

		var age = request.body.age;

		var query = `
		UPDATE sample_data 
		SET first_name = "${first_name}", 
		last_name = "${last_name}", 
		age = "${age}", 
		gender = "${gender}" 
		WHERE id = "${id}"
		`;

		database.query(query, function(error, data){
			response.json({
				message : 'Data Edited'
			});
		});
	}

});

module.exports = router;


6 - Delete Data in Node JS using Ajax



Here we will show you how can we perform delete data operation in Node.js Express Application with Ajax. So data will be deleted from MySQL Table without refresh of web page.

So for delete data operation, first we want to create dynamic delete button in each row of data.

After this, we write JavaScript code for capture delete button click event, so when we have click on delete button, the Ajax request will be send to Node JS Server Script for delete data operation and at Node.js Server side script it wil delete data from MySQL table by primary id key in Node js and it will send back response to Ajax request in JSON format and then after it will display success message on the web page and data will be deleted without refresh of web page.

views/sample_data.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">

        <link href="https://cdn.datatables.net/1.12.0/css/dataTables.bootstrap5.min.css" rel="stylesheet">

        <script src="https://code.jquery.com/jquery-3.6.0.js" integrity="sha256-H+K7U5CnXl1h5ywQfKtSj8PCmoN9aaq30gDh27Xc0jk=" crossorigin="anonymous"></script>
        <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.0.2/dist/js/bootstrap.bundle.min.js" integrity="sha384-MrcW6ZMFYlzcLA8Nl+NtUVF0sA7MsXsP1UyJoMp4YLEuNSfAP+JcXn/tWtIaxVXM" crossorigin="anonymous"></script>

        <script src="https://cdn.datatables.net/1.12.0/js/jquery.dataTables.min.js"></script>

        <script src="https://cdn.datatables.net/1.12.0/js/dataTables.bootstrap5.min.js"></script>

        <title></title>
    </head>
    <body>

        <div class="container">
            <h1 class="mt-4 mb-4 text-center text-primary"><b><%= title %></b></h1>

            <span id="message"></span>
            <div class="card">
                <div class="card-header">
                    <div class="row">
                        <div class="col col-sm-9">Sample Data</div>
                        <div class="col col-sm-3">
                            <button type="button" id="add_data" class="btn btn-success btn-sm float-end">Add</button>
                        </div>
                    </div>
                </div>
                <div class="card-body">
                    <div class="table-responsive">
                        <table class="table table-striped table-bordered" id="sample_data">
                            <thead>
                                <tr>
                                    <th>First Name</th>
                                    <th>Last Name</th>
                                    <th>Gender</th>
                                    <th>Age</th>
                                    <th>Action</th>
                                </tr>
                            </thead>
                            <tbody></tbody>
                        </table>
                    </div>
                </div>
            </div>
        </div>
    </body>
</html>

<div class="modal" tabindex="-1" id="action_modal">
    <div class="modal-dialog">
        <div class="modal-content">
            <form method="post" id="sample_form">
                <div class="modal-header" id="dynamic_modal_title">
                    <h5 class="modal-title"></h5>
                    <button type="button" class="btn-close" data-bs-dismiss="modal" aria-label="Close"></button>
                </div>
                <div class="modal-body">
                    <div class="mb-3">
                        <label class="form-label">First Name</label>
                        <input type="text" name="first_name" id="first_name" class="form-control" />
                    </div>
                    <div class="mb-3">
                        <label class="form-label">Last Name</label>
                        <input type="text" name="last_name" id="last_name" class="form-control" />
                    </div>
                    <div class="mb-3">
                        <label class="form-label">Gender</label>
                        <select name="gender" id="gender" class="form-control">
                            <option value="Male">Male</option>
                            <option value="Female">Female</option>
                        </select>
                    </div>
                    <div class="mb-3">
                        <label class="form-label">Age</label>
                        <input type="nummber" name="age" id="age" class="form-control" />
                    </div>
                </div>
                <div class="modal-footer">
                    <input type="hidden" name="id" id="id" />
                    <input type="hidden" name="action" id="action" value="Add" />
                    <button type="button" class="btn btn-secondary" data-bs-dismiss="modal">Close</button>
                    <button type="submit" class="btn btn-primary" id="action_button">Add</button>
                </div>
            </form>
        </div>
    </div>
</div>

<script>

$(document).ready(function(){

    load_data();

    function load_data()
    {
        $.ajax({
            url:"http://localhost:3000/sample_data/action",
            method:"POST",
            data:{action:'fetch'},
            dataType : "JSON",
            success:function(data)
            {
                var html = '';

                if(data.data.length > 0)
                {
                    for(var count = 0; count < data.data.length; count++)
                    {
                        html += `
                        <tr>
                            <td>`+data.data[count].first_name+`</td>
                            <td>`+data.data[count].last_name+`</td>
                            <td>`+data.data[count].gender+`</td>
                            <td>`+data.data[count].age+`</td>
                            <td><button type="button" class="btn btn-warning btn-sm edit" data-id="`+data.data[count].id+`">Edit</button>&nbsp;<button type="button" class="btn btn-danger btn-sm delete" data-id="`+data.data[count].id+`">Delete</button></td>
                        </tr>
                        `;
                    }
                }

                $('#sample_data tbody').html(html);
            }
        });
    }

    $('#add_data').click(function(){

        $('#dynamic_modal_title').text('Add Data');

        $('#sample_form')[0].reset();

        $('#action').val('Add');

        $('#action_button').text('Add');

        $('#action_modal').modal('show');

    });

    $('#sample_form').on('submit', function(event){

        event.preventDefault();

        $.ajax({
            url:"http://localhost:3000/sample_data/action",
            method:"POST",
            data:$('#sample_form').serialize(),
            dataType:"JSON",
            beforeSend:function(){
                $('#action_button').attr('disabled', 'disabled');
            },
            success:function(data)
            {
                $('#action_button').attr('disabled', false);

                $('#message').html('<div class="alert alert-success">'+data.message+'</div>');

                $('#action_modal').modal('hide');

                load_data();

                setTimeout(function(){
                    $('#message').html('');
                }, 5000);
            }
        });

    });

    $(document).on('click', '.edit', function(){

        var id = $(this).data('id');

        $('#dynamic_modal_title').text('Edit Data');

        $('#action').val('Edit');

        $('#action_button').text('Edit');

        $('#action_modal').modal('show');

        $.ajax({
            url:"http://localhost:3000/sample_data/action",
            method:"POST",
            data:{id:id, action:'fetch_single'},
            dataType:"JSON",
            success:function(data)
            {
                $('#first_name').val(data.first_name);
                $('#last_name').val(data.last_name);
                $('#gender').val(data.gender);
                $('#age').val(data.age);
                $('#id').val(data.id);
            }
        });

    });

    $(document).on('click', '.delete', function(){

        var id = $(this).data('id');

        if(confirm("Are you sure you want to delete this data?"))
        {
            $.ajax({
                url:"http://localhost:3000/sample_data/action",
                method:"POST",
                data:{action:'delete', id:id},
                dataType:"JSON",
                success:function(data)
                {
                    $('#message').html('<div class="alert alert-success">'+data.message+'</div>');
                    load_data();
                    setTimeout(function(){
                        $('#message').html('');
                    }, 5000);
                }
            });
        }

    });
});

</script>


routes/sample_data.js

var express = require('express');

var router = express.Router();

var database = require('../database');

router.get("/", function(request, response, next){

	response.render('sample_data', {title : 'Node JS Ajax CRUD Application'});

});

router.post("/action", function(request, response, next){

	var action = request.body.action;

	if(action == 'fetch')
	{
		var query = "SELECT * FROM sample_data ORDER BY id DESC";

		database.query(query, function(error, data){

			response.json({
				data:data
			});

		});
	}

	if(action == 'Add')
	{
		var first_name = request.body.first_name;

		var last_name = request.body.last_name;

		var age = request.body.age;

		var gender = request.body.gender;

		var query = `
		INSERT INTO sample_data 
		(first_name, last_name, age, gender) 
		VALUES ("${first_name}", "${last_name}", "${age}", "${gender}")
		`;

		database.query(query, function(error, data){

			response.json({
				message : 'Data Added'
			});

		});
	}

	if(action == 'fetch_single')
	{
		var id = request.body.id;

		var query = `SELECT * FROM sample_data WHERE id = "${id}"`;

		database.query(query, function(error, data){

			response.json(data[0]);

		});
	}

	if(action == 'Edit')
	{
		var id = request.body.id;

		var first_name = request.body.first_name;

		var last_name = request.body.last_name;

		var gender = request.body.gender;

		var age = request.body.age;

		var query = `
		UPDATE sample_data 
		SET first_name = "${first_name}", 
		last_name = "${last_name}", 
		age = "${age}", 
		gender = "${gender}" 
		WHERE id = "${id}"
		`;

		database.query(query, function(error, data){
			response.json({
				message : 'Data Edited'
			});
		});
	}

	if(action == 'delete')
	{
		var id = request.body.id;

		var query = `DELETE FROM sample_data WHERE id = "${id}"`;

		database.query(query, function(error, data){

			response.json({
				message : 'Data Deleted'
			});

		});
	}

});

module.exports = router;